Subject: Proposal: Recent mutated table tracking in memory

From: Nadav Shatz <[email-masked]>
Date: Tue, 6 Jan 2026 13:25:54 +0200

Hello,

As initially proposed under "Proposal: recent access based routing for
primary-replica setups" and then broken into separate tasks - i am adding
here a patch to implement tracking of latest mutated table, and then using
the replication lag as a base - deciding where to point queries when query
load balancing and parsing is enabled.

More details as in the patch:
Feature: add in-memory table tracking to prevent stale reads from replicas

Implement "memory map" feature that tracks recently-written database
tables in shared memory to prevent stale reads during replication lag.
When a write (INSERT/UPDATE/DELETE) occurs on a table, that table is
marked as "dirty" for a configurable TTL period. Any SELECT on a dirty
table within the TTL window is routed to primary instead of replica.

Key features:
- Shared memory hash table for tracking table mutations with TTL
- Query parse cache with LRU eviction for performance
- Cold start protection (routes all queries to primary initially)
- Automatic TTL calculation: replication_delay × configurable factor
- Per-table staleness tracking with microsecond precision

New configuration parameters:
- memory_map_enabled: Enable/disable the feature (default: off)
- memory_map_ttl_factor: TTL multiplier for replication delay (default: 5.0)
- memory_map_cold_start_duration: Cold start period in ms (default: 2000)
- memory_map_table_buckets: Hash buckets for table map (default: 1024)
- memory_map_table_size: Max tracked tables (default: 2048)
- memory_map_query_buckets: Hash buckets for query cache (default: 2048)
- memory_map_query_cache_size: Max cached queries (default: 10000)

Patch applies properly and tests pass.

Open to all feedback - thank you!

-- 
Nadav Shatz
Tailor Brands | CTO

---

From: Nadav Shatz <[email-masked]>
Date: Wed, 14 Jan 2026 10:55:41 +0200

Hi all,

Any comments or concerns? can we merge it if not?

-- 
Nadav Shatz
Tailor Brands | CTO

---

From: Nadav Shatz <[email-masked]>
Date: Mon, 26 Jan 2026 13:02:40 +0200

Doing another ping - sorry for that, it's been 20 days.

Would appreciate any feedback. thank you

-- 
Nadav Shatz
Tailor Brands | CTO

---

From: Tatsuo Ishii <[email-masked]>
Date: Wed, 28 Jan 2026 14:08:40 +0900 (JST)

Hi Nadav,

Sorry for the late reply. I just your email now. Will check and reply
back soon.
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

---

From: Nadav Shatz <[email-masked]>
Date: Wed, 28 Jan 2026 07:37:07 +0200

Thank you Tatsuo!

Nadav Shatz
Tailor Brands | CTO

---

From: Tatsuo Ishii <[email-masked]>
Date: Thu, 29 Jan 2026 17:28:10 +0900 (JST)

> Sorry for the late reply. I just your email now. Will check and reply

Unfortunately your patch failed to apply to current master branch.

$ git apply ~/mutated_table.patch 
error: patch failed: src/streaming_replication/pool_worker_child.c:1032
error: src/streaming_replication/pool_worker_child.c: patch does not apply

It tried with patch command and it seems failed here:

--- src/streaming_replication/pool_worker_child.c
+++ src/streaming_replication/pool_worker_child.c
@@ -1034,6 +1036,10 @@ check_replication_time_lag_with_cmd(void)
 			bkinfo->standby_delay = delay;
 			bkinfo->standby_delay_by_time = true;
 
+			/* Track maximum delay for memory map TTL calculation */
+			if (delay > max_delay_us)
+				max_delay_us = delay;
+
 			/* Log delay if necessary */
 			delay_threshold_by_time = pool_config->delay_threshold_by_time * 1000;	/* threshold is in
 																					 * milliseconds, convert

Need rebase?

---

From: Nadav Shatz <[email-masked]>
Date: Thu, 29 Jan 2026 10:54:48 +0200

yes indeed, please find attached.

On Thu, Jan 29, 2026 at 10:28 AM Tatsuo Ishii <[email-masked]> wrote:

>                         delay_threshold_by_time =
> pool_config->delay_threshold_by_time * 1000;  /* threshold is in
>
>
>                    * milliseconds, convert
>
> Need rebase?
> --
> Tatsuo Ishii
> SRA OSS K.K.

-- 
Nadav Shatz
Tailor Brands | CTO

---

From: Tatsuo Ishii <[email-masked]>
Date: Fri, 30 Jan 2026 17:09:50 +0900 (JST)

> yes indeed, please find attached.

Thanks. Here some comments on the patch:

- It seems you use a table name (and schema) for a key to identify the
  TableMutationEntry and other objects. I think you should use table
  oids for the key because the same table name could exists in
  different schema. Moreover, if the database is different from the
  database when the map entry was created, a map look up could return
  incorrect result. In summary the key should be table oid and
  database oid (which is already done by query cache subsystem).

- In the patch spin lock primitives are introduced. Why can't we use
  semaphore instead? A spin lock uses busy loop, which could increase
  the system load if the duration of locking becomes longer.

- What would happen if the leader watchdog fails and other watchdog
  node take the place of the leader role?

- pool_memory_map_get_ttl() and pool_memory_map_get_stats() are
  defined but are not used anywhere. Why do you have them?

- I think "memory_map" is a too generic name. Can we use more specific
  name for the feature?

Best regards,

---

From: Nadav Shatz <[email-masked]>
Date: Sat, 31 Jan 2026 19:11:14 +0200

Thank you for the comments!

I agree with all of them. Let me know what you think of the changes and new
naming.

Please see attached another version of the patch.

-- 
Nadav Shatz
Tailor Brands | CTO

---

From: Tatsuo Ishii <[email-masked]>
Date: Tue, 03 Feb 2026 16:43:53 +0900 (JST)

Hi Nadav,

Thank you for updating the patch!

> I agree with all of them. Let me know what you think of the changes and new

I still think "memory_map" is too generic. Anything put on memory for
data mapping could be called "memory map". I recommend to change the
name to more feature specific one: What about replacing "memory_map"
with "track_table_mutation"? It's a little bit longer name but it
clearly represents the feature. Any better ideas are welcome.

Also I feel memory_map_query_cache_size is confusing because there's
already "query cache" feature in pgpool.  Can we change it something
like "query_parse_cache_size"?

Review comments:

(1) Why the regression test is 45? Shouldn't it be 42? (the last
feature test is 041.external_replication_delay).

(2) You enhance the patch to deal with leader watch changing. That's
good. However, I don't see a test case for it in test.sh.

(3) It seems the patch does not support TRUNCATE, MERGE, PREPARE and
WITH + updating. If so, it should be noted in the docs as a limitation
of the feature.
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

---

From: Tatsuo Ishii <[email-masked]>
Date: Wed, 04 Feb 2026 08:23:43 +0900 (JST)

From: Tatsuo Ishii <[email-masked]>
Subject: Re: Proposal: Recent mutated table tracking in memory
Date: Tue, 03 Feb 2026 16:43:53 +0900 (JST)
Message-ID: <[email-masked]>

> WITH + updating. If so, it should be noted in the docs as a limitation

(4) It seems the patch does not consider transactions. If an UPDATE is
performed in a transaction and the transaction gets rollbacked, load
balance is disabled despite that fact that the table modification did
not happen.

Best regards,

---

From: Nadav Shatz <[email-masked]>
Date: Fri, 6 Feb 2026 13:29:10 +0200

Hi Tatsuo,

Thank you for all the great comments and questions! I took under
consideration all of them either adding support/tests or detailing the
limitations in the docs.

Let me know what you think of the latest patch attached here

-- 
Nadav Shatz
Tailor Brands | CTO

---

From: Nadav Shatz <[email-masked]>
Date: Tue, 10 Feb 2026 17:16:33 +0200

Hi Tatsuo,

After reading more about disable_load_balance_on_write=dml_adaptive i came
to the thought that this feature is actually an "extension" of that since
it covers "global" and not just per transaction behavior. in any case i
think it makes more sense that it sits under
the disable_load_balance_on_write and not as a standalone for clarity.

I'm attaching below an updated patch with these adjustments.

Please let me know what you think.

-- 
Nadav Shatz
Tailor Brands | CTO

---

From: Tatsuo Ishii <[email-masked]>
Date: Wed, 11 Feb 2026 19:28:45 +0900 (JST)

> Please let me know what you think.

I worry about the transactional behavior with the patch:

+    This means that if a transaction is rolled back, the table remains marked as stale until
+    the TTL expires, even though no actual data modification occurred. This is by design:

This allows attackers to issue simple command continuously to
effectively disable load balance (and increase the load of primary) in
whole system:

BEGIN;
UPDATE t1 SET i = 1 WHERE FALSE;
ROLLBACK;

I think if the patch allows that, we cannot accept the patch.

Best regards,

---

From: Nadav Shatz <[email-masked]>
Date: Thu, 12 Feb 2026 11:05:19 +0200

Hi Tatsuo,

  Thank you for the careful review. You raised an important concern. I've
addressed it in the updated patch — here's the explanation:

  The attack scenario you describe is now handled. In the updated patch,
writes inside explicit transactions are only flushed to the shared-memory
table map at COMMIT time. If the transaction is rolled back, the table is
never marked as stale. So the attack pattern:

  BEGIN;

  has zero effect on the shared-memory table map. The dml_adaptive_global
mode piggybacks on the existing dml_adaptive per-transaction write list
(transaction_temp_write_list). On COMMIT, the accumulated table names are
resolved to OIDs and flushed to shared memory. On ROLLBACK,
  the list is simply discarded (the existing dml_adaptive behavior).

  For autocommit statements (outside explicit transactions), tables are
marked immediately — but in that case the write is committed, so this is
correct.

  Regression test included. Test 042 now includes:
  - Test 10: verifies that BEGIN; INSERT; ROLLBACK; SELECT does NOT route
the SELECT to primary
  - Test 11: verifies that BEGIN; INSERT; COMMIT; SELECT DOES route the
SELECT to primary

  Additional context on the threat model:

  1. This feature requires disable_load_balance_on_write =
'dml_adaptive_global' — it is opt-in, not enabled by default. Operators who
enable it accept documented trade-offs (additional shared memory, TTL-based
staleness window).
  2. An attacker who can connect and execute SQL against pgpool already has
the ability to cause far more damage (DROP TABLE, mass DELETEs, resource
exhaustion via expensive queries, connection flooding, etc.). The
table-marking via committed writes is a minor concern compared to
  those vectors. Authentication, connection limits, and network security
are the appropriate defenses at that layer.
  3. Even in the worst case (an attacker commits real writes in a loop),
the impact is bounded: the stale marking is temporary (TTL-based, typically
a few seconds), and only affects load-balancing decisions — it doesn't
cause data loss or correctness issues.
  4. The existing dml_adaptive mode has analogous behavior: within a
transaction, a write to table T causes all reads of T to go to primary for
the remainder of that transaction. The only difference is scope —
dml_adaptive_global extends this across sessions with a TTL.

Thanks!

On Wed, Feb 11, 2026 at 12:28 PM Tatsuo Ishii <[email-masked]> wrote:

> >>> > - memory_map_ttl_factor: TTL multiplier for replication delay
> (default:
> >>> 5.0)

-- 
Nadav Shatz
Tailor Brands | CTO

---

From: Tatsuo Ishii <[email-masked]>
Date: Thu, 19 Feb 2026 08:51:21 +0900 (JST)

> addressed it in the updated patch ― here's the explanation:
> 
> marked immediately ― but in that case the write is committed, so this is
> correct.
> 
> 'dml_adaptive_global' ― it is opt-in, not enabled by default. Operators who
> a few seconds), and only affects load-balancing decisions ― it doesn't
> the remainder of that transaction. The only difference is scope ―

Thank you for the patch. While I am looking into it, I noticed a
regression test failure.

t-ishii$ ./regress.sh 04[12]
creating pgpool-II temporary installation ...
:
:
testing 041.external_replication_delay...ok.
testing 042.track_table_mutation...failed.
out of 2 ok:1 failed:1 timeout:0

However if I run 042 only, it succeeds.

t-ishii$ ./regress.sh 042
:
:
testing 042.track_table_mutation...ok.
out of 1 ok:1 failed:0 timeout:0

Can you please take a look at this? log/042.track_table_mutation
attached.

Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
creating test environment...PostgreSQL major version: 180
Starting set up in streaming replication mode
creating startall and shutdownall
creating failover script
creating database cluster /home/t-ishii/work/Pgpool-II/current/pgpool2/src/test/regression/tests/042.track_table_mutation/testdir/data0...done.
update postgresql.conf
creating pgpool_remote_start
creating basebackup.sh
creating recovery.conf
creating database cluster /home/t-ishii/work/Pgpool-II/current/pgpool2/src/test/regression/tests/042.track_table_mutation/testdir/data1...done.
update postgresql.conf
creating pgpool_remote_start
creating basebackup.sh
creating recovery.conf
temporarily start data0 cluster to create extensions
temporarily start pgpool-II to create standby nodes
psql: error: connection to server at "localhost" (127.0.0.1), port 11000 failed: Connection refused
	Is the server running on that host and accepting TCP/IP connections?
psql: error: connection to server at "localhost" (127.0.0.1), port 11000 failed: Connection refused
	Is the server running on that host and accepting TCP/IP connections?
psql: error: connection to server at "localhost" (127.0.0.1), port 11000 failed: Connection refused
	Is the server running on that host and accepting TCP/IP connections?
psql: error: connection to server at "localhost" (127.0.0.1), port 11000 failed: Connection refused
	Is the server running on that host and accepting TCP/IP connections?
psql: error: connection to server at "localhost" (127.0.0.1), port 11000 failed: Connection refused
	Is the server running on that host and accepting TCP/IP connections?
psql: error: connection to server at "localhost" (127.0.0.1), port 11000 failed: Connection refused
	Is the server running on that host and accepting TCP/IP connections?
psql: error: connection to server at "localhost" (127.0.0.1), port 11000 failed: Connection refused
	Is the server running on that host and accepting TCP/IP connections?
psql: error: connection to server at "localhost" (127.0.0.1), port 11000 failed: Connection refused
	Is the server running on that host and accepting TCP/IP connections?
psql: error: connection to server at "localhost" (127.0.0.1), port 11000 failed: Connection refused
	Is the server running on that host and accepting TCP/IP connections?
psql: error: connection to server at "localhost" (127.0.0.1), port 11000 failed: Connection refused
	Is the server running on that host and accepting TCP/IP connections?
psql: error: connection to server at "localhost" (127.0.0.1), port 11000 failed: Connection refused
	Is the server running on that host and accepting TCP/IP connections?
psql: error: connection to server at "localhost" (127.0.0.1), port 11000 failed: Connection refused
	Is the server running on that host and accepting TCP/IP connections?
psql: error: connection to server at "localhost" (127.0.0.1), port 11000 failed: Connection refused
	Is the server running on that host and accepting TCP/IP connections?
psql: error: connection to server at "localhost" (127.0.0.1), port 11000 failed: Connection refused
	Is the server running on that host and accepting TCP/IP connections?
psql: error: connection to server at "localhost" (127.0.0.1), port 11000 failed: Connection refused
	Is the server running on that host and accepting TCP/IP connections?
psql: error: connection to server at "localhost" (127.0.0.1), port 11000 failed: Connection refused
	Is the server running on that host and accepting TCP/IP connections?
psql: error: connection to server at "localhost" (127.0.0.1), port 11000 failed: Connection refused
	Is the server running on that host and accepting TCP/IP connections?
psql: error: connection to server at "localhost" (127.0.0.1), port 11000 failed: Connection refused
	Is the server running on that host and accepting TCP/IP connections?
psql: error: connection to server at "localhost" (127.0.0.1), port 11000 failed: Connection refused
	Is the server running on that host and accepting TCP/IP connections?
psql: error: connection to server at "localhost" (127.0.0.1), port 11000 failed: Connection refused
	Is the server running on that host and accepting TCP/IP connections?
psql: error: connection to server at "localhost" (127.0.0.1), port 11000 failed: Connection refused
	Is the server running on that host and accepting TCP/IP connections?
recovery node 1...ERROR: connection to host "localhost" failed with error "Connection refused"
done.
creating follow primary script
psql: error: connection to server at "localhost" (127.0.0.1), port 11000 failed: Connection refused
	Is the server running on that host and accepting TCP/IP connections?
shutdown all

pgpool-II setting for streaming replication mode is done.
To start the whole system, use /home/t-ishii/work/Pgpool-II/current/pgpool2/src/test/regression/tests/042.track_table_mutation/testdir/startall.
To shutdown the whole system, use /home/t-ishii/work/Pgpool-II/current/pgpool2/src/test/regression/tests/042.track_table_mutation/testdir/shutdownall.
pcp command user name is "t-ishii", password is "t-ishii".
Each PostgreSQL, pgpool-II and pcp port is as follows:
#1 port is 11002
#2 port is 11003
pgpool port is 11000
pcp port is 11001
The info above is in README.port.
done.
waiting for server to start....364373 2026-02-19 08:42:03.860 JST LOG:  redirecting log output to logging collector process
364373 2026-02-19 08:42:03.860 JST HINT:  Future log output will appear in directory "log".
 done
server started
waiting for server to start....364388 2026-02-19 08:42:03.963 JST LOG:  redirecting log output to logging collector process
364388 2026-02-19 08:42:03.963 JST HINT:  Future log output will appear in directory "log".
 done
server started
psql: error: connection to server on socket "/tmp/.s.PGSQL.11000" failed: ERROR:  unable to read message kind
DETAIL:  kind does not match between main(53) slot[1] (45)
=== Test 1: Cold Start Routing ===
2026-02-19 08:42:24.305: main pid 364625: DEBUG:  initializing pool configuration
2026-02-19 08:42:24.305: main pid 364625: DETAIL:  num_backends: 2 total_weight: 1.000000
2026-02-19 08:42:24.305: main pid 364625: DEBUG:  initializing pool configuration
2026-02-19 08:42:24.305: main pid 364625: DETAIL:  backend 0 weight: 0.000000 flag: 0000
2026-02-19 08:42:24.305: main pid 364625: DEBUG:  initializing pool configuration
2026-02-19 08:42:24.305: main pid 364625: DETAIL:  backend 1 weight: 2147483647.000000 flag: 0000
2026-02-19 08:42:24.305: main pid 364625: LOG:  stop request sent to pgpool (pid: 364400). waiting for termination...
.done.
waiting for server to shut down.... done
server stopped
waiting for server to shut down.... done
server stopped
waiting for server to start....364640 2026-02-19 08:42:25.531 JST LOG:  redirecting log output to logging collector process
364640 2026-02-19 08:42:25.531 JST HINT:  Future log output will appear in directory "log".
 done
server started
waiting for server to start....364655 2026-02-19 08:42:25.633 JST LOG:  redirecting log output to logging collector process
364655 2026-02-19 08:42:25.633 JST HINT:  Future log output will appear in directory "log".
 done
server started
Test 1 FAILED: Cold start routing not detected
2026-02-19 08:42:45.929: main pid 364901: DEBUG:  initializing pool configuration
2026-02-19 08:42:45.929: main pid 364901: DETAIL:  num_backends: 2 total_weight: 1.000000
2026-02-19 08:42:45.929: main pid 364901: DEBUG:  initializing pool configuration
2026-02-19 08:42:45.929: main pid 364901: DETAIL:  backend 0 weight: 0.000000 flag: 0000
2026-02-19 08:42:45.929: main pid 364901: DEBUG:  initializing pool configuration
2026-02-19 08:42:45.929: main pid 364901: DETAIL:  backend 1 weight: 2147483647.000000 flag: 0000
2026-02-19 08:42:45.929: main pid 364901: LOG:  stop request sent to pgpool (pid: 364670). waiting for termination...
.done.
waiting for server to shut down.... done
server stopped
waiting for server to shut down.... done
server stopped

---

From: Nadav Shatz <[email-masked]>
Date: Thu, 19 Feb 2026 06:40:21 +0200

Thanks! I’ll look into it and share an updated patch

Nadav Shatz
Tailor Brands | CTO

---

From: Nadav Shatz <[email-masked]>
Date: Thu, 19 Feb 2026 13:05:41 +0200

Added some handling for possible causes - works now.

-- 
Nadav Shatz
Tailor Brands | CTO

---

From: Tatsuo Ishii <[email-masked]>
Date: Thu, 26 Feb 2026 08:55:56 +0900 (JST)

>   the list is simply discarded (the existing dml_adaptive behavior).

Ok.

> marked immediately ― but in that case the write is committed, so this is

Agreed.

> enable it accept documented trade-offs (additional shared memory, TTL-based

Ok.

>   those vectors.

These existing risks are widely understood by PostgreSQL users and
already accepted (with various measures/workarounds). But the concern
is new one. If the concern is left, it will make users to hesitate to
use the new feature. I don't want to add a feature which users might
avoid to use it especially when we know that it is technically
possible to remove the concern.

> Authentication, connection limits, and network security

> cause data loss or correctness issues.

But it makes Pgpool's load balance feature less usefull. In my
uderstanding the new feature tries to keep load balance usefully while
it also tries to avoid steal data read.

> dml_adaptive_global extends this across sessions with a TTL.

The scope difference is huge. With existing dml_adaptive mode, it just
hits himself in the foot. Your patch allows him to hit someone else's
foot.

Best regards,

---

From: Tatsuo Ishii <[email-masked]>
Date: Thu, 26 Feb 2026 09:02:14 +0900 (JST)

> Added some handling for possible causes - works now.

Unfortunately this doesn't work here. Still 042 test fails if it is
executed *after* 041.  i.e.

./regress.sh 04[12]	<-- 042 fails
./regress.sh 042	<-- Ok

Best regards,

---

From: Tatsuo Ishii <[email-masked]>
Date: Thu, 26 Feb 2026 16:47:42 +0900 (JST)

> Added some handling for possible causes - works now.

Here are comments for the patch.

- Some code lines are too long. We recommend to limit each source code
  line up to 78 chars. You can use following script to detect too long
  lines (you can ignore reports other than *.[c]) See
  https://wiki.postgresql.org/wiki/Committing_checklist

git diff origin/master | grep -E '^(\+|diff)' | sed 's/^+//' | expand -t4 | awk "length > 78 || /^diff/"

--- /dev/null
+++ b/src/test/regression/tests/043.track_table_mutation_watchdog/.gitignore

Please avoid to install .gitignore. .gitignore file are maintained by
pgpool core developers.

+++ b/src/test/regression/tests/043.track_table_mutation_watchdog/leader.conf

To test watchdog, you should use the standard watchdog_setup too.

+++ b/src/utils/pool_track_table_mutation.c

+static inline void
+query_cache_lock(void)

"query_cache_*" is confusing since we already have query cache
feature. Please use different name.

+static int
+track_table_mutation_get_database_oid_internal(void)
+{
:
:
+	/* Ensure we have a valid query context */
+	if (session_context->query_context == NULL)
+		return oid;

Why does this need? The query context is not used in this function.

+/* ----------------
+ * Public API implementation
+ * ----------------
+ */

Please add a comments on what these function do.

+Size
+pool_track_table_mutation_shmem_size(void)

+void
+pool_track_table_mutation_init(void)

+void
+pool_track_table_mutation_child_init(void)

+bool
+pool_track_table_mutation_in_cold_start(void)

+void
+pool_track_table_mutation_trigger_global_cold_start(void)

+bool
+pool_track_table_mutation_table_is_stale(int table_oid, int dboid)

__sync_fetch_and_add are old functions.  I recommend to replace with
ordinary statements using semaphore to protect the critical region.

+		__sync_fetch_and_add(&track_table_mutation_shmem->state.stats_queries_checked, 1);

Please add a comments on what these function do.

+pool_track_table_mutation_mark_tables_written(const int *table_oids, int num_tables, int dboid)

+void
+pool_track_table_mutation_update_ttl(uint64 delay_us)

+bool
+pool_track_table_mutation_get_cached_parse(uint64 hash, bool *is_write,

+void
+pool_track_table_mutation_cache_parse(uint64 hash, bool is_write,
+							const char table_names[][TRACK_TABLE_MUTATION_TABLE_NAME_LEN],
+							int num_tables)

+uint64
+pool_track_table_mutation_normalize_and_hash(const char *query)

Best regards,

---

From: Nadav Shatz <[email-masked]>
Date: Thu, 26 Feb 2026 17:26:33 +0200

Hi Tatsuo,

Thank you for the thorough review and the thoughtful pushback on the
cross-session security concern. You're right that "dml_adaptive just hits
himself in the foot, your patch allows him to hit someone else's foot" —
that asymmetry needs a technical answer, not just a threat-model argument.

I've addressed this in the updated patch with two mechanisms:

*1. Maximum staleness cap (`track_table_mutation_max_staleness`)*
New configuration parameter (default: 60 seconds, range: 0–3600000 ms).
This bounds how long any single table entry can continuously force primary
routing, measured from the first write that created the entry. Even under
sustained writes, the entry expires after this period. If the table is
written to again after expiry, a fresh entry is created.

This directly addresses the concern: the worst-case cross-session impact is
bounded and configurable. An operator can look at this parameter and know:
"no matter what, a table's staleness effect on other sessions cannot exceed
X seconds continuously."

For legitimately busy tables, the brief gap between forced expiry and the
next write re-marking the table is negligible — typically milliseconds,
since writes are frequent. The correctness guarantee is preserved.

*2. Database-scoped isolation (documented)*

The tracking is already scoped by database OID — writes in one database
never affect routing decisions for sessions in a different database. I've
documented this explicitly as a security boundary in the docs. In
multi-tenant deployments with separate databases, tenants are isolated from
each other's write activity.

Combined with the existing safeguards (committed writes only, bounded table
map size, opt-in mode), the cross-session impact is now bounded in both
duration and scope.

I've also addressed the other review comments — they should be applied in
the patch as well. tests and code structure.

Thanks!

On Thu, Feb 26, 2026 at 9:47 AM Tatsuo Ishii <[email-masked]> wrote:

> +++
> b/src/test/regression/tests/043.track_table_mutation_watchdog/.gitignore
>
> +++
> b/src/test/regression/tests/043.track_table_mutation_watchdog/leader.conf
>
>  __sync_fetch_and_add(&track_table_mutation_shmem->state.stats_queries_checked,
> 1);
>
> +                                                       const char
> table_names[][TRACK_TABLE_MUTATION_TABLE_NAME_LEN],
> +                                                       int num_tables)
>
> +uint64

