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@gallo.ucsf.edu

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

"Tena Sakai" <tsakai@gallo.ucsf.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