Re: select for update & lock contention

On Thursday May 6 2004 6:11, Tom Lane wrote:
> "Ed L." <pgsql@bluepolka.net> writes:
> > I think I'm seeing table-level lock contention in the following
> > function
>
> I think you're barking up the wrong tree entirely.  There's nothing in
> that function that would acquire a conflicting table lock.
>
> I'm wondering about foreign key lock contention, myself.  Look to what
> the DELETE must do.

We've dropped all foreign key constraints on the queued_item table and moved
the delete out of the loop as follows...


DECLARE
    rows record;
BEGIN
    PERFORM * FROM queued_item WHERE subscriber = $1 FOR UPDATE OF
queued_item;
    RAISE NOTICE 'getupdates(%):going to call select', $1;
    FOR rows IN SELECT * FROM queued_item where subscriber=$1 LOOP
        RAISE NOTICE 'getupdates(%): in select loop, returning %', $1,
rows.key;
        RETURN NEXT rows;
    END LOOP;
    RAISE NOTICE 'getupdates(%):going to call delete', $1;
    DELETE FROM queued_item WHERE subscriber = $1;
    RAISE NOTICE 'getupdates(%):done calling delete', $1;
    RETURN;
END;


So the delete seems a non-factor.  The delay is now occurring inside the
loop, sometimes for 4-8 seconds.  During this delay, it is possible that
other triggers are inserting into the queued_item table.  Other ideas as to
what is going on?

TIA.