-- 
Nadav Shatz
Tailor Brands | CTO

---

From: Tatsuo Ishii <[email-masked]>
Date: Mon, 09 Mar 2026 14:18:39 +0900 (JST)

Hi Nadav,

Sorry for late response. I have been working on this issue.

> ./regress.sh 04[12]	<-- 042 fails

I ran following script to see if some sockets are left after the 041 test.

./regress.sh '041';netstat -ap|grep 11000;./regress.sh 042
:
testing 041.external_replication_delay...ok.
out of 1 ok:1 failed:0 timeout:0
(一部のプロセスが識別されますが, 所有していないプロセスの情報は
表示されません。それら全てを見るにはルートになる必要があります.)
tcp        0      0 0.0.0.0:11000           0.0.0.0:*               LISTEN      1401942/bash        
tcp        0      0 localhost:36366         localhost:11000         TIME_WAIT   -                   
tcp        0      0 localhost:36380         localhost:11000         TIME_WAIT   -                   
tcp        0      0 localhost:36384         localhost:11000         TIME_WAIT   -                   
tcp        0      0 localhost:36390         localhost:11000         TIME_WAIT   -                   
tcp        0      0 localhost:43580         localhost:11000         TIME_WAIT   -                   
tcp        0      0 localhost:43596         localhost:11000         TIME_WAIT   -                   
tcp6       0      0 [::]:11000              [::]:*                  LISTEN      1401942/bash        
unix  2      [ ACC ]     STREAM     LISTENING     10164557 1401942/bash         /tmp/.s.PGSQL.11000
creating pgpool-II temporary installation ...
moving pgpool_setup to temporary installation path ...
moving watchdog_setup to temporary installation path ...
using pgpool-II at /home/t-ishii/work/Pgpool-II/current/pgpool2/src/test/regression/temp/installed
*************************
REGRESSION MODE          : install
Pgpool-II version        : pgpool-II version 4.8devel (mitsukakeboshi)
Pgpool-II install path   : /home/t-ishii/work/Pgpool-II/current/pgpool2/src/test/regression/temp/installed
PostgreSQL bin           : /usr/local/pgsql/bin
PostgreSQL Major version : 18
pgbench                  : /usr/local/pgsql/bin/pgbench
PostgreSQL jdbc          : /usr/local/pgsql/share/postgresql-9.2-1003.jdbc4.jar
*************************
testing 042.track_table_mutation...failed.
out of 1 ok:0 failed:1 timeout:0

It seems the cause of the issue is the bash process:

unix  2      [ ACC ]     STREAM     LISTENING     10164557 1401942/bash         /tmp/.s.PGSQL.11000

It keeps on listening to the socket even after the test and it
prevents pgpool in 042 test from binding the socket, which causes the
test failure. Possible solutions are:

1) fix the external replication delay checking to close the listening
socket before starting bash.

2) close the listening socket when the streaming replication check
worker process forks.

While invensting the issue, I found similar problem is in other
places. For example, pcp process inherits pgpool listening sockets
which are not necessary for pcp. I posted a proposal to fix the issue:

https://www.postgresql.org/message-id/20260302.100028.1346768433787074248.ishii%40postgresql.org

This includes the fix #2.  I plan to commit the patch today. After our
buildfarm reports no new problem (it will take 2- 3 days), I am going
to test your patch again.

Best regards,

---

From: Nadav Shatz <[email-masked]>
Date: Mon, 9 Mar 2026 11:22:15 +0200

Thank you for looking into this, fixing it and getting back to me.

Looking forward to your update.

On Mon, Mar 9, 2026 at 6:18 AM Tatsuo Ishii <[email-masked]> wrote:

> using pgpool-II at
> /home/t-ishii/work/Pgpool-II/current/pgpool2/src/test/regression/temp/installed
> Pgpool-II install path   :
> /home/t-ishii/work/Pgpool-II/current/pgpool2/src/test/regression/temp/installed
> PostgreSQL jdbc          :
> /usr/local/pgsql/share/postgresql-9.2-1003.jdbc4.jar

-- 
Nadav Shatz
Tailor Brands | CTO

---

From: Tatsuo Ishii <[email-masked]>
Date: Mon, 23 Mar 2026 14:13:15 +0900 (JST)

> Looking forward to your update.

It seems my commit fixed the issue.
https://git.postgresql.org/gitweb/?p=pgpool2.git;a=commit;h=18f7f632de982d8fb5d0da2f2fdc48e26ac467e7

So, I continue the review.

+  <para>
+   This feature requires <xref linkend="guc-replication-delay-source-cmd"> to be configured
+   for monitoring replication delay from replicas.
+  </para>

Why this feature requires replication_delay_source_cmd to be set? Why
can't we enable the feature as well when delay_threshold_by_time > 0?
Both replication_delay_source_cmd and delay_threshold_by_time should
provide standy delay in time, which provides enogh information to
run the feature.

1. documentation

- I get a compile error.

openjade  -wall -wno-unused-param -wno-empty -wfully-tagged  -c /usr/share/sgml/docbook/stylesheet/dsssl/modular/catalog -d stylesheet.dsl -t sgml -i output-html  -V html-index pgpool.sgml
openjade:loadbalance.sgml:1122:21:X: reference to non-existent ID "RUNTIME-CONFIG-TRACK-TABLE-MUTATION"

Best regards,

---

From: Nadav Shatz <[email-masked]>
Date: Mon, 23 Mar 2026 14:07:33 +0100

Hi Tatsuo,

Thank you for the thorough review and the fix to the tests!. Here's the
updated patch addressing all your comments.

re - replication_delay_source_cmd requirement

Good catch — the feature now also works when `delay_threshold_by_time > 0`.
I've added the TTL update call to `check_replication_time_lag()` (the
pg_stat_replication path), not just
`check_replication_time_lag_with_cmd()`. The docs are updated to reflect
that either `replication_delay_source_cmd` or `delay_threshold_by_time` can
provide the time-based delay.

re - Documentation compile error

Fixed — the xref was pointing to `runtime-config-track-table-mutation` but
the actual section ID is `runtime-config-table-mutation-map`.

Thanks again and looking forward to hearing back from you.

On Mon, Mar 23, 2026 at 6:13 AM Tatsuo Ishii <[email-masked]> wrote:

> +   This feature requires <xref
> linkend="guc-replication-delay-source-cmd"> to be configured
> openjade  -wall -wno-unused-param -wno-empty -wfully-tagged  -c
> /usr/share/sgml/docbook/stylesheet/dsssl/modular/catalog -d stylesheet.dsl
> -t sgml -i output-html  -V html-index pgpool.sgml

-- 
Nadav Shatz
Tailor Brands | CTO

---

From: Tatsuo Ishii <[email-masked]>
Date: Tue, 07 Apr 2026 09:08:02 +0900 (JST)

Hi Nadav,

> Good catch ― the feature now also works when `delay_threshold_by_time > 0`.
> Fixed ― the xref was pointing to `runtime-config-track-table-mutation` but

While looking into your patch, I noticed that following part would be
better to be committed as a separate patch as it's actually a bug fix,
not related to the feature: query cache is not invalidated if MERGE
statement is executed. I will take care of it and get back to you.

--- a/src/query_cache/pool_memqcache.c
+++ b/src/query_cache/pool_memqcache.c
@@ -1305,6 +1305,12 @@ pool_extract_table_oids(Node *node, int **oidsp)
 		}
 		return num_oids;
 	}
+	else if (IsA(node, MergeStmt))
+	{
+		MergeStmt  *stmt = (MergeStmt *) node;
+
+		table = make_table_name_from_rangevar(stmt->relation);
+	}
 	else if (IsA(node, ExplainStmt))
 	{
 		ListCell   *cell;

Thanks for letting me know about the bug!

Regards,

---

From: Nadav Shatz <[email-masked]>
Date: Tue, 7 Apr 2026 07:45:03 +0200

Hi Tatsuo,

Yes I ran into it during the work on the feature. Let me know if you want
me to separately submit it.

Cheers

Nadav Shatz
Tailor Brands | CTO

---

From: Tatsuo Ishii <[email-masked]>
Date: Tue, 07 Apr 2026 18:10:09 +0900 (JST)

Hi Nadav,

> me to separately submit it.

Thank you for the offering, but I have already pushed the part.

https://git.postgresql.org/gitweb/?p=pgpool2.git;a=commit;h=24755985692be577bdcf487ebddb2c2ff6116661

Regards,

---

From: Tatsuo Ishii <[email-masked]>
Date: Tue, 07 Apr 2026 18:43:48 +0900 (JST)

Hi Nadav,

> https://git.postgresql.org/gitweb/?p=pgpool2.git;a=commit;h=24755985692be577bdcf487ebddb2c2ff6116661

I have modified your patch by just running pgindent (plus subtle
addition to typedefs.list). No detailed code review is done yet.  Also
I created a commit message which tries to summarize the
feature. Please let me know any correction and enhancement.

Based on this, I will start more detailed review. It will take a
while.

Regards,

---

From: Nadav Shatz <[email-masked]>
Date: Thu, 9 Apr 2026 09:21:06 +0200

Hi Tatsuo,

Looks good to me thanks!

Please go ahead with your review. waiting to hear back from you.

Best,

-- 
Nadav Shatz
Tailor Brands | CTO

---

From: Tatsuo Ishii <[email-masked]>
Date: Wed, 15 Apr 2026 07:43:16 +0900 (JST)

Hi Nadav,

> Please go ahead with your review. waiting to hear back from you.

Here are the code review results.

diff --git a/doc/src/sgml/loadbalance.sgml b/doc/src/sgml/loadbalance.sgml
index 9e1e7b39b..7384ce81a 100644
--- a/doc/src/sgml/loadbalance.sgml
+++ b/doc/src/sgml/loadbalance.sgml
:
+ <sect2 id="runtime-config-table-mutation-map">
+  <title>Table Mutation Map Configuration (Lagless Replica Reads)</title>

"(Lagless Replica Reads)" sounds like an advertisement to me. It
should be removed.

+  <para>
+   These parameters configure the track table mutation feature, which is activated by setting
+   <xref linkend="guc-disable-load-balance-on-write"> to <literal>dml_adaptive_global</literal>.
+   The feature tracks recently written tables to prevent stale reads from replica nodes during
+   replication lag, implementing the "lagless" architecture pattern for distributed systems
+   with read replicas.

I think the feature does not guarantee "lagless" anytime, in all cases.

+  <para>
+   This feature requires time-based replication delay monitoring. This can be provided by either
+   <xref linkend="guc-replication-delay-source-cmd"> (external command mode) or by setting
+   <xref linkend="guc-delay-threshold-by-time"> (which uses <literal>pg_stat_replication.replay_lag</literal>
+   from PostgreSQL 10+). At least one of these must be configured for the TTL calculation to work.

If one of these is not set, what happens? Error? Need to describe it.

+  </para>
+
+  <warning>
+   <para>
+    Enabling <literal>dml_adaptive_global</literal> increases shared memory consumption. With default settings,
+    the feature requires approximately 6.4 MB of shared memory (0.1 MB for table tracking + 6.3 MB for query cache).

"query cache" should be "query parse cache".

+    Memory usage scales with configuration parameters:
+   </para>
+   <itemizedlist>
+    <listitem>
+     <para>
+      Table tracking: <literal>track_table_mutation_table_size * 40 bytes</literal> (default: 2048 * 40 = ~80 KB)
+     </para>
+    </listitem>
+    <listitem>
+     <para>
+      Query cache: <literal>track_table_mutation_query_parse_cache_size * 640 bytes</literal> (default: 10000 * 640 = ~6.3 MB)

"query cache" should be "query parse cache".

+   <title>Limitations</title>

I think number of tables tacked in a SELECT is limited to 8. It should
be mentioned.

diff --git a/src/context/pool_query_context.c b/src/context/pool_query_context.c
index a056ac596..0190d3673 100644
--- a/src/context/pool_query_context.c
+++ b/src/context/pool_query_context.c
@@ -1828,15 +1829,23 @@ is_in_list(char *name, List *list)
 static bool
 is_select_object_in_temp_write_list(Node *node, void *context)
 {
-	if (node == NULL || pool_config->disable_load_balance_on_write != DLBOW_DML_ADAPTIVE)
+	if (node == NULL ||
+		!DLBOW_IS_DML_ADAPTIVE(
+							   pool_config->disable_load_balance_on_write))

You don't need to split the line.

+		is_adaptive = DLBOW_IS_DML_ADAPTIVE(
+											pool_config->disable_load_balance_on_write);

You don't need to split the line. 

-		if (pool_config->disable_load_balance_on_write == DLBOW_DML_ADAPTIVE && session_context->is_in_transaction)
+		if (is_adaptive &&
+			session_context->is_in_transaction)
 		{
 			ereport(DEBUG1,
 					(errmsg("is_select_object_in_temp_write_list: \"%s\", found relation \"%s\"", (char *) context, rgv->relname)));
This line is too long. Please split.

@@ -1880,7 +1889,13 @@ static char *get_associated_object_from_dml_adaptive_relations
 void
 check_object_relationship_list(char *name, bool is_func_name)
 {
-	if (pool_config->disable_load_balance_on_write == DLBOW_DML_ADAPTIVE && pool_config->parsed_dml_adaptive_object_relationship_list)
+	bool		is_adaptive;
+
+	is_adaptive = DLBOW_IS_DML_ADAPTIVE(
+										pool_config->disable_load_balance_on_write);

I wrote in the commit message:

modifications are only detected in the same transaction). Note,
however, you cannot use dml_adaptive_object_relationship_list to track
dependency among table and other objects.

In my understanding the feature does not use
dml_adaptive_object_relationship_list. If this is correct, why
check_object_relationship_list() is called here in case
dml_adaptive_global?  If the feature uses
dml_adaptive_object_relationship_list, test cases should be included.

diff --git a/src/utils/pool_track_table_mutation.c b/src/utils/pool_track_table_mutation.c
new file mode 100644
index 000000000..9be46b28f
--- /dev/null
+++ b/src/utils/pool_track_table_mutation.c

It seems following functions are not used anywhere. I wonder if this
feature actually use "query parse cache".

pool_track_table_mutation_get_cached_parse
pool_track_table_mutation_cache_parse
pool_track_table_mutation_normalize_and_hash

Besides the code review, I mutated one of regression tests to check
whether the feature co exists with in the existing memory query cache
feature. After attached patch applied, I ran 006.memqcache and got the
following result.

cd src/test/regression
./regress.sh 006
testing 006.memqcache...failed.

log/006.memqcache shows:

../expected.txt result.txt differ: char 1, line 1

So I checked the test script and found the error was generated by a
Java program test.

	java jdbctest > result.txt 2>&1
	cmp ../expected.txt result.txt
	if [ $? != 0 ];then
		./shutdownall
		exit 1
	fi

In jdbctest.java:

		/*
		 * Cache test in an explicit transaction
		 */
		conn.setAutoCommit(false);
		// execute DML. This should prevent SELECTs from using query cache in the transaction.
		sql = "UPDATE t1 SET i = 2;";
		pst = conn.createStatement();
		pst.executeUpdate(sql);
		pst.close();
		// should not use the cache and should return "2", rather than "1"
		prest = conn.prepareStatement("SELECT * FROM t1");
		rs = prest.executeQuery();

The expected file (expected.txt) has "2" but the result file
(testdir/result.txt) was "1".  This is the reason why the test
failed. I wonder if there's something wrong with the feature when the
query cache is enabled. Can you look into this?

Regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
diff --git a/src/test/regression/tests/006.memqcache/test.sh b/src/test/regression/tests/006.memqcache/test.sh
index f2371744d..1e854acc4 100755
--- a/src/test/regression/tests/006.memqcache/test.sh
+++ b/src/test/regression/tests/006.memqcache/test.sh
@@ -37,6 +37,7 @@ do
 	echo "log_per_node_statement = on" >> etc/pgpool.conf
 	echo "log_client_messages = on" >> etc/pgpool.conf
 	echo "log_min_messages = debug5" >> etc/pgpool.conf
+	echo "disable_load_balance_on_write = dml_adaptive_global" >> etc/pgpool.conf
 
 	source ./bashrc.ports

---

From: Nadav Shatz <[email-masked]>
Date: Wed, 15 Apr 2026 15:17:17 +0300

Hi Tatsuo,

hank you for the detailed review. Attached patch addresses all items.

memqcache bug fix
-----------------

Good catch. The root cause: pool_set_writing_transaction() was
explicitly skipping dml_adaptive_global, so
pool_is_writing_transaction() always returned false in this mode.
The query cache fetch guard at pool_proto_modules.c:270
(!pool_is_writing_transaction()) then served stale cached results
after DML in the same transaction.

Fix: pool_set_writing_transaction() now sets the flag for
dml_adaptive_global (only 'off' and 'dml_adaptive' skip it). This
ensures the query cache is properly bypassed after writes within
the same transaction.

Removed dead query parse cache code (~700 lines)
-------------------------------------------------

You're right -- pool_track_table_mutation_get_cached_parse,
pool_track_table_mutation_cache_parse, and
pool_track_table_mutation_normalize_and_hash were never called.
These were leftover from an earlier design where we planned to
cache SQL parse results in shared memory. The feature ended up
using pgpool's existing parser directly, and this code was never
wired up.

Removed: QueryParseCache and QueryParseEntry structs, all related
static functions, the TRACK_TABLE_MUTATION_QUERY_SEM semaphore,
and the track_table_mutation_query_buckets /
track_table_mutation_query_parse_cache_size configuration
parameters. This also reduces shared memory usage from ~6.4 MB
to ~80 KB with default settings.

check_object_relationship_list scope
-------------------------------------

You're correct -- dml_adaptive_global does not use
dml_adaptive_object_relationship_list. Changed
check_object_relationship_list() to check for DLBOW_DML_ADAPTIVE
only, not DLBOW_IS_DML_ADAPTIVE (which includes global).

Documentation fixes
-------------------

- Removed "(Lagless Replica Reads)" from section title and
  "lagless" language from description.

- Described fallback behavior when neither
  replication_delay_source_cmd nor delay_threshold_by_time is
  configured (TTL stays at 100ms default minimum).

- "query cache" references removed (the query parse cache is gone).

- Added 128-table-per-SELECT limit to Limitations section
  (uses POOL_MAX_SELECT_OIDS).

Code style fixes
----------------

- DLBOW_IS_DML_ADAPTIVE() calls no longer split across lines.

- Split the long errmsg line in
  is_select_object_in_temp_write_list.

- Removed redundant is_adaptive variable in
  is_select_object_in_temp_write_list (the check at function
  entry already guarantees it).

Thanks!

On Wed, Apr 15, 2026 at 1:43 AM Tatsuo Ishii <[email-masked]> wrote:

> +   <xref linkend="guc-delay-threshold-by-time"> (which uses
> <literal>pg_stat_replication.replay_lag</literal>
> +    Enabling <literal>dml_adaptive_global</literal> increases shared
> memory consumption. With default settings,
> +    the feature requires approximately 6.4 MB of shared memory (0.1 MB
> for table tracking + 6.3 MB for query cache).
>
> +      Table tracking: <literal>track_table_mutation_table_size * 40
> bytes</literal> (default: 2048 * 40 = ~80 KB)
> +     </para>
> +    </listitem>
> +    <listitem>
> +     <para>
> +      Query cache: <literal>track_table_mutation_query_parse_cache_size *
> 640 bytes</literal> (default: 10000 * 640 = ~6.3 MB)
>
> pool_config->disable_load_balance_on_write))
>
>              pool_config->disable_load_balance_on_write);
>
> -               if (pool_config->disable_load_balance_on_write ==
> DLBOW_DML_ADAPTIVE && session_context->is_in_transaction)
> +               if (is_adaptive &&
> (errmsg("is_select_object_in_temp_write_list: \"%s\", found relation
> \"%s\"", (char *) context, rgv->relname)));
> @@ -1880,7 +1889,13 @@ static char
> *get_associated_object_from_dml_adaptive_relations
>  void
> -       if (pool_config->disable_load_balance_on_write ==
> DLBOW_DML_ADAPTIVE &&
> pool_config->parsed_dml_adaptive_object_relationship_list)
> +       bool            is_adaptive;
> +
>      pool_config->disable_load_balance_on_write);
>

-- 
Nadav Shatz
Tailor Brands | CTO

---

From: Tatsuo Ishii <[email-masked]>
Date: Sun, 19 Apr 2026 16:24:44 +0900 (JST)

> hank you for the detailed review. Attached patch addresses all items.

I guess the attached patch is on top of
v1-0001-Feature-load-balancing-control-by-table-tracking.patch.  To
apply v2-0001-address-review.patch, we need to apply
v1-0001-Feature-load-balancing-control-by-table-tracking.patch first.
Unfortunately due to recent commit, it does not apply anymore. Can you
please provide v1 + v2 that are rebased against latest master branch?
Also 042 regression test is already used by recent commit. Can you
renumber 042.track_table_mutation and
043.track_table_mutation_watchdog to 043.track_table_mutation and
044.track_table_mutation_watchdog accordingly?

Looking forward to seeing new patch.

Regards,

---

From: Nadav Shatz <[email-masked]>
Date: Sun, 19 Apr 2026 17:29:42 +0300

Hi Tatsuo,

Rebased onto current master, renumbered the regression tests
(043/044 to avoid collision with the new 042.ssl_reload), and
combined everything into a single commit.

Attached: v2-0001-Feature-load-balancing-control-by-table-tracking.patch

Looking forward to your review.

On Sun, Apr 19, 2026 at 10:25 AM Tatsuo Ishii <[email-masked]> wrote:

