Thread: implicit rollback?

implicit rollback?

From
Carol Walter
Date:
Hello,

I have a program (PHP) that is executing a number of SQL commands as a
single transaction.  At the beginning of the transaction, I have a
"BEGIN" and at the end I have a "COMMIT".  If one of the transactions
fails, do I have to explicitly issue a "ROLLBACK" command, or will
postgres do this automatically because one of the commands fails?

Carol

Re: implicit rollback?

From
"Mohlomi Moloi"
Date:
I think you must explicitly issue a "ROLLBACK" command, reason being I
once created multiple exports to different tables for one form on to
postgres DB and I noticed when I had technical glitch prior ALL exports
being executed some of my records would be missing in my other tables
whilst other tables would populated.


-----Original Message-----
From: Carol Walter [mailto:walterc@indiana.edu]
Sent: 17 June 2009 14:27
To: pgsql-novice@postgresql.org
Subject: [NOVICE] implicit rollback?

Hello,

I have a program (PHP) that is executing a number of SQL commands as a
single transaction.  At the beginning of the transaction, I have a
"BEGIN" and at the end I have a "COMMIT".  If one of the transactions
fails, do I have to explicitly issue a "ROLLBACK" command, or will
postgres do this automatically because one of the commands fails?

Carol

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



Re: implicit rollback?

From
John DeSoi
Date:
On Jun 17, 2009, at 8:26 AM, Carol Walter wrote:

> I have a program (PHP) that is executing a number of SQL commands as
> a single transaction.  At the beginning of the transaction, I have a
> "BEGIN" and at the end I have a "COMMIT".  If one of the
> transactions fails, do I have to explicitly issue a "ROLLBACK"
> command, or will postgres do this automatically because one of the
> commands fails?

Postgres does not automatically issue a "ROLLBACK" command. But you
can use "END" instead of "COMMIT". This will commit the transaction if
there are no errors, otherwise it will rollback:

db=# begin;
BEGIN
db=# select 1/0;
ERROR:  division by zero
db=# end;
ROLLBACK



Interesting, though, I just noticed that this behavior is not
discussed in the documentation.


http://www.postgresql.org/docs/8.3/interactive/sql-end.html





John DeSoi, Ph.D.