Re: LOCK for non-tables

> On Tue, Jan 11, 2011 at 4:46 AM, Tatsuo Ishii <ishii(at)postgresql(dot)org> wrote:
>>> 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.
>
> Those aren't doing the same thing. The first is locking the one and
> only tuple that is contained within the sequence, while the second is
> locking the sequence object itself.

But a sequence relation contains only 1 tuple and there's no
difference among them, no?

> At this point, I'm inclined to think that the pg_dump comment is just
> wrong, and we ought to fix it to say that we don't really want to be
> able to lock other relations after all, and call it good.
>
> As a side node, locking a sequence for replication seems like it could
> have pretty dire effects on performance in certain workloads. Why do
> you need to do that, anyway?

Pgpool not only needs to replicate sequences but replicates tuples
updated by DMLs which are using sequence value(I am talking about
SERIAL data types). For this purpose, pgpool issue nextval() to master
DB server first, then use the value for subsequent INSERT/UPDATE. This
will guarantee that inserted/updated values using sequences are
identical among master and slave DB servers. Problem is, if this
process happens in concurrent sessions, inserted/updated tuples might
not have identical value among DB servers. So I need "sequence lock"
here. This is the price statement based replication tools have to pay
for:-<
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp