Re: [PERFORM] 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 [EMAIL PROTECTED] wrote: Litao Wu [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] insert waits for delete with trigger
Litao Wu [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] insert waits for delete with trigger
Litao Wu [EMAIL PROTECTED] 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
Re: [PERFORM] insert waits for delete with trigger
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 [EMAIL PROTECTED] wrote: Litao Wu [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] insert waits for delete with trigger
Litao Wu [EMAIL PROTECTED] writes: Did I miss something? Your join omits all transaction locks. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
