[Pgpool-hackers] Alternative to locking sequences

[Toshihiro Kitagawa]

On Wed, 15 Jun 2011 17:03:45 +0900 (JST)
Tatsuo Ishii <ishii at sraoss.co.jp> wrote:

> 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
-------------- next part --------------
A non-text attachment was scrubbed...
Name: insert_lock.patch
Type: application/octet-stream
Size: 31509 bytes
Desc: not available
URL: <http://pgfoundry.org/pipermail/pgpool-hackers/attachments/20110629/a691ca92/attachment-0002.obj>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: insert_lock.sql
Type: application/octet-stream
Size: 494 bytes
Desc: not available
URL: <http://pgfoundry.org/pipermail/pgpool-hackers/attachments/20110629/a691ca92/attachment-0003.obj>