Thread

pg_get_constraintdef: Schema qualify foreign tables unless pretty printing is enabled

Lukas Fittl <lukas@fittl.com> — 2022-08-10T00:10:35Z

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