Skip site navigation (1) Skip section navigation (2)

Re: How would I "close" a atble?

From: "Tena Sakai" <tsakai(at)gallo(dot)ucsf(dot)edu>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-admin(at)postgresql(dot)org>
Subject: Re: How would I "close" a atble?
Date: 2007-12-18 03:03:44
Message-ID: FE44E0D7EAD2ED4BB2165071DB8E328C03062E2E@egcrc-ex01.egcrc.org (view raw or flat)
Thread:
Lists: pgsql-admin
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

In response to

Responses

pgsql-admin by date

Next:From: Benjamin KrajmalnikDate: 2007-12-18 04:09:42
Subject: Re: Looking for an E/R tool for Postgres
Previous:From: Alvaro HerreraDate: 2007-12-18 01:05:11
Subject: Re: Dump database more than 1 flat file

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group