Add pg_get_publication_ddl function

First seen: 2026-01-11 11:20:40+00:00 · Messages: 6 · Participants: 5

Latest Update

2026-05-18 · claude-opus-4-6

Technical Analysis: pg_get_publication_ddl Function

Core Problem

PostgreSQL lacks a built-in function to reconstruct the DDL (Data Definition Language) statement for PUBLICATION objects. This is part of a broader effort (initiated by Andrew Dunstan) to provide pg_get_{object}_ddl functions that can reverse-engineer the CREATE statement for various database objects directly from the system catalogs. For publications specifically, this is particularly valuable because:

  1. Logical replication setup complexity: Publications can have intricate configurations including table lists with column lists, row filters, schema-level inclusion, partition handling options, and generated column behavior. Reconstructing these manually from catalog queries is error-prone.

  2. Migration and documentation: DBAs need to extract publication definitions for migration scripts, disaster recovery procedures, and documentation purposes without relying on external tools like pg_dump.

  3. Consistency with existing infrastructure: PostgreSQL already has pg_get_viewdef(), pg_get_indexdef(), pg_get_constraintdef(), etc. Publications are a notable gap, especially given their growing complexity in modern PostgreSQL versions.

Proposed Solution

The patch introduces pg_get_publication_ddl(publication_name text) (with an OID variant) in src/backend/utils/adt/ruleutils.c, following the established pattern of DDL reconstruction functions. The implementation:

  1. Looks up the publication via GetPublicationByName() or by OID
  2. Retrieves published relations via GetPublicationRelations() with PUBLICATION_PART_ALL
  3. Reconstructs the FOR clause: handling ALL TABLES, ALL SEQUENCES, per-table specifications (with column lists and row filters), and FOR TABLES IN SCHEMA
  4. Reconstructs WITH options: publish, publish_generated_columns, publish_via_partition_root

Key Design Decisions

Always emitting WITH parameters: The author deliberately includes all WITH options (publish, publish_generated_columns, publish_via_partition_root) even when they match defaults. The rationale is that these values are always stored in the Publication struct and making them explicit aids debugging—particularly publish_via_partition_root which affects TRUNCATE behavior in non-obvious ways.

Default values philosophy: This is a design choice that trades verbosity for clarity. The reconstructed DDL will always show the full configuration state, which is useful for understanding replication behavior but produces longer output than what a user might have originally typed.

v2 architectural changes: The second version adopts the pattern established by already-committed pg_get_tablespace_def and pg_get_database_def patches, adding:

Critical Bugs Identified

1. Schema Name Leakage Between Tables (Huseyin)

The schemaname variable was initialized to NULL before the table loop but never reset per iteration. This caused tables in the public schema to incorrectly inherit the schema prefix from a preceding table in a different schema. This is a semantic correctness bug that would produce invalid DDL output.

2. Missing ONLY Keyword (Huseyin)

FOR TABLE ONLY syntax is critical for publications on tables with inheritance hierarchies or partitioning—it restricts publication to the named table only, excluding child tables. The v1 patch silently dropped this keyword, producing DDL that would change publication semantics when re-executed. This information is stored in pg_publication_rel.prrelid vs checking pg_publication_rel.prrelid inheritance status.

3. "ALL SEQUENCE" vs "ALL SEQUENCES" (Zengman, Peter Smith, Cary Huang)

A trivial but syntax-breaking typo that would produce invalid SQL.

4. Dead Code / Incorrect NULL Handling (Zengman)

The if (pub == NULL) return (Datum) NULL; block was unreachable (the lookup function already throws an error) and used incorrect return convention—should use PG_RETURN_NULL() macro for consistency with SRF conventions.

Security Considerations

The author raises an interesting privilege question: there's no explicit "view" privilege for publications. Since publications reference table and column names, calling pg_get_publication_ddl() could leak information about objects the caller doesn't have access to. This is analogous to the information disclosure concerns with other pg_get_* functions. The thread doesn't resolve this—it remains an open design question whether the function should check pg_publication ownership or superuser status, or whether publication metadata is considered non-sensitive (consistent with pg_publication being world-readable in the catalogs).

Technical Debt and Future Concerns

Peter Smith's comment #7 identifies a forward-compatibility risk: the v1 code assumes ALL TABLES and ALL SEQUENCES are mutually exclusive with per-table specifications (using else logic). If PostgreSQL later supports mixed syntax like FOR ALL SEQUENCES, FOR TABLE t1, this assumption breaks. The suggestion to avoid the else branch is architecturally prudent.

The pretty-print question is deliberately deferred, with the expectation that a common infrastructure will emerge across all pg_get_{object}_ddl functions to handle formatting consistently.

v2 Improvements Summary

The v2 patch (May 2026) represents substantial rework: