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