Обсуждение: Lock problem

SELECT bl.pid          AS blocked_pid,      a.usename       AS blocked_user,      kl.pid          AS blocking_pid,      ka.usename      AS blocking_user,      a.current_query AS blocked_statement
FROM  pg_catalog.pg_locks        bl
JOIN pg_catalog.pg_stat_activity a  ON a.procpid = bl.pid
JOIN pg_catalog.pg_locks         kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid
JOIN pg_catalog.pg_stat_activity ka ON ka.procpid = kl.pid
WHERENOT bl.granted;

DISCLAIMER:
Acest mesaj de posta electronica si documentele aferente sunt confidentiale. Este interzisa distribuirea, dezvaluirea sau orice alt mod de utilizare a lor. Daca nu sunteti destinatarul acestui mesaj, este interzis sa actionati in baza acestor informatii. Citirea, copierea, distribuirea, dezvaluirea sau utilizarea in alt mod a informatiei continute in acest mesaj constituie o incalcare a legii. Daca ati primit mesajul din greseala, va rugam sa il distrugeti, anuntand expeditorul de eroarea comisa. Intrucat nu poate fi garantat faptul ca posta electronica este un mod sigur si lipsit de erori de transmitere a informatiilor, este responsabilitatea dvs. sa va asigurati ca mesajul (inclusiv documentele alaturate lui) este validat si autorizat spre a fi utilizat in mediul dvs.

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Victor Sterpu
Sent: Wednesday, April 02, 2014 11:19 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Lock problem

Hello
 
I have a problem that it seems to be very hard to debug.
Problem is from some postgresql locks. I use PostgreSQL 9.1.8.
 
I runned this query to fid the locks:
SELECT bl.pid          AS blocked_pid,
       a.usename       AS blocked_user,
       kl.pid          AS blocking_pid,
       ka.usename      AS blocking_user,
       a.current_query AS blocked_statement
FROM  pg_catalog.pg_locks        bl
JOIN pg_catalog.pg_stat_activity a  ON a.procpid = bl.pid
JOIN pg_catalog.pg_locks         kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid
JOIN pg_catalog.pg_stat_activity ka ON ka.procpid = kl.pid
WHERENOT bl.granted;
The result is a recursive lock.
Pid 10665 is blocked by pid 9844 and pid 9844 is blocked by 10665.
These 2 inserts are in 2 separate transactions.
Can this be a postgresql bug?
 
blocked_pid
blocked_user
blocking_statement
blocking_duration
blocking_pid
blocking_user
blocked_statement
blocked_duration
10665
postgres
INSERT INTO paraclinic_results VALUES (DEFAULT,1074986, 10.4, 15,15,0, now(), now(), NULL, null, null, NULL, NULL  )
00:47:33.995919
9844
postgres
INSERT INTO paraclinic_results VALUES (DEFAULT,1075033, 248.54, 10,17,0, now(), now(), NULL, null, null, NULL, NULL  )
00:37:36.175607
9844
postgres
INSERT INTO paraclinic_results VALUES (DEFAULT,1075033, 248.54, 10,17,0, now(), now(), NULL, null, null, NULL, NULL  )
00:37:36.175607
10665
postgres
INSERT INTO paraclinic_results VALUES (DEFAULT,1074986, 10.4, 15,15,0, now(), now(), NULL, null, null, NULL, NULL  )
00:47:33.995919
10665
postgres
<IDLE> in transaction
00:55:42.876538
9830
postgres
INSERT INTO paraclinic_results VALUES (DEFAULT,1075033, 248.54, 10,17,0, now(), now(), NULL, null, null, NULL, NULL  )
00:37:36.175607
10680
postgres
INSERT INTO paraclinic_results VALUES (DEFAULT,1075033, 248.54, 10,17,0, now(), now(), NULL, null, null, NULL, NULL  )
00:37:36.175607
10665
postgres
INSERT INTO paraclinic_results VALUES (DEFAULT,1075033, 248.54, 10,17,0, now(), now(), NULL, null, null, NULL, NULL  )
00:31:47.211123
9844
postgres
<IDLE> in transaction
00:55:42.876538
9830
postgres
INSERT INTO paraclinic_results VALUES (DEFAULT,1074986, 10.4, 15,15,0, now(), now(), NULL, null, null, NULL, NULL  )
00:47:33.995919
10706
postgres
INSERT INTO paraclinic_results VALUES (DEFAULT,1074986, 10.4, 15,15,0, now(), now(), NULL, null, null, NULL, NULL  )
00:47:33.995919
9844
postgres
INSERT INTO paraclinic_results VALUES (DEFAULT,1074986, 10.4, 15,15,0, now(), now(), NULL, null, null, NULL, NULL  )
00:18:45.763758
 
I never use LOCK command in my application.
All locks are made by postgresql.
I use transactional support a lot.
Can someoane give some advice about how can I prevent this locking?
 
Thank you.
 

So, did you check (in pg_stat_activity) what pid 9830 is doing, because looks like this session is holding other
sessions.
I don't see " recursive lock" in your query output.

Regards,
Igor Neyman

"Victor Sterpu" <victor@caido.ro> writes:
> I have a problem that it seems to be very hard to debug.
> Problem is from some postgresql locks. I use PostgreSQL 9.1.8.

You haven't actually explained what your problem is.

> I runned this query to fid the locks:
> SELECT bl.pid AS blocked_pid, a.usename AS blocked_user, kl.pid AS
> blocking_pid, ka.usename AS blocking_user, a.current_query AS
> blocked_statement FROM pg_catalog.pg_locks bl JOIN
> pg_catalog.pg_stat_activity a ON a.procpid = bl.pid JOIN
> pg_catalog.pg_locks kl ON kl.transactionid = bl.transactionid AND kl.pid
> != bl.pid JOIN pg_catalog.pg_stat_activity ka ON ka.procpid = kl.pid
> WHERENOT bl.granted;

This query proves little.  It might find two different transactions
waiting for the same transactionid, but it doesn't show that one is
waiting for the other.  They could both be waiting for some third
transaction.

            regards, tom lane

On Wed, Apr 2, 2014 at 10:19 AM, Victor Sterpu <victor@caido.ro> wrote:
>
> Hello
>
> I have a problem that it seems to be very hard to debug.
> Problem is from some postgresql locks. I use PostgreSQL 9.1.8.
>
> I runned this query to fid the locks:
>
> SELECT bl.pid          AS blocked_pid,
>        a.usename       AS blocked_user,
>        kl.pid          AS blocking_pid,
>        ka.usename      AS blocking_user,
>        a.current_query AS blocked_statement
> FROM  pg_catalog.pg_locks        bl
> JOIN pg_catalog.pg_stat_activity a  ON a.procpid = bl.pid
> JOIN pg_catalog.pg_locks         kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid
> JOIN pg_catalog.pg_stat_activity ka ON ka.procpid = kl.pid
> WHERENOT bl.granted;
>
> The result is a recursive lock.
> Pid 10665 is blocked by pid 9844 and pid 9844 is blocked by 10665.
> These 2 inserts are in 2 separate transactions.
> Can this be a postgresql bug?
>
> blocked_pid blocked_user blocking_statement blocking_duration blocking_pid blocking_user blocked_statement
blocked_duration

> 10665 postgres <IDLE> in transaction

"<IDLE> in transaction" is a locking red flag.  It means your
application has opened a transaction and is sitting there holding the
transaction open.  This is a very common cause of subtle application
locking bugs. It can be legit if the application is doing heavy
processing during a transaction or you simply raced to an idle
transaction in pg_stat_activity, but in my experience 95%+ of the time
it means transaction leakage which in turn leads to locking problems.

merlin

------ Original Message ------
From: "Merlin Moncure" <mmoncure@gmail.com>
To: "Victor Sterpu" <victor@caido.ro>
Cc: "PostgreSQL General" <pgsql-general@postgresql.org>
Sent: 4/2/2014 6:49:28 PM
Subject: Re: [GENERAL] Lock problem

