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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
