Message-ID: <gh-pgjdbc-pgjdbc-3895-c3953315567@github.com>
From: "vlsi (@vlsi)" <noreply+vlsi@github.com>
To: "pgjdbc/pgjdbc" <noreply+pgjdbc-pgjdbc@github.com>
Date: Tue, 24 Feb 2026 16:32:44 +0000
Subject: Re: [pgjdbc/pgjdbc] PR #3895: implement require_auth, this is pretty much how libpq does this. 
In-Reply-To: <gh-pgjdbc-pgjdbc-3895@github.com>
References: <gh-pgjdbc-pgjdbc-3895@github.com>
List-Id: <gh-pgjdbc-pgjdbc.github.com>
X-GitHub-Author-Login: vlsi
X-GitHub-Comment-Id: 3953315567
X-GitHub-Comment-Type: issue_comment
X-GitHub-Issue: 3895
X-GitHub-Repo: pgjdbc/pgjdbc
X-GitHub-Type: comment
X-GitHub-Url: https://github.com/pgjdbc/pgjdbc/pull/3895#issuecomment-3953315567
Content-Type: text/plain; charset=utf-8

By the way, I asked Opus 4.6 to identify the reason for the failure (I gave it a stacktrace and a link to the CI log), and it ended up with virtually the same analysis (I haven't had time to test it manually, so I did not post the results):

> The failure occurs in only one CI configuration:
> Java 21, PG 9.1, autosave always, rewrite_batch_inserts, query_timeout 15
> The key parameter is **autosave=always**.
> **What's happening**
> 1. DatabaseEncodingTest.encoding() performs ~11,100 INSERTs in a single transaction (autoCommit=false):
>   - 1..0xd800 step 100 → ~553 inserts
>   - 0xe000..0x10000 → ~82 inserts
>   - 0x10000..0x110000 → ~10,486 inserts
> 2. With autosave=always, pgjdbc wraps every statement in SAVEPOINT / RELEASE SAVEPOINT. That's ~11,100 nested subtransactions.
> 3. In PostgreSQL, each savepoint (subtransaction) consumes entries in the shared lock table. Even after RELEASE SAVEPOINT, lock entries are merged into the parent transaction, but shared memory slots are not freed until commit.
> 4. The lock table size = max_locks_per_transaction × max_connections = 64 × 100 = 6,400 slots (defaults). With 11,000+ subtransactions, the table overflows.
> 5. PG 9.1 is a very old version with less efficient subtransaction lock management. The same test passes on newer PG versions (9.3, 11, 14, 17).
> **How to investigate further**
> 1. Check if it reproduces without autosave=always — most likely it won't
> 2. Check history — this may be a flaky test unrelated to PR #3895. Look at whether this job also fails intermittently on master
> 3. Reproduce locally — run PG 9.1 with default max_locks_per_transaction=64 and autosave=always, then run the encoding test
> **Possible fixes**
> - Increase max_locks_per_transaction in the CI entrypoint.sh (e.g., to 256)
> - Add periodic con.commit() calls inside the test loop to release locks
> - Skip the test when autosave=always on old PG versions
> - Drop PG 9.1 from the test matrix — it has been EOL since 2016
> The simplest and most correct fix is either increasing max_locks_per_transaction in the CI PostgreSQL configuration, or removing PG 9.1 from the test matrix entirely.