Red Maple <redmapleleaf@xxxxxxxxx> wrote: > Here is my function. If I comment out the update then it would run > all the cores, if not then only one core will run.... > CREATE OR REPLACE FUNCTION > [...] > select sysuptime > into this_sysuptime > from ap_sysuptime > where ap_id = this_id > for update; > > -- ================================================== > -- >>>>>>>> if I comment out the next update > -- >>>>>>>> then all cores will be running, > -- >>>>>>>> else only one core will be running > -- ================================================== > update ap_sysuptime > set sysuptime = this_sysuptime, > last_contacted = now() > where ap_id = this_id; This proves that you're not showing us the important part. The update locks the same row previously locked by the SELECT FOR UPDATE, so any effect at the row level would be a serialization failure based on a write conflict, which doesn't sound like your problem. They get different locks at the table level, though: http://www.postgresql.org/docs/9.0/interactive/explicit-locking.html#LOCKING-TABLES Somewhere in code you're not showing us you're acquiring a lock on the ap_sysuptime table which conflicts with a ROW EXCLUSIVE lock but not with a ROW SHARE lock. The lock types which could do that are SHARE and SHARE ROW EXCLUSIVE. CREATE INDEX (without CONCURRENTLY) could do that; otherwise it seems that you would need to be explicitly issuing a LOCK statement at one of these levels somewhere in your transaction. That is what is causing the transactions to run one at a time. -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
