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;