PSQL undesired transaction behavior when connection is lost.

[Mike Benoit]

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 <ip**@snappymail.ca>
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Steven Klassen]

* Mike Benoit <ip**@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, [phone]

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Steven Klassen]

* Steven Klassen <sk******@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, [phone]

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

Steven Klassen <sk******@commandprompt.com> writes:

* Mike Benoit <ip**@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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Steven Klassen]

* Tom Lane <tg*@sss.pgh.pa.us> [2004-10-07 16:33:26 -0400]:

Steven Klassen <sk******@commandprompt.com> writes:

* Mike Benoit <ip**@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, [phone]

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly