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
