select for update & lock contention - Mailing list pgsql-general

From Ed L.
Subject select for update & lock contention
Date
Msg-id 200405052242.38873.pgsql@bluepolka.net
Whole thread Raw
Responses Re: select for update & lock contention  ("Ed L." <pgsql@bluepolka.net>)
Re: select for update & lock contention  ("Ed L." <pgsql@bluepolka.net>)
Re: select for update & lock contention  (Tom Lane <tgl@sss.pgh.pa.us>)
List 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;



pgsql-general by date:

Previous
From: Bruce Momjian
Date:
Subject: Re: error code with psql
Next
From: "Ed L."
Date:
Subject: Re: select for update & lock contention