select for update & lock contention

Поиск
Список
Период
Сортировка
От Ed L.
Тема select for update & lock contention
Дата
Msg-id 200405052242.38873.pgsql@bluepolka.net
обсуждение исходный текст
Ответы 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>)
Список 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 по дате отправления:

Предыдущее
От: Bruce Momjian
Дата:
Сообщение: Re: error code with psql
Следующее
От: "Ed L."
Дата:
Сообщение: Re: select for update & lock contention