Thread: "slow" queries

From:
Brian Cox
Date:

Actually, they're all deadlocked. The question is why?

Here's a brief background. The ts_defects table is partitioned by
occurrence date; each partition contains the rows for 1 day. When the
data gets old enough, the partition is dropped. Since the correct
partition can be determined from the occurrence date, there is no
trigger: inserts are done directly into the correct partition. Multiple
threads may be inserting into a partition at the same time. The thread
that checks for old data to be dropped runs at 00:30 each night. It also
creates the partition for the next day.

Below is the output from:
select xact_start,query_start,substring(current_query from 0 for 40)
from pg_stat_activity order by xact_start;

run at 18:40 on 28 Feb 2009 (i.e. these queries have been running for
 > 6 hours). The 1st select is not on any of the ts_defect partitions
nor is the CREATE VIEW. The SELECT's shown last are not (directly)
generated by the java program that is running the drop table, inserts,
the 1st select and the CREATE VIEW.

Thanks for your ideas,
Brian


  2009-02-28 00:30:00.01572-08  | 2009-02-28 00:30:00.015758-08 | drop
table ts_defects_20090225
  2009-02-28 00:30:00.693353-08 | 2009-02-28 00:30:00.69337-08  | select
transetdef0_.ts_id as ts1_85_0_,
  2009-02-28 00:30:01.875671-08 | 2009-02-28 00:30:01.875911-08 | insert
