[Pgpool-hackers] Alternative to locking sequences

[Tatsuo Ishii]

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.

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

Comments?
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp