Opened 8 years ago

Closed 7 years ago

Last modified 6 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: master
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: UI/UX:

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 follow-up: Changed 8 years ago by mir@…

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Resolution set to duplicate
  • Status changed from new to closed

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.

comment:2 in reply to: ↑ 1 Changed 8 years ago by 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).

comment:3 Changed 8 years ago by anonymous

  • Cc metajack@… added

comment:4 Changed 8 years ago by 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

comment:5 Changed 8 years ago by Nis Jørgensen <nis@…>

  • Resolution duplicate deleted
  • Status changed from closed to reopened

comment:6 Changed 8 years ago by mir@…

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

comment:7 Changed 7 years ago by mir

  • Triage Stage changed from Unreviewed to Design decision needed

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

comment:8 Changed 7 years ago by jacob

  • Resolution set to duplicate
  • Status changed from reopened to closed

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 Changed 6 years ago by Richard Davies <richard.davies@…>

  • Cc richard.davies@… added

comment:10 Changed 6 years ago by Richard Davies <richard.davies@…>

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