Thread: PSQL undesired transaction behavior when connection is lost.
I assume I'm not the first person to run in to this, however searching
google didn't seem to come up with anything useful.
its=> begin; delete from pay_stub_entry where pay_stub_id in (select id
from pay_stub where created_date >= 1096527603 order by created_date
desc); delete from pay_stub where id in (select id from pay_stub where
created_date >= 1096527603 order by created_date desc); commit;
FATAL: terminating connection due to administrator command
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
DELETE 274
DELETE 19
WARNING: there is no transaction in progress
COMMIT
its=> select version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 7.4.5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.1
(Mandrakelinux (Alpha 3.4.1-3mdk)
(1 row)
On connection reset, shouldn't "begin;" be sent again? Either that or
shouldn't the entire command fail in this case, not just the begin?
If I had a syntax error in the first delete command, I definitely would
not want the second delete to succeed, which had the potential to happen
in the above case. (Luckily it didn't)
BTW: I had restarted the server manually, so it didn't crash or
anything.
--
Mike Benoit <ipso@snappymail.ca>
* Mike Benoit <ipso@snappymail.ca> [2004-10-07 11:47:50 -0700]: > I assume I'm not the first person to run in to this, however > searching google didn't seem to come up with anything useful. AFAICT, the first query is just constructed poorly, while the second seems to recurse on itself. The order in the sub-selects doesn't seen necessary either. > its=> begin; delete from pay_stub_entry where pay_stub_id in (select > id from pay_stub where created_date >= 1096527603 order by > created_date desc); DELETE FROM pay_stub_entry JOIN pay_stub ON (pay_stub_entry.pay_stub_id = pay_stub.id) WHERE pay_stub.created_data >=1096527603; > delete from pay_stub where id in (select id from pay_stub where > created_date >= 1096527603 order by created_date desc); commit; DELETE FROM pay_stub WHERE created_data >=1096527603; -- Steven Klassen - Lead Programmer Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Replication & Support Services, (503) 667-4564
* Steven Klassen <sklassen@commandprompt.com> [2004-10-07 12:33:34 -0700]: > DELETE FROM pay_stub_entry > JOIN pay_stub ON (pay_stub_entry.pay_stub_id = pay_stub.id) > WHERE pay_stub.created_date >=1096527603; After RTFM'ing it appears you can't do actual joins with delete so we'll just have to daisy-chain the where clause. DELETE FROM pay_stub_entry WHERE pay_stub_entry.pay_stub_id = pay_stub.id AND pay_stub.created_date >=1096527603; -- Steven Klassen - Lead Programmer Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Replication & Support Services, (503) 667-4564
Steven Klassen <sklassen@commandprompt.com> writes:
> * Mike Benoit <ipso@snappymail.ca> [2004-10-07 11:47:50 -0700]:
>> I assume I'm not the first person to run in to this, however
>> searching google didn't seem to come up with anything useful.
> AFAICT, the first query is just constructed poorly, while the second
> seems to recurse on itself. The order in the sub-selects doesn't seen
> necessary either.
Agreed, but I think that's irrelevant to his point: psql probably
should abandon whatever is left in its input buffer after getting an
error from the backend, and almost certainly should do so after loss
of connection. In the noninteractive case I believe it will quit
executing the script file, which is good, but in the interactive case
it seems like a mistake not to flush the query buffer.
Peter, do you know if this behavior was intentional, or just an
implementation artifact?
regards, tom lane
* Tom Lane <tgl@sss.pgh.pa.us> [2004-10-07 16:33:26 -0400]: > Steven Klassen <sklassen@commandprompt.com> writes: > > * Mike Benoit <ipso@snappymail.ca> [2004-10-07 11:47:50 -0700]: > >> I assume I'm not the first person to run in to this, however > >> searching google didn't seem to come up with anything useful. > > > AFAICT, the first query is just constructed poorly, while the second > > seems to recurse on itself. The order in the sub-selects doesn't seen > > necessary either. > > Agreed, but I think that's irrelevant to his point: I thought I had punctuated with something like: "I can speak to the query format, but someone more in touch with the internals will have to address the actual error", but it most have gotten blown away during edits. -- Steven Klassen - Lead Programmer Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Replication & Support Services, (503) 667-4564