Status: Accepted
ADR-0016 added the multi-row VALUES batch rewrite (rewriteBatchUsingMultipleValues), the
pgjdbc-faithful analogue of reWriteBatchedInserts. It decomposes a batched INSERT into a few
power-of-two-sized chunks to bound both the 65535-parameter cap and prepared-statement-cache churn, and
described each chunk as "one round-trip and one execution," collapsing N round-trips into ceil(N/chunk).
The executor realized that literally: each chunk runs through core.execute(sql, params) -- a blocking
Bind/Execute/Sync drained to ReadyForQuery before the next chunk. So a 100-row batch (chunks 64+32+4) costs
three serial network round trips, versus the array rewrite's single round trip (ADR-0014). Benchmarks
put the compat multi-values path at ~81% of pgjdbc throughput; the gap is the extra serial round trips
(pgjdbc sends its rewritten batch under a single Sync).
An attempt to run the chunks through the general pipelining API (ADR-0019, PgPipeline) under one Sync
regressed further (~61%). PgPipeline scopes its named statements to a single pipeline and Closes them at
close(); a pipeline's lifetime is one executeBatch, so every batch re-Parsed -- and the server
re-planned -- the 64/32/4-row VALUES statements. The serial path avoided this by reusing the connection's
persistent prepared-statement cache (ADR-0012).
The requirements are jointly: one network round trip per batch (like the array rewrite and pgjdbc), and reuse of the persistent statement cache (parse each chunk SQL once, reuse across all batches).
Execute all chunks of one multi-values batch over a single Sync section, via a new core primitive that
resolves each chunk through the existing persistent-cache promotion policy. Do not extend PgPipeline.
Add CoreConnection.executeMultiStatementBatch(sqls, parameterSets) -- a sibling to executeBatch -- that
runs a small set of heterogeneous statements (the chunks) under one Sync. It reuses the shared machinery:
PipelinedExchange (the windowed engine already behind executeBatch and PgPipeline), batchRunStatement
(the cache lookup/promotion policy), BatchSectionSink (positional CommandComplete counting), and
failedBatchStatement (per-statement cleanup). PgPipeline is deliberately left untouched: making it share
the connection's persistent cache would force a busy connection to perform the synchronous, idle-only
Describe that promotion requires, and would change semantics for every pipeline consumer to serve one batch
use case (rejected; may be revisited under its own ADR).
Each chunk's statement is resolved before the connection goes busy, reusing
batchRunStatement(sql, oids, canPrepare=true): a cache hit reuses the server statement with no Parse and no
Close (the reuse that fixes the regression); a miss at prepareThreshold promotes with a one-time
synchronous prepare; below threshold it uses a fresh per-call named statement parsed inline and Closed after.
Every chunk binds a distinct named statement, so no two chunks contend for the unnamed statement.
Resolution (codec and declared-type-OID selection, including any composite lookup) runs up front while idle,
so a resolution error surfaces before any send; the value bytes are not materialized here -- they encode
write-through into the outbound buffer as each Bind is appended, with no intermediate byte[][].
The chunks' Parse-if-needed / Bind / Execute are streamed into one PipelinedExchange window terminated by a
single Sync. The whole row-free batch is buffered and flushed once (as pgjdbc sends a rewritten INSERT batch)
rather than flushing windows mid-section -- the server's tiny replies never fill its send buffer, so even a
large single write cannot deadlock, and buffering it all keeps the batch atomic (nothing is flushed and
committed piecemeal). All chunks therefore run in one implicit transaction: any chunk's server error --
or a commit-time error at the Sync (e.g. a deferred constraint) -- rolls the whole batch back, and every
element is reported Statement.EXECUTE_FAILED.
This changes the multi-values failure contract from the prior per-chunk partial commit (earlier chunks committed, the failing chunk applied nothing, later chunks did not run) to all-or-nothing. The prior behavior was an artifact of serial per-chunk execution, not a deliberate contract. All-or-nothing:
- matches pgjdbc, whose rewritten INSERT batch runs under a single Sync (it inserts an intermediate Sync only when the estimated receive buffer crosses 64 KB, which a row-less INSERT never does), so a mid-batch failure rolls the whole batch back;
- matches pg-java's own array rewrite (ADR-0014), which is a single statement and thus inherently all-or-nothing;
- is permitted by the JDBC spec, which lets a driver stop or continue after a failing batch element and leaves persistence of executed commands to the transaction context.
Per-element success counts are unchanged: each expanded tuple inserts one row, so every element reports 1
(CountSemantics.ONE_PER_ELEMENT, ADR-0016).
- The multi-values batch is one network round trip per batch (steady state), matching the array rewrite and pgjdbc, closing the ~20% throughput gap; allocation is unchanged (a round-trip fix, not an allocation one).
- Chunk statements are parsed once and reused across all batches via the persistent cache (ADR-0012); the regressing per-batch re-Parse is gone. A bounded, small number of prepared statements (the distinct power-of-two chunk sizes) is created regardless of batch count -- added as a regression test.
- The multi-values failure contract becomes all-or-nothing, revising the per-chunk partial-commit behavior described in ADR-0016 and its integration test. This aligns it with the array rewrite and pgjdbc.
- A new, contained core execution primitive (
executeMultiStatementBatch) joinsexecute/executeBatch;PgPipelineand every other consumer are unaffected. Heterogeneous-under-one-Sync execution stays in the batch path and does not tangle with the homogeneous-per-windowexecuteBatchcontrol flow. - pgjdbc parity improves on both plan-cache profile (ADR-0016) and now round-trip/commit behavior, strengthening apples-to-apples comparison.
Relates to ADR-0016 (the multi-values rewrite whose execution this refines), ADR-0014 (the sibling array
rewrite and its all-or-nothing precedent), ADR-0019 (the pipelining engine reused here, and the PgPipeline
API deliberately not used), ADR-0012 (prepared-statement cache and promotion policy), and ADR-0004
(documented divergences from pgjdbc).