Re: "slow" queries

Tom Lane [tgl(at)sss(dot)pgh(dot)pa(dot)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)