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

[Avi Raboah]

Thanks, I'll check it out.

On Mon, 11 Jul 2022 at 13:18 Tatsuo Ishii <ishii at sraoss.co.jp> wrote:

> > 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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20220711/62d9492d/attachment.htm>