> >> +   These parameters configure the track table mutation feature, which
> is
> >> activated by setting
> >> +   The feature tracks recently written tables to prevent stale reads
> from
> >> replica nodes during
> >> +   from PostgreSQL 10+). At least one of these must be configured for
> the
> >> TTL calculation to work.
> >>
> >> +      Query cache:
> <literal>track_table_mutation_query_parse_cache_size *
> >> -       if (node == NULL || pool_config->disable_load_balance_on_write
> !=
> >> DLBOW_DML_ADAPTIVE)
> >> +       if (node == NULL ||
> >>                 // should not use the cache and should return "2",
> rather
> >> than "1"

-- 
Nadav Shatz
Tailor Brands | CTO

---

From: Tatsuo Ishii <[email-masked]>
Date: Thu, 23 Apr 2026 17:14:08 +0900 (JST)

Hi Nadav,

Unfortunately the mutated 006.memqcache failed (timeout).

Regards,

---

From: Nadav Shatz <[email-masked]>
Date: Thu, 23 Apr 2026 17:16:24 +0300

Hi Tatsuo,

Good catch on the 006.memqcache timeout. My previous fix had
wrong side effects -- setting writing_transaction for
dml_adaptive_global also changed routing behavior (it forced the
whole transaction to primary, effectively reducing the feature to
'transaction' mode). That's what caused the hang.

Fixed properly in v3: instead of touching writing_transaction,
added a memqcache-specific guard that checks whether the current
dml_adaptive* session has tracked writes in the current
transaction, and skips the cache fetch if so.

Attached: v3-0001-Feature-load-balancing-control-by-table-tracking.patch

Changes in v3 vs v2:

- pool_set_writing_transaction() reverted to original behavior
  (dml_adaptive_global no longer sets writing_transaction, so
  routing stays per-table as intended).

- Added new helper pool_has_dml_adaptive_write_in_transaction()
  in pool_session_context.c. Returns true when the current session
  is in dml_adaptive* mode, is inside an explicit transaction, and
  has already tracked at least one write (via
  transaction_temp_write_list).

- The two memqcache fetch guards in pool_proto_modules.c
  (simple query at line 270, extended query at line 1028) now
  also call !pool_has_dml_adaptive_write_in_transaction().
  Autocommit writes in dml_adaptive_global are still handled by
  the existing pool_invalidate_query_cache() at COMMIT time --
  no change needed there.

Verified locally by mutating 006.memqcache with
disable_load_balance_on_write = 'dml_adaptive_global' in the
streaming replication mode (the only mode where dml_adaptive
applies) and the jdbctest now correctly returns "2" instead of
the stale cached "1". Both 006.memqcache and 043.track_table_mutation
pass.

Thanks!

On Thu, Apr 23, 2026 at 11:14 AM Tatsuo Ishii <[email-masked]> wrote:

> >> >> +   replication lag, implementing the "lagless" architecture pattern
> for
> >> >> distributed systems
> >> >> +   with read replicas.
> >> >>
> >> >> +   This feature requires time-based replication delay monitoring.
> This
> >> >> can be provided by either
> >> >> +   <xref linkend="guc-replication-delay-source-cmd"> (external
> command
> >> >> mode) or by setting
> >> >> -       if (node == NULL ||
> pool_config->disable_load_balance_on_write
> >> !=
> >> >> DLBOW_DML_ADAPTIVE)
> >> >> +       if (node == NULL ||
> >> >>                 // execute DML. This should prevent SELECTs from
> using
> >> >> query cache in the transaction.

-- 
Nadav Shatz
Tailor Brands | CTO

---

From: Nadav Shatz <[email-masked]>
Date: Mon, 18 May 2026 12:54:20 +0300

Hi Tatsuo,

Any update on this proposal?

Thanks

On Thu, Apr 23, 2026 at 5:16 PM Nadav Shatz <[email-masked]> wrote:

>> >> >> +   replication lag, implementing the "lagless" architecture
>> pattern for
>> >> >> distributed systems
>> >> >> +   with read replicas.
>> >> >>

-- 
Nadav Shatz
Tailor Brands | CTO

---

From: Tatsuo Ishii <[email-masked]>
Date: Mon, 18 May 2026 19:11:05 +0900 (JST)

Hi Nadav,

Sorry, I missed your last email.
Will check & test tomorrow.

Regards,

---

From: Tatsuo Ishii <[email-masked]>
Date: Wed, 20 May 2026 13:28:10 +0900 (JST)

> Will check & test tomorrow.

I finally got a chance to test your v3 patch.
Unfortunately the test failed with timeout again.

testing 006.memqcache...timeout.
out of 1 ok:0 failed:0 timeout:1

---

From: Nadav Shatz <[email-masked]>
Date: Wed, 20 May 2026 15:25:54 +0300

Hi Tatsuo,

Thanks for checking the V3, sorry for missing the test issue.

I reproduced the timeout locally. Found and fixed the root cause.

Root cause
----------

In CommandComplete.c, the autocommit write-tracking code was
gated only on session_context->is_in_transaction, not on the
cluster mode.

In native replication and snapshot isolation modes,
dml_adaptive() is never called (it lives inside
where_to_send_main_replica), so is_in_transaction is never set
to true even inside an explicit BEGIN/COMMIT block.  That meant
every DML in those modes was treated as autocommit by the
write-tracking code, triggering
pool_track_table_mutation_get_database_oid() — which does a
relcache do_query — while a transaction was actually in flight
on the backend connection.  The do_query conflicts with the
in-flight transaction and hangs the session.  Subsequent
shutdown then hangs in terminate_all_childrens / waitpid.

Fix
---

Gate the autocommit write-tracking in CommandComplete.c on
MAIN_REPLICA in addition to the existing checks.
dml_adaptive_global is only meaningful in streaming replication
mode anyway (the matching routing logic in
where_to_send_main_replica is already SR-only), so this just
makes the autocommit path consistent.

Also broadened the query cache bypass to all dml_adaptive*
modes.  The new helper pool_has_dml_adaptive_write_in_transaction()
checks the existing memqcache DML oid buffer (oidbufp via the
new pool_has_dml_table_oids()), which is populated for any DML
in any cluster mode and reset on transaction boundary.  This
fixes the original "SELECT returns stale 1 instead of 2 after
UPDATE" regression in streaming replication and avoids the same
class of bug in plain dml_adaptive too.

Verified
--------

- 006.memqcache with disable_load_balance_on_write =
  'dml_adaptive_global' appended in all three modes: PASS
- 043.track_table_mutation: PASS

Attached: v4-0001-Feature-load-balancing-control-by-table-tracking.patch

Thanks!

On Wed, May 20, 2026 at 7:28 AM Tatsuo Ishii <[email-masked]> wrote:

> From src/test/regression/log/006.memqcache:
>
> 2026-05-20 13:08:33.798: main pid 3562591: LOG:  stop request sent to
> pgpool (pid: 3561918). waiting for termination...
> .....2026-05-20 13:08:38.799: main pid 3562591: LOG:  stop request sent to
> pgpool (pid: 3561918). waiting for termination...
> .....2026-05-20 13:08:43.801: main pid 3562591: LOG:  stop request sent to
> pgpool (pid: 3561918). waiting for termination...
>
> It seems pgpool main process won't stop.
>
> Regards,
> --
> Tatsuo Ishii
> SRA OSS K.K.

-- 
Nadav Shatz
Tailor Brands | CTO

---

From: Tatsuo Ishii <[email-masked]>
Date: Thu, 21 May 2026 18:50:44 +0900 (JST)

Hi Nadav,

> I reproduced the timeout locally. Found and fixed the root cause.

I think you are talking about the logic to judge whether we are in an
explicite transaction or not here. Current dml_adaptive checks
supplied query is a transaction starting command like BEGIN. IMO this
is fundamentaly wrong because the command may fail by various reasons.
The correct way is checking transaction state by using TSTATE
macro. Note that the macro can only be used at leat one ready for
query response returned from backend (simple query protocol case), or
command complete response is returned from backend (extended query
protocol case).

> pool_track_table_mutation_get_database_oid() ― which does a
> relcache do_query ― while a transaction was actually in flight
> in-flight transaction and hangs the session.

Assuming "a transaction was actually in flight" means a transaction
was open (explicit transaction), not really. do_query can be called
inside or outside of an explicit transaction.

Anyway, I found dml_adaptive is completely broken (it brings wrong
results if query cache enabled). Unless there are users for the
feature, maybe we should remove dml_adaptive entirely?

Regards,

---

From: Tatsuo Ishii <[email-masked]>
Date: Sat, 23 May 2026 20:18:16 +0900 (JST)

> feature, maybe we should remove dml_adaptive entirely?

It appears that other options of disable_load_balance_on_write are all
broken too, except "transaction". I don't want to discard all of them,
so I come up with attached patch.

The query cache relies on is_writing_transaction of session context to
judge whether cache can be safely used. However,
disable_load_balance_on_write overrides it to true when it should not,
and vice versa for its own purpose. To fix this, a new session context
variable "really_writing_transaction" is introduced. It is almost same
as existing writing_transaction, but it faithfully tracks whether a
writing query appears in an explicit transaction. The query cache uses
it instead of writing_transaction variable.

Currently, master branch is broken because of commit 2ae004a48.  If
you want to try the patch, I recommend to checkout 48e1d6d3c, then
apply the patch.

Regards,

---

From: Nadav Shatz <[email-masked]>
Date: Sun, 24 May 2026 20:00:47 +0300

Hi Tatsuo,

Your really_writing_transaction approach is the right fix -- it
addresses the root cause across all DLBOW modes, not just ours.
Thanks for digging into it.

I applied your v1 patch and rebased our feature on top.  Attaching
both patches separately so they can land independently in the
order you prefer:

  v5-0001-Fix-disable_load_balance_on_write-and-query-cache.patch
    -- your patch unchanged (just rebased to apply cleanly on
       current master without our feature underneath).

  v5-0002-Feature-load-balancing-control-by-table-tracking.patch
    -- our feature, on top of your fix.

Changes in v5-0002 vs v4:

- Dropped pool_has_dml_adaptive_write_in_transaction() helper and
  the matching pool_has_dml_table_oids() exposure.  The cache
  fetch guards in pool_proto_modules.c now correctly use
  pool_is_really_writing_transaction() from your patch, so the
  helper became redundant.

- Kept the MAIN_REPLICA gate in CommandComplete.c for the
  autocommit mark-stale branch.  dml_adaptive_global is only
  meaningful in streaming replication mode (matches the routing
  logic in where_to_send_main_replica), and gating prevents the
  hang we saw in native_replication where the autocommit branch
  could run while an explicit transaction was actually in flight
  on the backend.

I tried to run 006.memqcache with the mutation against the
combined branch but local master is currently broken (commit
2ae004a48 as you noted), so the standby setup fails before
reaching the jdbctest part.  Both patches build cleanly and our
043.track_table_mutation passes on an earlier base.  Will retest
once master is unbroken.

Thanks!

-- 
Nadav Shatz
Tailor Brands | CTO

---

From: Tatsuo Ishii <[email-masked]>
Date: Sun, 31 May 2026 14:09:28 +0900 (JST)

Hi Nadav,

Thank you for v5 patches. I applied both of them to today's master and
all regression tests and modified 006 passed. Conguratulations!

I will continue to review your patches.

Regards,

---

From: Tatsuo Ishii <[email-masked]>
Date: Thu, 04 Jun 2026 16:14:40 +0900 (JST)

Hi Nadav,

>>   v5-0001-Fix-disable_load_balance_on_write-and-query-cache.patch

I updated the patch to:
v2-0001-Fix-disable_load_balance_on_write-to-not-break-qu.patch

I slightly modifies the first line of the commit message, and the file
name has been changed too. In v2 patch, I added test cases for
disable_load_balance_on_write options. Currently it includes test
cases for trans_transaction, dml_adaptive and always. Other than that,
nothing has been changed since v1. I plan to push to all supported
branches after the June minor releases.

After finishing review for your patch, I will push your patch along
with addition to 006.memqcache so that it tests the new option.

Regards,

---

From: Nadav Shatz <[email-masked]>
Date: Thu, 4 Jun 2026 10:16:02 +0300

Amazing news! Thank you so much!

When can I expect it to be live in a version then ?

Nadav Shatz
Tailor Brands | CTO

---

From: Tatsuo Ishii <[email-masked]>
Date: Thu, 04 Jun 2026 16:33:11 +0900 (JST)

> Amazing news! Thank you so much!

You are welcome!

> When can I expect it to be live in a version then ?

It will get into v4.8, which is supposed to be released in this
December.

Regards,

---

From: Nadav Shatz <[email-masked]>
Date: Thu, 4 Jun 2026 11:55:12 +0300

no way to get it earlier? we really wanna start running this in prod.
or is it too big for a patch version and minor only come then?

-- 
Nadav Shatz
Tailor Brands | CTO

---

From: Tatsuo Ishii <[email-masked]>
Date: Thu, 04 Jun 2026 18:59:36 +0900 (JST)

> no way to get it earlier?

Right. The project policy only allows to add new features to the next
major version (this is the same policy as PostgreSQL). If new features
are added to stable releases, it will bring enough confusions to
existing users which we want to avoid on the stable versions.

>  we really wanna start running this in prod.

Regards,

---

From: Nadav Shatz <[email-masked]>
Date: Thu, 4 Jun 2026 13:20:01 +0300

understood. we'll wait patiently or try master.

thanks!

-- 
Nadav Shatz
Tailor Brands | CTO

---

From: Tatsuo Ishii <[email-masked]>
Date: Fri, 05 Jun 2026 13:49:06 +0900 (JST)

Hi Nadav,

> branches after the June minor releases.

pushed.

https://git.postgresql.org/gitweb/?p=pgpool2.git;a=commit;h=1333d2ae88ac180db6ed423866086b31424a455e

Regards,