On Tue, Jan 11, 2011 at 6:31 AM, Tatsuo Ishii <is...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
