[pgpool-hackers: 3859] Supporting SERIAL and others in certain clustering mode

[Tatsuo Ishii]

Currently some kind of objects are not supported or poorly supported
in native replication mode and snapshot isolation mode.

random()
XID (transaction id)
OID

Following objects are supported but it requires table lock:

SERIAL data type
SEQUENCE

To eliminate the limitation, I think we can use INSERT RETURNING and
UPDATE RETURNING.

1. Rewrite INSERT or UPDATE to INSERT RETURNING or UPDATE RETURNING on
main node.

2. Send the INSERT RETURNING or UPDATE RETURNING to the main node.

3. Receive the reply from backend to know what value was actually
inserted/updated on the main node.

4. Rewrite INSERT or UPDATE for replica node so that they use the
value just returned from the main node.

5. Send the rewritten INSERT or UPDATE to the replica node.

With this method we can replicate functions like random(). Also we do
not need to use table for SERIAL or SEQUENCE.

And maybe time stamp rewriting could be re-implemented by using this
technique.

Questions remaining are:

a. Is it useful to replicate XID or OID? Maybe not because XID/OID are
only useful within local server.

b. Do we unconditionally perform the rewriting for INSERT and UPDATE?
>From performance perspective we should do the rewriting only when
specific data objects like random(), SERIAL are included in the
INSERT/UPDATE. But this makes it impossible to support INSERT/UPDATE
including C functions which calls random() for example.

c. What about triggers like INSERT random() to other table when fired?
I don't think we can support them.

Comments and suggestions are welcome.
--
Tarts Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp