Обсуждение: insert waits for delete with trigger
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'
From ps command, it is easy to see another
insert is waiting:
ps -elfww|grep 18951
040 S postgres 18951 870 0 69 0 - 81274
semtim 16:34 ? 00:00:00 postgres: postgres mxl
xxx.xxx.x.xxx:49986 INSERT waiting
ps -elfww|grep 14643
040 S postgres 14643 870 79 70 0 - 81816
semtim 15:56 ? 00:44:02 postgres: postgres mxl
xxx.xxx.x.xxx:47236 DELETE
I do not understand why process 18951 (insert)
is waiting (subqery SELECT of INSERT INTO
is not a problem as I know)
PG version is: 7.3.2
Can someone explain?
Thanks,
__________________________________
Do you Yahoo!?
New and Improved Yahoo! Mail - Send 10MB messages!
http://promotions.yahoo.com/new_mail
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
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
Litao Wu <litaowu@yahoo.com> writes:
> Did I miss something?
Your join omits all transaction locks.
regards, tom lane
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
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