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.