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 <[email protected]> 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 >
