pgpool versus sequences (was Re: [ADMIN] 'SGT DETAIL: Could not open file "pg_clog/05DC": No such file or directory' - what to do now?)

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. 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