into ts_defects_20090228 (ts_id,
  2009-02-28 00:30:01.875673-08 | 2009-02-28 00:30:01.875911-08 | insert
into ts_defects_20090228 (ts_id,
  2009-02-28 00:30:01.875907-08 | 2009-02-28 00:30:01.87611-08  | insert
into ts_defects_20090228 (ts_id,
  2009-02-28 00:30:01.87615-08  | 2009-02-28 00:30:01.876334-08 | insert
into ts_defects_20090228 (ts_id,
  2009-02-28 00:30:01.87694-08  | 2009-02-28 00:30:01.877153-08 | insert
into ts_defects_20090228 (ts_id,
  2009-02-28 00:30:01.876952-08 | 2009-02-28 00:30:01.877171-08 | insert
into ts_defects_20090228 (ts_id,
  2009-02-28 00:30:01.876965-08 | 2009-02-28 00:30:01.87716-08  | insert
into ts_defects_20090228 (ts_id,
  2009-02-28 00:30:01.877267-08 | 2009-02-28 00:30:01.877483-08 | insert
into ts_defects_20090228 (ts_id,
  2009-02-28 00:30:01.877928-08 | 2009-02-28 00:30:01.878101-08 | insert
into ts_defects_20090228 (ts_id,
  2009-02-28 00:30:06.822733-08 | 2009-02-28 00:30:06.822922-08 | insert
into ts_defects_20090228 (ts_id,
  2009-02-28 01:01:00.95051-08  | 2009-02-28 01:01:00.950605-08 | CREATE
VIEW TranSetGroupSlaPerformanceA
  2009-02-28 09:12:33.181039-08 | 2009-02-28 09:12:33.181039-08 | SELECT
c.oid, c.relname, pg_get_userbyi
  2009-02-28 09:19:47.335621-08 | 2009-02-28 09:19:47.335621-08 | SELECT
c.oid, c.relname, pg_get_userbyi
  2009-02-28 10:52:36.638467-08 | 2009-02-28 10:52:36.638467-08 | SELECT
c.oid, c.relname, pg_get_userbyi
  2009-02-28 11:01:05.023126-08 | 2009-02-28 11:01:05.023126-08 | SELECT
c.oid, c.relname, pg_get_userbyi

From:
Robert Haas
Date:

On Sat, Feb 28, 2009 at 9:51 PM, Brian Cox <> wrote:
> Actually, they're all deadlocked. The question is why?
>
> Here's a brief background. The ts_defects table is partitioned by occurrence
> date; each partition contains the rows for 1 day. When the data gets old
> enough, the partition is dropped. Since the correct partition can be
> determined from the occurrence date, there is no trigger: inserts are done
> directly into the correct partition. Multiple threads may be inserting into
> a partition at the same time. The thread that checks for old data to be
> dropped runs at 00:30 each night. It also creates the partition for the next
> day.
>
> Below is the output from:
> select xact_start,query_start,substring(current_query from 0 for 40) from
> pg_stat_activity order by xact_start;

Can you post this again with procpid added to the column list and
without truncating current_query?  And then also post the results of
"select * from pg_locks"?

Is there anything interesting in the postmaster log?

...Robert

From:
Tom Lane
Date:

Brian Cox <> writes:
> Actually, they're all deadlocked. The question is why?

Probably because the DROP is trying to acquire exclusive lock on its
target table, and some other transaction already has a read or write
lock on that table, and everything else is queuing up behind the DROP.

It's not a true deadlock that is visible to the database, or else
Postgres would have failed enough of the transactions to remove the
deadlock.  Rather, what you've got is some very-long-running transaction
that is still making progress, or else is sitting idle because its
client is neglecting to close it; and everything else is blocked behind
that.

If it is not clear to you exactly who is waiting for what, a look into
the pg_locks view might help.

            regards, tom lane

From:
"Cox, Brian"
Date:


>Probably because the DROP is trying to acquire exclusive lock on its
>target table, and some other transaction already has a read or write
>lock on that table, and everything else is queuing up behind the DROP.

>It's not a true deadlock that is visible to the database, or else
>Postgres would have failed enough of the transactions to remove the
>deadlock.  Rather, what you've got is some very-long-running transaction
>that is still making progress, or else is sitting idle because its
>client is neglecting to close it; and everything else is blocked behind
>that.

This "deadlock" finished after 18h and 48m. As there is only 1 select
on a table with 400 rows and 10 inserts into a separate partition than
the one being dropped, what could possible take 18:48 to do?

I also don't understand why inserts into a separate partition or a select on
an unrelated table should cause any locks on the table being dropped in
the 1st place. I assume that the CREATE VIEW, which started 1 hour
after the DROP, can't possibly be the cause of this "deadlock".

Brian

From:
Tom Lane
Date:

"Cox, Brian" <> writes:
>> Probably because the DROP is trying to acquire exclusive lock on its
>> target table, and some other transaction already has a read or write
>> lock on that table, and everything else is queuing up behind the DROP.

>> It's not a true deadlock that is visible to the database, or else
>> Postgres would have failed enough of the transactions to remove the
>> deadlock.  Rather, what you've got is some very-long-running transaction
>> that is still making progress, or else is sitting idle because its
>> client is neglecting to close it; and everything else is blocked behind
>> that.

> This "deadlock" finished after 18h and 48m. As there is only 1 select
> on a table with 400 rows and 10 inserts into a separate partition than
> the one being dropped, what could possible take 18:48 to do?

[ shrug... ]  You tell us.  To me it sounds a whole lot like some client
program sitting on an open transaction that has a nonexclusive lock on
the table to be dropped.  That transaction wasn't necessarily doing any
useful work; it might have just been waiting on the client.

At this point I suppose arguing about it is moot because the evidence
is all gone.  If it happens again, capture the contents of pg_locks and
pg_stat_activity while things are still stuck.

            regards, tom lane

From:
Brian Cox
Date:

Tom Lane [] wrote:
> [ shrug... ]  You tell us.  To me it sounds a whole lot like some client
> program sitting on an open transaction that has a nonexclusive lock on
> the table to be dropped.  That transaction wasn't necessarily doing any
> useful work; it might have just been waiting on the client.

I wish I could... And, in any event, aren't all transactions listed in
the pg_stat_activity select?

> At this point I suppose arguing about it is moot because the evidence
> is all gone.  If it happens again, capture the contents of pg_locks and
> pg_stat_activity while things are still stuck.

This happened again last night. This time I'd added a lock (in the java
code) to prevent inserts into other partitions of ts_defects while the
drop is in progress. Below is the output from:
select xact_start,datid,datname,procpid,usesysid,substring(current_query
from 0 for 40),waiting,client_addr from  pg_stat_activity order by
xact_start;

and

select locktype,database,relation,virtualxid,virtualtransaction,pid,mode
from pg_locks order by mode;

As you can see there are only 3 transactions and 1 starts 1 hour after
the drop begins. I'm still trying to figure out how to interpret the
pg_locks output, but (presumably) you/others on this forum have more
experience at this than I.

Thanks,
Brian



cemdb=> select
xact_start,datid,datname,procpid,usesysid,substring(current_query from 0
for 40),waiting,client_addr from  pg_stat_activity order by xact_start;
           xact_start           |  datid   | datname | procpid |
usesysid |                substring                | waiting |  client_addr

-------------------------------+----------+---------+---------+----------+-----------------------------------------+---------+----------------
  2009-03-01 14:10:42.606592-08 | 26472437 | cemdb   |   13833 |
16392 | <IDLE> in transaction                   | f       | 130.200.164.15
  2009-03-02 00:30:00.039977-08 | 26472437 | cemdb   |   13842 |
16392 | drop table ts_defects_20090227          | t       | 127.0.0.1
  2009-03-02 00:30:00.066728-08 | 26472437 | cemdb   |   13865 |
16392 | select transetdef0_.ts_id as ts1_85_0_, | t       | 127.0.0.1
  2009-03-02 01:01:00.992486-08 | 26472437 | cemdb   |   13840 |
16392 | CREATE VIEW TranSetGroupSlaPerformanceA | t       | 127.0.0.1
  2009-03-02 10:16:21.252969-08 | 26472437 | cemdb   |   29985 |
16392 | select xact_start,datid,datname,procpid | f       |
                                | 26472437 | cemdb   |   13735 |
16392 | <IDLE>                                  | f       | 127.0.0.1
                                | 26472437 | cemdb   |   13744 |
16392 | <IDLE>                                  | f       | 127.0.0.1
                                | 26472437 | cemdb   |   13857 |
16392 | <IDLE>                                  | f       | 127.0.0.1
                                | 26472437 | cemdb   |   13861 |
16392 | <IDLE>                                  | f       | 127.0.0.1
                                | 26472437 | cemdb   |   13864 |
16392 | <IDLE>                                  | f       | 127.0.0.1
                                | 26472437 | cemdb   |   13855 |
16392 | <IDLE>                                  | f       | 127.0.0.1
                                | 26472437 | cemdb   |   13740 |
16392 | <IDLE>                                  | f       | 127.0.0.1
(12 rows)

cemdb=> select
locktype,database,relation,virtualxid,virtualtransaction,pid,mode from
pg_locks order by mode;
locktype    | database | relation | virtualxid | virtualtransaction |
pid  |        mode
---------------+----------+----------+------------+--------------------+-------+---------------------
  relation      | 26472437 | 26592616 |            | 15/69749
| 13842 | AccessExclusiveLock
  relation      | 26472437 | 26592608 |            | 15/69749
| 13842 | AccessExclusiveLock
  relation      | 26472437 | 26592615 |            | 15/69749
| 13842 | AccessExclusiveLock
  relation      | 26472437 | 26592613 |            | 15/69749
| 13842 | AccessExclusiveLock
  relation      | 26472437 | 26472508 |            | 15/69749
| 13842 | AccessExclusiveLock
  relation      | 26472437 | 26493706 |            | 11/131
| 13833 | AccessShareLock
  relation      | 26472437 | 26473141 |            | 11/131
| 13833 | AccessShareLock
  relation      | 26472437 |    10969 |            | 1/77414
| 29985 | AccessShareLock
  relation      | 26472437 | 26473176 |            | 11/131
| 13833 | AccessShareLock
  relation      | 26472437 | 26493307 |            | 11/131
| 13833 | AccessShareLock
  relation      | 26472437 | 26493271 |            | 11/131
| 13833 | AccessShareLock
  relation      | 26472437 | 26493704 |            | 11/131
| 13833 | AccessShareLock
  relation      | 26472437 | 26493711 |            | 11/131
| 13833 | AccessShareLock
  relation      | 26472437 |     2674 |            | 15/69749
| 13842 | AccessShareLock
  relation      | 26472437 | 26493279 |            | 11/131
| 13833 | AccessShareLock
  relation      | 26472437 | 26473227 |            | 11/131
| 13833 | AccessShareLock
  relation      | 26472437 | 26493705 |            | 11/131
| 13833 | AccessShareLock
  relation      | 26472437 | 26472869 |            | 14/70049
| 13840 | AccessShareLock
  relation      | 26472437 | 26493306 |            | 11/131
| 13833 | AccessShareLock
  relation      | 26472437 | 26493712 |            | 11/131
| 13833 | AccessShareLock
  relation      | 26472437 | 26472508 |            | 11/131
| 13833 | AccessShareLock
  relation      | 26472437 | 26493709 |            | 11/131
| 13833 | AccessShareLock
  relation      | 26472437 | 26472508 |            | 14/70049
| 13840 | AccessShareLock
  relation      | 26472437 | 26472595 |            | 11/131
| 13833 | AccessShareLock
  relation      | 26472437 | 26493269 |            | 11/131
| 13833 | AccessShareLock
  relation      | 26472437 | 26493710 |            | 11/131
| 13833 | AccessShareLock
  relation      | 26472437 |     2702 |            | 15/69749
| 13842 | AccessShareLock
  relation      | 26472437 | 26493267 |            | 11/131
| 13833 | AccessShareLock
  relation      | 26472437 | 26493700 |            | 11/131
| 13833 | AccessShareLock
  relation      | 26472437 | 26472508 |            | 29/69612
| 13865 | AccessShareLock
  relation      | 26472437 | 26493259 |            | 11/131
| 13833 | AccessShareLock
  relation      | 26472437 | 26493103 |            | 11/131
| 13833 | AccessShareLock
  virtualxid    |          |          | 14/70049   | 14/70049
| 13840 | ExclusiveLock
  transactionid |          |          |            | 15/69749
| 13842 | ExclusiveLock
  virtualxid    |          |          | 29/69612   | 29/69612
| 13865 | ExclusiveLock
  virtualxid    |          |          | 15/69749   | 15/69749
| 13842 | ExclusiveLock
  virtualxid    |          |          | 1/77414    | 1/77414
| 29985 | ExclusiveLock
  virtualxid    |          |          | 11/131     | 11/131
| 13833 | ExclusiveLock
  relation      | 26472437 |     2620 |            | 15/69749
| 13842 | RowExclusiveLock
  relation      | 26472437 |     2608 |            | 15/69749
| 13842 | RowExclusiveLock
(40 rows)

From:
Scott Carey
Date:

In my experience, 13833, “<IDLE> in transaction” is your culprit.  It is a transaction that has been there for 10 hours longer than all others, and is doing nothing at all.  It has locks on a lot of objects in there.  You’ll have to take the oid’s in the lock table and look them up in the pg_class table to figure out what those are.  Alternatively, the procpid (13833) may be all you need to track down the user or program that needs to have a talking-to.

Something as dumb as:

Open psql
BEGIN;
// do some stuff

//.. Go home with the terminal open, get some dinner, go to sleep
//.. Wake up, dorp the kids off at school
//.. Arrive at work, get some coffee
//.. Realize your psql terminal is open and close it

Can be your culprit.  
Common culprits are applications that don’t open and close their transactions properly when errors occur and pool connections forever.

Pg_locks, even on a really busy db, should not have that many locks in the view.  If there are a lot, and they aren’t ‘churning and changing’ then you have some long running transactions.  
The pid column in the locks table corresponds with procpid in the activity table.  The ‘relation’ column in the lock table corresponds with stuff in pg_class.



On 3/2/09 10:22 AM, "Brian Cox" <> wrote:

Tom Lane [] wrote:
> [ shrug... ]  You tell us.  To me it sounds a whole lot like some client
> program sitting on an open transaction that has a nonexclusive lock on
> the table to be dropped.  That transaction wasn't necessarily doing any
> useful work; it might have just been waiting on the client.

I wish I could... And, in any event, aren't all transactions listed in
the pg_stat_activity select?

> At this point I suppose arguing about it is moot because the evidence
> is all gone.  If it happens again, capture the contents of pg_locks and
> pg_stat_activity while things are still stuck.

This happened again last night. This time I'd added a lock (in the java
code) to prevent inserts into other partitions of ts_defects while the
drop is in progress. Below is the output from:
select xact_start,datid,datname,procpid,usesysid,substring(current_query
from 0 for 40),waiting,client_addr from  pg_stat_activity order by
xact_start;

and

select locktype,database,relation,virtualxid,virtualtransaction,pid,mode
from pg_locks order by mode;

As you can see there are only 3 transactions and 1 starts 1 hour after
the drop begins. I'm still trying to figure out how to interpret the
pg_locks output, but (presumably) you/others on this forum have more
experience at this than I.

Thanks,
Brian



cemdb=> select
xact_start,datid,datname,procpid,usesysid,substring(current_query from 0
for 40),waiting,client_addr from  pg_stat_activity order by xact_start;
           xact_start           |  datid   | datname | procpid |
usesysid |                substring                | waiting |  client_addr
-------------------------------+----------+---------+---------+----------+-----------------------------------------+---------+----------------
  2009-03-01 14:10:42.606592-08 | 26472437 | cemdb   |   13833 |
16392 | <IDLE> in transaction                   | f       | 130.200.164.15
  2009-03-02 00:30:00.039977-08 | 26472437 | cemdb   |   13842 |
16392 | drop table ts_defects_20090227          | t       | 127.0.0.1
  2009-03-02 00:30:00.066728-08 | 26472437 | cemdb   |   13865 |
16392 | select transetdef0_.ts_id as ts1_85_0_, | t       | 127.0.0.1
  2009-03-02 01:01:00.992486-08 | 26472437 | cemdb   |   13840 |
16392 | CREATE VIEW TranSetGroupSlaPerformanceA | t       | 127.0.0.1
  2009-03-02 10:16:21.252969-08 | 26472437 | cemdb   |   29985 |
16392 | select xact_start,datid,datname,procpid | f       |
                                | 26472437 | cemdb   |   13735 |
16392 | <IDLE>                                  | f       | 127.0.0.1
                                | 26472437 | cemdb   |   13744 |
16392 | <IDLE>                                  | f       | 127.0.0.1
                                | 26472437 | cemdb   |   13857 |
16392 | <IDLE>                                  | f       | 127.0.0.1
                                | 26472437 | cemdb   |   13861 |
16392 | <IDLE>                                  | f       | 127.0.0.1
                                | 26472437 | cemdb   |   13864 |
16392 | <IDLE>                                  | f       | 127.0.0.1
                                | 26472437 | cemdb   |   13855 |
16392 | <IDLE>                                  | f       | 127.0.0.1
                                | 26472437 | cemdb   |   13740 |
16392 | <IDLE>                                  | f       | 127.0.0.1
(12 rows)

cemdb=> select
locktype,database,relation,virtualxid,virtualtransaction,pid,mode from
pg_locks order by mode;
locktype    | database | relation | virtualxid | virtualtransaction |
pid  |        mode
---------------+----------+----------+------------+--------------------+-------+---------------------
  relation      | 26472437 | 26592616 |            | 15/69749
| 13842 | AccessExclusiveLock
  relation      | 26472437 | 26592608 |            | 15/69749
| 13842 | AccessExclusiveLock
  relation      | 26472437 | 26592615 |            | 15/69749
| 13842 | AccessExclusiveLock
  relation      | 26472437 | 26592613 |            | 15/69749
| 13842 | AccessExclusiveLock
  relation      | 26472437 | 26472508 |            | 15/69749
| 13842 | AccessExclusiveLock
  relation      | 26472437 | 26493706 |            | 11/131
| 13833 | AccessShareLock
  relation      | 26472437 | 26473141 |            | 11/131
| 13833 | AccessShareLock
  relation      | 26472437 |    10969 |            | 1/77414
| 29985 | AccessShareLock
  relation      | 26472437 | 26473176 |            | 11/131
| 13833 | AccessShareLock
  relation      | 26472437 | 26493307 |            | 11/131
| 13833 | AccessShareLock
  relation      | 26472437 | 26493271 |            | 11/131
| 13833 | AccessShareLock
  relation      | 26472437 | 26493704 |            | 11/131
| 13833 | AccessShareLock
  relation      | 26472437 | 26493711 |            | 11/131
| 13833 | AccessShareLock
  relation      | 26472437 |     2674 |            | 15/69749
| 13842 | AccessShareLock
  relation      | 26472437 | 26493279 |            | 11/131
| 13833 | AccessShareLock
  relation      | 26472437 | 26473227 |            | 11/131
| 13833 | AccessShareLock
  relation      | 26472437 | 26493705 |            | 11/131
| 13833 | AccessShareLock
  relation      | 26472437 | 26472869 |            | 14/70049
| 13840 | AccessShareLock
  relation      | 26472437 | 26493306 |            | 11/131
| 13833 | AccessShareLock
  relation      | 26472437 | 26493712 |            | 11/131
| 13833 | AccessShareLock
  relation      | 26472437 | 26472508 |            | 11/131
| 13833 | AccessShareLock
  relation      | 26472437 | 26493709 |            | 11/131
| 13833 | AccessShareLock
  relation      | 26472437 | 26472508 |            | 14/70049
| 13840 | AccessShareLock
  relation      | 26472437 | 26472595 |            | 11/131
| 13833 | AccessShareLock
  relation      | 26472437 | 26493269 |            | 11/131
| 13833 | AccessShareLock
  relation      | 26472437 | 26493710 |            | 11/131
| 13833 | AccessShareLock
  relation      | 26472437 |     2702 |            | 15/69749
| 13842 | AccessShareLock
  relation      | 26472437 | 26493267 |            | 11/131
| 13833 | AccessShareLock
  relation      | 26472437 | 26493700 |            | 11/131
| 13833 | AccessShareLock
  relation      | 26472437 | 26472508 |            | 29/69612
| 13865 | AccessShareLock
  relation      | 26472437 | 26493259 |            | 11/131
| 13833 | AccessShareLock
  relation      | 26472437 | 26493103 |            | 11/131
| 13833 | AccessShareLock
  virtualxid    |          |          | 14/70049   | 14/70049
| 13840 | ExclusiveLock
  transactionid |          |          |            | 15/69749
| 13842 | ExclusiveLock
  virtualxid    |          |          | 29/69612   | 29/69612
| 13865 | ExclusiveLock
  virtualxid    |          |          | 15/69749   | 15/69749
| 13842 | ExclusiveLock
  virtualxid    |          |          | 1/77414    | 1/77414
| 29985 | ExclusiveLock
  virtualxid    |          |          | 11/131     | 11/131
| 13833 | ExclusiveLock
  relation      | 26472437 |     2620 |            | 15/69749
| 13842 | RowExclusiveLock
  relation      | 26472437 |     2608 |            | 15/69749
| 13842 | RowExclusiveLock
(40 rows)

--
Sent via pgsql-performance mailing list ()
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

From:
Robert Haas
Date:

On Mon, Mar 2, 2009 at 1:22 PM, Brian Cox <> wrote:
> As you can see there are only 3 transactions and 1 starts 1 hour after
> the drop begins. I'm still trying to figure out how to interpret the
> pg_locks output, but (presumably) you/others on this forum have more
> experience at this than I.

I'm rather suspicious of that line that says <IDLE> in transaction.
Connections that are idle, but in a transaction, can be holding locks.
 And since they are idle, things can stay that way for a very long
time... hours, days...  coincidentally, that idle-in-transaction
procpid is holding AccessShareLocks on a whole boatload of relations.

It's a little hard to decode this output because the "relation" column
from pg_locks is an OID, and we don't know what relation it
represents.  It's helpful to cast that column to "regclass": select
locktype,database,relation::regclass,virtualxid,virtualtransaction,pid,mode
from pg_locks order by mode;

For now, though, try this:

select oid, relname from pg_class where relname like 'ts_defects%';

I suspect you'll find that the oid of the table that you were trying
to drop is one of the ones on which the idle in transaction process is
holding an AccessShareLock on...

...Robert

From:
Tom Lane
Date:

Brian Cox <> writes:
> select locktype,database,relation,virtualxid,virtualtransaction,pid,mode
> from pg_locks order by mode;

If you hadn't left out the "granted" column we could be more sure,
but what it looks like to me is the DROP (pid 13842) is stuck behind
the <IDLE> transaction (pid 13833).  In particular these two rows of
pg_locks look like a possible conflict:

>   relation      | 26472437 | 26472508 |            | 15/69749
> | 13842 | AccessExclusiveLock

>   relation      | 26472437 | 26472508 |            | 11/131
> | 13833 | AccessShareLock

            regards, tom lane

From:
Brian Cox
Date:

Tom Lane [] wrote:
> If you hadn't left out the "granted" column we could be more sure,
> but what it looks like to me is the DROP (pid 13842) is stuck behind
> the <IDLE> transaction (pid 13833).  In particular these two rows of
> pg_locks look like a possible conflict:
>
>  >   relation      | 26472437 | 26472508 |            | 15/69749
>  > | 13842 | AccessExclusiveLock
>
>  >   relation      | 26472437 | 26472508 |            | 11/131
>  > | 13833 | AccessShareLock

select c.oid,c.relname,l.pid,l.mode,l.granted from pg_class c join
pg_locks l on c.oid=l.relation order by l.pid;

26472508 | ts_transets  | 13833 | AccessShareLock     | t
26472508 | ts_transets  | 13842 | AccessExclusiveLock | f

pid 13833 is the idle transaction and 13842 is doing the drop table.

So, the idle transaction is the problem. Thanks to you, Scott Carey and
Robert Haas for pointing this out. However, why does the drop of
ts_defects_20090227 need exclusive access to ts_transets? I assume it
must be due to this FK?

alter table ts_defects_20090227 add constraint FK34AA2B629DADA24
foreign key (ts_transet_id) references ts_transets;


Thanks again,
Brian

From:
Tom Lane
Date:

Brian Cox <> writes:
> So, the idle transaction is the problem. Thanks to you, Scott Carey and
> Robert Haas for pointing this out. However, why does the drop of
> ts_defects_20090227 need exclusive access to ts_transets? I assume it
> must be due to this FK?

> alter table ts_defects_20090227 add constraint FK34AA2B629DADA24
> foreign key (ts_transet_id) references ts_transets;

Well, that's certainly a sufficient reason, if perhaps not the only
reason.  Dropping ts_defects_20090227 will require removal of FK triggers
on ts_transets, and we can't do that concurrently with transactions that
might be trying to fire those triggers.

Now admittedly, it would probably be sufficient to take ExclusiveLock
rather than AccessExclusiveLock when removing triggers, since we do not
have triggers ON SELECT.  Right now though, we just take
AccessExclusiveLock for most any DDL on a table.  There was a patch
submitted last fall to reduce DDL locking in some cases, but it hasn't
been reworked to fix the problems that were pointed out (and I
disremember if it addressed DROP TRIGGER in particular anyway).

            regards, tom lane

From:
Brian Cox
Date:

Tom Lane [] wrote:
> Well, that's certainly a sufficient reason, if perhaps not the only
> reason.  Dropping ts_defects_20090227 will require removal of FK triggers
> on ts_transets, and we can't do that concurrently with transactions that
> might be trying to fire those triggers.
>
> Now admittedly, it would probably be sufficient to take ExclusiveLock
> rather than AccessExclusiveLock when removing triggers, since we do not
> have triggers ON SELECT.  Right now though, we just take
> AccessExclusiveLock for most any DDL on a table.  There was a patch
> submitted last fall to reduce DDL locking in some cases, but it hasn't
> been reworked to fix the problems that were pointed out (and I
> disremember if it addressed DROP TRIGGER in particular anyway).
>
>                         regards, tom lane

Thanks for furthering my understanding of postgres (and probably other
SQL servers as well). I can fix this problem easily.

Brian



From:
Tim Bunce
Date:

On Mon, Mar 02, 2009 at 02:29:31PM -0500, Tom Lane wrote:
> Brian Cox <> writes:
> > select locktype,database,relation,virtualxid,virtualtransaction,pid,mode
> > from pg_locks order by mode;
>
> If you hadn't left out the "granted" column we could be more sure,
> but what it looks like to me is the DROP (pid 13842) is stuck behind
> the <IDLE> transaction (pid 13833).  In particular these two rows of
> pg_locks look like a possible conflict:
>
> >   relation      | 26472437 | 26472508 |            | 15/69749
> > | 13842 | AccessExclusiveLock
>
> >   relation      | 26472437 | 26472508 |            | 11/131
> > | 13833 | AccessShareLock

Would it be possible to write a stored procedure that would read
pg_locks, and other relevant tables, and list what's blocking what
in a simplified form?

Tim.

From:
Scott Marlowe
Date:

On Mon, Mar 2, 2009 at 2:24 PM, Tim Bunce <> wrote:
> On Mon, Mar 02, 2009 at 02:29:31PM -0500, Tom Lane wrote:
>> Brian Cox <> writes:
>> > select locktype,database,relation,virtualxid,virtualtransaction,pid,mode
>> > from pg_locks order by mode;
>>
>> If you hadn't left out the "granted" column we could be more sure,
>> but what it looks like to me is the DROP (pid 13842) is stuck behind
>> the <IDLE> transaction (pid 13833).  In particular these two rows of
>> pg_locks look like a possible conflict:
>>
>> >   relation      | 26472437 | 26472508 |            | 15/69749
>> > | 13842 | AccessExclusiveLock
>>
>> >   relation      | 26472437 | 26472508 |            | 11/131
>> > | 13833 | AccessShareLock
>
> Would it be possible to write a stored procedure that would read
> pg_locks, and other relevant tables, and list what's blocking what
> in a simplified form?

I'm sure a query could use just connect by from tablefuncs, or WITH
under 8.4 and get it.