On Tue, Jan 11, 2011 at 6:31 AM, Tatsuo Ishii <ishii@postgresql.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