Обсуждение: transactions from PHP - double COMMIT required?
Hi,
I have this problem - after sending a query from PHP script with
BEGIN-COMMIT transaction block that contains an error (e.g. I
intentionally insert wrong data into the field of "date" type) I get
an error (which is OK):ERROR: date/time field value out of range.
Now, when I try to DELETE or perform some other query later in the
same PHP script I receive this error:
ERROR: current transaction is aborted, commands ignored until end of
transaction block
I am 100% sure that I have closed the transaction block with
"COMMIT;". When I do the same in the console (psql) I get something
like:
BEGIN; INSERT INTO table1 (somecolumn) VALUES ('somedata'); INSERTsdf; COMMIT;
I get this reply
BEGIN
INSERT 180610 1
ERROR: syntax error at or near "INSERTsdf" at character 1
ROLLBACK
Everything seems to be logical and works fine. So why this does not
work from PHP? I figured one thing to workaround my problem: I am
sending the query like this:
BEGIN;
(here are few insert queries, one is syntax error);
COMMIT;
Then I check the pg_result of this query in PHP, and if it is FALSE I
have to send another simple query : "COMMIT;" - yes I know this is
second commit already. But it works this way!
It seems like if PHP ignored my COMMIT command from the transaction query.
I also tried sending the transaction query like this:
BEGIN;
some queries;
COMMIT;COMMIT;
but this is not working. I need to send one transaction query, check
if the result is false, then send another COMMIT.
Any ideas where I got something missed?
Regards,
Mike
Hi,
I think you should use ROLLBACK if the query cannot be executed.
one can commit only results of the queries that work.
Try ROLLBACK statement.
Mihail
Quoting mikie <mikie.pl@gmail.com>:
> Hi,
>
> I have this problem - after sending a query from PHP script with
> BEGIN-COMMIT transaction block that contains an error (e.g. I
> intentionally insert wrong data into the field of "date" type) I get
> an error (which is OK):ERROR: date/time field value out of range.
>
> Now, when I try to DELETE or perform some other query later in the
> same PHP script I receive this error:
> ERROR: current transaction is aborted, commands ignored until end of
> transaction block
>
> I am 100% sure that I have closed the transaction block with
> "COMMIT;". When I do the same in the console (psql) I get something
> like:
>
> BEGIN; INSERT INTO table1 (somecolumn) VALUES ('somedata');
> INSERTsdf; COMMIT;
>
> I get this reply
> BEGIN
> INSERT 180610 1
> ERROR: syntax error at or near "INSERTsdf" at character 1
> ROLLBACK
>
> Everything seems to be logical and works fine. So why this does not
> work from PHP? I figured one thing to workaround my problem: I am
> sending the query like this:
> BEGIN;
> (here are few insert queries, one is syntax error);
> COMMIT;
> Then I check the pg_result of this query in PHP, and if it is FALSE I
> have to send another simple query : "COMMIT;" - yes I know this is
> second commit already. But it works this way!
> It seems like if PHP ignored my COMMIT command from the transaction query.
> I also tried sending the transaction query like this:
> BEGIN;
> some queries;
> COMMIT;COMMIT;
> but this is not working. I need to send one transaction query, check
> if the result is false, then send another COMMIT.
>
> Any ideas where I got something missed?
>
> Regards,
> Mike
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
--
Mihail Mihailov, lehtori
Käännöstiede (venäjä)
Kieli- ja käännöstieteiden laitos
33014 Tampereen yliopisto
puh. (03) 3551 6123
sähköposti: mihail.mihailov@uta.fi
2007/3/1, Mihail Mihailov <Mihail.Mihailov@uta.fi>:
The thing is that when I try my transaction from psql console then the command ROLLBACK is being issued by the postgres server itself right after I close my transaction with COMMIT;. According to transaction logic and idea - if something goes wrong then the entire transaction is being cancelled by ROLLBACK - it is the server responsibility to rollback such transaction. Hi,
I think you should use ROLLBACK if the query cannot be executed.
one can commit only results of the queries that work.
Try ROLLBACK statement.
Now, when I try to do some inserts within my transaction I don't know if they will fail or not. Eitherway I am closing my transaction with the COMMIT; command - is that correct way ?
It works logically OK from within psql console (I start with BEGIN and then close my sql commands with commit) but it does not work this way from within PHP script. I believe there is some issue with PHP - I am not sure if PHP processor applies some changes to my query and thus ignores the commit command?
--
Mike
If you are using PHP5 (and the PDO PGSQL) I would suggest you use exceptions to trap the error
try {
$db->beginTransaction();
// other queries here, if one fails, an exception is thrown
$db->commit();
}
catch (Exception $e) {
// do whatever with error
$db->rollback();
}
try {
$db->beginTransaction();
// other queries here, if one fails, an exception is thrown
$db->commit();
}
catch (Exception $e) {
// do whatever with error
$db->rollback();
}
On 3/1/07, mikie < mikie.pl@gmail.com> wrote:
2007/3/1, Mihail Mihailov < Mihail.Mihailov@uta.fi>:The thing is that when I try my transaction from psql console then the command ROLLBACK is being issued by the postgres server itself right after I close my transaction with COMMIT;. According to transaction logic and idea - if something goes wrong then the entire transaction is being cancelled by ROLLBACK - it is the server responsibility to rollback such transaction.Hi,
I think you should use ROLLBACK if the query cannot be executed.
one can commit only results of the queries that work.
Try ROLLBACK statement.
Now, when I try to do some inserts within my transaction I don't know if they will fail or not. Eitherway I am closing my transaction with the COMMIT; command - is that correct way ?
It works logically OK from within psql console (I start with BEGIN and then close my sql commands with commit) but it does not work this way from within PHP script. I believe there is some issue with PHP - I am not sure if PHP processor applies some changes to my query and thus ignores the commit command?
--
Mike
2007/3/1, David Legault <legault.david@gmail.com>:
> If you are using PHP5 (and the PDO PGSQL) I would suggest you use exceptions to trap the error
>
> try {
>
> $db->beginTransaction();
>
> // other queries here, if one fails, an exception is thrown
>
> $db->commit();
> }
> catch (Exception $e) {
>
> // do whatever with error
> $db->rollback();
>
> }
Yes, that is nice way to work with databases, but I am on PHP4 and I
am not using the PDO.
But getting back to my problem - perhaps there is something I
misunderstood: is it the client application responsibility to check if
the transaction failed or succeeded and issue COMMIT or ROLLBACK
accordingly (how do I close the transaction block in that case)?
Or is it the database server that is suppose to check if transaction
succeded and perform the query, or ROLLBACK if anything went wrong?
--
Mike
On Thu, 1 Mar 2007, mikie wrote:
>
> Now, when I try to do some inserts within my transaction I don't know if
> they will fail or not. Eitherway I am closing my transaction with the
> COMMIT; command - is that correct way ?
Yes.
--
21:50:04 up 2 days, 9:07, 0 users, load average: 0.92, 0.37, 0.18
---------------------------------------------------------
Lic. Martín Marqués | SELECT 'mmarques' ||
Centro de Telemática | '@' || 'unl.edu.ar';
Universidad Nacional | DBA, Programador,
del Litoral | Administrador
---------------------------------------------------------
On Thu, 1 Mar 2007, mikie wrote:
>
> Yes, that is nice way to work with databases, but I am on PHP4 and I
> am not using the PDO.
MDB2 has a similar sintaxis.
> But getting back to my problem - perhaps there is something I
> misunderstood: is it the client application responsibility to check if
> the transaction failed or succeeded and issue COMMIT or ROLLBACK
> accordingly (how do I close the transaction block in that case)?
> Or is it the database server that is suppose to check if transaction
> succeded and perform the query, or ROLLBACK if anything went wrong?
PG will rollback all transactions that have an error in some part, as it
can't commit the transaction as a whole.
--
21:50:04 up 2 days, 9:07, 0 users, load average: 0.92, 0.37, 0.18
---------------------------------------------------------
Lic. Martín Marqués | SELECT 'mmarques' ||
Centro de Telemática | '@' || 'unl.edu.ar';
Universidad Nacional | DBA, Programador,
del Litoral | Administrador
---------------------------------------------------------
> > But getting back to my problem - perhaps there is something I > > misunderstood: is it the client application responsibility to check if > > the transaction failed or succeeded and issue COMMIT or ROLLBACK > > accordingly (how do I close the transaction block in that case)? > > Or is it the database server that is suppose to check if transaction > > succeded and perform the query, or ROLLBACK if anything went wrong? > > PG will rollback all transactions that have an error in some part, as it > can't commit the transaction as a whole. OK, so my way of programming seems correct. Then why do I have to send another COMMIT after failed transaction to continue with next queries in the same php script ? -- Mike
Probably because the pgsql extension in PHP has an internal representation that doesn't fit with what the DB does (auto rollback on error).
it keeps a state of transaction flag as active even though the DB rolled back when the error occured thus flagging an error when you want to start another one or execute another query.
BEGIN
Queries
check for error (pg_last_error) after each query, if error, rollback and exit transaction "block"
COMMIT (no errors found)
The best would be to enclose the query in a function so that you can "exit" the block with a return statement or some similar construct. I can't remember if PHP supports block definition like in C
{
// group
// of
// calls
// block
}
David
it keeps a state of transaction flag as active even though the DB rolled back when the error occured thus flagging an error when you want to start another one or execute another query.
BEGIN
Queries
check for error (pg_last_error) after each query, if error, rollback and exit transaction "block"
COMMIT (no errors found)
The best would be to enclose the query in a function so that you can "exit" the block with a return statement or some similar construct. I can't remember if PHP supports block definition like in C
{
// group
// of
// calls
// block
}
David
On 3/1/07, mikie <mikie.pl@gmail.com > wrote:
> > But getting back to my problem - perhaps there is something I
> > misunderstood: is it the client application responsibility to check if
> > the transaction failed or succeeded and issue COMMIT or ROLLBACK
> > accordingly (how do I close the transaction block in that case)?
> > Or is it the database server that is suppose to check if transaction
> > succeded and perform the query, or ROLLBACK if anything went wrong?
>
> PG will rollback all transactions that have an error in some part, as it
> can't commit the transaction as a whole.
OK, so my way of programming seems correct.
Then why do I have to send another COMMIT after failed transaction to
continue with next queries in the same php script ?
--
Mike
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
The client must issue a command to commit or rollback the transaction. In your example, are you sending all of the SQL lines (including the COMMIT) as a single execution? It sounds like the interface stops executing as soon as an error is reached. So perhaps change your code to execute the COMMIT separately after all the other commands have executed. John On Mar 1, 2007, at 7:45 AM, mikie wrote: > Yes, that is nice way to work with databases, but I am on PHP4 and I > am not using the PDO. > But getting back to my problem - perhaps there is something I > misunderstood: is it the client application responsibility to check if > the transaction failed or succeeded and issue COMMIT or ROLLBACK > accordingly (how do I close the transaction block in that case)? > Or is it the database server that is suppose to check if transaction > succeded and perform the query, or ROLLBACK if anything went wrong? John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL
mikie wrote:
>> > But getting back to my problem - perhaps there is something I
>> > misunderstood: is it the client application responsibility to check if
>> > the transaction failed or succeeded and issue COMMIT or ROLLBACK
>> > accordingly (how do I close the transaction block in that case)?
>> > Or is it the database server that is suppose to check if transaction
>> > succeded and perform the query, or ROLLBACK if anything went wrong?
>>
>> PG will rollback all transactions that have an error in some part, as it
>> can't commit the transaction as a whole.
>
> OK, so my way of programming seems correct.
> Then why do I have to send another COMMIT after failed transaction to
> continue with next queries in the same php script ?
I may not have gotten your question right, but with one commit (or end)
is enough. Transaction gets closed (commited or rolled back), and you
can start a new transaction with BEGIN.
--
21:50:04 up 2 days, 9:07, 0 users, load average: 0.92, 0.37, 0.18
---------------------------------------------------------
Lic. Martín Marqués | SELECT 'mmarques' ||
Centro de Telemática | '@' || 'unl.edu.ar';
Universidad Nacional | DBA, Programador,
del Litoral | Administrador
---------------------------------------------------------
John DeSoi wrote:
> The client must issue a command to commit or rollback the transaction.
>
> In your example, are you sending all of the SQL lines (including the
> COMMIT) as a single execution? It sounds like the interface stops
> executing as soon as an error is reached. So perhaps change your code to
> execute the COMMIT separately after all the other commands have executed.
Logs would also help.
--
21:50:04 up 2 days, 9:07, 0 users, load average: 0.92, 0.37, 0.18
---------------------------------------------------------
Lic. Martín Marqués | SELECT 'mmarques' ||
Centro de Telemática | '@' || 'unl.edu.ar';
Universidad Nacional | DBA, Programador,
del Litoral | Administrador
---------------------------------------------------------
2007/3/1, Martin Marques <martin@bugs.unl.edu.ar>: > mikie wrote: > >> > But getting back to my problem - perhaps there is something I > >> > misunderstood: is it the client application responsibility to check if > >> > the transaction failed or succeeded and issue COMMIT or ROLLBACK > >> > accordingly (how do I close the transaction block in that case)? > >> > Or is it the database server that is suppose to check if transaction > >> > succeded and perform the query, or ROLLBACK if anything went wrong? > >> > >> PG will rollback all transactions that have an error in some part, as it > >> can't commit the transaction as a whole. > > > > OK, so my way of programming seems correct. > > Then why do I have to send another COMMIT after failed transaction to > > continue with next queries in the same php script ? > > I may not have gotten your question right, but with one commit (or end) > is enough. Transaction gets closed (commited or rolled back), and you > can start a new transaction with BEGIN. Thats exactly what I was thinking, too. But PHP does things in some other way, I guess. I am sending an entire sql transaction block to the PHP pg_query command, so it starts with BEGIN and ends with COMMIT. Then I still have to execute another pg_query with "commit;" only - thats why I am asking here if maybe someone know what the problem is.
mikie wrote: > 2007/3/1, Martin Marques <martin@bugs.unl.edu.ar>: > >> mikie wrote: >> >> > But getting back to my problem - perhaps there is something I >> >> > misunderstood: is it the client application responsibility to >> check if >> >> > the transaction failed or succeeded and issue COMMIT or ROLLBACK >> >> > accordingly (how do I close the transaction block in that case)? >> >> > Or is it the database server that is suppose to check if transaction >> >> > succeded and perform the query, or ROLLBACK if anything went wrong? >> >> >> >> PG will rollback all transactions that have an error in some part, >> as it >> >> can't commit the transaction as a whole. >> > >> > OK, so my way of programming seems correct. >> > Then why do I have to send another COMMIT after failed transaction to >> > continue with next queries in the same php script ? >> >> I may not have gotten your question right, but with one commit (or end) >> is enough. Transaction gets closed (commited or rolled back), and you >> can start a new transaction with BEGIN. > > > Thats exactly what I was thinking, too. But PHP does things in some > other way, I guess. > I am sending an entire sql transaction block to the PHP pg_query > command, so it starts with BEGIN and ends with COMMIT. Then I still > have to execute another pg_query with "commit;" only - thats why I am > asking here if maybe someone know what the problem is. Try executing the query without the last commit, and afterwords execute another query with commit. I personaly don't like to mix diferent querys and comands in one pg_query() line. Also, I am more bound to using things like MDB2 or PDO, which makes life easier. BTW, if you have log_statments = 'all', could you see those logs to check if the commits are really getting to the server? Martín
On Thursday 01 March 2007 11:42 am, mikie wrote: [..snipped..] > Thats exactly what I was thinking, too. But PHP does things in some > other way, I guess. > I am sending an entire sql transaction block to the PHP pg_query > command, so it starts with BEGIN and ends with COMMIT. Then I still > have to execute another pg_query with "commit;" only - thats why I am > asking here if maybe someone know what the problem is. > greetings, I don't know if this will solve your problem or not, but on my machines, "autoCommit" is set to "true" by default (using PEAR::DB).. so when I want to do an operation that I expect to have to rollback, I turn *off* autocommit, do my begin, run my queries, then a commit or rollback at the end, followed by restoring autoCommit. it sounds to me like that is what is happening. regards, -- Jeff MacDonald, Zoid Technologies <http://zoidtechnologies.com/>
On Thursday 01 March 2007 12:25, Martin A. Marques wrote:
> mikie wrote:
> > 2007/3/1, Martin Marques <martin@bugs.unl.edu.ar>:
> >> mikie wrote:
> >> >> > But getting back to my problem - perhaps there is something I
> >> >> > misunderstood: is it the client application responsibility to
> >>
> >> check if
> >>
> >> >> > the transaction failed or succeeded and issue COMMIT or ROLLBACK
> >> >> > accordingly (how do I close the transaction block in that case)?
> >> >> > Or is it the database server that is suppose to check if
> >> >> > transaction succeded and perform the query, or ROLLBACK if anything
> >> >> > went wrong?
> >> >>
> >> >> PG will rollback all transactions that have an error in some part,
> >>
> >> as it
> >>
> >> >> can't commit the transaction as a whole.
> >> >
> >> > OK, so my way of programming seems correct.
> >> > Then why do I have to send another COMMIT after failed transaction to
> >> > continue with next queries in the same php script ?
> >>
> >> I may not have gotten your question right, but with one commit (or end)
> >> is enough. Transaction gets closed (commited or rolled back), and you
> >> can start a new transaction with BEGIN.
> >
> > Thats exactly what I was thinking, too. But PHP does things in some
> > other way, I guess.
> > I am sending an entire sql transaction block to the PHP pg_query
> > command, so it starts with BEGIN and ends with COMMIT. Then I still
> > have to execute another pg_query with "commit;" only - thats why I am
> > asking here if maybe someone know what the problem is.
>
It's possible that php is receiving an error back from one query and ignoring
the rest of the queries being sent (though that's not my recollection of how
it works, that's the behavior of what your describing, and you're using older
software aiui)
> Try executing the query without the last commit, and afterwords execute
> another query with commit.
>
> I personaly don't like to mix diferent querys and comands in one
> pg_query() line. Also, I am more bound to using things like MDB2 or PDO,
> which makes life easier.
>
> BTW, if you have log_statments = 'all', could you see those logs to
> check if the commits are really getting to the server?
>
Yeah, do the above recommendation to see if all of your statements are making
thier way into pg
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
2007/3/1, Robert Treat <xzilla@users.sourceforge.net>: > On Thursday 01 March 2007 12:25, Martin A. Marques wrote: > > mikie wrote: > > > 2007/3/1, Martin Marques <martin@bugs.unl.edu.ar>: > > >> mikie wrote: > > >> >> > But getting back to my problem - perhaps there is something I > > >> >> > misunderstood: is it the client application responsibility to > > >> > > >> check if > > >> > > >> >> > the transaction failed or succeeded and issue COMMIT or ROLLBACK > > >> >> > accordingly (how do I close the transaction block in that case)? > > >> >> > Or is it the database server that is suppose to check if > > >> >> > transaction succeded and perform the query, or ROLLBACK if anything > > >> >> > went wrong? > > >> >> > > >> >> PG will rollback all transactions that have an error in some part, > > >> > > >> as it > > >> > > >> >> can't commit the transaction as a whole. > > >> > > > >> > OK, so my way of programming seems correct. > > >> > Then why do I have to send another COMMIT after failed transaction to > > >> > continue with next queries in the same php script ? > > >> > > >> I may not have gotten your question right, but with one commit (or end) > > >> is enough. Transaction gets closed (commited or rolled back), and you > > >> can start a new transaction with BEGIN. > > > > > > Thats exactly what I was thinking, too. But PHP does things in some > > > other way, I guess. > > > I am sending an entire sql transaction block to the PHP pg_query > > > command, so it starts with BEGIN and ends with COMMIT. Then I still > > > have to execute another pg_query with "commit;" only - thats why I am > > > asking here if maybe someone know what the problem is. > > > > It's possible that php is receiving an error back from one query and ignoring > the rest of the queries being sent (though that's not my recollection of how > it works, that's the behavior of what your describing, and you're using older > software aiui) > > > Try executing the query without the last commit, and afterwords execute > > another query with commit. > > > > I personaly don't like to mix diferent querys and comands in one > > pg_query() line. Also, I am more bound to using things like MDB2 or PDO, > > which makes life easier. > > > > BTW, if you have log_statments = 'all', could you see those logs to > > check if the commits are really getting to the server? > > > > Yeah, do the above recommendation to see if all of your statements are making > thier way into pg OK, I have checked the logs. I can see both COMMIT statements in the log - the first commit from my transaction and then the other that I send to workaround the problem.
mikie wrote:
> 2007/3/1, Robert Treat <xzilla@users.sourceforge.net>:
>> Yeah, do the above recommendation to see if all of your statements are
>> making
>> thier way into pg
>
> OK, I have checked the logs. I can see both COMMIT statements in the
> log - the first commit from my transaction and then the other that I
> send to workaround the problem.
There must be some other problem. Do you have trasaction ID on the logs?
See if both commits go in the same transaction.
Any errors during the transaction?
--
21:50:04 up 2 days, 9:07, 0 users, load average: 0.92, 0.37, 0.18
---------------------------------------------------------
Lic. Martín Marqués | SELECT 'mmarques' ||
Centro de Telemática | '@' || 'unl.edu.ar';
Universidad Nacional | DBA, Programador,
del Litoral | Administrador
---------------------------------------------------------
2007/3/2, Martin Marques <martin@bugs.unl.edu.ar>: > mikie wrote: > > 2007/3/1, Robert Treat <xzilla@users.sourceforge.net>: > >> Yeah, do the above recommendation to see if all of your statements are > >> making > >> thier way into pg > > > > OK, I have checked the logs. I can see both COMMIT statements in the > > log - the first commit from my transaction and then the other that I > > send to workaround the problem. > > There must be some other problem. Do you have trasaction ID on the logs? > See if both commits go in the same transaction. Could you please advise me how to check the transaction ID in the log? > Any errors during the transaction? As I wrote in my first post, I make this error intentionally to check if transaction works (if something fails, then the entire transaction should be rolled back). I intentionally give wrong data in the "date" field for example. 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?
mikie wrote:
> 2007/3/2, Martin Marques <martin@bugs.unl.edu.ar>:
>> mikie wrote:
>> > 2007/3/1, Robert Treat <xzilla@users.sourceforge.net>:
>> >> Yeah, do the above recommendation to see if all of your statements are
>> >> making
>> >> thier way into pg
>> >
>> > OK, I have checked the logs. I can see both COMMIT statements in the
>> > log - the first commit from my transaction and then the other that I
>> > send to workaround the problem.
>>
>> There must be some other problem. Do you have trasaction ID on the logs?
>> See if both commits go in the same transaction.
>
> Could you please advise me how to check the transaction ID in the log?
Edit postgresql.conf:
log_line_prefix = '<%t %x>'
And restart postmaster.
>> Any errors during the transaction?
>
> As I wrote in my first post, I make this error intentionally to check
> if transaction works (if something fails, then the entire transaction
> should be rolled back). I intentionally give wrong data in the "date"
> field for example.
> 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?
One question: Why dont you but each query in a diferent pg_exec() command?
--
21:50:04 up 2 days, 9:07, 0 users, load average: 0.92, 0.37, 0.18
---------------------------------------------------------
Lic. Martín Marqués | SELECT 'mmarques' ||
Centro de Telemática | '@' || 'unl.edu.ar';
Universidad Nacional | DBA, Programador,
del Litoral | Administrador
---------------------------------------------------------
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
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.
2007/3/5, Martin Marques <martin@bugs.unl.edu.ar>: > mikie wrote: > > 2007/3/2, Martin Marques <martin@bugs.unl.edu.ar>: > >> mikie wrote: > >> > 2007/3/1, Robert Treat <xzilla@users.sourceforge.net>: > >> >> Yeah, do the above recommendation to see if all of your statements are > >> >> making > >> >> thier way into pg > >> > > >> > OK, I have checked the logs. I can see both COMMIT statements in the > >> > log - the first commit from my transaction and then the other that I > >> > send to workaround the problem. > >> > >> There must be some other problem. Do you have trasaction ID on the logs? > >> See if both commits go in the same transaction. > > > > Could you please advise me how to check the transaction ID in the log? > > Edit postgresql.conf: > > log_line_prefix = '<%t %x>' > > And restart postmaster. Thanks, it works. I can see the transaction IDs -- I described my solution in my previous post. > >> Any errors during the transaction? > > > > As I wrote in my first post, I make this error intentionally to check > > if transaction works (if something fails, then the entire transaction > > should be rolled back). I intentionally give wrong data in the "date" > > field for example. > > 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? > > One question: Why dont you but each query in a diferent pg_exec() command? It does not make any difference if I put single query in each pg_query() command. --