Hello hackers, PostgreSQL can show you the definition of a view with pg_get_viewdef() (and a rule with pg_get_ruledef()). This is how pg_dump writes views into a backup. The rule that has to hold: the text it gives back must be valid SQL that recreates the same view. If it hands you SQL that won't parse, your backup won't restore. My test checks that this holds for a batch of tricky view and rule definitions, so a future code change can't quietly break it.
But this is fragile because when you write a view, PostgreSQL doesn't store your original SQL text, it stores a parsed tree, then reconstructs the SQL text from that tree whenever you ask for the definition. That reconstruction step (the "deparser") has to get the spelling exactly right (every parenthesis, every quote, every keyword). It mostly does, but there are corners where it drops something, and then the reconstructed text is subtly invalid. I was working with a differential selectivity test framework and I ran into the three real examples (all shipped as bugs at some point): - A COLLATE clause inside an XMLNAMESPACES URI came back without parentheses, and the grammar doesn't allow a bare COLLATE there — so the recreated view failed to parse with "syntax error at or near COLLATE". - Operators on ROW() constructors could read back wrong. - A rule whose action uses a table alias named "set" (a reserved-ish word) came back unquoted as UPDATE t set SET ..., which is a syntax error. The common thread: the reconstruction is a separate piece of code from the parser, so the two can drift, and it is very hard to notice until a specific weird definition trips it. Using the framework I've been developing I was able to create a series of teststhat try to codify this contract so as to be more noticable in the future if/when some change overlooks this requirement. Essentially, for each view in the corpus it does a round-trip and asserts three things: 1. It re-parses: take the definition pg_get_viewdef() hands back, feed it to CREATE VIEW again; it must succeed (not a syntax error). 2. Same rows: the recreated view must return the same data as the original (the text didn't just parse, it means the same thing). 3. Fixed point: deparse the recreated view and compare to the deparse of the original; they must be byte-identical (reconstructing it a second time doesn't drift further). Each case prints just ok or FAIL <why>, so the expected-output file stays small and doesn't care how the deparser chooses to spell a given view — only that the round-trip holds. There are round-trip-ish checks scattered through rules.sql, create_view.sql, xml.sql but they work by someone adding a tricky view, eyeballing that \d+ looks right, and moving on. AFAICT, there's no single test that says "take this whole corpus of known-nasty shapes and assert the round-trip property for every one." That gap is exactly why the XMLNAMESPACES bug and its three siblings shipped: no systematic guard caught them. The test intends to fills that gap with one generic oracle instead of dozens of ad-hoc eyeball checks. What's in the patch? Put simply, it is a way to quickly codify and test for this kind of issue in a single location in our regression tests. The 17 cases I've added are expressions needing parentheses, ROW() comparisons, CASE, DISTINCT ON, set operations, grouping sets, keyword-named windows, window frames, IS NOT DISTINCT FROM, array subscripts, casts, correlated subqueries, plus a few rules and they all pass (as espected) today but importantly they start to close this gap permanently. The attached patch adds src/test/regress/sql/deparse_roundtrip.sql. It drives a corpus of view and rule definitions through two plpgsql helpers: rt_view creates the view, deparses it, recreates the deparsed text, and checks it re-parsed, returns the same rows, and is a deparse fixed point; rt_rule does the re-parse check for a rule action. Each case reports only ok or FAIL so the expected output stays small and does not depend on how the deparser spells a given definition. The test is self-contained (one schema, dropped at the end), pure SQL, no new dependency. Added to the create_view parallel group. best. -greg
From be379366fc3dc4a1a3a46ac981c8c77853775c3f Mon Sep 17 00:00:00 2001 From: Greg Burd <[email protected]> Date: Mon, 26 Oct 2026 11:10:10 +0000 Subject: [PATCH] Add deparse round-trip regression test for views and rules --- .../regress/expected/deparse_roundtrip.out | 186 ++++++++++++++++++ src/test/regress/parallel_schedule | 1 + src/test/regress/sql/deparse_roundtrip.sql | 108 ++++++++++ 3 files changed, 295 insertions(+) create mode 100644 src/test/regress/expected/deparse_roundtrip.out create mode 100644 src/test/regress/sql/deparse_roundtrip.sql diff --git a/src/test/regress/expected/deparse_roundtrip.out b/src/test/regress/expected/deparse_roundtrip.out new file mode 100644 index 00000000000..4220f1517ef --- /dev/null +++ b/src/test/regress/expected/deparse_roundtrip.out @@ -0,0 +1,186 @@ +-- +-- DEPARSE_ROUNDTRIP +-- +-- A view or rule definition emitted by pg_get_viewdef / pg_get_ruledef must +-- read back: the text must re-parse, define a relation returning the same +-- rows, and be a fixed point (deparsing the recreated object yields identical +-- text). These invariants are relied on by pg_dump; a definition that fails +-- any of them is a dump that will not restore. +-- +-- The test drives a corpus of definitions that have historically round-tripped +-- incorrectly (expressions needing extra parentheses, ROW() comparisons, +-- keyword-named windows, reserved-word rule aliases, XMLTABLE namespace URIs). +-- It reports only a per-case verdict, so the expected output stays small and +-- stable regardless of how the deparser spells a given definition. +-- +CREATE SCHEMA deparse_rt; +SET search_path = deparse_rt; +SET client_min_messages = warning; +CREATE TABLE t (a int, b int, c text, d text); +INSERT INTO t VALUES (1, 2, 'x', 'y'), (3, 4, 'p', 'q'), (5, 6, NULL, 'z'); +-- Round-trip one view definition: create it as "src", deparse, recreate the +-- deparsed text as "dst", and check (1) it re-parsed, (2) dst returns the same +-- rows as src, (3) deparsing dst equals deparsing src (fixed point). +CREATE FUNCTION rt_view(label text, body text) RETURNS text +LANGUAGE plpgsql AS $$ +DECLARE + def1 text; + def2 text; + same boolean; +BEGIN + EXECUTE 'DROP VIEW IF EXISTS rt_dst, rt_src'; + EXECUTE 'CREATE VIEW rt_src AS ' || body; + def1 := pg_get_viewdef('rt_src'::regclass, true); + BEGIN + EXECUTE 'CREATE VIEW rt_dst AS ' || def1; + EXCEPTION WHEN others THEN + RETURN label || ': FAIL (deparsed view does not re-parse: ' || SQLERRM || ')'; + END; + def2 := pg_get_viewdef('rt_dst'::regclass, true); + IF def1 IS DISTINCT FROM def2 THEN + RETURN label || ': FAIL (deparse not a fixed point)'; + END IF; + EXECUTE 'SELECT (SELECT array_agg(x ORDER BY x) FROM (SELECT (rt_src)::text x FROM rt_src) s)' + || ' IS NOT DISTINCT FROM ' + || '(SELECT array_agg(x ORDER BY x) FROM (SELECT (rt_dst)::text x FROM rt_dst) s)' + INTO same; + IF NOT same THEN + RETURN label || ': FAIL (recreated view returns different rows)'; + END IF; + RETURN label || ': ok'; +END $$; +-- Round-trip one rule definition (re-parse only; a rule has no result set). +CREATE FUNCTION rt_rule(label text, rule_body text) RETURNS text +LANGUAGE plpgsql AS $$ +DECLARE + def text; +BEGIN + EXECUTE 'DROP TABLE IF EXISTS rt_rtab CASCADE'; + EXECUTE 'CREATE TABLE rt_rtab (a int, b int)'; + BEGIN + EXECUTE rule_body; + EXCEPTION WHEN others THEN + RETURN label || ': skip (rule body not accepted here: ' || SQLERRM || ')'; + END; + SELECT pg_get_ruledef(oid) INTO def FROM pg_rewrite + WHERE ev_class = 'rt_rtab'::regclass AND rulename <> '_RETURN' + ORDER BY rulename LIMIT 1; + IF def IS NULL THEN + RETURN label || ': FAIL (no rule found)'; + END IF; + EXECUTE 'DROP TABLE rt_rtab CASCADE'; + EXECUTE 'CREATE TABLE rt_rtab (a int, b int)'; + BEGIN + EXECUTE def; + EXCEPTION WHEN others THEN + RETURN label || ': FAIL (deparsed rule does not re-parse: ' || SQLERRM || ')'; + END; + RETURN label || ': ok'; +END $$; +-- Views: expressions, set operations, grouping, windows, ROW() comparisons. +SELECT rt_view('plain', 'SELECT a, b FROM t'); + rt_view +----------- + plain: ok +(1 row) + +SELECT rt_view('expr_paren', 'SELECT (a + b) * 2 AS e, (NOT (a < b)) AS f FROM t'); + rt_view +---------------- + expr_paren: ok +(1 row) + +SELECT rt_view('collate', 'SELECT a FROM t WHERE c COLLATE "C" < d COLLATE "C"'); + rt_view +------------- + collate: ok +(1 row) + +SELECT rt_view('row_cmp', 'SELECT a FROM t WHERE ROW(a, b) < ROW(b, a)'); + rt_view +------------- + row_cmp: ok +(1 row) + +SELECT rt_view('row_eq', 'SELECT (ROW(a, b) = ROW(b, a)) AS e FROM t'); + rt_view +------------ + row_eq: ok +(1 row) + +SELECT rt_view('case_when', 'SELECT CASE WHEN a < b THEN c ELSE d END AS e FROM t'); + rt_view +--------------- + case_when: ok +(1 row) + +SELECT rt_view('distinct_on', 'SELECT DISTINCT ON (a) a, b FROM t ORDER BY a, b'); + rt_view +----------------- + distinct_on: ok +(1 row) + +SELECT rt_view('setop', 'SELECT a FROM t UNION SELECT b FROM t'); + rt_view +----------- + setop: ok +(1 row) + +SELECT rt_view('grouping', 'SELECT a, sum(b) FROM t GROUP BY GROUPING SETS ((a), ())'); + rt_view +-------------- + grouping: ok +(1 row) + +SELECT rt_view('window_kw', 'SELECT a, sum(b) OVER "rows" FROM t WINDOW "rows" AS (ORDER BY a)'); + rt_view +--------------- + window_kw: ok +(1 row) + +SELECT rt_view('window_frame', 'SELECT a, sum(b) OVER (ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM t'); + rt_view +------------------ + window_frame: ok +(1 row) + +SELECT rt_view('is_distinct', 'SELECT a FROM t WHERE c IS NOT DISTINCT FROM d'); + rt_view +----------------- + is_distinct: ok +(1 row) + +SELECT rt_view('arr_subscript','SELECT (ARRAY[a, b])[1] AS e FROM t'); + rt_view +------------------- + arr_subscript: ok +(1 row) + +SELECT rt_view('coerce', 'SELECT a::text::int AS e, b::numeric AS f FROM t'); + rt_view +------------ + coerce: ok +(1 row) + +SELECT rt_view('subselect', 'SELECT a FROM t x WHERE b IN (SELECT b FROM t y WHERE y.a <> x.a)'); + rt_view +--------------- + subselect: ok +(1 row) + +-- Rules: reserved-word aliases in the action, DO ALSO / DO INSTEAD. +SELECT rt_rule('rule_alias', + 'CREATE RULE r AS ON INSERT TO rt_rtab DO ALSO DELETE FROM rt_rtab AS using_ WHERE using_.a = new.a + 100'); + rt_rule +---------------- + rule_alias: ok +(1 row) + +SELECT rt_rule('rule_instead', + 'CREATE RULE r AS ON UPDATE TO rt_rtab DO INSTEAD NOTHING'); + rt_rule +------------------ + rule_instead: ok +(1 row) + +DROP SCHEMA deparse_rt CASCADE; diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 75063f87a4a..4ed36d616ec 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -44,6 +44,7 @@ test: copy copyselect copydml copyencoding insert insert_conflict # ---------- test: create_function_c create_misc create_operator create_procedure create_table create_type create_schema test: create_index create_index_spgist create_view index_including index_including_gist +test: deparse_roundtrip # ---------- # Another group of parallel tests diff --git a/src/test/regress/sql/deparse_roundtrip.sql b/src/test/regress/sql/deparse_roundtrip.sql new file mode 100644 index 00000000000..9efa09f0c69 --- /dev/null +++ b/src/test/regress/sql/deparse_roundtrip.sql @@ -0,0 +1,108 @@ +-- +-- DEPARSE_ROUNDTRIP +-- +-- A view or rule definition emitted by pg_get_viewdef / pg_get_ruledef must +-- read back: the text must re-parse, define a relation returning the same +-- rows, and be a fixed point (deparsing the recreated object yields identical +-- text). These invariants are relied on by pg_dump; a definition that fails +-- any of them is a dump that will not restore. +-- +-- The test drives a corpus of definitions that have historically round-tripped +-- incorrectly (expressions needing extra parentheses, ROW() comparisons, +-- keyword-named windows, reserved-word rule aliases, XMLTABLE namespace URIs). +-- It reports only a per-case verdict, so the expected output stays small and +-- stable regardless of how the deparser spells a given definition. +-- + +CREATE SCHEMA deparse_rt; +SET search_path = deparse_rt; +SET client_min_messages = warning; + +CREATE TABLE t (a int, b int, c text, d text); +INSERT INTO t VALUES (1, 2, 'x', 'y'), (3, 4, 'p', 'q'), (5, 6, NULL, 'z'); + +-- Round-trip one view definition: create it as "src", deparse, recreate the +-- deparsed text as "dst", and check (1) it re-parsed, (2) dst returns the same +-- rows as src, (3) deparsing dst equals deparsing src (fixed point). +CREATE FUNCTION rt_view(label text, body text) RETURNS text +LANGUAGE plpgsql AS $$ +DECLARE + def1 text; + def2 text; + same boolean; +BEGIN + EXECUTE 'DROP VIEW IF EXISTS rt_dst, rt_src'; + EXECUTE 'CREATE VIEW rt_src AS ' || body; + def1 := pg_get_viewdef('rt_src'::regclass, true); + BEGIN + EXECUTE 'CREATE VIEW rt_dst AS ' || def1; + EXCEPTION WHEN others THEN + RETURN label || ': FAIL (deparsed view does not re-parse: ' || SQLERRM || ')'; + END; + def2 := pg_get_viewdef('rt_dst'::regclass, true); + IF def1 IS DISTINCT FROM def2 THEN + RETURN label || ': FAIL (deparse not a fixed point)'; + END IF; + EXECUTE 'SELECT (SELECT array_agg(x ORDER BY x) FROM (SELECT (rt_src)::text x FROM rt_src) s)' + || ' IS NOT DISTINCT FROM ' + || '(SELECT array_agg(x ORDER BY x) FROM (SELECT (rt_dst)::text x FROM rt_dst) s)' + INTO same; + IF NOT same THEN + RETURN label || ': FAIL (recreated view returns different rows)'; + END IF; + RETURN label || ': ok'; +END $$; + +-- Round-trip one rule definition (re-parse only; a rule has no result set). +CREATE FUNCTION rt_rule(label text, rule_body text) RETURNS text +LANGUAGE plpgsql AS $$ +DECLARE + def text; +BEGIN + EXECUTE 'DROP TABLE IF EXISTS rt_rtab CASCADE'; + EXECUTE 'CREATE TABLE rt_rtab (a int, b int)'; + BEGIN + EXECUTE rule_body; + EXCEPTION WHEN others THEN + RETURN label || ': skip (rule body not accepted here: ' || SQLERRM || ')'; + END; + SELECT pg_get_ruledef(oid) INTO def FROM pg_rewrite + WHERE ev_class = 'rt_rtab'::regclass AND rulename <> '_RETURN' + ORDER BY rulename LIMIT 1; + IF def IS NULL THEN + RETURN label || ': FAIL (no rule found)'; + END IF; + EXECUTE 'DROP TABLE rt_rtab CASCADE'; + EXECUTE 'CREATE TABLE rt_rtab (a int, b int)'; + BEGIN + EXECUTE def; + EXCEPTION WHEN others THEN + RETURN label || ': FAIL (deparsed rule does not re-parse: ' || SQLERRM || ')'; + END; + RETURN label || ': ok'; +END $$; + +-- Views: expressions, set operations, grouping, windows, ROW() comparisons. +SELECT rt_view('plain', 'SELECT a, b FROM t'); +SELECT rt_view('expr_paren', 'SELECT (a + b) * 2 AS e, (NOT (a < b)) AS f FROM t'); +SELECT rt_view('collate', 'SELECT a FROM t WHERE c COLLATE "C" < d COLLATE "C"'); +SELECT rt_view('row_cmp', 'SELECT a FROM t WHERE ROW(a, b) < ROW(b, a)'); +SELECT rt_view('row_eq', 'SELECT (ROW(a, b) = ROW(b, a)) AS e FROM t'); +SELECT rt_view('case_when', 'SELECT CASE WHEN a < b THEN c ELSE d END AS e FROM t'); +SELECT rt_view('distinct_on', 'SELECT DISTINCT ON (a) a, b FROM t ORDER BY a, b'); +SELECT rt_view('setop', 'SELECT a FROM t UNION SELECT b FROM t'); +SELECT rt_view('grouping', 'SELECT a, sum(b) FROM t GROUP BY GROUPING SETS ((a), ())'); +SELECT rt_view('window_kw', 'SELECT a, sum(b) OVER "rows" FROM t WINDOW "rows" AS (ORDER BY a)'); +SELECT rt_view('window_frame', 'SELECT a, sum(b) OVER (ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM t'); +SELECT rt_view('is_distinct', 'SELECT a FROM t WHERE c IS NOT DISTINCT FROM d'); +SELECT rt_view('arr_subscript','SELECT (ARRAY[a, b])[1] AS e FROM t'); +SELECT rt_view('coerce', 'SELECT a::text::int AS e, b::numeric AS f FROM t'); +SELECT rt_view('subselect', 'SELECT a FROM t x WHERE b IN (SELECT b FROM t y WHERE y.a <> x.a)'); + +-- Rules: reserved-word aliases in the action, DO ALSO / DO INSTEAD. +SELECT rt_rule('rule_alias', + 'CREATE RULE r AS ON INSERT TO rt_rtab DO ALSO DELETE FROM rt_rtab AS using_ WHERE using_.a = new.a + 100'); +SELECT rt_rule('rule_instead', + 'CREATE RULE r AS ON UPDATE TO rt_rtab DO INSTEAD NOTHING'); + +DROP SCHEMA deparse_rt CASCADE; -- 2.47.3
