Re: Proposal: Recent mutated table tracking in memory

> 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