public inbox for [email protected]
help / color / mirror / Atom feedFrom: Tatsuo Ishii <[email protected]>
To: [email protected]
Cc: [email protected]
Subject: Re: Proposal: Recent mutated table tracking in memory
Date: Thu, 19 Feb 2026 08:51:21 +0900 (JST)
Message-ID: <[email protected]> (raw)
In-Reply-To: <CACeKOO21jgrafj6RzO+ibLjBDUdDeQ0Tuu3tatkmGUdQi+GAAQ@mail.gmail.com>
References: <CACeKOO2QqpnML1OkQqqCCc+xG1d2M+sS7y7zE9vw5W-DXu+xKQ@mail.gmail.com>
<[email protected]>
<CACeKOO21jgrafj6RzO+ibLjBDUdDeQ0Tuu3tatkmGUdQi+GAAQ@mail.gmail.com>
> 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
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
Attachments:
[text/plain] 042.track_table_mutation (7.8K, 2-042.track_table_mutation)
download | inline:
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
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Proposal: Recent mutated table tracking in memory
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox