Hi,

I hit this from the other direction, making logical slots while other
sessions ran DDL, and only found this thread afterwards. So here is some
review by testing, of v7 plus Zhijie's two top-up patches on master at
72b9498df9.

The attached slot_under_ddl.sh needs only pgbench, psql and
test_decoding. Six pgbench clients create a table, add a column, insert
into it in a transaction held open for 2ms, and drop it. One session
loops over creating a slot, decoding twice and dropping it. 300 rounds
took about 20 seconds here.

It also counts decoded inserts that lack the added column, since nothing
else reports those. That is the silent case Manu described in September,
and it turns out about as common as the errors. 1000 rounds,
assert-enabled builds:

                                errors   inserts missing the column
  REL_14_STABLE f72e4686e2        156      129
  REL_18_STABLE 813a74bc5a        188      143
  REL_19_STABLE 0b1eb8b917        180      128
  master        72b9498df9        168      150

The errors are "could not map filenumber" (filenode on 14) and
"pg_attribute catalog is missing N attribute(s)". One failed round can
count twice because it decodes twice. This was a Mac with load average
around 60 from unrelated work and slots being created in a tight loop,
so I wouldn't read anything into the rate.

master with v7 and the top-ups gave 0 errors and 0 missing columns in
5000 rounds.

The wrong output also isn't limited to transactions in flight at the
consistent point, or to missing columns. The attached
snapshot_build_stale.spec needs no load. ALTER TABLE t ADD COLUMN b and
TRUNCATE nk commit in BUILDING_SNAPSHOT, then inserts into t run across
and after CONSISTENT, one of them after a CHECKPOINT, followed by a
DELETE from nk, which has no replica identity, then an ANALYZE and one
more insert. On master with autovacuum off, every run:

  table public.t: INSERT: id[integer]:1 a[text]:'straddles'
  table public.t: INSERT: id[integer]:2 a[text]:'after consistent'
  table public.t: INSERT: id[integer]:3 a[text]:'after checkpoint'
  table public.t: INSERT: id[integer]:4 a[text]:'after analyze' b[integer]:5

With the patches all four inserts have b, and this line appears before
the last one:

  table public.nk: DELETE: (no-tuple-data)

So the DELETE is lost on master with no error. From reading the code,
SnapBuildProcessRunningXacts moves builder->xmin but keeps the cached
builder->snapshot, and SnapBuildProcessChange hands that to new
transactions until a catalog-changing commit is decoded, which here is
the ANALYZE. With no later DDL or ANALYZE I don't see what would end it.
The DELETE goes because the truncated table's new file maps to no
relation under that snapshot, and a change with no tuple data takes the
goto change_done branch in ReorderBufferProcessTXN that exists for
mapped catalogs, instead of raising the error. I didn't try this through
pgoutput, but that skip comes before any plugin is called.

The attached ddl_mix.sql is harsher: DDL in a subtransaction after the
top-level xid is assigned, COMMIT PREPARED, ROLLBACK TO, a table rewrite
and a rolled back CREATE. It is the script's fourth argument. 3000
rounds:

                                errors   inserts missing the column
  master                          677      250
  master + v7 only                 97      158
  master + v7 + top-ups             0        0

v7 alone was clean on the simple workload, so this agrees with Zhijie's
review that it isn't enough. I didn't work out which of the three top-up
changes accounts for what. With two_phase => true on the slots (a small
edit to the script), master gave 879 and 338, and the patched build 0
and 0.

I had also written Sawada-san's April 2025 idea as a quick fix before
finding the thread: call SnapBuildCommitTxn from xact_decode in
BUILDING_SNAPSHOT and treat XACT_XINFO_HAS_INVALS as a catalog change
before CONSISTENT, with no NEW_CID tracking. Clean on the simple
workload, 80 errors and 144 missing columns on the mix, and it fails the
second and third permutations of Zhijie's spec. Mine had neither the
subxact check nor the accumulated invalidations from the top-ups, so
that may say more about my version than the idea, but with Manu's
numbers on large objects and user catalog tables I'd stop looking in
that direction.

Zhijie's snapshot_build spec was stable for me: 30 of 30 runs pass with
the top-ups, and on master all three permutations fail in 30 of 30. It
doesn't have Manu's ALTER TABLE ADD COLUMN and COMMIT PREPARED
permutations, which seem worth merging in.

With the patches, make check passes in contrib/test_decoding,
src/test/subscription and src/test/recovery. I did not run check-world.

On back branches, the patches don't apply to REL_18_STABLE with git
apply and one hunk needed doing by hand, because the NEW_CID case has a
different shape there. That diff is attached with the code changes only.
On it, 5000 rounds of the simple workload were clean, the spec passed 5
of 5 and test_decoding passes. I haven't run the subscription or
recovery suites on it or tried 14 through 17. I see no change to the
on-disk snapshot format or to a function signature, so conflicts look
like the main work of a backpatch, but someone who knows this code
should say.

Not covered by any of this:

- pgoutput and subscribers, I only looked at test_decoding output
- user catalog tables, which test_decoding never reads
- fast-forward mode, so top-up item 3 isn't exercised
- the cost, which I didn't measure. From reading it, a catalog-changing
  transaction in BUILDING_SNAPSHOT now gets a ReorderBufferTXN, its
  invalidations and a slot in committed.xip, as it already does in
  FULL_SNAPSHOT.

Regards,
Colin
#!/bin/sh
#
# Create logical slots with test_decoding while other sessions run DDL, and
# count what goes wrong decoding from them.  Uses a throwaway cluster in a temp
# directory, and only initdb, pg_ctl, psql, pgbench and contrib/test_decoding.
#
#   slot_under_ddl.sh <bindir> [rounds] [port] [pgbench script]
#
# Exit status 1 means something went wrong in a round.  Exit status 2 means the
# DDL load stopped early, so the counts mean nothing.

BIN=$1; ROUNDS=${2:-300}; PORT=${3:-57123}; SCRIPT=$4
D=$(mktemp -d "${TMPDIR:-/tmp}/slotddl.XXXXXX") || exit 3

"$BIN/initdb" -D "$D/data" -A trust -U postgres -E UTF8 --locale=C --no-sync 
>/dev/null || exit 3
cat >> "$D/data/postgresql.conf" <<CONF
wal_level = logical
max_prepared_transactions = 20
fsync = off
listen_addresses = ''
unix_socket_directories = '$D'
port = $PORT
CONF
"$BIN/pg_ctl" -D "$D/data" -w -l "$D/log" start >/dev/null || exit 3
trap '[ -n "$LOAD" ] && { kill "$LOAD"; wait "$LOAD"; } 2>/dev/null; 
"$BIN/pg_ctl" -D "$D/data" -m immediate -w stop >/dev/null; rm -rf "$D"' EXIT

# Every insert into t_N happens after the table has column b, so a decoded
# insert without b was decoded with the catalog as it was before the ALTER.  The
# insert's transaction stays open long enough to overlap other clients' commits.
cat > "$D/ddl.sql" <<'SQL'
CREATE TABLE t_:client_id (id int, a text);
ALTER TABLE t_:client_id ADD COLUMN b int;
BEGIN;
INSERT INTO t_:client_id VALUES (1, 'x', 2);
\sleep 2 ms
COMMIT;
DROP TABLE t_:client_id;
SQL
[ -n "$SCRIPT" ] && cp "$SCRIPT" "$D/ddl.sql"
"$BIN/pgbench" -n -h "$D" -p "$PORT" -U postgres -c 6 -T 86400 -f "$D/ddl.sql" 
postgres >/dev/null 2>&1 &
LOAD=$!

# Each round makes a slot, decodes from it twice and drops it.  psql carries on
# after an error.  The decoding queries print the number of inserts without b.
DECODE="SELECT count(*) FILTER (WHERE data LIKE 'table public.t%: INSERT:%' AND 
data NOT LIKE '% b[integer]:%') FROM pg_logical_slot_get_changes('s', NULL, 
NULL);"
i=0
while [ "$i" -lt "$ROUNDS" ]; do
        printf '%s\n' "SELECT pg_create_logical_replication_slot('s', 
'test_decoding') IS NULL;" \
                "$DECODE" "SELECT pg_sleep(0.005) IS NULL;" "$DECODE" \
                "SELECT pg_drop_replication_slot('s') IS NULL;"
        i=$((i + 1))
done | "$BIN/psql" -X -q -At -h "$D" -p "$PORT" -U postgres -d postgres 
>"$D/out" 2>"$D/err"

"$BIN/postgres" --version
kill -0 "$LOAD" 2>/dev/null || { echo "the DDL load stopped early"; exit 2; }
SILENT=$(grep -E '^[0-9]+$' "$D/out" | awk '{ s += $1 } END { print s + 0 }')
echo "$ROUNDS rounds, $SILENT inserts decoded without column b, errors:"
sed -nE 's/^.*ERROR: *//p' "$D/err" | sed -E 's/[0-9]+/N/g' | sort | uniq -c | 
sort -rn
[ "$SILENT" = 0 ] && ! grep -q ERROR "$D/err"
\set r random(1, 5)
\if :r = 1
CREATE TABLE t_:client_id (id int, a text);
ALTER TABLE t_:client_id ADD COLUMN b int;
BEGIN;
INSERT INTO t_:client_id VALUES (1, 'x', 2);
\sleep 2 ms
COMMIT;
DROP TABLE t_:client_id;
\elif :r = 2
BEGIN;
SELECT pg_current_xact_id();
\sleep 1 ms
SAVEPOINT s;
CREATE TABLE t_:client_id (id int, a text, b int);
RELEASE s;
\sleep 1 ms
COMMIT;
BEGIN;
INSERT INTO t_:client_id VALUES (1, 'x', 2);
\sleep 2 ms
COMMIT;
DROP TABLE t_:client_id;
\elif :r = 3
BEGIN;
CREATE TABLE t_:client_id (id int, a text, b int);
\sleep 1 ms
PREPARE TRANSACTION 'g:client_id';
COMMIT PREPARED 'g:client_id';
BEGIN;
INSERT INTO t_:client_id VALUES (1, 'x', 2);
\sleep 2 ms
COMMIT;
DROP TABLE t_:client_id;
\elif :r = 4
CREATE TABLE t_:client_id (id int, a text);
BEGIN;
SAVEPOINT s;
ALTER TABLE t_:client_id ADD COLUMN z int;
\sleep 1 ms
ROLLBACK TO s;
ALTER TABLE t_:client_id ADD COLUMN b int;
\sleep 1 ms
COMMIT;
BEGIN;
INSERT INTO t_:client_id VALUES (1, 'x', 2);
\sleep 1 ms
INSERT INTO t_:client_id VALUES (2, 'y', 3);
COMMIT;
DROP TABLE t_:client_id;
\else
CREATE TABLE t_:client_id (id int, a text, b int);
INSERT INTO t_:client_id VALUES (0, 'x', 2);
ALTER TABLE t_:client_id ALTER COLUMN id TYPE bigint;
BEGIN;
UPDATE t_:client_id SET b = 3;
INSERT INTO t_:client_id VALUES (1, 'x', 2);
\sleep 2 ms
COMMIT;
BEGIN;
CREATE TABLE v_:client_id (id int);
\sleep 1 ms
ROLLBACK;
DROP TABLE t_:client_id;
\endif
# A catalog change committed in BUILDING_SNAPSHOT, then decoding after the
# slot reaches CONSISTENT, with no further catalog change until the ANALYZE.

setup
{
    DROP TABLE IF EXISTS t, nk;
    CREATE TABLE t (id int PRIMARY KEY, a text);
    CREATE TABLE nk (id int, a text);
    INSERT INTO nk VALUES (11, 'x'), (12, 'y');
}

teardown
{
    DROP TABLE t, nk;
    SELECT 'stop' FROM pg_drop_replication_slot('isolation_slot');
}

session "s1"
step "s1_begin" { BEGIN; SELECT pg_current_xact_id() IS NULL; }
step "s1_commit" { COMMIT; }

