Hi hackers,
Whilst debugging an issue with the output of pg_get_constraintdef, we've
discovered that pg_get_constraintdef doesn't schema qualify foreign tables
mentioned in the REFERENCES clause, even if pretty printing
(PRETTYFLAG_SCHEMA) is turned off.
This is a problem because it means there is no way to get a constraint
definition that can be recreated on another system when multiple schemas
are in use, but a different search_path is set. It's also different from
pg_get_indexdef, where this flag is correctly respected.
I assume this is an oversight, since the fix is pretty straightforward, see
attached patch. I'll register the patch for the next commitfest.
Here is a test case from my colleague Maciek showing this difference:
create schema s;
create table s.foo(a int primary key);
create table s.bar(a int primary key, b int references s.foo(a));
select pg_get_indexdef(indexrelid, 0, false) from pg_index order by
indexrelid desc limit 3;
pg_get_indexdef
-------------------------------------------------------------------------------------------------------
CREATE UNIQUE INDEX bar_pkey ON s.bar USING btree (a)
CREATE UNIQUE INDEX foo_pkey ON s.foo USING btree (a)
CREATE UNIQUE INDEX pg_toast_13593_index ON pg_toast.pg_toast_13593 USING
btree (chunk_id, chunk_seq)
(3 rows)
select pg_get_constraintdef(oid, false) from pg_constraint order by oid
desc limit 3;
pg_get_constraintdef
-----------------------------------
FOREIGN KEY (b) REFERENCES foo(a)
PRIMARY KEY (a)
PRIMARY KEY (a)
(3 rows)
Thanks,
Lukas
--
Lukas Fittl
From 83a1ab6081f70d0eed820b2b13bc3ab6e93af8ec Mon Sep 17 00:00:00 2001
From: Lukas Fittl <lukas@fittl.com>
Date: Tue, 9 Aug 2022 16:55:35 -0700
Subject: [PATCH v1] pg_get_constraintdef: Schema qualify foreign tables by
default
This matches pg_get_constraintdef to behave the same way as
pg_get_indexdef, which is to schema qualify referenced objects in the
definition, unless pretty printing is explicitly requested.
For pretty printing the previous behaviour is retained, which is to only
include the schema information if the referenced object is not in the
current search path.
---
src/backend/utils/adt/ruleutils.c | 5 +++--
src/test/regress/expected/foreign_key.out | 8 ++++----
2 files changed, 7 insertions(+), 6 deletions(-)
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index d575aa0066..b7a2a356b4 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -2249,8 +2249,9 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand,
/* add foreign relation name */
appendStringInfo(&buf, ") REFERENCES %s(",
- generate_relation_name(conForm->confrelid,
- NIL));
+ (prettyFlags & PRETTYFLAG_SCHEMA) ?
+ generate_relation_name(conForm->confrelid, NIL) :
+ generate_qualified_relation_name(conForm->confrelid));
/* Fetch and build referenced-column list */
val = SysCacheGetAttr(CONSTROID, tup,
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out
index da26f083bc..a70f7c2491 100644
--- a/src/test/regress/expected/foreign_key.out
+++ b/src/test/regress/expected/foreign_key.out
@@ -775,10 +775,10 @@ CREATE TABLE FKTABLE (
FOREIGN KEY (tid, fk_id_del_set_default) REFERENCES PKTABLE ON DELETE SET DEFAULT (fk_id_del_set_default)
);
SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass::oid ORDER BY oid;
- pg_get_constraintdef
---------------------------------------------------------------------------------------------------------------------
- FOREIGN KEY (tid, fk_id_del_set_null) REFERENCES pktable(tid, id) ON DELETE SET NULL (fk_id_del_set_null)
- FOREIGN KEY (tid, fk_id_del_set_default) REFERENCES pktable(tid, id) ON DELETE SET DEFAULT (fk_id_del_set_default)
+ pg_get_constraintdef
+---------------------------------------------------------------------------------------------------------------------------
+ FOREIGN KEY (tid, fk_id_del_set_null) REFERENCES public.pktable(tid, id) ON DELETE SET NULL (fk_id_del_set_null)
+ FOREIGN KEY (tid, fk_id_del_set_default) REFERENCES public.pktable(tid, id) ON DELETE SET DEFAULT (fk_id_del_set_default)
(2 rows)
INSERT INTO PKTABLE VALUES (1, 0), (1, 1), (1, 2);
--
2.34.0
