[Pgpool-hackers] Alternative to locking sequences

[Tatsuo Ishii]

Looks good to me. Great work!
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

>> Hi,
>> 
>> In the replication mode we have "insert_lock" functionality to keep
>> consistency of tables using SERIAL when inserting a row into a table.
>> 
>> Originally we locked the target table itself, which conflicted with
>> autovacuum, especially if the table is big enough. So from 3.0 we use
>> row lock against one of sequences the table is using. This way we can
>> avoid the autovacuum problem(no, you can not lock sequence table
>> itself).
>> 
>> Now we realize that we will have a problem with sequence row lock. If
>> xid wraparound happens, we will have clog access error, which is an
>> internal file managing transaction status.
>> 
>> To my surprise, PostgreSQL developers decided to "fix" the problem by
>> not allowing the sequence row lock. Of course this avoids could the
>> clog error, but users suddenly cannot use insert lock anymore.
>> 
>> So I decided to provide ugly work around to cope with the PostgreSQL's
>> "fix".
>> 
>> 1) insert lock needed
>> 
>> 2) create a secret table "_tablename_pgpool_dummy" if it's not already
>>    existed. For example, if the table name is "t1" t hen our secret
>>    table will be "_t1_pgpool_dummy"
>> 
>> 3) lock the dummy table
>> 
>> This is pretty ugly but we have no choice.
>> 
>> Probably we should provide choices not using the method because some
>> users might not want to the dummy files be created by pgpool.
> 
> I've attached my patch. My patch is different from the above.
> 
> 2) create secret table "pgpool_catalog.insert_lock" in all databases
>    to use by executing insert_lock.sql file beforehand. This method
>    is same as pgpool_regclass().
>    insert_lock table has only "reloid" column of oid type.
> 
> 3) lock the row where reloid is the oid of the insert target table.
>    If the oid does not exist, pgpool insert it into insert_lock
>    table automatically. At this time, pgpool locks the row where
>    reloid is 0 because to avoid "duplicate key violates..." error
>    by concurrent inserts. The row(reloid:0) is inserted by
>    insert_lock.sql. And if the row does not exist, pgpool insert it
>    automatically.
>    The reason to lock the row is because effect is small than locking
>    whole of insert_lock table.
> 
> My patch uses pgpool_regclass() if possible, when pgpool locks.
> 
>   SELECT 1 FROM pgpool_catalog.insert_lock WHERE key = pgpool_regclass('
>   TABLE NAME') FOR UPDATE;
> 
> If using pgpool_regclass() is impossible, pgpool issues following query:
> 
>   SELECT 1 FROM pgpool_catalog.insert_lock WHERE key = (SELECT oid FROM
>   pg_catalog.pg_class WHERE relname = 'TABLE NAME' ORDER BY oid LIMIT 1)
>   FOR UPDATE;
> 
> In my patch, pgpool locks the row on only master node. I think it's no
> problem.
> 
> If pgpool could not lock the row, it try to lock insert target table
> finally. This behavior is compatible with pgpool-II 2.2 and 2.3 series.
> 
>> My thought is, provide compiling time option to choose one of follows:
>> 
>> 1) work in the same way as existing 3.0.x (sequence row lock)
>> 
>> 2) work in the same way as pre 3.0 (table lock against the target
>>    table)
>> 
>> 3) the new way I just propose
> 
> My patch add following options:
> 
> --enable-sequence-lock  insert_lock compatible with pgpool-II 3.0 series
>                         (until 3.0.4)
> --enable-table-lock     insert_lock compatible with pgpool-II 2.2 and 2.3
>                         series
> 
> Any comments are welcome.
> 
> -- 
> Toshihiro Kitagawa
> SRA OSS, Inc. Japan