Re-run query on automatic reconnect

On Mon, Sep 26, 2005 at 01:23:03PM -0500, Jim C. Nasby wrote:

Is there any particular reason why psql doesn't re-run a query that
failed due to disconnect from the server after re-connecting? I've
thought maybe it's because it might somehow be dangerous, but I can't
think of any case where that's actually true.

What if the query itself resulted in the disconnect by causing the
backend to crash? Re-running such a query automatically would be
a bad idea. Or did I misunderstand what you're asking?

--
Michael Fuhr

"Jim C. Nasby" <jnasby@pervasive.com> writes:

Is there any particular reason why psql doesn't re-run a query that
failed due to disconnect from the server after re-connecting? I've
thought maybe it's because it might somehow be dangerous, but I can't
think of any case where that's actually true.

You haven't thought very hard.

(1) psql cannot tell whether the query was already completed before the
connection dropped; if so, reissuing it would make its effects if any
happen twice (eg, duplicate insert, or adding something to a bank
balance twice).

(2) If inside a transaction block, the query might or might not depend
on previous operations in the same transaction.

(3) The query might depend on previous session-local operations,
such as SET commands or creation of temp tables.

(4) If the query actually caused the server crash, re-issuing it will
probably cause another crash. Instant infinite loop, complete with
denial of service to all other database users.

regards, tom lane

On Mon, Sep 26, 2005 at 02:52:02PM -0600, Michael Fuhr wrote:

On Mon, Sep 26, 2005 at 01:23:03PM -0500, Jim C. Nasby wrote:

Is there any particular reason why psql doesn't re-run a query that
failed due to disconnect from the server after re-connecting? I've
thought maybe it's because it might somehow be dangerous, but I can't
think of any case where that's actually true.

What if the query itself resulted in the disconnect by causing the
backend to crash? Re-running such a query automatically would be
a bad idea. Or did I misunderstand what you're asking?

The case I was thinking of was...

psql> run some command...

psql returns from call

user does something else; meanwhile, server forced shutdown/crash

user comes back, tries to run some query and gets automatic reconnect

In any case, Tom brought up a bunch of cases where this would be a bad
idea, so my question's answored.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461