2007/3/5, Charley Tiggs <lists@xxxxxxxxx>:
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.
