Thread: do I need a rollback() after commit that fails?
I'm running Pg 8.3.7 on FreeBSD 7.2. I have some code in Perl that does a bunch of inserts and updates with all constraints deferred. On occasion, one of the FK's gets violated and the transaction commit fails. I trap this with code like this: unless ($dbh->commit()) { warn "commit failure ".$dbh->errstr; $dbh->rollback(); return 'failed'; } The DBI is telling me that the rollback() is useless with AutoCommit is on (which it is). I did some direct testing with psql and it seems that this is not Perl DBI specific behavior. So, it seems that if commit fails, I don't need to issue a rollback. Is this portable to other databases, or is this Postgres specific? I also note that if I do not defer the constraints, and issue the commit even after the INSERT reports error, that the statement result printed by commit is instead "ROLLBACK". If I have constraints deferred, the commit output is just the "ERROR" statement, without any indication of ROLLBACK.
On Tue, Sep 29, 2009 at 1:26 PM, Vick Khera <vivek@khera.org> wrote: > I'm running Pg 8.3.7 on FreeBSD 7.2. > > I have some code in Perl that does a bunch of inserts and updates with > all constraints deferred. On occasion, one of the FK's gets violated > and the transaction commit fails. > > I trap this with code like this: > > unless ($dbh->commit()) { > warn "commit failure ".$dbh->errstr; > $dbh->rollback(); > return 'failed'; > } > > The DBI is telling me that the rollback() is useless with AutoCommit > is on (which it is). > > I did some direct testing with psql and it seems that this is not Perl > DBI specific behavior. > > So, it seems that if commit fails, I don't need to issue a rollback. > Is this portable to other databases, or is this Postgres specific? > > I also note that if I do not defer the constraints, and issue the > commit even after the INSERT reports error, that the statement result > printed by commit is instead "ROLLBACK". If I have constraints > deferred, the commit output is just the "ERROR" statement, without any > indication of ROLLBACK. > If a query within a transaction has errors, COMMIT at the end of the transaction is automatically turned into a ROLLBACK.
Vick Khera wrote: > I'm running Pg 8.3.7 on FreeBSD 7.2. > > I have some code in Perl that does a bunch of inserts and updates with > all constraints deferred. On occasion, one of the FK's gets violated > and the transaction commit fails. > > I trap this with code like this: > > unless ($dbh->commit()) { > warn "commit failure ".$dbh->errstr; > $dbh->rollback(); > return 'failed'; > } > > The DBI is telling me that the rollback() is useless with AutoCommit > is on (which it is). > Unless I'm mistaken, if AutoCommit is enabled, then each statement will be commit for you. The commit() and the rollback() are both useless. -Andy
On Tue, Sep 29, 2009 at 3:56 PM, Andy Colson <andy@squeakycode.net> wrote: > Unless I'm mistaken, if AutoCommit is enabled, then each statement will be > commit for you. The commit() and the rollback() are both useless. > To clarify, the DBI driver turns off AutoCommit in postgres when begin_work() is called. It does not however disable its internal AutoCommit flag. The question still stands: if the COMMIT fails, ROLLBACK is not required in Postgres. Is this portable to other databases?
On 30 Sep 2009, at 4:01, Vick Khera wrote: > The question still stands: if the COMMIT fails, ROLLBACK is not > required in Postgres. Is this portable to other databases? I don't think so. I recall messages on this list claiming that some databases (MS SQL, MySQL if memory serves me) commit the queries up to the failed query anyway if you issue a COMMIT (which is just wrong!), so the commit succeeds and there's nothing to rollback after that. Some searching should turn up those messages, if I recall correctly the issue at hand was that people expected that behaviour in Postgres too. But I don't know what Perl DBI does internally when issuing $dbh- >commit(), maybe it's taking such things into account already. Alban Hertroys -- Screwing up is the best way to attach something to the ceiling. !DSPAM:737,4ac356da11681178911724!