[PATCH] Add pg_get_subscription_ddl() function

First seen: 2025-10-31 10:57:15+00:00 · Messages: 21 · Participants: 5

Latest Update

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

Technical Analysis: pg_get_subscription_ddl() Function Patch

Core Problem

PostgreSQL lacks a built-in function to reconstruct the DDL (Data Definition Language) statement for logical replication subscriptions. This is part of a broader "Retail DDL functions" project (led by Andrew Dunstan) that aims to provide programmatic access to DDL reconstruction for various database objects — similar to how pg_get_indexdef(), pg_get_constraintdef(), and pg_get_viewdef() already work for their respective object types.

The immediate use case: given a subscription name or OID, return a complete CREATE SUBSCRIPTION statement that would recreate that subscription with all its current settings. This is valuable for:

Architectural Context

The implementation lives in src/backend/utils/adt/ruleutils.c, which is PostgreSQL's canonical location for DDL reconstruction functions. The function reads subscription metadata from pg_subscription catalog via syscache lookups and assembles the CREATE SUBSCRIPTION statement.

Key Design Decisions and Tradeoffs

1. Function Signature — Name vs OID overloading

The patch provides two entry points:

This mirrors the pattern used by other ruleutils functions and provides flexibility for different caller contexts.

2. The Subscription->publications String/Node controversy

Álvaro Herrera identified that Subscription->publications stores publication names as a List of String (Value) nodes, which is unusual for a non-Node struct. This seemed wasteful. The initial suggestion was to strip the String wrapping in a preparatory patch. However, after deeper analysis, both Álvaro and Vaibhav realized this would be counterproductive because the same code paths handle both Subscription (non-Node, runtime struct) and CreateSubscriptionStmt (parser Node), and the parser naturally produces String-wrapped lists via name_list grammar rules. Forcing C strings into Subscription->publications would require conversion contortions elsewhere.

The resolution: introduce a local static helper text_array_to_string_list() in ruleutils.c rather than exposing textarray_to_stringlist() from pg_subscription.h. This avoids API pollution while solving the immediate need.

3. connect = false — Critical semantic requirement

The generated DDL always includes connect = false because executing the reconstructed DDL should NOT automatically initiate a connection to the publisher. This matches pg_dump's behavior and is essential for safety — blindly connecting could trigger replication slot creation on a publisher that doesn't expect it.

4. Options that lack catalog storage: create_slot and copy_data

These two options have no corresponding column in pg_subscription because they are transient actions (performed once at subscription creation time), not persistent state. The patch omits them from output (using server defaults) rather than hardcoding values that might become stale if defaults change in future versions. Peter Smith challenged this decision, arguing the DDL should reproduce the subscription "exactly as-is," but Vaibhav's position (supported by pg_dump precedent) is that omitting unknowable transient options is safer.

5. Whether to emit all options or only non-default ones

Peter Smith raised whether the function should emit only non-default options (producing more compact output). Álvaro noted that GUC-dependent defaults make this problematic — what's "default" on one server configuration may not be on another. The current approach emits all options explicitly, which is verbose but unambiguous and portable across server configurations.

6. Password exposure in connection strings

Chao Li raised a security concern: the connection string (which may contain passwords) is emitted verbatim, yet the function only requires pg_read_all_data and/or pg_create_subscription privileges. Vaibhav's response acknowledged this but noted the privilege check already provides reasonable access control. The function doesn't attempt to redact passwords from connection strings — matching pg_dump's behavior where the user with catalog access can already see pg_subscription.subconninfo directly.

7. Boolean representation: true/false vs on/off

A stylistic debate emerged about whether boolean options should use true/false or on/off in generated DDL. Peter Smith argued for consistent true/false everywhere (matching PostgreSQL boolean literals and pg_dump output). The streaming parameter is correctly treated as an enum (off/on/parallel) rather than a boolean. The final version adopts true/false for actual booleans.

8. Code structure — static helper vs exported function

Álvaro insisted the core DDL-building function be static within ruleutils.c, not exported. The naming was also refined from build_subscription_ddl_string to pg_get_subscription_string to match existing conventions like pg_get_indexdef_string and pg_get_constraintdef_command.

Implementation Details

The function:

  1. Validates permissions (requires pg_read_all_data OR pg_create_subscription)
  2. Looks up subscription by name/OID via syscache
  3. Reads all columns from pg_subscription catalog
  4. Constructs CREATE SUBSCRIPTION <name> CONNECTION '<conninfo>' PUBLICATION <publist> WITH (...)
  5. Properly quotes identifiers (subscription name, publication names) using quote_identifier()
  6. Uses quote_literal_cstr() for string values like slot_name
  7. Always sets connect = false for safety
  8. Returns NULL for invalid/non-existent input rather than raising an error

Open Design Questions (as of last activity)

Patch Status

As of the final message (May 2025), v8 passed CI (CFBot green) and received positive testing from an additional reviewer (Solai). The patch was moved to "Ready for Committer" status, awaiting Andrew Dunstan's final review as the project coordinator.