Re: [PATCH] Add immediate_global_insert_visibility GUC for immediate frozen inserts

Hi Hannu,

I've tested your immediate_global_insert_visibility patch on my local
PostgreSQL build.

*Test results:*

-

Isolation tests: all 134 passed.
-

New immediate_insert_visibility regression test: passed.
-

domain regression test run individually: passed.
-

Full regression suite run sequentially: all 240 tests passed.
-

Full regression suite run with the standard parallel schedule: one
failure in domain, reporting a deadlock between two processes waiting
for relation locks.

The domain failure did not reproduce when I ran the full regression suite
sequentially. This suggests a concurrency-dependent interaction, but I
haven't established whether the patch is responsible. I wanted to report it
rather than assume the sequential pass rules out a problem.

The patch is committed locally on branch
Add_immediate_global_insert_visibility_GUC, at commit 168c6530183.

Best regards,
Osama Abdul Qader

On Sat, 17 Oct 2026 at 19:13, Hannu Krosing <hannuk(at)google(dot)com> wrote:

> Hi hackers,
>
> Attached is a patch introducing a new transaction-local GUC parameter:
>
> SET LOCAL immediate_global_insert_visibility = '<list of tables>';
>
> While a table is listed in immediate_global_insert_visibility (which
> defaults
> to ""), any rows inserted into that table via INSERT, INSERT ... ON
> CONFLICT,
> or COPY are marked frozen in place (HEAP_XMIN_FROZEN) immediately after
> index
> tuple insertion succeeds and flushed to WAL via XLogFlush().
>
> Motivation & Use Cases
> ======================
>
> Many workloads need a lightweight way to record durable, rollback-safe rows
> from inside a transaction without the overhead of opening a separate
> backend
> connection (e.g., via dblink) to simulate an autonomous transaction:
>
> 1. Rollback-Safe Audit / Error / Security Logging:
> Triggers, procedures, or application transactions can append audit or
> diagnostic records into a log table that must persist even if the
> enclosing
> transaction later fails a constraint check or executes ROLLBACK.
>
> 2. Simplified / Crash-Safe Two-Phase Commit (2PC) Coordinator Logging:
> When a local transaction coordinates distributed 2PC across remote
> shards
> (for example in PL/Proxy or federated FDW setups), the coordinator can
> record the Global Transaction ID (GID) into a local tracking table with
> immediate_global_insert_visibility enabled right before sending
> PREPARE TRANSACTION '<gid>' to remote nodes, and DELETE that row in the
> same local transaction once COMMIT PREPARED / ROLLBACK PREPARED
> succeeds:
> - Because the inserted row is frozen and WAL-flushed immediately, it is
> immediately visible to concurrent recovery monitors and survives even
> if
> the local transaction aborts or the local PostgreSQL instance crashes
> before 2PC completes.
> - When the coordinator subsequently executes DELETE on the row in the
> same
> transaction, HeapTupleHeaderAdjustCmax() sees HEAP_XMIN_COMMITTED and
> records a normal xmax (without allocating a ComboCid). Thus, the GID
> row
> remains visible to concurrent sessions while the deleting transaction
> is
> in progress, disappears when the deleting transaction commits, and
> survives if the deleting transaction aborts after the DELETE.
>
> 3. Progress reporting from inside a long-running transaction
>
> Design & Implementation Details
> ===============================
>
> 1. SET LOCAL Enforcement (GUC_LOCAL_ONLY):
> To prevent accidental session-wide or cluster-wide activation, a new GUC
> flag GUC_LOCAL_ONLY (0x020000) is enforced in set_config_with_handle().
> Attempting to set immediate_global_insert_visibility via SET (without
> LOCAL), RESET, set_config(..., is_local=false), or configuration files
> is
> rejected with an error. At transaction or subtransaction end, the GUC
> stack
> automatically restores the previous setting.
>
> 2. Eager Relation OID Resolution:
> In check_immediate_global_insert_visibility(), the comma-separated table
> list is eagerly parsed and resolved to relation OIDs stored in GUC extra
> state. Partitioned tables (RELKIND_PARTITIONED_TABLE) are automatically
> expanded to include all leaf partitions via find_all_inheritors().
>
> 3. Freezing After ExecInsertIndexTuples() Succeeds:
> Rather than setting HEAP_XMIN_FROZEN before index insertion, the main
> heap
> tuple is inserted normally and frozen right after
> ExecInsertIndexTuples()
> succeeds via heap_freeze_inserted_tuple(), which sets HEAP_XMIN_FROZEN
> via
> heap_execute_freeze_tuple() and logs an XLOG_HEAP2_PRUNE_ON_ACCESS WAL
> record via log_heap_prune_and_freeze().
> This ensures that if a NOT NULL, CHECK, BEFORE trigger, or UNIQUE index
> constraint fails (or a speculative conflict occurs in INSERT ... ON
> CONFLICT), the heap tuple is never frozen and rolls back cleanly without
> leaving unindexed ghost tuples in the heap.
>
> 4. TOAST Chunk Handling (HEAP_INSERT_IMMEDIATE_FROZEN):
> Out-of-line TOAST chunks created during heap_prepare_insert() are frozen
> using a dedicated HEAP_INSERT_IMMEDIATE_FROZEN flag so that VACUUM never
> removes TOAST chunks belonging to a frozen main tuple whose inserting
> XID
> later aborted, while avoiding COPY FREEZE's visibility-map assertions in
> RelationGetBufferForTuple().
>
> 5. Configurable WAL Durability ('sync' vs 'async' mode):
> Each table entry (or the list) supports an optional durability modifier
> of
> 'sync' (the default) or 'async' (e.g., 'tab1, tab2:async' or
> 'async:tab1, tab2').
> - In 'sync' mode (default), single-row inserts flush WAL immediately in
> heap_freeze_inserted_tuple(), and multi-insert batches in
> CopyMultiInsertBufferFlush() perform a single XLogFlush() for the
> highest
> freeze LSN at the end of the batch, providing immediate crash safety.
> - In 'async' mode, the freeze WAL record is still logged and the tuple
> is
> immediately visible and rollback-safe in shared buffers, but
> XLogFlush()
> is skipped to avoid per-insert fsync latency.
>
> 6. Halloween Problem Protection:
> In HeapTupleSatisfiesMVCC(), when immediate_insert_visibility_active is
> true, frozen tuples whose raw xmin matches the current transaction and
> whose cmin >= snapshot->curcid are treated as invisible to the inserting
> command itself, preventing infinite loops in INSERT INTO t SELECT *
> FROM t
> while remaining immediately visible to subsequent commands and
> concurrent
> sessions.
>
> Testing
> =======
>
> - Regression tests (src/test/regress/sql/immediate_insert_visibility.sql):
> Covers SET LOCAL enforcement, basic rollback survival, TOAST survival
> across
> ROLLBACK + VACUUM FREEZE, unique constraint failure rollback, Halloween
> problem protection, 2PC GID insert-then-delete patterns, INSERT ... ON
> CONFLICT, COPY multi-insert, partitioned tables, and role privilege
> checks.
> - Isolation tests
> (src/test/isolation/specs/immediate-insert-visibility.spec):
> Verifies immediate visibility to concurrent sessions (including under a
> pre-existing REPEATABLE READ snapshot) while the inserting transaction
> is in
> progress, survival after ROLLBACK, and cross-session visibility during
> the
> 2PC GID insert/delete lifecycle.
>
> Feedback and comments are very welcome.
>
> Best regards,
> Hannu Krosing
>