Hi Tom,
By a bit of poking around, I am discovering interesting
things, but I am a bit lost... (Before I go on, I should
state that I am running postgres 8.2.4 on RedHat Linux with
Dell hardware.)
Here's what I did:
I issued: select locktype, relation, transactionid, transaction, mode, pid, granted
from pg_locks
order by pid, relation;
and it returned bunch of lines like:
locktype | relation | transactionid | transaction | mode | pid | granted
---------------+----------+---------------+-------------+---------------------+-------+---------
relation | 16490 | | 147605258 | ShareLock | 6697 | t
relation | 16496 | | 147605258 | AccessExclusiveLock | 6697 | f
transactionid | | 147605258 | 147605258 | ExclusiveLock | 6697 | t
relation | 10328 | | 147611506 | AccessShareLock | 7988 | t
transactionid | | 147611506 | 147611506 | ExclusiveLock | 7988 | t
relation | 16490 | | 147611384 | AccessShareLock | 12930 | t
relation | 16496 | | 147611384 | AccessShareLock | 12930 | f
relation | 16517 | | 147611384 | AccessShareLock | 12930 | t
transactionid | | 147611384 | 147611384 | ExclusiveLock | 12930 | t
relation | 16490 | | 147605344 | AccessShareLock | 18177 | t
relation | 16496 | | 147605344 | AccessShareLock | 18177 | f
relation | 16517 | | 147605344 | AccessShareLock | 18177 | t
transactionid | | 147605344 | 147605344 | ExclusiveLock | 18177 | t
relation | 16490 | | 147611403 | AccessShareLock | 18825 | t
relation | 16496 | | 147611403 | AccessShareLock | 18825 | f
relation | 16517 | | 147611403 | AccessShareLock | 18825 | t
That pid 6697 caught my interest and I fished it out of "ps -ef":
postgres 6697 4916 0 Dec15 ? 00:19:45 postgres: postgres canon [local] REINDEX waiting
(and for the sake of completeness...)
postgres 12930 4916 0 11:24 ? 00:00:00 postgres: gbrush canon 172.16.1.106(37819) SELECT waiting
postgres 18177 4916 0 Dec15 ? 00:00:02 postgres: gbrush canon 172.16.1.106(53874) SELECT waiting
postgres 18825 4916 0 12:55 ? 00:00:00 postgres: tsakai canon 127.0.0.1(44558) SELECT waiting
This (pid 6697) is a cron job from wee hour Saturday and it
is part of vacuuming script. I have used this particular
script to do the vacuuming for a few weeks and never caused
hanging like this before. I could kill this process from unix
level, but before I do so, would you please comment as to why
this might have happened and what repercussion might I have
from killing it, if any?
Thank you.
Regards,
Tena Sakai
tsakai@xxxxxxxxxxxxxx
-----Original Message-----
From: Tom Lane [mailto:tgl@xxxxxxxxxxxxx]
Sent: Mon 12/17/2007 4:11 PM
To: Tena Sakai
Cc: pgsql-admin@xxxxxxxxxxxxxx
Subject: Re: How would I "close" a atble?
"Tena Sakai" <tsakai@xxxxxxxxxxxxxx> writes:
> I have a weired situation. Out of 17 tables which
> comprise a database, one of them refuses to talk to
> me. When I issue:
> select * from marker;
> it hangs.
Some other session holding an exclusive lock on the table, perhaps?
Have you looked into pg_locks for matches to that table?
regards, tom lane
