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

> I think your problem is not relevant to pgpool-II.
>
> PostgreSQL's "synchronous" replication is actually not synchronous
> (it's confusing but the naming was developer's decision). Primary
> server sends the committed transaction's WAL record to standby and
> wait for it is written to the standby's WAL file (and synched to the
> disk if synchronous_commit = on). Then report to the client "the
> transaction has been committed". That means if you send query on the
> just committed row to the standby, it may returns an old row because
> WAL record may replay yet.

Thanks for the insight. I wasn't aware of that.
I assumed that if the slave said "transaction applied" this would also be visible "on the SQL level".

> 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?

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?

> Postgres-XC (or Postgres-XL which is a fork of Postgres-XC) overcomes
> the issue by using "global transaction management" technique.

I know of those two options, but those are currently not on our roadmap
(although I'd really like to play around with them at some time).

Regards
Thomas

[1] http://www.pgpool.net/docs/latest/where_to_send_queries.pdf