[PATCH] Add pg_get_trigger_ddl() to retrieve the CREATE TRIGGER statement

First seen: 2025-10-14 01:59:18+00:00 · Messages: 85 · Participants: 16

Latest Update

2026-06-01 · claude-opus-4-6

Monthly Summary: pg_get_trigger_ddl() and pg_get_policy_ddl() — May 2026

Overview

This thread encompasses two intertwined sub-patches under Andrew Dunstan's Retail DDL Functions project, which aims to expose per-object DDL reconstruction as first-class SQL-callable functions (beyond pg_dump). May 2026 saw continued technical debate on identifier semantics, a major API redesign for the policy DDL function, and several correctness fixes.

Key Developments

1. Identifier Parsing Tension for Trigger Names

The most substantive unresolved dispute concerns how pg_get_trigger_ddl(regclass, name) handles its second argument (trigger name as text):

2. pg_get_policy_ddl() Adopts VARIADIC Options API

Akshay Joshi substantially reworked the policy DDL function to use the VARIADIC options pattern established by the recently committed pg_get_database_ddl():

pg_get_policy_ddl(table regclass, policy_name name, VARIADIC options text[]) RETURNS setof text

This replaces the earlier (regclass, name, bool pretty) signature. The change supersedes the GET_DDL_PRETTY_FLAGS macro approach, with pretty-printing now gated through key-value option pairs (e.g., 'pretty', 'true'). The return type changed to setof text (returning empty set for NULL inputs rather than NULL).

3. Double-Parenthesization Bug Fixed (v3)

Japinli identified that pg_get_expr() output already includes parentheses, so the format string "USING (%s)" produced USING ((expr)). Akshay's v3 patch (2026-05-25) fixes this for both USING and WITH CHECK clauses.

4. Code Organization: ddlutils.c

Philip noted that these DDL functions "now live in ddlutils.c" — a new module splitting DDL-generation functions away from ruleutils.c. This signals the Retail DDL project is being consolidated into its own subsystem.

Accepted Design Decisions

Decision Resolution Authority
Use regclass (not name) for table argument Accepted Álvaro Herrera (committer)
New function pg_get_trigger_ddl vs overloading pg_get_triggerdef New function Andrew Dunstan (project originator)
Return NULL for invalid OID in trigger DDL Accepted Jian He, following pg_basetype precedent
Explicit error for schema-qualified trigger name Accepted Philip (challenged by Jim Jones)
VARIADIC options API for policy DDL Accepted Akshay Joshi, following pg_get_database_ddl()
Trailing semicolon in DDL output Accepted Philip

Unresolved Issues

History (1 prior analysis)
2026-06-01 · claude-opus-4-6

Incremental Update: pg_get_policy_ddl() v11 — Parenthesization bug confirmed and fixed with round-trip test

Key Development

A new reviewer (Ilmar Yunusov) identified a correctness regression introduced in v10 when Japinli's double-parenthesization feedback was applied. The fix in v10 removed parentheses around pg_get_expr() output, but this broke reconstruction for atomic expressions (boolean constants like true/false) where pg_get_expr() does NOT add outer parentheses.

Technical Root Cause

pg_get_expr() only wraps output in parentheses for composite expressions (OpExpr, BoolExpr, etc.). For atomic top-level nodes (Const, Var, current_user, NULL), no parentheses are added. Since CREATE POLICY ... USING syntax requires parentheses around the expression, omitting them produces invalid SQL:

-- Broken (v10):
CREATE POLICY p_true ON public.t USING true;  -- syntax error

-- Correct (v11):
CREATE POLICY p_true ON public.t USING (true);

Resolution: Revert to v9 behavior

Akshay acknowledged that his original v9 patch was correct — the unconditional USING (%s) / WITH CHECK (%s) wrapping is necessary. This is consistent with pg_dump's approach (cited src/bin/pg_dump/pg_dump.c:4473-4477) which always wraps unconditionally. Japinli acknowledged his earlier review was incorrect ("My bad! I had not considered this situation.").

New regression test added

v11 adds a round-trip regression test: generates DDL, drops the policy, re-executes the generated DDL, and verifies the policy is recreated. This directly addresses the reviewer's suggestion and provides protection against future parenthesization regressions.

Reviewer Confirmation

Ilmar re-tested v11 against current master and confirmed both the USING and WITH CHECK cases now produce valid, executable SQL. The commitfest status moved from "Waiting on Author" back to passing.

Lesson for the Retail DDL project

This episode illustrates a general principle for DDL-reconstruction functions: pg_get_expr() output cannot be assumed to be self-parenthesized. Any DDL reconstruction that embeds pg_get_expr() output into a syntactic position requiring parentheses must add them unconditionally. This applies equally to the trigger DDL patch and any future pg_get_*_ddl() functions that reconstruct expressions.