Re: transactions from PHP - double COMMIT required?

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