Thanks everyone, it could be that the Linux server is problematic (it's got a long uptime).
I ran the strace thing,
and it kept waiting there with minimal information, nothing else was shown...
|
On Fri, Apr 29, 2016 at 2:33 PM, Jerry Sievers <gsievers19@xxxxxxxxxxx> wrote:
Ciprian Grigoras <ciprian.grigoras@xxxxxxxxxx> writes:
> 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.
What do you get from strace -p $pid?
--
> 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
>
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@xxxxxxxxxxx
p: 312.241.7800
| Ciprian Grigoras Software Engineer P : 201.459.6275 | M : 845.517.8710 |
