Re: transactions from PHP - double COMMIT required?

2007/3/5, 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.

I can see the ROLLBACK command displayed and it seems to do it automagically.

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

Here I can see that transaction is aborted and at the end a ROLLBACK
is displayed, so it is also automagically issued

It looks like there is no difference between these two methods that
you suggested.

Anyway I came to the solution like this:
I send pg_query with "BEGIN; insert 1;insert 2; etc...;" -- here there
is no commit nor rollback at the end.
Now I check the result of this pg_query.
If it is OK then I send single query "COMMIT;" and the case is closed
(it means everything went OK).
In case the result of pg_query gives FALSE I send another pg_query "ROLLBACK;".
I checked the logs and the transaction ID is still the same when I
send the other query with "rollback;" or "commit;" after checking the
result.

This seems to be the proper way of handling transaction in PHP without
PDO. It has to be done manually.