ADR 001 Deferred Bind ConnectionProvider

ADR 001: Deferred-Bind ConnectionProvider for Transaction-Local GUC

Status

Accepted

Context

The audit trigger framework (V1.0.5) writes complete OLD/NEW JSONB row images into hrs.audit_event and hrs.audit_change. To support distributed tracing correlation, the B3 trace ID from the current Micrometer span must be captured into audit_event.correlation_id without introducing AOP, request filters, or application ThreadLocal state.

PostgreSQL GUCs set with set_config('app.correlation_id', ?, true) are transaction-local — they are discarded on commit/rollback and do not bleed into subsequent transactions on pooled connections. The challenge is when to execute the set_config statement: Hibernate acquires the JDBC connection before applying the caller's transaction isolation level.

The project uses SERIALIZABLE isolation on service methods. Any statement executed during connection acquisition opens the PostgreSQL transaction block first, making a subsequent SET TRANSACTION ISOLATION LEVEL SERIALIZABLE fail with:

PSQLException: Cannot change transaction isolation level in the middle of a transaction

Decision

Use a deferred-bind ConnectionProvider decorator (CorrelationIdConnectionProvider) that wraps the pooled connection in a DeferredCorrelationIdConnection. The wrapper executes SELECT set_config('app.correlation_id', ?, true) lazily — immediately before the first statement-producing call (createStatement, prepareStatement, prepareCall, or setSavepoint), not at connection acquisition time.

This ordering ensures the GUC binding lands after Spring has applied transaction isolation, inside whatever transaction Spring/Hibernate established.

Additionally, pool-wide spring.datasource.hikari.auto-commit=false is required so that set_config(..., true) executes inside an active transaction block (autocommit-on single statements never have one).

Consequences

Positive

• No AOP, no request filter, no application ThreadLocal — minimal surface area.
• GraalVM/native-image safe: no reflection, no proxies, no runtime bytecode.
• Transaction-local GUC (true argument) eliminates need for explicit reset bookkeeping.
• Works with Hibernate's default DELAYED_ACQUISITION_AND_RELEASE_AFTER_TRANSACTION connection handling mode.

Negative

• The DeferredCorrelationIdConnection wrapper implements the full java.sql.Connection interface (464 lines) — every method must forward correctly.
• Pool-wide auto-commit=false changes the default connection behavior for all Hibernate operations.
• The HibernatePropertiesCustomizer bean uses deprecated DatasourceConnectionProviderImpl APIs (@SuppressWarnings({"deprecation", "removal"})) — intentional and must not be simplified to bean-container SPI.

Risks

• Native-image compilation and runtime validation were not run during the spike. Follow-up validation required.
• The set_config cost per connection acquisition should be measured if connection handling changes.

Alternatives Considered

Option A — Eager binding in getConnection()

Execute setAutoCommit(false) + set_config directly in getConnection(). Rejected: opens the transaction block before Spring applies isolation, breaking every SERIALIZABLE service method.

Option B — Spring AOP / @Around advice

Intercept service methods and set the GUC after transaction setup. Rejected: introduces AOP proxy complexity, breaks for self-invocation, and is not GraalVM-safe without additional configuration.

Option C — Request filter (OncePerRequestFilter)

Set the GUC in an HTTP filter. Rejected: filters execute before the transaction begins; the GUC would be session-scoped and could bleed across requests on pooled connections.

Option D — Session-scoped GUC + reset in closeConnection()

Use set_config(..., false) (session-scoped) and reset in closeConnection(). Rejected: leaks state on any missed close path; the deferred-bind design with transaction-local GUC is strictly safer.

Option E — LazyConnectionDataSourceProxy

Hibernate's LazyConnectionDataSourceProxy buffers statements until first use. Rejected: does not solve the isolation-level ordering problem; the proxy still opens the transaction before the caller's isolation is applied.

Follow-up

• GraalVM native-image compilation and runtime validation
• Measure set_config cost per connection acquisition
• V1.0.6 adds or guards correlation_id, creates a partial lookup index, and reads the GUC with current_setting('app.correlation_id', true). Existing V1.0.5 databases retain the effective VARCHAR(128) width; any narrowing requires a separate reviewed migration.

Sources

• CorrelationIdConnectionProvider.java
• GlobalConfiguration.java
• V1.0.6__audit_correlation_id.sql
• PR #1258