Consider this example in an empty database:
db=# create table t1 (f1 int);
CREATE TABLE
db=# create unique index t1f1 on t1(f1);
CREATE INDEX
db=# create table t2 (f2 int references t1(f1));
CREATE TABLE
db=# create table t3(f3 int primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t3_pkey" for
table "t3"
CREATE TABLE
db=# select * from information_schema.referential_constraints;
constraint_catalog | constraint_schema | constraint_name |
unique_constraint_catalog | unique_constraint_schema | unique_constraint_name |
match_option | update_rule | delete_rule
--------------------+-------------------+-----------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------
db | public | t2_f2_fkey |
| | | NONE | NO
ACTION | NO ACTION
(1 row)
Okay so far. The lack of unique_constraint_name etc is correct because
there is no unique constraint supporting this FK constraint, only a
unique index. But now:
db=# alter table t1 add constraint t1_ref_t3 foreign key (f1) references t3;
ALTER TABLE
db=# select * from information_schema.referential_constraints;
constraint_catalog | constraint_schema | constraint_name |
unique_constraint_catalog | unique_constraint_schema | unique_constraint_name |
match_option | update_rule | delete_rule
--------------------+-------------------+-----------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------
db | public | t1_ref_t3 | db
| public | t3_pkey | NONE | NO
ACTION | NO ACTION
(1 row)
Ooops, what became of t2_f2_fkey?
The reason is that the core of the view is
FROM (pg_namespace ncon
INNER JOIN pg_constraint con ON ncon.oid = con.connamespace
INNER JOIN pg_class c ON con.conrelid = c.oid)
LEFT JOIN
(pg_constraint pkc
INNER JOIN pg_namespace npkc ON pkc.connamespace = npkc.oid)
ON con.confrelid = pkc.conrelid
AND _pg_keysequal(con.confkey, pkc.conkey)
WHERE c.relkind = 'r'
AND con.contype = 'f'
AND (pkc.contype IN ('p', 'u') OR pkc.contype IS NULL)
and that last line is failing to consider the possibility that we'll
find an accidental match to a pkc row that has contype other than
'p' or 'u'. Instead of plastering on an IS NULL alternative, the
restriction on pkc.contype needs to be in or below the LEFT JOIN.
There might be other bugs of the same sort, I haven't looked.
But wait, there's more:
db=# drop table t1,t2,t3;
DROP TABLE
db=# create table t1 (f1 int primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for
table "t1"
CREATE TABLE
db=# alter table t1 add constraint useless_duplicate unique(f1);
NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index
"useless_duplicate" for table "t1"
ALTER TABLE
db=# create table t2 (f2 int references t1(f1));
CREATE TABLE
db=# select * from information_schema.referential_constraints;
constraint_catalog | constraint_schema | constraint_name |
unique_constraint_catalog | unique_constraint_schema | unique_constraint_name |
match_option | update_rule | delete_rule
--------------------+-------------------+-----------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------
db | public | t2_f2_fkey | db
| public | t1_pkey | NONE | NO
ACTION | NO ACTION
db | public | t2_f2_fkey | db
| public | useless_duplicate | NONE | NO
ACTION | NO ACTION
(2 rows)
t2_f2_fkey is shown twice, because there are two matches to potential
supporting unique constraints. This is bogus because it violates
the supposed primary key of the view.
It gets worse:
db=# drop table t1,t2;
DROP TABLE
db=# create table t1 (f1 int);
CREATE TABLE
db=# create unique index t1f1 on t1(f1);
CREATE INDEX
db=# create table t2 (f2 int references t1(f1));
CREATE TABLE
db=# select * from information_schema.referential_constraints;
constraint_catalog | constraint_schema | constraint_name |
unique_constraint_catalog | unique_constraint_schema | unique_constraint_name |
match_option | update_rule | delete_rule
--------------------+-------------------+-----------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------
db | public | t2_f2_fkey |
| | | NONE | NO
ACTION | NO ACTION
(1 row)
db=# alter table t1 add constraint useless_duplicate unique(f1);
NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index
"useless_duplicate" for table "t1"
ALTER TABLE
db=# select * from information_schema.referential_constraints;
constraint_catalog | constraint_schema | constraint_name |
unique_constraint_catalog | unique_constraint_schema | unique_constraint_name |
match_option | update_rule | delete_rule
--------------------+-------------------+-----------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------
db | public | t2_f2_fkey | db
| public | useless_duplicate | NONE | NO
ACTION | NO ACTION
(1 row)
Now the view is claiming that t2_f2_fkey depends on a constraint that,
in fact, it does not depend on.
I think the only way to fix these latter cases is to have the view link
through pg_depend to find the specific index and constraint (if any)
that the FK constraint actually depends on, rather than just finding
matches of column numbers that suggest that it *might* depend on that
unique constraint. I haven't tried to code that yet, but it seems like
it shouldn't be too hard.
Is this important enough to back-patch? We can't force initdb in back
branches, but we could suggest that people could drop and re-create the
information_schema (I think that's supposed to work).
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
