pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Litao Wu <litaowu@yahoo.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: insert waits for delete with trigger
Date: Tue, 10 Aug 2004 07:48:38 -0700 (PDT)
Message-ID: <20040810144838.72955.qmail@web13126.mail.yahoo.com> (raw)
In-Reply-To: <24197.1092112893@sss.pgh.pa.us>

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@sss.pgh.pa.us> wrote:

> Litao Wu <litaowu@yahoo.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



view thread (6+ messages)  latest in thread

Message-ID: <20040810144838.72955.qmail@web13126.mail.yahoo.com>
Permalink:  ../20040810144838.72955.qmail@web13126.mail.yahoo.com/
Also on:    postgresql.org/message-id/20040810144838.72955.qmail@web13126.mail.yahoo.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: litaowu@yahoo.com, tgl@sss.pgh.pa.us
  Subject: Re: insert waits for delete with trigger
  In-Reply-To: <20040810144838.72955.qmail@web13126.mail.yahoo.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox