Re: How would I "close" a atble?

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(at)gallo(dot)ucsf(dot)edu

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Mon 12/17/2007 4:11 PM
To: Tena Sakai
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: [ADMIN] How would I "close" a atble?

"Tena Sakai" <tsakai(at)gallo(dot)ucsf(dot)edu> 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