Re: Lock problem

On Wed, Apr 2, 2014 at 3:01 PM, Igor Neyman <ineyman(at)perceptron(dot)com> wrote:
>
>
>> -----Original Message-----
>> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
>> owner(at)postgresql(dot)org] On Behalf Of Victor Sterpu
>> Sent: Wednesday, April 02, 2014 2:25 PM
>> To: Victor Sterpu; Merlin Moncure
>> Cc: PostgreSQL General
>> Subject: Re: [GENERAL] Lock problem
>>
>> I'm sure is not right, but is a there a server side solution for such sitations?
>> A configuration - timeout for idle transactions.
>>
>
> I don't think PG has such configuration parameter.
> But, you could easily write a function (say in PgPlSQL) and run it on schedule, where you could check "ILE IN TRANSACTDION" session and compare their start_time to system time, and then based on your criteria you could kill suspect session/transaction.
> But this could be dangerous; some long-running transactions could be perfectly valid.

I'd look for 'Idle In Transaction' backends that have
clock_timestamp() - state_change > x, where x is the maximum amount of
time your application does stuff between queries while in transaction.
Generally, x should never be more than about 10 seconds or so...or if
it is, it's advisable to restructure your application so that more
preprocessing is done before grabbing the transaction initially. In
fact, for well written applications, seeing 'idle in transaction'
should be quite exceptional.

merlin