Re: 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(at)gmail(dot)com>
To: "Victor Sterpu" <victor(at)caido(dot)ro>
Cc: "PostgreSQL General" <pgsql-general(at)postgresql(dot)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(at)caido(dot)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