>On Wed, Apr 2, 2014 at 10:19 AM, Victor Sterpu <victor@caido.ro> wrote:
>>
>>  Hello
>>
>>  I have a problem that it seems to be very hard to debug.
>>  Problem is from some postgresql locks. I use PostgreSQL 9.1.8.
>>
>>  I runned this query to fid the locks:
>>
>>  SELECT bl.pid AS blocked_pid,
>>         a.usename AS blocked_user,
>>         kl.pid AS blocking_pid,
>>         ka.usename AS blocking_user,
>>         a.current_query AS blocked_statement
>>  FROM pg_catalog.pg_locks bl
>>  JOIN pg_catalog.pg_stat_activity a ON a.procpid = bl.pid
>>  JOIN pg_catalog.pg_locks kl ON kl.transactionid = bl.transactionid
>>AND kl.pid != bl.pid
>>  JOIN pg_catalog.pg_stat_activity ka ON ka.procpid = kl.pid
>>  WHERENOT bl.granted;
>>
>>  The result is a recursive lock.
>>  Pid 10665 is blocked by pid 9844 and pid 9844 is blocked by 10665.
>>  These 2 inserts are in 2 separate transactions.
>>  Can this be a postgresql bug?
>>
>>  blocked_pid blocked_user blocking_statement blocking_duration
>>blocking_pid blocking_user blocked_statement blocked_duration
>
>>  10665 postgres <IDLE> in transaction
>
>"<IDLE> in transaction" is a locking red flag. It means your
>application has opened a transaction and is sitting there holding the
>transaction open. This is a very common cause of subtle application
>locking bugs. It can be legit if the application is doing heavy
>processing during a transaction or you simply raced to an idle
>transaction in pg_stat_activity, but in my experience 95%+ of the time
>it means transaction leakage which in turn leads to locking problems.
>
>merlin

This is weird because all of my transaction have commit or rollback. I
don't leave unterminated transactions.
I can't see how this is happening.
Is there a possibility that some network problems generate this problem?
If this is the case is these some server protection for this situation?

But why a unterminated transaction blocks all table operations?

------ Original Message ------
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Victor Sterpu" <victor@caido.ro>
Cc: pgsql-general@postgresql.org
Sent: 4/2/2014 6:31:13 PM
Subject: Re: [GENERAL] Lock problem

>"Victor Sterpu" <victor@caido.ro> writes:
>>  I have a problem that it seems to be very hard to debug.
>>  Problem is from some postgresql locks. I use PostgreSQL 9.1.8.
>
>You haven't actually explained what your problem is.
>
>>  I runned this query to fid the locks:
>>  SELECT bl.pid AS blocked_pid, a.usename AS blocked_user, kl.pid AS
>>  blocking_pid, ka.usename AS blocking_user, a.current_query AS
>>  blocked_statement FROM pg_catalog.pg_locks bl JOIN
>>  pg_catalog.pg_stat_activity a ON a.procpid = bl.pid JOIN
>>  pg_catalog.pg_locks kl ON kl.transactionid = bl.transactionid AND
>>kl.pid
>>  != bl.pid JOIN pg_catalog.pg_stat_activity ka ON ka.procpid = kl.pid
>>  WHERENOT bl.granted;
>
>This query proves little. It might find two different transactions
>waiting for the same transactionid, but it doesn't show that one is
>waiting for the other. They could both be waiting for some third
>transaction.
>
>    regards, tom lane

Problem is that my application is hanging because of this locks and I
can't point the problem.
What query would prove more?
All my transactions have commit or rollback.
I don't know how to fix or how to begin to find the problem

Thank you.

------ Original Message ------
From: "Igor Neyman" <ineyman@perceptron.com>
To: "Victor Sterpu" <victor@caido.ro>; "pgsql-general@postgresql.org"
<pgsql-general@postgresql.org>
Sent: 4/2/2014 6:29:17 PM
Subject: RE: [GENERAL] Lock problem

>
>
>From: pgsql-general-owner@postgresql.org
>[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Victor Sterpu
>Sent: Wednesday, April 02, 2014 11:19 AM
>To: pgsql-general@postgresql.org
>Subject: [GENERAL] Lock problem
>
>Hello
>
>I have a problem that it seems to be very hard to debug.
>Problem is from some postgresql locks. I use PostgreSQL 9.1.8.
>
>I runned this query to fid the locks:
>SELECT bl.pid AS blocked_pid,
>        a.usename AS blocked_user,
>        kl.pid AS blocking_pid,
>        ka.usename AS blocking_user,
>        a.current_query AS blocked_statement
>FROM pg_catalog.pg_locks bl
>JOIN pg_catalog.pg_stat_activity a ON a.procpid = bl.pid
>JOIN pg_catalog.pg_locks kl ON kl.transactionid = bl.transactionid AND
>kl.pid != bl.pid
>JOIN pg_catalog.pg_stat_activity ka ON ka.procpid = kl.pid
>WHERENOT bl.granted;
>The result is a recursive lock.
>Pid 10665 is blocked by pid 9844 and pid 9844 is blocked by 10665.
>These 2 inserts are in 2 separate transactions.
>Can this be a postgresql bug?
>
>blocked_pid
>blocked_user
>blocking_statement
>blocking_duration
>blocking_pid
>blocking_user
>blocked_statement
>blocked_duration
>10665
>postgres
>INSERT INTO paraclinic_results VALUES (DEFAULT,1074986, 10.4, 15,15,0,
>now(), now(), NULL, null, null, NULL, NULL )
>00:47:33.995919
>9844
>postgres
>INSERT INTO paraclinic_results VALUES (DEFAULT,1075033, 248.54,
>10,17,0, now(), now(), NULL, null, null, NULL, NULL )
>00:37:36.175607
>9844
>postgres
>INSERT INTO paraclinic_results VALUES (DEFAULT,1075033, 248.54,
>10,17,0, now(), now(), NULL, null, null, NULL, NULL )
>00:37:36.175607
>10665
>postgres
>INSERT INTO paraclinic_results VALUES (DEFAULT,1074986, 10.4, 15,15,0,
>now(), now(), NULL, null, null, NULL, NULL )
>00:47:33.995919
>10665
>postgres
><IDLE> in transaction
>00:55:42.876538
>9830
>postgres
>INSERT INTO paraclinic_results VALUES (DEFAULT,1075033, 248.54,
>10,17,0, now(), now(), NULL, null, null, NULL, NULL )
>00:37:36.175607
>10680
>postgres
>INSERT INTO paraclinic_results VALUES (DEFAULT,1075033, 248.54,
>10,17,0, now(), now(), NULL, null, null, NULL, NULL )
>00:37:36.175607
>10665
>postgres
>INSERT INTO paraclinic_results VALUES (DEFAULT,1075033, 248.54,
>10,17,0, now(), now(), NULL, null, null, NULL, NULL )
>00:31:47.211123
>9844
>postgres
><IDLE> in transaction
>00:55:42.876538
>9830
>postgres
>INSERT INTO paraclinic_results VALUES (DEFAULT,1074986, 10.4, 15,15,0,
>now(), now(), NULL, null, null, NULL, NULL )
>00:47:33.995919
>10706
>postgres
>INSERT INTO paraclinic_results VALUES (DEFAULT,1074986, 10.4, 15,15,0,
>now(), now(), NULL, null, null, NULL, NULL )
>00:47:33.995919
>9844
>postgres
>INSERT INTO paraclinic_results VALUES (DEFAULT,1074986, 10.4, 15,15,0,
>now(), now(), NULL, null, null, NULL, NULL )
>00:18:45.763758
>
>I never use LOCK command in my application.
>All locks are made by postgresql.
>I use transactional support a lot.
>Can someoane give some advice about how can I prevent this locking?
>
>Thank you.
>
>
>So, did you check (in pg_stat_activity) what pid 9830 is doing, because
>looks like this session is holding other sessions.
>I don't see " recursive lock" in your query output.
>
>Regards,
>Igor Neyman

No, I didn't look.
I will next time I have the problem.

Thank you.

On Wed, Apr 2, 2014 at 11:00 AM, Victor Sterpu <victor@caido.ro> wrote:
> All my transactions have commit or rollback.

Well, you have to verify that.  There's a couple of ways to do it.
Probably the most direct is to:
1. Make sure database is logging pids in log_line_prefix (this is a
good idea all around)
2. turn on all statement logging (be advised: this can eat a lot of
log space and slow down the server).

Those two changes do not require a restart.   A pg_ctl reload should
be sufficient.

Once you can do that, you should be able to locate database sessions
per pg_stat_activity that are 'idle in transaction' for a long time
without activity (anything over a second or so should be suspicious).
That will give the pid which you can then use to grep through the
statement log.

Common culprits are:
*) Dubious connection pooling solutions (php pconnect comes to mind)
*) Bad error handling logic in application (say, badly handled thrown exception)

