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!