Opened 17 years ago

Closed 16 years ago

Last modified 16 years ago

#5043 closed (duplicate)

Postgresql transaction error handling is different

Reported by: Nis Jørgensen <nis@…> Owned by: nobody
Component: Database layer (models, ORM) Version: dev
Severity: Keywords:
Cc: metajack@…, richard.davies@… Triage Stage: Design decision needed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

(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)

Change History (10)

comment:1 by mir@…, 17 years ago

Resolution: duplicate
Status: newclosed

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.

in reply to:  1 comment:2 by anonymous, 17 years ago

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).

comment:3 by anonymous, 17 years ago

Cc: metajack@… added

comment:4 by Nis Jørgensen <nis@…>, 17 years ago

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

comment:5 by Nis Jørgensen <nis@…>, 17 years ago

Resolution: duplicate
Status: closedreopened

comment:6 by mir@…, 17 years ago

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

comment:7 by Michael Radziej, 17 years ago

Triage Stage: UnreviewedDesign decision needed

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

comment:8 by Jacob, 16 years ago

Resolution: duplicate
Status: reopenedclosed

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.

comment:9 by Richard Davies <richard.davies@…>, 16 years ago

Cc: richard.davies@… added

comment:10 by Richard Davies <richard.davies@…>, 16 years ago

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

(this ticket was incorrectly closed before that)

Note: See TracTickets for help on using tickets.
Back to Top