From: | Bob Lunney <blunney(at)meetme(dot)com> |
---|---|
To: | Ciprian Grigoras <ciprian(dot)grigoras(at)vitals(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Postgres processes getting stuck (bug?) |
Date: | 2016-04-29 17:39:39 |
Message-ID: | 56E1377C-961F-477C-99C3-A683800A04DF@meetme.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Check the wait state for the backend process. I’ve seen this happen when a process ran a per-row trigger that tried to connect to something else, and it filled the ip_conntrack table. So, kernel level wait along with a whole bunch of locks on the table in question.
Running pg_terminate_backend() didn’t work, as the signal queued behind the kernel wait. We had to bounce the database to get rid of the problem. Immediately afterwards we disabled the trigger.
HTH,
Bob Lunney
> On Apr 29, 2016, at 1:30 PM, Ciprian Grigoras <ciprian(dot)grigoras(at)vitals(dot)com> wrote:
>
> Hi guys,
>
> I had a question. We're running Postgresql 9.0.7 , and all of a sudden we started seeing unexpected behavior. One table got locked after we ran one simple INSERT on one test item.
> Nothing else was running against that table as far as we know, and the query is frozen there now. After some time we tried to terminate it forcefully (pg_terminate_backend), ran that and the return of the command is "true" but the query still stays on, shows up on pg_stat_activity etc.
> Reading from the table is fine, but we believe any other inserts / updates are not possible.
>
> Restarting the Postgresql server is not an option at this moment, since it's a production box and another high-demand database is running from that.
>
> Has anyone seen a similar issue (maybe a bug in the 9.0 version ?) where simple statements don't finish and get locked there and can't be killed by the pg_terminate_backed ? What is the cause of this ?
> Any reasonable way to find out more details on what caused this, how to prevent it in the future, and how it can be fixed sensitively now ? Thanks !
>
> just fyi, checking the data in the "pg_locks" (for that process that is frozen), shows a bunch of rows all with the same virtualtransactionid. Only one of them has the mode of "ExclusiveLock" (the only record there with a locktype of "virtualxid"), a few have the "RowExclusiveLock" mode and the vast majority have the "AccessShareLock" mode.
>
> Thanks,
> Ciprian
From | Date | Subject | |
---|---|---|---|
Next Message | Dustin Kempter | 2016-04-29 18:07:59 | Primary Keys |
Previous Message | Ciprian Grigoras | 2016-04-29 17:30:34 | Postgres processes getting stuck (bug?) |