Re: LOCK for non-tables

On Tue, Jan 11, 2011 at 6:31 AM, Tatsuo Ishii <ishii(at)postgresql(dot)org> wrote:
>>> 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?

No, not really. It's still a different object.

>> 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:-<

Ouch.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company