Monthly Summary: Add pg_get_publication_ddl Function — May 2026

Overview

This thread progressed significantly in May 2026, moving from an initial v1 patch with multiple bugs to a substantially reworked v2 patch that adopts patterns from already-committed pg_get_tablespace_def and pg_get_database_def functions. The v2 patch then received a comprehensive 32-comment review from Peter Smith, identifying structural, style, and testing concerns.

Problem Statement

PostgreSQL lacks a built-in function to reconstruct DDL for PUBLICATION objects. Publications have grown increasingly complex (column lists, row filters, schema-level inclusion, partition handling, generated column behavior), making manual reconstruction from catalog queries error-prone. The function fills a gap analogous to existing pg_get_viewdef(), pg_get_indexdef(), etc.

v2 Patch Architecture

The implementation adds pg_get_publication_ddl(publication_name text) (with OID variant) in ruleutils.c:

1. Looks up the publication via GetPublicationByName() or OID
2. Retrieves published relations via GetPublicationRelations() with PUBLICATION_PART_ALL
3. Reconstructs the FOR clause (ALL TABLES, ALL SEQUENCES, per-table with columns/row-filters, FOR TABLES IN SCHEMA, EXCEPT)
4. Reconstructs WITH options (publish, publish_generated_columns, publish_via_partition_root)
5. Adds owner reconstruction via ALTER PUBLICATION ... OWNER TO
6. Includes pretty-print option

Key v2 improvements over v1:

• Fixes schema name leakage between tables in the output loop
• Adds missing ONLY keyword for inheritance-restricted publications
• Fixes "ALL SEQUENCE" → "ALL SEQUENCES" typo
• Adds EXCEPT clause support
• Switches from relation locks to catalog cache lookups
• Always emits all WITH parameters explicitly (design choice for debugging clarity)

Critical Review Findings (Peter Smith, 32 Comments)

Architectural Issues

• EXCEPT clause placement: Should be co-located with pub->alltables handling since EXCEPT only applies to FOR ALL TABLES
• FOR TABLES IN SCHEMA ordering: Should appear before specific table listings for readability
• Documentation of reordering: Advises against documenting one arbitrary ordering choice (ALL TABLES before ALL SEQUENCES) while ignoring others

Code Style (6 occurrences)

• Objects to embedding conditional comma-prefixing inside appendStringInfo format strings; prefers separating comma logic from content appending

Other Technical Points

• Boolean values ('true'/'false') in WITH options don't need quoting in generated DDL
• Test structure should use CREATE/DROP cycles with a single publication name rather than 22+ named publications with bulk cleanup
• Stack-allocated StringInfoData (with initStringInfo()) preferred over heap-allocated makeStringInfo() (Jian He, citing recent precedent commits)

Open Design Questions

• Security/privileges: No explicit "view" privilege exists for publications. The function could leak table/column names to callers without access to those objects. Whether to check ownership or treat publication metadata as non-sensitive remains unresolved.
• Forward compatibility: The code assumes ALL TABLES and ALL SEQUENCES are mutually exclusive with per-table specs. If mixed syntax is later supported, the logic breaks.
• Pretty-print infrastructure: Deliberately deferred pending a common formatting approach across all pg_get_{object}_ddl functions.

Status

The v2 patch needs revision to address Peter Smith's review feedback, particularly the structural issues (EXCEPT clause placement, schema ordering), pervasive style concerns (comma handling pattern × 6), testing approach overhaul, and the StringInfoData stack allocation fix.