pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHow would I "close" a atble?
8+ messages / 2 participants
[nested] [flat]
* How would I "close" a atble?
@ 2007-12-17 23:38 Tena Sakai <tsakai@gallo.ucsf.edu>
2007-12-18 00:11 ` Re: How would I "close" a atble? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Tena Sakai @ 2007-12-17 23:38 UTC (permalink / raw)
To: pgsql-admin
Hi Everybody,
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. When I replace * with amy column name, it
makes no difference. It does not respond, unless I
issue ctrl-C. I have checked with all other tables
and they are appear healthy.
I suspect for some reason the table got "opened" and
whoever opened, never closed it. (This is my grasping-
the-straw theory.) I have been using this database
pretty actively for last 3 months and this never
happened before. Is there any way to explicitly close
this table?
The only other solution I can think of would be to
shutdown and restarting the database, which is what
I would like to avoid, if possible.
Any suggestions are welcome.
Regards,
Tena Sakai
tsakai@gallo.ucsf.edu
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How would I "close" a atble?
2007-12-17 23:38 How would I "close" a atble? Tena Sakai <tsakai@gallo.ucsf.edu>
@ 2007-12-18 00:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-12-18 00:39 ` Re: How would I "close" a atble? Tena Sakai <tsakai@gallo.ucsf.edu>
2007-12-18 03:03 ` Re: How would I "close" a atble? Tena Sakai <tsakai@gallo.ucsf.edu>
0 siblings, 2 replies; 8+ messages in thread
From: Tom Lane @ 2007-12-18 00:11 UTC (permalink / raw)
To: Tena Sakai <tsakai@gallo.ucsf.edu>; +Cc: pgsql-admin
"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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How would I "close" a atble?
2007-12-17 23:38 How would I "close" a atble? Tena Sakai <tsakai@gallo.ucsf.edu>
2007-12-18 00:11 ` Re: How would I "close" a atble? Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-12-18 00:39 ` Tena Sakai <tsakai@gallo.ucsf.edu>
1 sibling, 0 replies; 8+ messages in thread
From: Tena Sakai @ 2007-12-18 00:39 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
Thank you, Tom!
> Some other session holding an exclusive lock on the table, perhaps?
That is exactly the case.
I see what I must do. Many thanks.
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How would I "close" a atble?
2007-12-17 23:38 How would I "close" a atble? Tena Sakai <tsakai@gallo.ucsf.edu>
2007-12-18 00:11 ` Re: How would I "close" a atble? Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-12-18 03:03 ` Tena Sakai <tsakai@gallo.ucsf.edu>
2007-12-18 04:25 ` Re: How would I "close" a atble? Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 8+ messages in thread
From: Tena Sakai @ 2007-12-18 03:03 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: 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@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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How would I "close" a atble?
2007-12-17 23:38 How would I "close" a atble? Tena Sakai <tsakai@gallo.ucsf.edu>
2007-12-18 00:11 ` Re: How would I "close" a atble? Tom Lane <tgl@sss.pgh.pa.us>
2007-12-18 03:03 ` Re: How would I "close" a atble? Tena Sakai <tsakai@gallo.ucsf.edu>
@ 2007-12-18 04:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-12-18 06:25 ` Re: How would I "close" a atble? Tena Sakai <tsakai@gallo.ucsf.edu>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2007-12-18 04:25 UTC (permalink / raw)
To: Tena Sakai <tsakai@gallo.ucsf.edu>; +Cc: pgsql-admin
"Tena Sakai" <tsakai@gallo.ucsf.edu> writes:
> 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
The deal here is that the REINDEX is blocked trying to get exclusive
lock on table 16496, while those other guys are (apparently) stacked
up behind it. What you have not shown us is what transaction has
actually *got* a lock on 16496.
> 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?
kill -INT would probably be safe enough, but you should first look into
what is blocking the REINDEX from going through.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How would I "close" a atble?
2007-12-17 23:38 How would I "close" a atble? Tena Sakai <tsakai@gallo.ucsf.edu>
2007-12-18 00:11 ` Re: How would I "close" a atble? Tom Lane <tgl@sss.pgh.pa.us>
2007-12-18 03:03 ` Re: How would I "close" a atble? Tena Sakai <tsakai@gallo.ucsf.edu>
2007-12-18 04:25 ` Re: How would I "close" a atble? Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-12-18 06:25 ` Tena Sakai <tsakai@gallo.ucsf.edu>
2007-12-18 06:43 ` Re: How would I "close" a atble? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Tena Sakai @ 2007-12-18 06:25 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
Hi Tom,
> What you have not shown us is what transaction has
> actually *got* a lock on 16496.
Would you mind enlightening me as to how I can do so?
I stared at the output from pg_locks a long time and
just can't get more than what I had gotten out of.
Regards,
Tena Sakai
tsakai@gallo.ucsf.edu
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Mon 12/17/2007 8:25 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:
> 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
The deal here is that the REINDEX is blocked trying to get exclusive
lock on table 16496, while those other guys are (apparently) stacked
up behind it. What you have not shown us is what transaction has
actually *got* a lock on 16496.
> 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?
kill -INT would probably be safe enough, but you should first look into
what is blocking the REINDEX from going through.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How would I "close" a atble?
2007-12-17 23:38 How would I "close" a atble? Tena Sakai <tsakai@gallo.ucsf.edu>
2007-12-18 00:11 ` Re: How would I "close" a atble? Tom Lane <tgl@sss.pgh.pa.us>
2007-12-18 03:03 ` Re: How would I "close" a atble? Tena Sakai <tsakai@gallo.ucsf.edu>
2007-12-18 04:25 ` Re: How would I "close" a atble? Tom Lane <tgl@sss.pgh.pa.us>
2007-12-18 06:25 ` Re: How would I "close" a atble? Tena Sakai <tsakai@gallo.ucsf.edu>
@ 2007-12-18 06:43 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-12-18 07:20 ` Re: How would I "close" a atble? Tena Sakai <tsakai@gallo.ucsf.edu>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2007-12-18 06:43 UTC (permalink / raw)
To: Tena Sakai <tsakai@gallo.ucsf.edu>; +Cc: pgsql-admin
"Tena Sakai" <tsakai@gallo.ucsf.edu> writes:
>> What you have not shown us is what transaction has
>> actually *got* a lock on 16496.
> Would you mind enlightening me as to how I can do so?
Are there no other rows in pg_locks that reference relation 16496?
None of the ones you showed us had granted=t, but there must be one
unless things are much more broken than I think.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How would I "close" a atble?
2007-12-17 23:38 How would I "close" a atble? Tena Sakai <tsakai@gallo.ucsf.edu>
2007-12-18 00:11 ` Re: How would I "close" a atble? Tom Lane <tgl@sss.pgh.pa.us>
2007-12-18 03:03 ` Re: How would I "close" a atble? Tena Sakai <tsakai@gallo.ucsf.edu>
2007-12-18 04:25 ` Re: How would I "close" a atble? Tom Lane <tgl@sss.pgh.pa.us>
2007-12-18 06:25 ` Re: How would I "close" a atble? Tena Sakai <tsakai@gallo.ucsf.edu>
2007-12-18 06:43 ` Re: How would I "close" a atble? Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-12-18 07:20 ` Tena Sakai <tsakai@gallo.ucsf.edu>
0 siblings, 0 replies; 8+ messages in thread
From: Tena Sakai @ 2007-12-18 07:20 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
Hi Tom,
Here it is!
locktype | relation | transactionid | transaction | mode | pid | granted
---------------+----------+---------------+-------------+---------------------+--------+---------
relation | 16496 | | 147195029 | AccessShareLock | 22833 | t
I kept looking at the ones I emailed to you which
omitted a few rows from the bottom. That was dumb!
I issued "kill -INT 22833" and in a few minutes
pg_locks view shrunk to 2 rows from 41 rows.
Many thanks for following up with this problem.
Regards,
Tena Sakai
tsakai@gallo.ucsf.edu
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Mon 12/17/2007 10:43 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:
>> What you have not shown us is what transaction has
>> actually *got* a lock on 16496.
> Would you mind enlightening me as to how I can do so?
Are there no other rows in pg_locks that reference relation 16496?
None of the ones you showed us had granted=t, but there must be one
unless things are much more broken than I think.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2007-12-18 07:20 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-12-17 23:38 How would I "close" a atble? Tena Sakai <tsakai@gallo.ucsf.edu>
2007-12-18 00:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-12-18 00:39 ` Tena Sakai <tsakai@gallo.ucsf.edu>
2007-12-18 03:03 ` Tena Sakai <tsakai@gallo.ucsf.edu>
2007-12-18 04:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-12-18 06:25 ` Tena Sakai <tsakai@gallo.ucsf.edu>
2007-12-18 06:43 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-12-18 07:20 ` Tena Sakai <tsakai@gallo.ucsf.edu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox