Support EXCEPT for ALL SEQUENCES / TABLES IN SCHEMA Publications

Architectural Context

PostgreSQL's logical replication publication model historically offered two granularities:

1. Explicit object enumeration (FOR TABLE t1, t2, ...)
2. All-or-nothing wholesale (FOR ALL TABLES, and later FOR ALL SEQUENCES and FOR TABLES IN SCHEMA)

Thread [1] previously introduced EXCEPT for FOR ALL TABLES, closing the gap where users wanted "almost everything" semantics without the operational burden of maintaining an explicit allow-list as schemas evolve. This thread pursues two parallel extensions of the same idea:

• Shlok Kyal's patches: EXCEPT for FOR ALL SEQUENCES
• Nisha Moond's patches: EXCEPT for FOR TABLES IN SCHEMA (schema-scoped exclusion)

Both depend on the pg_publication_rel.prexcept column that was added by the prior ALL TABLES EXCEPT work.

Core Design Decision #1: Catalog Reuse vs. Separation (Sequences)

Peter Smith raised the most architecturally significant concern: should sequence exclusions live in pg_publication_rel alongside tables (Option 1), or in a new dedicated catalog pg_publication_seq (Option 2)?

His argument for Option 2:

• pg_publication_rel rows become semantically overloaded. Without inspecting pg_class.relkind, a row's meaning (included table, excluded table, excluded sequence, future included sequence) is ambiguous.
• prattrs (column list) and prquals (row filter) are meaningless for sequences — sequences are single-row objects with fixed columns (last_value, is_called). Storing them in a catalog with these columns "feels like a square peg jammed into a round hole."
• Every consumer query now needs join + relkind filter.
• Complexity grows combinatorially once FOR SEQUENCE <name> (analogous to FOR TABLE) is eventually added.

Shveta Malik and Vignesh argued for Option 1 (which Shlok had chosen), with reasoning that carries the decision:

1. Name: The catalog is pg_publication_rel — "relation", not "table". relkind already disambiguates; sequences are relations.

2. Performance: get_rel_relkind is cache-backed; relkind checks are cheap. The dominant hot paths are:

   • get_rel_sync_entry in pgoutput — cached in RelationSyncCache
   • CheckCmdReplicaIdentity / PublicationDesc build — cached in rd_pubdesc

   So the relkind branch is taken once per relation-epoch, not per row.

3. Maintenance burden: A parallel catalog duplicates DDL, pg_dump, psql \dRp+, and dependency-tracking infrastructure.

Amit Kapila's tacit endorsement (no objection, and he is the de-facto committer for logical replication) plus committer-track reviewer consensus locked in Option 1. Shlok confirmed the design after verifying the arguments.

Implication: The prattrs/prquals columns will simply be NULL for RELKIND_SEQUENCE rows, and code paths must be careful to never look them up for sequences. This is the tradeoff accepted — a degree of catalog sparseness in exchange for avoiding catalog proliferation.

Core Design Decision #2: EXCEPT Syntax Grammar

Two sub-debates occurred:

(a) Keyword required inside EXCEPT — TABLE/SEQUENCE mandatory?

For sequences, Shlok settled on EXCEPT (SEQUENCE s1, s2) mirroring the existing EXCEPT (TABLE t1, t2). The [, ...] at two levels of the grammar permits both SEQUENCE s1, s2 and SEQUENCE s1, SEQUENCE s2, which Vignesh initially flagged as a doc omission but Shlok showed was correctly covered.

For TABLES IN SCHEMA, Nisha initially omitted the TABLE keyword, arguing that since only tables can appear in that context, the keyword is noise. Peter Smith pushed back on consistency grounds: having different EXCEPT grammars (TABLE required in one place, forbidden in another) increases cognitive load and is not forward-compatible — once shipped, making a keyword mandatory later breaks user code, while making an optional keyword optional-going-forward is always safe.

Resolution: Nisha adopted EXCEPT (TABLE t1, ...) uniformly. This was the right call — forward compatibility with a hypothetical FOR TABLES, SEQUENCES IN SCHEMA s1 EXCEPT (TABLE t1, SEQUENCE s1) mixed-object syntax is preserved.

(b) EXCEPT scope: per-schema or trailing?

Nisha's v1 allowed a trailing EXCEPT:

CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1, s2 EXCEPT (TABLE t1, t2);

Amit Kapila immediately identified the ambiguity: if t1 exists in both s1 and s2, which is excluded? Nisha's implementation resolved against the immediately preceding schema (s2), analogous to column-lists/row-filters attaching to the adjacent table (Peter Smith's defense). But Shveta observed a more insidious case:

ALTER PUBLICATION pub ADD TABLES IN SCHEMA s2 EXCEPT (TABLE s1.t1);

