Incremental Update: v2→v4 Patch Evolution and Scope Expansion

Key Development: Scope Expanded to \d+ Command

Jim Jones identified an analogous redundancy problem in the \d+ (describeOneTableDetails) output. When a table belongs to a schema published via FOR TABLES IN SCHEMA and is also individually listed via FOR TABLE, the "Included in publications:" section shows the same publication name twice (once for the schema-level inclusion, once for the explicit table entry — potentially with a WHERE clause that is semantically redundant).

Example of the problem in \d+:

Included in publications:
    "pub1" WHERE (c > 42)
    "pub1"

The fix applies the same NOT EXISTS subquery pattern to filter out the redundant explicit-table publication entry when the table's schema is already published by the same publication.

Patch Version Progression

• v2: Minor fix — restored missing \n at end of a SQL string literal in the \dRp+ query (cosmetic/correctness fix flagged by Jim Jones).
• v3: Two-patch series — Jim Jones contributed a POC 0002 patch applying similar deduplication logic to describeOneTableDetails (\d+).
• v4: Combined single patch — Peter Smith merged both fixes into one patch, standardizing the SQL style to use NOT EXISTS (SELECT 1 FROM pg_catalog.pg_publication_namespace pn WHERE ...) consistently across both \dRp+ and \d+ code paths (rather than mixing NOT IN and NOT EXISTS styles).

Open Question: Test Coverage

Peter Smith notes there is no existing test case covering the \d+ duplicate publication display. He asks whether a new test should be added. This remains unresolved.

Technical Detail: SQL Style Unification

The v4 patch deliberately chose the NOT EXISTS correlated subquery form over NOT IN (SELECT ...) for both locations. This is a minor but deliberate consistency decision — both forms are semantically equivalent here since pnnspid is NOT NULL, but NOT EXISTS is the more idiomatic/safer pattern in PostgreSQL (avoids NULL-related surprises with NOT IN).

PSQL Publication Describe Command: Filtering Redundant Table Listings

Core Problem

The \dRp+ command in psql (which describes publications in detail) displays redundant information when a publication combines FOR TABLE and FOR TABLES IN SCHEMA clauses that overlap. Specifically, if a table is explicitly listed via FOR TABLE and its schema is published via FOR TABLES IN SCHEMA, the describe output shows the table in both the "Tables:" section and implicitly under "Tables from schemas:" — creating visual noise and potential confusion about the publication's effective scope.

Example of the Redundancy

CREATE PUBLICATION pub1 FOR TABLES IN SCHEMA myschema, TABLE myschema.t1, t99;

Current \dRp+ output shows:

• Tables: "myschema.t1", "public.t99"
• Tables from schemas: "myschema"

The table myschema.t1 is already fully covered by the schema-level publication of myschema, making its separate listing redundant.

Architectural Context

Publication Internals

PostgreSQL's logical replication publications are stored across several catalog tables:

• pg_publication — the publication itself
• pg_publication_rel — individually published tables (with optional column lists and row filters)
• pg_publication_namespace — schema-level publication entries

When both mechanisms overlap, PostgreSQL's replication logic treats the schema-level entry as a superset — the documentation explicitly states that row filters on individually listed tables become "redundant" when the table belongs to a schema that is also published. The storage model intentionally allows this overlap (it's not an error), but the display logic in psql doesn't account for it.

Why This Matters

1. User confusion about effective replication scope: If a table appears in "Tables:" with a row filter, but also belongs to a published schema (where the docs say the row filter is redundant/ignored), users may incorrectly believe the filter is active.

2. Future complexity with EXCEPT clauses: There is ongoing work to add FOR TABLES IN SCHEMA ... EXCEPT syntax. When combined with explicit table listings, the display becomes even harder to parse without intelligent filtering of redundant entries.

3. Principle of least surprise: The describe command should show the effective publication configuration, not raw catalog contents that require mental deduplication.

Proposed Solution

The patch modifies the psql \dRp+ describe logic to filter out tables from the "Tables:" listing when:

• The table's schema is already present in the "Tables from schemas:" listing
• The table has no column list (a column list would make the explicit entry semantically distinct from the schema-level inclusion, since schema-level publishing always publishes all columns)

This is purely a display-layer change in the psql client — it does not modify catalog storage or replication behavior. The query that populates the "Tables:" section would gain a NOT EXISTS or WHERE clause excluding tables whose schemaname matches any entry in pg_publication_namespace for the same publication.

Technical Tradeoffs and Design Considerations

Arguments for the patch:

• Reduces visual clutter and user confusion
• Aligns display with documented semantic behavior (schema superset absorbs individual tables)
• Prevents misleading row filter display
• Prepares the ground for cleaner EXCEPT clause display

Potential concerns:

• Loss of information: The current output faithfully reflects what's stored in the catalog. Some users might want to see exactly what was specified in the CREATE/ALTER PUBLICATION statement for debugging purposes.
• Column list edge case: If a table has an explicit column list, the individual listing IS semantically meaningful (only those columns are published via the explicit entry, while the schema publishes all columns). The patch needs to handle this correctly — only suppress tables without column lists.
• Row filter edge case: Similarly, while the docs say row filters become redundant when the schema is also published, the filter is still stored and might be relevant for understanding the publication's intent or for cases where the schema might later be removed.

Relationship to Other Work

The patch references the FOR TABLES IN SCHEMA EXCEPT feature being developed separately. That feature would add an "Except tables:" section to the describe output, making the interaction between schema-level and table-level publication even more complex to display. This deduplication patch would help keep that future output comprehensible.

Status

This is an initial v1 patch submission with no responses yet. It's a relatively small, self-contained UX improvement to psql's describe infrastructure, touching the client-side SQL queries that format publication information.