From: mikie <mikie.pl@gmail.com>
To: pgsql-php@postgresql.org
Subject: Re: transactions from PHP - double COMMIT required?
Date: Mon, 5 Mar 2007 16:14:59 +0100
Message-ID: <ca35ce500703050714k2e025a42ke45c70ef33b84db@mail.gmail.com> (raw)
In-Reply-To: <45EC13B1.3070601@tiggs.net>
References: <ca35ce500703010306nd06dcfcy1247fb2b4297b0c2@mail.gmail.com>
<ca35ce500703010842v193a90cavdf8052330482ee9a@mail.gmail.com>
<45E70C9A.200@bugs.unl.edu.ar>
<200703011353.07621.xzilla@users.sourceforge.net>
<ca35ce500703011138g2a7e0440w86fb85fd8f639604@mail.gmail.com>
<45E81DC5.6090308@bugs.unl.edu.ar>
<ca35ce500703050130g46db55f1r23e2ef96fd8b18e7@mail.gmail.com>
<45EC13B1.3070601@tiggs.net>
2007/3/5, Charley Tiggs <lists@tiggs.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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-php@postgresql.org
Cc: mikie.pl@gmail.com
Subject: Re: transactions from PHP - double COMMIT required?
In-Reply-To: <ca35ce500703050714k2e025a42ke45c70ef33b84db@mail.gmail.com>
References: <ca35ce500703010306nd06dcfcy1247fb2b4297b0c2@mail.gmail.com> <ca35ce500703010842v193a90cavdf8052330482ee9a@mail.gmail.com> <45E70C9A.200@bugs.unl.edu.ar> <200703011353.07621.xzilla@users.sourceforge.net> <ca35ce500703011138g2a7e0440w86fb85fd8f639604@mail.gmail.com> <45E81DC5.6090308@bugs.unl.edu.ar> <ca35ce500703050130g46db55f1r23e2ef96fd8b18e7@mail.gmail.com> <45EC13B1.3070601@tiggs.net> <ca35ce500703050714k2e025a42ke45c70ef33b84db@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox