Re: pgpool versus sequences

From: Tatsuo Ishii <ishii(at)postgresql(dot)org>
To: tgl(at)sss(dot)pgh(dot)pa(dot)us
Cc: mangoo(at)wpkg(dot)org, scott(dot)marlowe(at)gmail(dot)com, t-ishii(at)sra(dot)co(dot)jp, Kevin(dot)Grittner(at)wicourts(dot)gov, pgsql-hackers(at)postgresql(dot)org
Subject: Re: pgpool versus sequences
Date: 2011-05-31 15:14:30
Message-ID: 20110601.001430.1098605953350881110.t-ishii@sraoss.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin pgsql-hackers

> Tomasz Chmielewski <mangoo(at)wpkg(dot)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

In response to

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Wells Oliver 2011-05-31 17:40:28 Server crash... trying to figure it out
Previous Message Kevin Grittner 2011-05-31 13:45:40 Re: Restore master from slave

Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2011-05-31 15:22:52 Re: [HACKERS] DOCS: SGML identifier may not exceed 44 characters
Previous Message Robert Haas 2011-05-31 15:14:02 Re: Getting a bug tracker for the Postgres project