Re: select for update & lock contention

On Thursday May 6 2004 6:11, Tom Lane wrote:
> "Ed L." <pgsql(at)bluepolka(dot)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.