merlin

I followed all your advice and it is obiuos that this log will show
exactly what I need to debug the situation.
Great tip, thank you.

------ Original Message ------
From: "Merlin Moncure" <mmoncure@gmail.com>
To: "Victor Sterpu" <victor@caido.ro>
Cc: "PostgreSQL General" <pgsql-general@postgresql.org>
Sent: 4/2/2014 7:08:08 PM
Subject: Re: [GENERAL] Lock problem

>On Wed, Apr 2, 2014 at 11:00 AM, Victor Sterpu <victor@caido.ro> wrote:
>>  All my transactions have commit or rollback.
>
>Well, you have to verify that. There's a couple of ways to do it.
>Probably the most direct is to:
>1. Make sure database is logging pids in log_line_prefix (this is a
>good idea all around)
>2. turn on all statement logging (be advised: this can eat a lot of
>log space and slow down the server).
>
>Those two changes do not require a restart. A pg_ctl reload should
>be sufficient.
>
>Once you can do that, you should be able to locate database sessions
>per pg_stat_activity that are 'idle in transaction' for a long time
>without activity (anything over a second or so should be suspicious).
>That will give the pid which you can then use to grep through the
>statement log.
>
>Common culprits are:
>*) Dubious connection pooling solutions (php pconnect comes to mind)
>*) Bad error handling logic in application (say, badly handled thrown
>exception)
>
>merlin

There may pass a few days or weeks until next lock.
But I don't  undesrtand why the whole table is locked if there is one
uncommited transaction.

The following scenario might be the cause:
1. Transaction is started
2. the client application is closed because of a power surge, the
started transaction will never be commited
3. from the server point of view there is a unfinished transaction that
will block future statements

Is this normal behaviour?


------ Original Message ------
From: "Victor Sterpu" <victor@caido.ro>
To: "Merlin Moncure" <mmoncure@gmail.com>
Cc: "PostgreSQL General" <pgsql-general@postgresql.org>
Sent: 4/2/2014 7:19:06 PM
Subject: Re: [GENERAL] Lock problem

>I followed all your advice and it is obiuos that this log will show
>exactly what I need to debug the situation.
>Great tip, thank you.
>
>------ Original Message ------
>From: "Merlin Moncure" <mmoncure@gmail.com>
>To: "Victor Sterpu" <victor@caido.ro>
>Cc: "PostgreSQL General" <pgsql-general@postgresql.org>
>Sent: 4/2/2014 7:08:08 PM
>Subject: Re: [GENERAL] Lock problem
>
>>On Wed, Apr 2, 2014 at 11:00 AM, Victor Sterpu <victor@caido.ro>
>>wrote:
>>>  All my transactions have commit or rollback.
>>
>>Well, you have to verify that. There's a couple of ways to do it.
>>Probably the most direct is to:
>>1. Make sure database is logging pids in log_line_prefix (this is a
>>good idea all around)
>>2. turn on all statement logging (be advised: this can eat a lot of
>>log space and slow down the server).
>>
>>Those two changes do not require a restart. A pg_ctl reload should
>>be sufficient.
>>
>>Once you can do that, you should be able to locate database sessions
>>per pg_stat_activity that are 'idle in transaction' for a long time
>>without activity (anything over a second or so should be suspicious).
>>That will give the pid which you can then use to grep through the
>>statement log.
>>
>>Common culprits are:
>>*) Dubious connection pooling solutions (php pconnect comes to mind)
>>*) Bad error handling logic in application (say, badly handled thrown
>>exception)
>>
>>merlin
>
>
>
>-- Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-general

