Re: transactions from PHP - double COMMIT required?

[Charley Tiggs <lists(at)tiggs(dot)net>]

mikie wrote:
> Perhaps I should ask again: is it my responsibility to check if the
> transaction failed and issue a ROLLBACK command, or will the PG server
> do it automatically?

If it were me, I'd assume that responsibility as a matter of course. 
Several folks here have given you names of abstraction layers that will 
make that simple for you.  By using ADOdb or PearDB or MDB, if the 
transaction fails, it will rollback for you.  If you're not going to use 
one of those abstraction layers, assume that your request is going to 
fail and capture the error and rollback yourself.  At least then, you're 
guaranteed a rollback no matter what you pass to the server.

Try this:

In a text file, enter all of your queries.  Start with "BEGIN;" and end 
with "COMMIT;".  Be sure to include the error that you mentioned at the 
appropriate place.

Login to db using psql command line utility and issue the following command:

\i /path/to/file_with_commands.txt

On my system, when I encounter the error, it does not automatically 
issue a rollback.  I have to issue the rollback manually.

However, if I run the file as follows:

psql mydb myusername -f /path/to/file_with_commands.txt

The file will be processed to the end and a rollback will be issued.  At 
point of error, I start seeing entries about transaction aborted, 
waiting til end of file to rollback.

I think libpq behaves as if you are using the \i switch from within the 
psql shell.

Charley