select for update & lock contention

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;

Responses

Browse pgsql-general by date

  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