Re: Re: Synchronous replication + pgPool: not all transactions immediately visible on standby

>> If you dislike the PostgreSQL's behavior, you may want to try
>> pgpool-II's "native replication mode" (set replication_mode = on and
>> master_slave_mode = off). In the mode, pgpool-II does not return
>> response to the client until all PostgreSQL returns a commit
>> response. Thus right after the commit, querying to any PostgreSQL
>> should return committed row immediately.
>
> OK, we will try that out.
>
> The documentation could a bit more specific on what exactly "replication mode" means.
> It seems that this only influences the way pgPool distributes queries, it does not actually turn on any kind of replication, right?

With replication mode pgpool-II implicitely sends all write queries to
all PostgreSQL servers. The idea is, "sending identical query will
result in same result". Thus some queries having oid, xid, random() or
any object which results in different result among PostgreSQL servers
will bring different data in replication mode. So you should be very
carefull if you want to use such queries. Queries including time/data
datatypes, functions (for example now()) are rewritten by pgpool-II so
that it uses local time at pgpool-II to avoid the problem.

With the mode, PostgreSQL's streaming replication mode should be turn
off by the way.

>>From the flow chart[1] it also seems that this will only properly distribute read-only queries if we turn off auto-commit.

> Did I understand that correctly?

Yes, the chart only explains read queries. Write queries are sent to
all PostgreSQL servers as stated above.

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