session "s2"
step "s2_init" { SELECT 'init' FROM 
pg_create_logical_replication_slot('isolation_slot', 'test_decoding'); }
step "s2_peek" { SELECT data FROM 
pg_logical_slot_peek_changes('isolation_slot', NULL, NULL, 'skip-empty-xacts', 
'1', 'include-xids', '0') WHERE data LIKE 'table%'; }

session "s3"
step "s3_begin" { BEGIN; SELECT pg_current_xact_id() IS NULL; }
step "s3_commit" { COMMIT; }

session "s4"
step "s4_ddl" { ALTER TABLE t ADD COLUMN b int; }
step "s4_truncate" { TRUNCATE nk; }
step "s4_refill" { INSERT INTO nk VALUES (11, 'x'), (12, 'y'); }
step "s4_begin" { BEGIN; }
step "s4_straddle" { INSERT INTO t VALUES (1, 'straddles', 2); }
step "s4_commit" { COMMIT; }
step "s4_after" { INSERT INTO t VALUES (2, 'after consistent', 3); }
step "s4_checkpoint" { CHECKPOINT; }
step "s4_after_ckpt" { INSERT INTO t VALUES (3, 'after checkpoint', 4); }
step "s4_delete" { DELETE FROM nk WHERE id = 11; }
step "s4_analyze" { ANALYZE t; }
step "s4_after_analyze" { INSERT INTO t VALUES (4, 'after analyze', 5); }

permutation "s1_begin" "s2_init" "s3_begin" "s4_ddl" "s4_truncate" "s4_refill" 
"s1_commit" "s4_begin" "s4_straddle" "s3_commit" "s4_commit" "s4_after" 
"s4_checkpoint" "s4_after_ckpt" "s4_delete" "s4_analyze" "s4_after_analyze" 
"s2_peek"
diff --git a/src/backend/replication/logical/decode.c 
b/src/backend/replication/logical/decode.c
index 86421e4b8b..19dec04cda 100644
--- a/src/backend/replication/logical/decode.c
+++ b/src/backend/replication/logical/decode.c
@@ -206,12 +206,16 @@ xact_decode(LogicalDecodingContext *ctx, XLogRecordBuffer 
*buf)
        uint8           info = XLogRecGetInfo(r) & XLOG_XACT_OPMASK;
 
        /*
-        * If the snapshot isn't yet fully built, we cannot decode anything, so
-        * bail out.
+        * If the snapshot hasn't started building yet, the transaction won't be
+        * decoded or tracked by the snapshot, so bail out.
         */
-       if (SnapBuildCurrentState(builder) < SNAPBUILD_FULL_SNAPSHOT)
+       if (SnapBuildCurrentState(builder) < SNAPBUILD_BUILDING_SNAPSHOT)
                return;
 
