Thread: 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
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
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.