I'm sure is not right, but is a there a server side solution for such
sitations?
A configuration - timeout for idle transactions.

------ Original Message ------
From: "Victor Sterpu" <victor@caido.ro>
To: "Victor Sterpu" <victor@caido.ro>; "Merlin Moncure"
<mmoncure@gmail.com>
Cc: "PostgreSQL General" <pgsql-general@postgresql.org>
Sent: 4/2/2014 9:13:22 PM
Subject: Re[2]: [GENERAL] Lock problem

>There may pass a few days or weeks until next lock.
>But I don't undesrtand why the whole table is locked if there is one
>uncommited transaction.
>
>The following scenario might be the cause:
>1. Transaction is started
>2. the client application is closed because of a power surge, the
>started transaction will never be commited
>3. from the server point of view there is a unfinished transaction that
>will block future statements
>
>Is this normal behaviour?
>
>
>------ Original Message ------
>From: "Victor Sterpu" <victor@caido.ro>
>To: "Merlin Moncure" <mmoncure@gmail.com>
>Cc: "PostgreSQL General" <pgsql-general@postgresql.org>
>Sent: 4/2/2014 7:19:06 PM
>Subject: Re: [GENERAL] Lock problem
>
>>I followed all your advice and it is obiuos that this log will show
>>exactly what I need to debug the situation.
>>Great tip, thank you.
>>
>>------ Original Message ------
>>From: "Merlin Moncure" <mmoncure@gmail.com>
>>To: "Victor Sterpu" <victor@caido.ro>
>>Cc: "PostgreSQL General" <pgsql-general@postgresql.org>
>>Sent: 4/2/2014 7:08:08 PM
>>Subject: Re: [GENERAL] Lock problem
>>
>>>On Wed, Apr 2, 2014 at 11:00 AM, Victor Sterpu <victor@caido.ro>
>>>wrote:
>>>>  All my transactions have commit or rollback.
>>>
>>>Well, you have to verify that. There's a couple of ways to do it.
>>>Probably the most direct is to:
>>>1. Make sure database is logging pids in log_line_prefix (this is a
>>>good idea all around)
>>>2. turn on all statement logging (be advised: this can eat a lot of
>>>log space and slow down the server).
>>>
>>>Those two changes do not require a restart. A pg_ctl reload should
>>>be sufficient.
>>>
>>>Once you can do that, you should be able to locate database sessions
>>>per pg_stat_activity that are 'idle in transaction' for a long time
>>>without activity (anything over a second or so should be suspicious).
>>>That will give the pid which you can then use to grep through the
>>>statement log.
>>>
>>>Common culprits are:
>>>*) Dubious connection pooling solutions (php pconnect comes to mind)
>>>*) Bad error handling logic in application (say, badly handled thrown
>>>exception)
>>>
>>>merlin
>>
>>
>>
>>-- Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>>To make changes to your subscription:
>>http://www.postgresql.org/mailpref/pgsql-general
>

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.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 "IDLE IN
TRANSACTION"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.

Regards,
Igor Neyman

On 02 Apr 2014, at 20:13, Victor Sterpu <victor@caido.ro> wrote:

> There may pass a few days or weeks until next lock.
> But I don't  undesrtand why the whole table is locked if there is one uncommited transaction.
>
> The following scenario might be the cause:
> 1. Transaction is started
> 2. the client application is closed because of a power surge, the started transaction will never be commited
> 3. from the server point of view there is a unfinished transaction that will block future statements
>
> Is this normal behaviour?

I’m pretty sure that with such a powersurge the connection gets closed, causing the transaction to roll back.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

On Wed, Apr 2, 2014 at 3:01 PM, Igor Neyman <ineyman@perceptron.com> wrote:
>
>
>> -----Original Message-----
>> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
>> owner@postgresql.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
suspectsession/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