do I need a rollback() after commit that fails?

[Vick Khera <vivek@xxxxxxxxx>]

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.

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general