Re: taking actions on rollback (PHP) - Mailing list pgsql-general

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