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> |
Subject: | Re: Lock problem |
Date: | 2014-04-02 16:08:08 |
Message-ID: | CAHyXU0ywZAmQ+1RfE8ntQQ6Q_Vq58HCJxtr4s0MLyEwChKH=nw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Victor Sterpu | 2014-04-02 16:19:06 | Re: Lock problem |
Previous Message | Victor Sterpu | 2014-04-02 16:00:41 | Re: Lock problem |