select for update & lock contention

Поиск
Список
Период
Сортировка
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 по дате отправления: