DOCS - CREATE PUBLICATION ... EXCEPT has no mention on what happens after drop/create tables

Thread Overview

This is a single-message documentation-oriented thread raising a subtle but legitimate concern about the behavior of CREATE PUBLICATION ... FOR ALL TABLES EXCEPT (and related EXCEPT clauses in publication DDL) with respect to how the exclusion list interacts with subsequent DDL on the excluded tables.

The Core Technical Problem

CREATE PUBLICATION supports specifying tables to exclude from a publication. The current documentation for the EXCEPT clause states simply that it "specifies a list of tables to be excluded from the publication." This wording is static and timeless — a reader would reasonably assume that the named tables are excluded by name, permanently, from the publication's scope.

However, the actual implementation is OID-based, not name-based. Inside PostgreSQL, when the publication is created the catalog (pg_publication_rel / pg_publication_namespace and associated metadata) stores the object identifier of the excluded relation. Consequently:

1. If the excluded table is dropped, the catalog entry tracking the exclusion is removed along with it (via dependency tracking).
2. If a new table with the same name is then created, it gets a fresh OID. Since the exclusion list referenced the old OID (now gone), the new table is not excluded — and if the publication is FOR ALL TABLES style, the new table will be automatically included in the publication's replicated set.

This is effectively the inverse of the familiar FOR ALL TABLES behavior: just as FOR ALL TABLES automatically picks up newly created tables, EXCEPT does not automatically re-exclude recreated tables bearing the same name. This asymmetry is surprising from an operational/DBA perspective, especially in logical replication setups where unintended table inclusion can leak data to subscribers.

Why This Matters Architecturally

The OID-based behavior is a direct consequence of how PostgreSQL's dependency system works. Catalog references to database objects are always by OID; name-based references would be fragile across rename operations and would require name resolution on every publication evaluation. The current design is internally consistent with the rest of the system (e.g., views, foreign keys, and regular publication membership all behave by OID).

The issue is therefore not a bug but a documentation gap. The concern Satya raises is whether users relying on EXCEPT for security/compliance reasons (e.g., "never replicate the sensitive_data table") could be caught off guard by a drop/recreate cycle — perhaps during a migration, a pg_dump/restore on a specific table, or an application-level schema rebuild — silently causing the formerly-excluded table to start being replicated.

Proposed Solution

Satya proposes a documentation enhancement only — no code change. The doc for the EXCEPT clause should explicitly note:

• The exclusion is tracked by object identity (OID), not by name.
• If an excluded table is dropped and recreated, the new table is not automatically excluded and will be included in the publication (when the publication otherwise covers it, e.g., FOR ALL TABLES).
• Users who need persistent name-based exclusion must re-issue ALTER PUBLICATION ... ADD TABLES IN SCHEMA ... EXCEPT ... or equivalent after recreation.

Key Technical Insights

• This is a general pattern in PostgreSQL: any catalog feature that records "a set of objects" records OIDs, and drop-and-recreate breaks the association. The same concern applies (with different user-visible consequences) to row-level security policies, default privileges scoping, and extension membership.
• The FOR ALL TABLES [IN SCHEMA ...] EXCEPT feature is relatively new (added as part of the generalized publication filtering work in PG 15/16), and its documentation has had less scrutiny than long-established clauses.
• The asymmetry is particularly sharp because FOR ALL TABLES is dynamic (new tables auto-included) while EXCEPT is static (dropped-and-recreated tables lose their exclusion). A naive reader would expect either both to be dynamic or both static.

Discussion Status

As of this single message, no responses have been captured in the thread. There is no committer weigh-in yet, no patch posted, and no counter-proposal. The likely outcome is a small documentation patch clarifying the OID-based semantics — a non-controversial change that a docs-focused committer would typically accept after minor wordsmithing.

Participant Assessment

Satya Narlapuram is the sole participant, raising the issue in an observational/reporter capacity rather than proposing a concrete patch. The question is framed deferentially ("Should we enhance the documentation...?") inviting community consensus before effort is expended on a patch.