Re: Shared row locking

On Mon, 20 Dec 2004 21:44:01 +0100, <simon(at)2ndquadrant(dot)com> wrote:
>Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote on 20.12.2004, 19:34:21:
>> #1 could have a pretty serious performance impact, too. For small
>> numbers of FOR UPDATE locks (too few to force spill to disk) I would
>> expect #2 to substantially beat #1. #1 essentially imposes the worst
>> case performance at all times, whereas #2 degrades (at a currently
>> unknown rate) when there are lots and lots of FOR UPDATE locks.

I don't see too much of a difference between #1 (an on-disk structure
buffered in shared memory) and #2 (a shared memory structure spilling
to disk).

As long as we can avoid unnecessary writes, #1 has the nice property
that it automatically adapts to different usage patterns because it
uses the normal shared buffer pool and cache replacement strategy.

>[My gut feeling would be against another permanent on-disk structure,
>since this is one more thing for a user to delete "to save space"
>etc...]

Irrelevant, IMHO. Whoever manually deletes any file under PGDATA
deserves whatever this may cause.

>I haven't seen many programs that use
>extended SELECT FOR UPDATE logic.

AFAICS, SELECT FOR UPDATE is not the primary issue here, because it
locks rows exclusively. This thread is about shared locks, which
should be used for foreign key checks.

Servus
Manfred