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?
In any case, at some point in my commenting and re-running of a script with
a transaction, PHP returned error:
"Unable to jump to row 0 on PostgreSQL result index 6".
The query in question was:
update uledger set cnt=2 where hr=4098 and yr='1' and mid=29;
I then tried this query via psql. I got no output, not even a prompt. I hit ^c
and got:
Cancel request sent
ERROR: Query cancel requested while waiting lock
This re-occured everytime i tried the update. To try and clear what appeared
to be a lock, I'd restarted postgresql. I re-entered psql and re-tried the
same update. I got:
ERROR: Cannot insert a duplicate key into unique index uq_hym
This was a update that had worked correctly hundreds of times.
I took a break, came back a few hours later, ran a few Selects (that's all) and
retried the same update. It worked fine.
What could have been going on? The problem is gone for now but i'd to learn
what happened to avoid the problem in the future.
Thank you,
George Herson