Re: Request: pg_cancel_backend variant that handles 'idle in transaction' sessions

On Wed, Nov 4, 2015 at 2:09 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> 2015-11-04 20:35 GMT+01:00 Merlin Moncure <mmoncure(at)gmail(dot)com>:
>>
>> On Wed, Nov 4, 2015 at 11:26 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>

>> > it doesn't help. How I can set transaction_timeout if I have series of
>> > slow
>> > statements? In this case I cannot to set transaction_timeout before any
>> > statement or after any success statement.
>>
>> Not quite following you. The client has to go:
>> BEGIN;
>> SET transaction_timeout = x;
>> ....
>
> where is the point when transaction_timeout start? In BEGIN or in SET
> transaction_timeout ?

transaction start (BEGIN).

> How I can emulate transaction_idle_timeout? Can I refresh
> transaction_timeout?

Well, for my part, I'd probably set default to around an hour with
longer running batch driven tasks having to override.

> My issue isn't long statements, but broken client, that is broken in wrong
> state - connect is still active, but no any statement will coming.

Right, 'Idle in transaction'. Agree that a setting directed purely at
that problem could set a much lower timeout, say, 5 minutes or less
since it almost never comes up in real applications. In fact, in 15
years of postgres development, I've never seen 'idle transaction' that
indicated anything but application malfunction.

That being said, hour timeout for general case would work for me. It
would only have to be set lower for very busy OLTP databases where
continuous vacuum is essential. In those cases, I don't mind forcing
all batch processes to disclose in advance they are running long.

merlin