X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 3A64314B281A for ; Tue, 10 Aug 2004 11:48:58 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 07206-07 for ; Tue, 10 Aug 2004 14:48:44 +0000 (GMT) Received: from web13126.mail.yahoo.com (web13126.mail.yahoo.com [216.136.174.163]) by svr1.postgresql.org (Postfix) with SMTP id 9155314B2825 for ; Tue, 10 Aug 2004 11:48:37 -0300 (ADT) Message-ID: <20040810144838.72955.qmail@web13126.mail.yahoo.com> Received: from [63.78.248.48] by web13126.mail.yahoo.com via HTTP; Tue, 10 Aug 2004 07:48:38 PDT Date: Tue, 10 Aug 2004 07:48:38 -0700 (PDT) From: Litao Wu Subject: Re: insert waits for delete with trigger To: Tom Lane Cc: pgsql-performance@postgresql.org In-Reply-To: <24197.1092112893@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/121 X-Sequence-Number: 7757 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 | q_20040810 | 488 | RowExclusiveLock | t | 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 | pg_locks | 7660 | AccessShareLock | t | pg_database | 7660 | AccessShareLock | t | pg_class | 7660 | AccessShareLock | t | pg_stat_activity | 7660 | AccessShareLock | t | pg_class_oid_index | 7660 | AccessShareLock | t | 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 wrote: > Litao Wu 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