Re: pgpool versus sequences

> Tomasz Chmielewski <mangoo@wpkg.org> writes:
>> On 31.05.2011 05:16, Tom Lane wrote:
>>> I think the most appropriate solution may be to disallow SELECT FOR
>>> UPDATE/SHARE on sequences ... so if you have a good reason why we
>>> shouldn't do so, please explain it.
> 
>> I grepped the sources of the application using postgres, and it certainly doesn't do it.
>> [ but pgpool does, as of a couple months ago ]
>> This is a message explaining why it was introduced to pgpool:
>> http://comments.gmane.org/gmane.comp.db.postgresql.pgpool.devel/348
> 
> Too bad that wasn't mentioned on pgsql-hackers, where someone might have
> pointed out the major flaws in the idea.
> 
>> 2) is pgpool behaviour correct?
> 
> No.  Quite aside from the lack-of-XID-maintenance problem, the proposal
> seems just plain bizarre to me.  SELECT FOR UPDATE wouldn't block
> nextval(), so the command doesn't actually guarantee serialization of
> sequence value acquisition.

Actually it was already explained before:

http://archives.postgresql.org/pgsql-hackers/2011-01/msg00805.php

At the time no one noticed the lack-of-XID-maintenance
problem. Tomasz, thanks for the report. I will go back to old way as
pgpool-II used to do, which is very inefficient unfortunately...

>  Taking a table lock on the sequence could
> do so, and wouldn't run into any implementation issues, so I fail to see
> why that alternative was rejected.

Table lock on the sequence? PostgreSQL doesn't allow it...

> I'm also wondering a bit how one
> determines *which* sequence to lock, in a case where the table has
> multiple serial columns ...

No problem at least for pgpool-II. Just choose one of them and obtain
lock on it is enough. Because purpose for the lock is to prevent
concurrent INSERT to the table.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp