Skip site navigation (1) Skip section navigation (2)

idle in txn query cancellation

From: Andres Freund <andres(at)anarazel(dot)de>
To: Simon Riggs <simon(at)2ndquadrant(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: idle in txn query cancellation
Date: 2010-02-13 21:37:41
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
Hi all,

I know it is late in the cycle, but I still think that the current behaviour 
of ERRORing during execution of a query but FATALing during IDLE IN 
TRANSACTION is very confusing to the user. Especially as you are not even able 
to read the reason for getting disconnected because the client doesnt expect 
input in that state...

The first patch adds the capability to add a flag to ereport like:
Tom earlier suggested using COMERROR but thats just a version of LOG which 
doesnt report to the client. The patch makes that to be a synonym of LOG | 
While its not the most pretty API I dont think its that bad because the 
directionality is somewhat a property of the loglevel. Beside it would 
generate a lot of useless noise and breakage.

The second patch changes the FATAL during cancelling an idle in txn query into  
To avoid breaking the known state there also may no "ready for query" message 
get sent. The patch ensures that by setting and checking a 
"silent_error_while_idle" variable.

That way the client will not see that an error occured until the next command 
sent but I dont think there is a solution to that in 9.0 timeframe if at all.

The patch only adds that for the recovery conflict path for now.

What do you think? Is it worth applying something like that now? If yes I 
would try to test the patch some more (obviously the patch survives the 
regression tests, but they do not seem to check the extended query protocol at 

One could argue that the LOG_NO_CLIENT flag should be added when a idle 
transaction gets terminated by force but I wouldn't bother.

On a related note I would also like to get rid of the restriction that a 
normal query cancellation will only be done if no subtransactions are stacked. 
But I guess its too late for that? (I have a patch ready, some cleanup would 
be needed)
The latter works by:
- adding a explicit error code (which should be done regardless of this 
- avoiding to catch such error at a few places (plperl, plpython)
- recursively aborting the subtransactions once the mainloop is reached
- relying on the fact that the cancellation signal will get resent
- possibly escalating to a FATAL if nothing happens after a certain number of 


PS: I know I sort-of-promised a patch earlier, but it didn't work out time-

Attachment: 0002-Dont-FATAL-a-IDLE-IN-TRANSACTIOn-backend-during-conf.patch
Description: text/x-patch (3.9 KB)
Attachment: 0001-Support-transporting-flags-in-the-elevel-argument-of.patch
Description: text/x-patch (6.1 KB)


pgsql-hackers by date

Next:From: David E. WheelerDate: 2010-02-13 22:25:48
Subject: Re: PostgreSQL::PLPerl::Call - Simple interface for calling SQL functions from PostgreSQL PL/Perl
Previous:From: Dimitri FontaineDate: 2010-02-13 21:20:25
Subject: Re: knngist patch support

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group