Thread: taking actions on rollback (PHP)
I've a set of statements...
pg_query('begin;');
pg_query('do stuff');
pg_query('do other stuff');
if(!pg_query('commit;')) {
pg_query('rollback;');
// DO SOME CLEANUP HERE
}
if something goes wrong the statement actually get rolled back... but
the pg_query('commit;') statement always succede so the cleanup never
get executed.
How am I going to see if the transaction succeeded without checking
what happens for each statement and getting the cleanup code execute?
thanks
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
On Sat, Apr 26, 2008 at 10:52:12PM +0200, Ivan Sergio Borgonovo wrote:
> How am I going to see if the transaction succeeded without checking
> what happens for each statement and getting the cleanup code execute?
You basically actually check for the errors in the earlier pg_query()
calls, since they will tell you. If you can't be bothered, you could
just do at the end of the transaction:
if( !pg_query("select 1") )
pg_query("rollback');
// DO CLEANUP HERE
}
else
{ pg_query("commit"); }
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.
Attachment
On Sat, 26 Apr 2008 23:14:07 +0200
Martijn van Oosterhout <kleptog@svana.org> wrote:
> On Sat, Apr 26, 2008 at 10:52:12PM +0200, Ivan Sergio Borgonovo
> wrote:
> > How am I going to see if the transaction succeeded without
> > checking what happens for each statement and getting the cleanup
> > code execute?
>
> You basically actually check for the errors in the earlier
> pg_query() calls, since they will tell you. If you can't be
> bothered, you could just do at the end of the transaction:
> if( !pg_query("select 1") )
> pg_query("rollback');
> // DO CLEANUP HERE
> }
> else
> { pg_query("commit"); }
I can guess the logic... but I'd like to be sure I got it.
Why does
if( !pg_query("commit;") )
doesn't report any error back?
So an error stays around and after a failure of any statement after
the begin all the following statement (on the same connection???) will
fail. Is it?
BTW your code had to be changed to
if( !@pg_query("select 1") )
pg_query("rollback');
// DO CLEANUP HERE
}
else
{ pg_query("commit"); }
Otherwise php get nervous, fail and the cleanup code doesn't get
executed and furthermore it seems that with a pending failed
transaction other statement fail too.
That was curious since I opened 2 pages from the same browser just to
check if the cleanup code was going to be executed and keep the form
I was submitting easy to be refilled with a back.
Form -> several errors +
ERROR: current transaction is aborted, commands ignored until end of
transaction block
Testing page that give me a view on the tables that should be
cleaned up -> refresh ->
ERROR: current transaction is aborted, commands ignored until end of
transaction block
With the added @ everything seemed to be OK.
I had to refresh a second time to get rid of the error.
I'd like to be sure I've understood how it works since I wouldn't
like the error went away just by chance and under heavy load I may
have troubles.
I'm using pg_connect not pg_pconnect.
Why did I get the
ERROR: current transaction is aborted, commands ignored until end of
transaction block
from 2 different pages?
> Have a nice day,
thanks, you've been very helpful.
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
On Sat, Apr 26, 2008 at 4:19 PM, Ivan Sergio Borgonovo <mail@webthatworks.it> wrote: > > With the added @ everything seemed to be OK. No, the @ is just making php quietly swallow the postgresql errors that are being returned. It changes nothing in operation. > I had to refresh a second time to get rid of the error. > I'd like to be sure I've understood how it works since I wouldn't > like the error went away just by chance and under heavy load I may > have troubles. I'm pretty sure you're not undestanding how it works. With postgresql, ANY error in a transaction results in the whole transaction being rolled back. Any commands thrown at the database after that result in this error message: > Why did I get the > ERROR: current transaction is aborted, commands ignored until end of > transaction block > from 2 different pages? because each page got an error in a statement inside its transaction. It then issued the above error over and over as you attempted to execute the next statement. In postgresql, without using savepoints, any error in the transaction will cause the whole transaction to fail, whether you type commit or rollback at the end. All the changes are lost either way. So, there's no "cleanup" to do for the transaction, it's already cleaned up.
On Sat, 26 Apr 2008 20:58:06 -0600
"Scott Marlowe" <scott.marlowe@gmail.com> wrote:
> On Sat, Apr 26, 2008 at 4:19 PM, Ivan Sergio Borgonovo
> <mail@webthatworks.it> wrote:
> >
> > With the added @ everything seemed to be OK.
>
> No, the @ is just making php quietly swallow the postgresql errors
> that are being returned. It changes nothing in operation.
That seems to be necessary otherwise the transaction won't be
"closed" and no other statement would be accepted.
> > I had to refresh a second time to get rid of the error.
> > I'd like to be sure I've understood how it works since I wouldn't
> > like the error went away just by chance and under heavy load I
> > may have troubles.
> I'm pretty sure you're not undestanding how it works.
> With postgresql, ANY error in a transaction results in the whole
> transaction being rolled back. Any commands thrown at the database
> after that result in this error message:
That's exactly why I started to use transactions.
> > Why did I get the
> > ERROR: current transaction is aborted, commands ignored until
> > end of transaction block
> > from 2 different pages?
> because each page got an error in a statement inside its
> transaction. It then issued the above error over and over as you
> attempted to execute the next statement.
That would make postgresql a BIG BIG BIG lock.
If every rollback is going to block all connections that's a problem.
That's exactly why I pointed out that I was using plain pg_connect
and not pg_pconnect (pooled connection).
So I'd expect that if one page, that's using one connection, got
stuck ignoring other statement cos it has a failed transaction
pending... another page using another connection can still at least
read the DB.
The @ seems to be necessary otherwise php will stop execution and
leave the transaction open and php will never reach the cleanup code.
I think the code would work even this way:
if( !pg_query("select 1") )
// commit even if failed just to signal end of transaction
pg_query("commit');
// DO CLEANUP HERE
}
else
{ pg_query("commit"); }
Still... why did I get
ERROR: current transaction is aborted, commands ignored until end of
transaction block
from *another page* even if I was not using pg_pconnect?
What is the state of the DB after the first failed statement?
Furthermore... what's going to happen if I use pg_pconnect?
Managing transactions is going to become much harder.
Is there a chance something like this is going to happen?
pg_query("begin;");
pg_query("statement from page 1");
pg_query("statement from page 1");
// FAIL, php code reach the end and connection is freed
pg_query("statement from page 1");
pg_query("statement from page 2 kick in in same connection");
// even statement from page 2 fail cos a transaction is pending on
the same connection
> In postgresql, without using savepoints, any error in the
> transaction will cause the whole transaction to fail, whether you
> type commit or rollback at the end. All the changes are lost
> either way.
> So, there's no "cleanup" to do for the transaction, it's already
> cleaned up.
That would be great if the framework I'm working with would be
transactional. So there are things done before my transaction that
still need cleanup.
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
On Sun, Apr 27, 2008 at 11:09:36AM +0200, Ivan Sergio Borgonovo wrote:
> > because each page got an error in a statement inside its
> > transaction. It then issued the above error over and over as you
> > attempted to execute the next statement.
>
> That would make postgresql a BIG BIG BIG lock.
> If every rollback is going to block all connections that's a problem.
> That's exactly why I pointed out that I was using plain pg_connect
> and not pg_pconnect (pooled connection).
Eh? Each page got an error in its own transaction. An error in one
transaction naturally does not affect any other concurrent
transactions.
> I think the code would work even this way:
> if( !pg_query("select 1") )
> // commit even if failed just to signal end of transaction
> pg_query("commit');
> // DO CLEANUP HERE
> }
True, for an aborted transaction COMMIT and ROLLBACK do the same thing.
> Still... why did I get
> ERROR: current transaction is aborted, commands ignored until end of
> transaction block
> from *another page* even if I was not using pg_pconnect?
> What is the state of the DB after the first failed statement?
That other page also got an error. Given then you never check the
return result of any of the queries I suggest you look at the server
logs: any statements that caused an error should be logged.
> Furthermore... what's going to happen if I use pg_pconnect?
> Managing transactions is going to become much harder.
> Is there a chance something like this is going to happen?
There's a reason why pg_pconnect is not recommended. The usual method
is some kind of connection pooling that understands transactions.
> > So, there's no "cleanup" to do for the transaction, it's already
> > cleaned up.
>
> That would be great if the framework I'm working with would be
> transactional. So there are things done before my transaction that
> still need cleanup.
It's unclear to me what kind of cleanup you're talking about, so I
can't help you here.
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.
Attachment
On Sun, 27 Apr 2008 11:26:33 +0200 Martijn van Oosterhout <kleptog@svana.org> wrote: > > That would make postgresql a BIG BIG BIG lock. > > If every rollback is going to block all connections that's a > > problem. That's exactly why I pointed out that I was using plain > > pg_connect and not pg_pconnect (pooled connection). > Eh? Each page got an error in its own transaction. An error in one > transaction naturally does not affect any other concurrent > transactions. "Eh?" was my same reaction. I'm going to check the logs... and be sure I wasn't dreaming. Do you confirm that if I wasn't dreaming and another page that should have opened another connection with pg_connect did cause another ERROR: current transaction is aborted, commands ignored until end of transaction block there is something wrong with the Universe or at least with what's happening with php/postgresql? > > Furthermore... what's going to happen if I use pg_pconnect? > > Managing transactions is going to become much harder. > > Is there a chance something like this is going to happen? > There's a reason why pg_pconnect is not recommended. The usual > method is some kind of connection pooling that understands > transactions. Are there any guidelines about usng pg_pconnect? Are you aware of some place that could enlighten me about the troubles I may face and what I should take care of? Explicitly using the resource param in pg_query doesn't look helpful. PGSQL_CONNECT_FORCE_NEW actually fail the scope of using pg_pconnect. For the transaction problem there is a proposed solution on the php online manual... send a commit when the script closes in register_shutdown_function(), so you avoid bookkeeping and you're sure the transaction will be closed. I still can't get the magic behing pg_pconnect. When does a connection is considered freed? This is interesting: http://archives.postgresql.org/pgsql-general/2007-08/msg00660.php And well since it doesn't seem I really need pg_pconnect and that when I'll need connection pooling I'll go for pgpool... consider the questions above just for the shake of understanding how things works behind the scene. > > > So, there's no "cleanup" to do for the transaction, it's already > > > cleaned up. > > > > That would be great if the framework I'm working with would be > > transactional. So there are things done before my transaction that > > still need cleanup. > It's unclear to me what kind of cleanup you're talking about, so I > can't help you here. Just to waste some bandwidth since this is absolutely not pg related... I'm using an object provided by a framework that doesn't use transactions. I'm enriching that object with other proprieties. Part of the object is created by the framework and saved to DB. Then control is passed to me and I add the other proprieties. If for any reason my code fail... I've to cleanup the simpler object created by the framework "by hand". Using transactions on my part won't solve the problem that the framework is not transactional... and I may still end up in mangled objects... but still better than nothing. thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it
On Sun, Apr 27, 2008 at 02:05:16PM +0200, Ivan Sergio Borgonovo wrote: > "Eh?" was my same reaction. I'm going to check the logs... and be > sure I wasn't dreaming. > Do you confirm that if I wasn't dreaming and another page that should > have opened another connection with pg_connect did cause another > > ERROR: current transaction is aborted, commands ignored until end of > transaction block > > there is something wrong with the Universe or at least with what's > happening with php/postgresql? Yes, if you're getting that message on a connection without having an error on that connection then something is really wrong. Note, I've never used PG with PHP so I can't help you with anything specific. > This is interesting: > http://archives.postgresql.org/pgsql-general/2007-08/msg00660.php Yes, that's a pretty good summary of the problems with pconnect. > If for any reason my code fail... I've to cleanup the simpler object > created by the framework "by hand". Ok... Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Please line up in a tree and maintain the heap invariant while > boarding. Thank you for flying nlogn airlines.
Attachment
Martijn van Oosterhout wrote: > Yes, if you're getting that message on a connection without having an > error on that connection then something is really wrong. Note, I've > never used PG with PHP so I can't help you with anything specific. Turn on pgsql.auto_reset_persistent. -- Best regards, Hannes Dorbath