Re: LOCK for non-tables

> On Fri, Jan 7, 2011 at 6:28 PM, Florian Pflug <fgp(at)phlo(dot)org> wrote:
>> I forgot about sequences earlier. If we dump while someone deletes all
>> rows and resets the sequence the dump might contain rows and still
>> reset the sequence. This could cause duplicate key errors on restore.
>> I haven't checked if this is really possible though - I guess it would
>> depend on the exact order of these events...
>
> To fix this, you'd need a lock that allowed getting values from the
> sequence but prevented resetting it, and...
>
>> I wonder how such locks would work. Would such locks prevent accessing
>> these objects? Or just modifications? For example, if I locked a function,
>> could someone else execute it while I held the lock?
>
> ...in fact we do very little locking of objects other than tables.
> DROP takes an AccessExclusiveLock on whatever it's dropping, and
> COMMENT and SECURITY LABEL take ShareUpdateExclusiveLocks to avoid
> orphaning pg_{sh,}description or pg_seclabel entries in the face of a
> concurrent drop, but most operations on non-table objects don't AFAIK
> take any lock at all. We probably don't want to make too many changes
> in this area, because there are already workloads where the
> heavyweight lock manager can become a bottleneck, and one can easily
> imagine that locking operators or namespaces could make that problem
> much worse.

For query based replication tools like pgpool-II (I don't know any
other tools, for example Postgres XC falls in this category or
not...), we need to be able to lock sequences. Fortunately it is allowed to:

SELECT 1 FROM foo_sequece FOR UPDATE;

but LOCK foo_sequence looks more appropreate syntax for me.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp