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

[Tatsuo Ishii]

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

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