Here s1.t1 is accepted because s1 is already in the publication, even though it's not in the current statement. This is confusing catalog state.

Resolution: EXCEPT must appear adjacent to each schema; mixed-style trailing EXCEPT is rejected. This mirrors how column lists / row filters bind per-table, which is the established pattern users already understand.

Core Design Decision #3: Identifier Renaming (Table → Relation)

Vignesh requested that identifier renames (pubtable → pubrelation, except_tables → except_relations, PublicationTable → PublicationRelation, and comment updates from "table" to "relation") be split into a dedicated 0001 patch. This is standard PostgreSQL patch hygiene: mechanical renames should be reviewable independently of semantic changes so that the functional diff in the follow-on patch is minimal. Shveta's compromise view prevailed — rename only what the feature touches, defer the rest to the patch that actually fetches sequences.

The AlterPublicationTables function name was flagged (since it now handles sequences too) and renamed to reflect the generalized semantics.

Significant Correctness Issues Surfaced

1. Stale EXCEPT entries on schema drop (Vignesh):

   ALTER PUBLICATION pub DROP TABLES IN SCHEMA sch1;
   -- sch1.t1 remained in except list, semantically meaningless
   

   Nisha's v1-003 patch handles this: dropping a schema cascades to remove associated EXCEPT entries (analogous to how partitioning-parent exclusion extends to partitions).

2. Error message for EXCEPT/explicit conflict: Original: ERROR: relation "t1" is already member of publication "pub1" — misleading because t1 was not added; it was in EXCEPT. Improved: ERROR: table "s1.t1" cannot be added because it is listed in EXCEPT clause of publication "pub8".

3. Unlogged/temporary sequence errors emitted "This operation is not supported for unlogged tables" — an obvious DETAIL message leak from the generalized code path.

4. Grammar useless-nonterminal warnings: opt_sequence was defined but never reduced (gram.y: warning: nonterminal useless in grammar). Classic symptom of speculative grammar production added in anticipation of future forms.

5. Tab completion regressions:

   • ALTER PUBLICATION ... SET ALL SEQUENCES EXCEPT (SEQUENCE was using Query_for_list_of_tables — a copy-paste bug listing tables where sequences were expected.
   • CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1 EXCEPT ( completes all tables rather than only those in s1.

6. Partial SET support leaking through: Vignesh found that before patch 0003, ALTER PUBLICATION pub SET TABLES IN SCHEMA sch1 EXCEPT (...) silently succeeded but ignored the EXCEPT list — a consequence of the AlterPublicationExceptTables early-return guard, which needs to be a hard error during the intermediate patch state, or the feature must be introduced atomically.

7. pg_dump ordering bug: the dump code using the new ADD ... EXCEPT syntax was placed in patch 0001 but the grammar supporting it landed in 0002 — a cross-patch ordering defect Peter Smith caught.

pg_dump and psql (\dRp+) Surface

A meaningful portion of reviewer effort went to the client-side presentation layer:

• \dRp+ sequence description should list publications where the sequence appears in EXCEPT — mirroring the existing table behavior. The SQL uses pr.prexcept with a pg_publication_rel join.
• describePublications() branching was previously if (!puballtables) ... else ...; it must generalize to handle the puballsequences axis, leading to if (!puballtables && !puballsequences) ... else if (puballtables) ... else if (puballsequences) ....
• Capitalization consistency: Shveta/Vignesh insisted on lowercase second-word conventions ("Except sequences:" not "Except Sequences:"), citing the earlier thread's precedent.

Unresolved / Deferred

• Peter Smith's proposal for a dedicated "Excluding objects from the Publication" chapter in the Logical Replication docs was deferred; the current doc delta is small and was judged not to justify a chapter split yet. This will recur as more EXCEPT variants ship.
• Future FOR SEQUENCE <name> explicit-list syntax is explicitly anticipated (it's the reason the SEQUENCE keyword is mandatory inside EXCEPT) but not implemented.
• A potential FOR TABLES, SEQUENCES IN SCHEMA combined form is discussed speculatively; grammar needs broader rework before it's viable.

Verdict

The designs are conservative extensions of an established pattern (ALL TABLES EXCEPT) and reuse the existing catalog column prexcept. The key intellectual content is:

1. Deciding not to fork pg_publication_rel despite the relkind-overloading cost (correct, given caching).
2. Enforcing per-schema EXCEPT adjacency rather than trailing EXCEPT (correct, avoids resolution ambiguity).
3. Insisting on grammar consistency across all EXCEPT variants (correct, forward-compatible).

None of these decisions are deeply novel, but the review discipline — catching cross-patch ordering, stale-catalog scenarios, and DETAIL-message leakage — is what matters for a feature that will be pg_dump'd and schema-migrated by users for years.