Re: select for update & lock contention

This is on 7.3.4/7.3.6.  Thx.

On Wednesday May 5 2004 10:42, Ed L. wrote:
> 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;