[pgpool-general: 8854] Re: queries not redirecting to primary when "delay_threshold_by_time" is exceeded

[zam bak]

On Tue, Jun 27, 2023 at 10:26 PM Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
>
> > After installing updated packages, I can confirm that replication lag
> > now does cause all SELECT queries to go to primary...
> > However, after the replication lag subsides below threshold, all
> > SELECT queries remain "sticking" to primary (they are no longer load
> > balancing)
> >
> > For testing I used pgbench initialization (this causes replication lag)
> >
> > 1. in one session runa simple query in a loop:
> > while true; do psql -U postgres -h localhost -p 9999 -d bench_test -c
> > "select case inet_server_addr() when '10.1.10.15' then 'replica_1'
> > else 'primary' end,count(*) from pg_tables WHERE schemaname !=
> > 'pg_catalog' AND schemaname != 'information_schema'"; sleep 2; done
> >
> > 2. tail the pgpool log (with per node logging enabled). You will
> > notice that the statement load balances (db node: 0, db node:1 show
> > select in log)
> >
> > 3. use pgbench initialize to generate load, causing replication lag
> > pgbench --quiet --initialize --scale=1000 --foreign-keys bench_test
> >
> > 4. initially in pgpool log once the replication lag threshold is
> > reached (you will see log entries with "....node: 1 is behind' X.XXXX
> > seconds...."), you will see select statement going against node: 0
> > only...
> >    but once lag falls below 1s (treshold) and long after pgbench is
> > done with initialization, select queries remain going to primary
> > server only !!!
>
> The test query above will never be load balanced regardless
> replication delay because it involves system catalogs
> (pg_tables). Please try test queries that do not involve system
> catalogs.
>

OK it works if I remove part of the query that uses system catalogs.

The docs do state this restriction but it says its only for native
replication, not streaming replication
See: https://www.pgpool.net/docs/44/en/html/runtime-config-load-balancing.html#RUNTIME-CONFIG-LOAD-BALANCING-CONDITION
Last bullet point says "in native replication mode, in addition to
above, following conditions must be met: "

In any case, thanks for pointing this out, and once again thanks for
the patched version release!


> Best reagards,
> --
> Tatsuo Ishii
> SRA OSS LLC
> English: http://www.sraoss.co.jp/index_en/
> Japanese:http://www.sraoss.co.jp