pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
insert waits for delete with trigger
6+ messages / 2 participants
[nested] [flat]

* insert waits for delete with trigger
@ 2004-08-10 00:05 Litao Wu <litaowu@yahoo.com>
  2004-08-10 00:31 ` Re: insert waits for delete with trigger Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 6+ messages in thread

From: Litao Wu @ 2004-08-10 00:05 UTC (permalink / raw)
  To: pgsql-performance

Hi all,

We have table q_20040805 and a delete trigger on
it. The delete trigger is: 
update table q_summary set count=count-1...

When we delete from q_20040805, we also insert into
related info q_process within the same 
transaction. There is a PK on q_process, but no
trigger on it. No FK on either of the 3 tables.

Here is info from pg_lock:
      relname      |  pid  |       mode       |
granted | current_query                               
                
-------------------+-------+------------------+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 q_process  | 14643 | RowExclusiveLock | t       |
DELETE FROM q_20040805  WHERE domain_id='20237906' AND
module='spam'
 q_summary  | 14643 | RowExclusiveLock | t       |
DELETE FROM q_20040805  WHERE domain_id='20237906' AND
module='spam'
 q_20040805 | 14643 | RowExclusiveLock | t       |
DELETE FROM q_20040805  WHERE domain_id='20237906' AND
module='spam'
 q_process  | 18951 | RowExclusiveLock | t       |
INSERT INTO q_process (...) SELECT ... FROM q_20040805
 WHERE domain_id='20237906' AND module='spam'



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: insert waits for delete with trigger
  2004-08-10 00:05 insert waits for delete with trigger Litao Wu <litaowu@yahoo.com>
@ 2004-08-10 00:31 ` Tom Lane <tgl@sss.pgh.pa.us>
  2004-08-10 04:35   ` Re: insert waits for delete with trigger Litao Wu <litaowu@yahoo.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Tom Lane @ 2004-08-10 00:31 UTC (permalink / raw)
  To: Litao Wu <litaowu@yahoo.com>; +Cc: pgsql-performance

Litao Wu <litaowu@yahoo.com> writes:
> Here is info from pg_lock:

All those locks are already granted, so they are not much help in
understanding what PID 18951 is waiting for.  What row does it have
with granted = 'f' ?

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: insert waits for delete with trigger
  2004-08-10 00:05 insert waits for delete with trigger Litao Wu <litaowu@yahoo.com>
  2004-08-10 00:31 ` Re: insert waits for delete with trigger Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-08-10 04:35   ` Litao Wu <litaowu@yahoo.com>
  2004-08-10 04:41     ` Re: insert waits for delete with trigger Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 6+ messages in thread

From: Litao Wu @ 2004-08-10 04:35 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance

Hi Tom, 

No row has granted='f'.
The result shown in the original email is from:
select c.relname, l.pid, l.mode, l.granted,
current_query
from pg_locks l, pg_class c, pg_stat_activity a
where relation is not null
  AND l.relation = c.oid
  AND l.pid = a.procpid
  AND l.mode != 'AccessShareLock'
order by l.pid;

After the above result, I went to OS
to get ps status.

Did I miss something?

Since the lock was granted to pid (18951), that
cause me confuse why OS ps shows it is waiting.

Also, I ntoiced that insert will be finished 
almost immediately after delete is done.

Thanks,


--- Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Litao Wu <litaowu@yahoo.com> writes:
> > Here is info from pg_lock:
> 
> All those locks are already granted, so they are not
> much help in
> understanding what PID 18951 is waiting for.  What
> row does it have
> with granted = 'f' ?
> 
> 			regards, tom lane
> 
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: don't forget to increase your free space map
> settings
> 



		
__________________________________
Do you Yahoo!?
Yahoo! Mail is new and improved - Check it out!
http://promotions.yahoo.com/new_mail



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: insert waits for delete with trigger
  2004-08-10 00:05 insert waits for delete with trigger Litao Wu <litaowu@yahoo.com>
  2004-08-10 00:31 ` Re: insert waits for delete with trigger Tom Lane <tgl@sss.pgh.pa.us>
  2004-08-10 04:35   ` Re: insert waits for delete with trigger Litao Wu <litaowu@yahoo.com>
@ 2004-08-10 04:41     ` Tom Lane <tgl@sss.pgh.pa.us>
  2004-08-10 14:48       ` Re: insert waits for delete with trigger Litao Wu <litaowu@yahoo.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Tom Lane @ 2004-08-10 04:41 UTC (permalink / raw)
  To: Litao Wu <litaowu@yahoo.com>; +Cc: pgsql-performance

Litao Wu <litaowu@yahoo.com> writes:
> Did I miss something?

Your join omits all transaction locks.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: insert waits for delete with trigger
  2004-08-10 00:05 insert waits for delete with trigger Litao Wu <litaowu@yahoo.com>
  2004-08-10 00:31 ` Re: insert waits for delete with trigger Tom Lane <tgl@sss.pgh.pa.us>
  2004-08-10 04:35   ` Re: insert waits for delete with trigger Litao Wu <litaowu@yahoo.com>
  2004-08-10 04:41     ` Re: insert waits for delete with trigger Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-08-10 14:48       ` Litao Wu <litaowu@yahoo.com>
  2004-08-10 16:53         ` Re: insert waits for delete with trigger Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 6+ messages in thread

From: Litao Wu @ 2004-08-10 14:48 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: insert waits for delete with trigger
  2004-08-10 00:05 insert waits for delete with trigger Litao Wu <litaowu@yahoo.com>
  2004-08-10 00:31 ` Re: insert waits for delete with trigger Tom Lane <tgl@sss.pgh.pa.us>
  2004-08-10 04:35   ` Re: insert waits for delete with trigger Litao Wu <litaowu@yahoo.com>
  2004-08-10 04:41     ` Re: insert waits for delete with trigger Tom Lane <tgl@sss.pgh.pa.us>
  2004-08-10 14:48       ` Re: insert waits for delete with trigger Litao Wu <litaowu@yahoo.com>
@ 2004-08-10 16:53         ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 6+ messages in thread

From: Tom Lane @ 2004-08-10 16:53 UTC (permalink / raw)
  To: Litao Wu <litaowu@yahoo.com>; +Cc: pgsql-performance

Litao Wu <litaowu@yahoo.com> writes:
> 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;

You can't join to pg_class without eliminating the transaction lock rows
(because they have NULLs in the relation field).

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2004-08-10 16:53 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-08-10 00:05 insert waits for delete with trigger Litao Wu <litaowu@yahoo.com>
2004-08-10 00:31 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-08-10 04:35   ` Litao Wu <litaowu@yahoo.com>
2004-08-10 04:41     ` Tom Lane <tgl@sss.pgh.pa.us>
2004-08-10 14:48       ` Litao Wu <litaowu@yahoo.com>
2004-08-10 16:53         ` Tom Lane <tgl@sss.pgh.pa.us>

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