Skip site navigation (1) Skip section navigation (2)

Re: taking actions on rollback (PHP)

From: Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: taking actions on rollback (PHP)
Date: 2008-04-27 09:09:36
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-general
On Sat, 26 Apr 2008 20:58:06 -0600
"Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com> wrote:

> On Sat, Apr 26, 2008 at 4:19 PM, Ivan Sergio Borgonovo
> <mail(at)webthatworks(dot)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"); }

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("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

In response to


pgsql-general by date

Next:From: Martijn van OosterhoutDate: 2008-04-27 09:26:33
Subject: Re: taking actions on rollback (PHP)
Previous:From: Matthew DennisDate: 2008-04-27 06:06:09
Subject: plpgsql functions and the planner

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group