Date: Thu, 19 Feb 2026 08:51:21 +0900 (JST)
Message-Id: <20260219.085121.1424536813757196179.ishii@postgresql.org>
To: nadav@tailorbrands.com
Cc: pgpool-hackers@lists.postgresql.org
Subject: Re: Proposal: Recent mutated table tracking in memory
From: Tatsuo Ishii <ishii@postgresql.org>
In-Reply-To: 
 <CACeKOO21jgrafj6RzO+ibLjBDUdDeQ0Tuu3tatkmGUdQi+GAAQ@mail.gmail.com>
References: 
 <CACeKOO2QqpnML1OkQqqCCc+xG1d2M+sS7y7zE9vw5W-DXu+xKQ@mail.gmail.com>
	<20260211.192845.1888610184173239311.ishii@postgresql.org>
	<CACeKOO21jgrafj6RzO+ibLjBDUdDeQ0Tuu3tatkmGUdQi+GAAQ@mail.gmail.com>
Mime-Version: 1.0
Content-Type: Multipart/Mixed;
 boundary="--Next_Part(Thu_Feb_19_08_51_21_2026_655)--"
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/20260219.085121.1424536813757196179.ishii%40postgresql.org>
Precedence: bulk

----Next_Part(Thu_Feb_19_08_51_21_2026_655)--
Content-Type: Text/Plain; charset=iso-2022-jp
Content-Transfer-Encoding: 7bit

>  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;
>   UPDATE t1 SET i = 1 WHERE FALSE;
>   ROLLBACK;
> 
>   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!

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

----Next_Part(Thu_Feb_19_08_51_21_2026_655)--
Content-Type: Text/Plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline; filename="042.track_table_mutation"

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

----Next_Part(Thu_Feb_19_08_51_21_2026_655)----