Re: Persistent connections and transactions

Frank Joerdens wrote:

On Fri, Jan 26, 2001 at 11:01:14AM +0000, Nuno Silva wrote: [ . . . ]

i ran across this transaction problem vs. persistent conn in pgsql some time ago. I found two workaround methods: - don't use persistent conn's :-) or; - start every conn with the usual pg_pconnect and then query a rollback to the server: $query="ROLLBACK work;" (this will kill any supposed transaction in ABORT STATE that some child had left). postgres don't support nested transactions (yet), but when it does maybe you should add multiple "ROLLBACK WORK;" queries :-)

Ah. Very cool. What I _still_ don't quite understand, though, is _how_ exactly this situation could come about. And what the worst case would be. What if you don't kill the transaction in ABORT STATE? Ta, Frank

Hi, this happens because pg_pconnect don't know about transactions. If some transaction reach ABORT and for some reason the script never makes it to COMMIT or ABORT queries the postgresql backend will be _always_ in ABORT STATE. The problem is present in several combinations... for instance: in mysql you could make the connection with one user to database X. While logged in you could change to database Y.... This database backend will be in database Y after that! Even if you persistent_connect with the original parameters! This is because the backend is never terminated and retains the last state, being it a ABORT STATE or another DB... :) queriing "ROLLBACK WORK;" right after the conect is my best shot at this one. Regards, Nuno Silva