Horton

Re: pgpool versus sequences

Tatsuo Ishii <ishii@postgresql.org>

View thread
From: Tatsuo Ishii <ishii@postgresql.org>
To: tgl@sss.pgh.pa.us
Cc: mangoo@wpkg.org, scott.marlowe@gmail.com, t-ishii@sra.co.jp, Kevin.Grittner@wicourts.gov, pgsql-hackers@postgresql.org
Date: 2011-05-31T15:14:30Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Rearrange ALTER TABLE syntax processing as per my recent proposal: the

> 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