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: 20080427110936.4608f4e0@dawn.webthatworks.it (view raw or flat)
Thread:
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');
  // 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


In response to

Responses

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-2014 The PostgreSQL Global Development Group