Re: incomplete transaction keeps table locked?

Yes, thanks, i'm using persistant connections.  You're right about the
php4.0.5 changelog (http://www.php.net/ChangeLog-4.php). It says:

"Fixed pgsql transaction support. (Stig, PEAR/DB)
. . .
PostgreSQL now does a rollback at the end of a request on every
  persistent connection. This is done by doing an "empty" transaction
  on the connection. This was advised by someone from the PostgreSQL
  core-team. (Thies)
Fixed PostgeSQL pg_connect() bug. We would sometimes close the default
  link by accident. (Patch by: aja@nlgroup.ca)"

so i upgraded.  That should help.

But since i made the original post i rebooted my server and now i think
that (a reboot) was also needed, as a couple of little things outside
PHP/Apache/Posgresql (in Netscape, actually) are now working that were
broken pre-boot.

Is it true that when Linux needs a reboot it sometimes isn't obvious?
That can be very dangerous from a time-spent-on-debugging point of
view.  Are there any tests (eg, vmstat) that will quickly tell me when a
reboot is a good idea?  (I'm more used to having to reboot in Windows,
which of course succeeds in making it fairly obvious when your time is
up. :)

thx,
George

Richard Huxton wrote:
>
> From: "George Herson" <gherson@snet.net>
>
> > Hi. I'm running PostgreSQL 7.0.2 on Linux 2.2.14, i686.
> >
> > Are there any circumstances when exiting a PHP web script
> > where you've started a transaction (with Begin Work) but haven't
> explicitly
> > done a Commit Work or Rollback before it ends will lock you out of
> > a table (until you restart postgresql)?  I wouldn't do this normally but i
> > was commenting out different portions of the PHP script in question to
> isolate
> > a bug in my code.  Does Postgres somehow get notified when a PHP script
> ends
> > so it knows to end a transaction in progress?  If not, how can i ensure a
> > clean slate before attempting more sql commands in the next running of a
> PHP
> > script?
>
> You using persistent connections? If so, Apache+PHP will keep connections
> hanging around and PG doesn't know your session is finished. See mail
> archives for details and the latest 4.0.x release (4/5?) changelog for
> details.
>
> If you're not using persistent connections you've got problems since PHP
> should be clearing down the connection at the end of each request.
>
> - Richard Huxton