Re: insert waits for delete with trigger

Thank you.

How about:

select c.relname, l.pid, l.mode, l.granted,
a.current_query
from pg_locks l, pg_class c, pg_stat_activity a
where
l.relation = c.oid
AND l.pid = a.procpid
order by l.granted, l.pid;

relname | pid |
mode | granted |

current_query

-----------------------------------+-------+------------------+---------+-----------------------------------------------
------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------
q_20040810 | 488 | AccessShareLock
| t | <IDLE>
q_20040810 | 488 | RowExclusiveLock
| t | <IDLE>
q_process | 3729 | AccessShareLock
| t | DELETE FROM q_20040805 WHERE domain_id
='2005761066' AND module='spam'
q_process | 3729 | RowExclusiveLock
| t | DELETE FROM q_20040805 WHERE domain_id
='2005761066' AND module='spam'
q_20040805 | 3729 | AccessShareLock
| t | DELETE FROM q_20040805 WHERE domain_id
='2005761066' AND module='spam'
q_20040805 | 3729 | RowExclusiveLock
| t | DELETE FROM q_20040805 WHERE domain_id
='2005761066' AND module='spam'
q_summary | 3729 | AccessShareLock
| t | DELETE FROM q_20040805 WHERE domain_id
='2005761066' AND module='spam'
q_summary | 3729 | RowExclusiveLock
| t | DELETE FROM q_20040805 WHERE domain_id
='2005761066' AND module='spam'
q_summary_did_dir_idx | 3729 | AccessShareLock
| t | DELETE FROM q_20040805 WHERE domain_id
='2005761066' AND module='spam'
pg_shadow | 7660 |
AccessShareLock | t | <IDLE>
pg_locks | 7660 |
AccessShareLock | t | <IDLE>
pg_database | 7660 |
AccessShareLock | t | <IDLE>
pg_class | 7660 |
AccessShareLock | t | <IDLE>
pg_stat_activity | 7660 |
AccessShareLock | t | <IDLE>
pg_class_oid_index | 7660 |
AccessShareLock | t | <IDLE>
q_process | 8593 | AccessShareLock
| t | DELETE FROM q_20040810 WHERE domain_id
='2002300623' AND module='spam'
q_process | 8593 | RowExclusiveLock
| t | DELETE FROM q_20040810 WHERE domain_id
='2002300623' AND module='spam'
q_20040810 | 8593 | AccessShareLock
| t | DELETE FROM q_20040810 WHERE domain_id
='2002300623' AND module='spam'
q_20040810 | 8593 | RowExclusiveLock
| t | DELETE FROM q_20040810 WHERE domain_id
='2002300623' AND module='spam'
q_summary | 8593 | AccessShareLock
| t | DELETE FROM q_20040810 WHERE domain_id
='2002300623' AND module='spam'
q_summary | 8593 | RowExclusiveLock
| t | DELETE FROM q_20040810 WHERE domain_id
='2002300623' AND module='spam'
q_summary_did_dir_idx | 8593 | AccessShareLock
| t | DELETE FROM q_20040810 WHERE domain_id
='2002300623' AND module='spam'
q_process | 19027 | AccessShareLock
| t | INSERT INTO q_process (...) SELECT ...
FROM q_20040805 WHERE domain_id='2005761066' AND
module='spam'
q_process | 19027 | RowExclusiveLock
| t | INSERT INTO q_process (...) SELECT ...
FROM q_20040805 WHERE domain_id='2005761066' AND
module='spam'
q_20040805 | 19027 | AccessShareLock
| t | INSERT INTO q_process (...) SELECT ...
FROM q_20040805 WHERE domain_id='2005761066' AND
module='spam'
q_did_mod_dir_20040805_idx | 19027 | AccessShareLock
| t | INSERT INTO q_process (...) SELECT ...
FROM q_20040805 WHERE domain_id='2005761066' AND
module='spam'
(26 rows)

ps -elfww|grep 19027
040 S postgres 19027 870 1 69 0 - 81290
semtim 07:31 ? 00:00:51 postgres: postgres mxl
192.168.0.177:38266 INSERT waiting

--- Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Litao Wu <litaowu(at)yahoo(dot)com> writes:
> > Did I miss something?
>
> Your join omits all transaction locks.
>
> regards, tom lane
>

__________________________________
Do you Yahoo!?
Yahoo! Mail - 50x more storage than other providers!
http://promotions.yahoo.com/new_mail