Postgresql transaction error handling is different

[Nis Jørgensen <nis@…>]

(Discussion is happening here): ​http://groups.google.com/group/django-users/browse_thread/thread/8004a40b3c607d08/7b7ed19d276033df

Postgresql does error handling inside transactions differently than mySQL and sqlite. When using the latter two, you can do this inside a transaction:

class MyModel(Model):
    uniquefield = CharField(max_length=200,unique=True)

MyModel.objects.create(uniquefield='Some non-unique value')
try:
    MyModel.objects.create(uniquefield='Some non-unique value')
except:
    MyModel.objects.create(uniquefield='A unique value')

while in postgres the last query will fail as well.

Note that postgresql is by default inside a transaction - ticket #3460 is aimed at fixing this.

I believe the solution is to wrap the "execute" method to generate savepoints when inside a transaction. Something like the following (untested) pseudocode:

def myexecute(self, *args,**kwargs):
    if self.is_in_transaction:
        self.create_save_point(identifier)  
        try: 
            self.execute(*args,**kwargs)
        except DatabaseError,e:
            self.rollback(identifier)
            raise
    else:
        self.execute(*args,**kwargs)

[mir@…]

The problem you see is not directly caused by the isolation level, but by the fact that postgresql is not able to continue a transaction once a statement has failed. I consider ticket #3460 the wrong solution, since it basically turns off all transaction support.

Your second block is the better solution.

I close this ticket as a duplicate, though.

[anonymous]

Replying to mir@noris.de:

The problem you see is not directly caused by the isolation level, but by the fact that postgresql is not able to continue a transaction once a statement has failed. I consider ticket #3460 the wrong solution, since it basically turns off all transaction support.

Your second block is the better solution.

I close this ticket as a duplicate, though.

#3460 turns off transaction by default, which is correct. You still get transaction if you explicitly use them (or perhaps I am confusing this with my Twisted patches since they had the same problem). You want singleton queries not inside BEGIN/END/COMMIT pairs, but you want multiple INSERT or DELETE queries inside the pairs. Before the patch you get BEGIN/END/COMMIT pairs on every query no matter what, which is quite inefficient if you have lots of simple selects (which is 80% of what most django apps do).

[Nis Jørgensen <nis@…>]

I disagree that this is a duplicate of #3460. This bug is about the behavior of django when in a transaction. 3460 is about whether django is, bu default, in a transaction. So I am reopening the ticket.

I do not have an opinion on whether 3460 should be fixed or not - I am not really sure about the implications of it.

Nis

[mir@…]

Nils: please start a thread on django-developers, so that we can settle this.

[Michael Radziej]

Looking at this again, I think Nis is right. savepoints would probably be nice to cope with this.

[Jacob]

This is indeed an issue of transaction isolation -- Postgres deliberately doesn't allow additional statements after a failed one, and if you want to do so you need different transaction handling. As such, this is a duplicate of #6064.

[Richard Davies <richard.davies@…>]

The savepoints that Nils asked for were eventually implemented in [8314]

(this ticket was incorrectly closed before that)