Re: taking actions on rollback (PHP)

On Sun, 27 Apr 2008 11:26:33 +0200
Martijn van Oosterhout <kleptog(at)svana(dot)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