Thread: "slow" queries
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
On Sat, Feb 28, 2009 at 9:51 PM, Brian Cox <brian.cox@ca.com> 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
Brian Cox <brian.cox@ca.com> 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
>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
"Cox, Brian" <Brian.Cox@ca.com> 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
Tom Lane [tgl@sss.pgh.pa.us] 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)
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" <brian.cox@ca.com> wrote:
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" <brian.cox@ca.com> wrote:
Tom Lane [tgl@sss.pgh.pa.us] 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 (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
On Mon, Mar 2, 2009 at 1:22 PM, Brian Cox <brian.cox@ca.com> 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
Brian Cox <brian.cox@ca.com> 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
Tom Lane [tgl@sss.pgh.pa.us] 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
Brian Cox <brian.cox@ca.com> 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
Tom Lane [tgl@sss.pgh.pa.us] 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
On Mon, Mar 02, 2009 at 02:29:31PM -0500, Tom Lane wrote: > Brian Cox <brian.cox@ca.com> 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.
On Mon, Mar 2, 2009 at 2:24 PM, Tim Bunce <Tim.Bunce@pobox.com> wrote: > On Mon, Mar 02, 2009 at 02:29:31PM -0500, Tom Lane wrote: >> Brian Cox <brian.cox@ca.com> 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.