[pgpool-general: 8327] Re: Read write split with cache enable question

[Tatsuo Ishii]

> Hi,
> 
> I am trying to check how the real write split working with caching, and I
> think I found an issue which return stale data.
>
> The scenario is the following:
> 1. I have a primary for write operation and read replica for reads.
> 
> 2. I am using streaming replication mode.
> 
> 3. When I am doing a write which cause an invalidation on the primary, how
> can I be sure that in case I am trying to read from the same table and the
> read replica not synced yet that I will not cached a stale data. Because in
> case the read replica not synced yet what will happened is pgpool will
> cache what he got from the read replica and the user will get stale data
> until the next invalidation.

Currently there's no way to completely avoid that. Probably you could
specify memqcache_expire so that the stale cache does not remain for
long time.

> Any thoughts on this? Do you encounter with this issue before?

The fundamental problem is, PostgreSQL's asynchronous replication does
not guarantee that any committed transaction data is also committed at
the same time on standby servers. The only way to guarantee it is,
using synchronous replication with "synchronous_commit = remote_apply"

Or you could use backend_clustering_mode = 'snapshot_isolation' in
pgpool. This is superior than streaming replication in that it allows
to have cross server consistent visibility. See manual for more
details to know why you need that.
https://www.pgpool.net/docs/latest/en/html/runtime-config-running-mode.html#GUC-SNAPSHOT-ISOLATION-MODE

However there are some down side in the mode including the only
allowed transaction isolation mode is REPEATABLE READ.  Also the mode
inherits same limitaions from native replication mode.
https://www.pgpool.net/docs/latest/en/html/runtime-config-running-mode.html#GUC-REPLICATION-MODE

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