From: | "Ed L(dot)" <pgsql(at)bluepolka(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | select for update & lock contention |
Date: | 2004-05-06 04:42:38 |
Message-ID: | 200405052242.38873.pgsql@bluepolka.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I think I'm seeing table-level lock contention in the following function
when I have many different concurrent callers, each with mutually distinct
values for $1. Is there a way to reimplement this function using
select-for-update (or equivalent) in order to get a row-level lock (and
thus less contention) while maintaining the function interface? The docs
seem to suggest so, but it's not clear how to return the SETOF queued_item
and also use select-for-update to get the row-level locks. TIA.
CREATE OR REPLACE FUNCTION getqueuedupdates (character)
RETURNS SETOF queued_item AS '
DECLARE
rows record;
BEGIN
FOR rows IN SELECT * FROM queued_item where subscriber=$1 LOOP
RETURN NEXT rows;
DELETE FROM queued_item WHERE key=rows.key;
END LOOP;
RETURN;
END;'
LANGUAGE plpgsql;
From | Date | Subject | |
---|---|---|---|
Next Message | Razvan Surdulescu | 2004-05-06 04:55:54 | Re: Cache lookup failure for pg_restore? |
Previous Message | Jan Wieck | 2004-05-06 03:30:11 | Re: [GENERAL] cache lookup of relation 165058647 failed |