[Pgpool-hackers] Idea to handle sequences

[Tatsuo Ishii]

>> Le 08/01/2011 04:24, Tatsuo Ishii a écrit :
>>>> On Fri, Jan 7, 2011 at 5:46 AM, Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
>>>>>
>>>>> On master we do:
>>>>>
>>>>> BEGIN;
>>>>> SELECT 1 FROM t1_i_seq FOR UPDATE;
>>>>
>>>> and why you are allowed to execute FOR UPDATE in a sequence? i mean,
>>>> if you can't lock the secuence it has no sense that you can lock the
>>>> rows on it... is it not a postgres bug that we should report instead
>>>> of exploit it?
>>> 
>>> Not sure I want to report it. 
>> 
>> In my opinion, *if* this is actually a bug, we must report it. It's all about
>> code quality, sanity and fiability.
> 
> I'm not against if you want to report it.
> 
>>> The "bug" does not hurt PostgreSQL users
>>> in any sense, for example security issues.
>>> 
>>> Theoreticaly we could fix this "bug" and provide "formal" way to lock
>>> sequences instead. But I doubt PostgreSQL cores agree to provide such
>>> new API, which is probably only benefitical to pgpool users.
>> 
>> *If* this is a bug, what if it just accept the lock, but doesn't respect it ?
> 
> It does respect. I already tested it.
> 
>>> So I guess reporting the "bug" and fixing it will not benefit anyone,
>>> on the other hand pgpool users will lose their benefit.
>> 
>> If we rely on a bug for a feature, then the design is probably somewhat broken no ?
> 
> What we need is someway to lock a sequence. I'm sure this is an
> important piece to implement query based replication which works with
> sequences.
> 
> If there's better way to do it, I'm happy to use it. Unfortunately
> there's no alternative in the current PostgreSQL implementation.

BTW, if cores decide that is a bug and remove the "future" without
providing alternative, we could do live with it by creating a dummy
table to be locked. (Actualy I first tried to lock sequence table
itself without success). This way is not convienient because we have
to create such table before hand though.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp