To: Tomasz Chmielewski <mangoo@wpkg.org>
cc: Scott Marlowe <scott.marlowe@gmail.com>, Tatsuo Ishii <t-ishii@sra.co.jp>,
	Kevin Grittner <Kevin.Grittner@wicourts.gov>, pgsql-hackers@postgresql.org
Subject: pgpool versus sequences (was Re: [ADMIN] 'SGT DETAIL: Could not open
	file "pg_clog/05DC": No such file or directory' - what to do now?)
In-reply-to: <4DE4A652.2040301@wpkg.org> 
References: <4DC0F063.70306@wpkg.org>
	<4DC15137020000250003D295@gw.wicourts.gov>
	<4DC19CD1.7070600@wpkg.org>
	<BANLkTikG3yN0xB_wTy1SVYOrA9E=FNBZLg@mail.gmail.com>
	<4DC1B378.6050105@wpkg.org> <4DC1B6C0.2000507@wpkg.org>
	<4DC3B469.80004@wpkg.org> <4DC5B754.3040506@wpkg.org>
	<4DE360D9.5060300@wpkg.org> <16063.1306811813@sss.pgh.pa.us>
	<4DE4A652.2040301@wpkg.org>
Comments: In-reply-to Tomasz Chmielewski <mangoo@wpkg.org>
	message dated "Tue, 31 May 2011 10:26:58 +0200"
Date: Tue, 31 May 2011 09:33:34 -0400
Message-ID: <25524.1306848814@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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.  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.  I'm also wondering a bit how one
determines *which* sequence to lock, in a case where the table has
multiple serial columns ...

			regards, tom lane