+       /*
+        * Note that if the snapshot isn't yet fully built, the xlog is only 
used
+        * to build the snapshot and won't be decoded.
+        */
        switch (info)
        {
                case XLOG_XACT_COMMIT:
@@ -418,7 +422,7 @@ heap2_decode(LogicalDecodingContext *ctx, XLogRecordBuffer 
*buf)
         * determining the candidate catalog_xmin for the replication slot. See
         * SnapBuildProcessRunningXacts().
         */
-       if (SnapBuildCurrentState(builder) < SNAPBUILD_FULL_SNAPSHOT)
+       if (SnapBuildCurrentState(builder) < SNAPBUILD_BUILDING_SNAPSHOT)
                return;
 
        switch (info)
@@ -429,15 +433,27 @@ heap2_decode(LogicalDecodingContext *ctx, 
XLogRecordBuffer *buf)
                                DecodeMultiInsert(ctx, buf);
                        break;
                case XLOG_HEAP2_NEW_CID:
-                       if (!ctx->fast_forward)
+                       if (!ctx->fast_forward &&
+                               SnapBuildCurrentState(builder) >= 
SNAPBUILD_FULL_SNAPSHOT)
                        {
                                xl_heap_new_cid *xlrec;
 
                                xlrec = (xl_heap_new_cid *) 
XLogRecGetData(buf->record);
                                SnapBuildProcessNewCid(builder, xid, 
buf->origptr, xlrec);
-
-                               break;
                        }
+                       else
+                       {
+                               /*
+                                * In BUILDING_SNAPSHOT or fast-forward mode, 
the transaction
+                                * will not be replayed, so tuple CID mappings 
and command IDs
+                                * are not needed, but we still must mark the 
transaction as
+                                * having catalog changes so that the historic 
snapshot tracks
+                                * it.
+                                */
+                               ReorderBufferXidSetCatalogChanges(ctx->reorder, 
xid,
+                                                                               
                  buf->origptr);
+                       }
+                       break;
                case XLOG_HEAP2_REWRITE:
 
                        /*
@@ -1304,6 +1320,7 @@ DecodeTXNNeedSkip(LogicalDecodingContext *ctx, 
XLogRecordBuffer *buf,
                                  Oid txn_dbid, RepOriginId origin_id)
 {
        if (SnapBuildXactNeedsSkip(ctx->snapshot_builder, buf->origptr) ||
+               SnapBuildCurrentState(ctx->snapshot_builder) < 
SNAPBUILD_CONSISTENT ||
                (txn_dbid != InvalidOid && txn_dbid != 
ctx->slot->data.database) ||
                FilterByOrigin(ctx, origin_id))
                return true;
diff --git a/src/backend/replication/logical/reorderbuffer.c 
b/src/backend/replication/logical/reorderbuffer.c
index 490dec290a..59f49ccbfa 100644
--- a/src/backend/replication/logical/reorderbuffer.c
+++ b/src/backend/replication/logical/reorderbuffer.c
@@ -3228,12 +3228,15 @@ ReorderBufferForget(ReorderBuffer *rb, TransactionId 
xid, XLogRecPtr lsn)
         * are any. Even if we're not interested in the transaction's contents, 
it
         * could have manipulated the catalog and we need to update the caches
         * according to that.
+        *
+        * Note that transactions committing in SNAPBUILD_BUILDING_SNAPSHOT will
+        * not have a base snapshot even if they accumulated invalidation
+        * messages; no snapshot has been built or used to access catalogs yet, 
so
+        * those invalidations do not need to be executed.
         */
        if (txn->base_snapshot != NULL && txn->ninvalidations > 0)
                ReorderBufferImmediateInvalidation(rb, txn->ninvalidations,
                                                                                
   txn->invalidations);
-       else
-               Assert(txn->ninvalidations == 0);
 
        /* remove potential on-disk data, and deallocate */
        ReorderBufferCleanupTXN(rb, txn);
diff --git a/src/backend/replication/logical/snapbuild.c 
b/src/backend/replication/logical/snapbuild.c
index 228ecd1887..5d001fca10 100644
--- a/src/backend/replication/logical/snapbuild.c
+++ b/src/backend/replication/logical/snapbuild.c
@@ -991,10 +991,15 @@ SnapBuildCommitTxn(SnapBuild *builder, XLogRecPtr lsn, 
TransactionId xid,
        /*
         * Transactions preceding BUILDING_SNAPSHOT will neither be decoded, nor
         * will they be part of a snapshot.  So we don't need to record 
anything.
+        * Note that a transaction whose top-level XID precedes next_phase_at 
may
+        * still have subtransactions assigned during BUILDING_SNAPSHOT (>=
+        * next_phase_at); since subxacts are ordered by XID assignment (see
+        * AtSubCommit_childXids), check the last subxact XID if any.
         */
        if (builder->state == SNAPBUILD_START ||
                (builder->state == SNAPBUILD_BUILDING_SNAPSHOT &&
-                TransactionIdPrecedes(xid, builder->next_phase_at)))
+                TransactionIdPrecedes(nsubxacts > 0 ? subxacts[nsubxacts - 1] 
: xid,
+                                                          
builder->next_phase_at)))
        {
                /* ensure that only commits after this are getting replayed */
                if (builder->start_decoding_at <= lsn)

Reply via email to