Thread: Bug in the information_schema.referential_constraints view
If PostgreSQL failed to compile on your computer or you found a bug that is likely to be specific to one platform then please fill out this form and e-mail it to pgsql-ports@postgresql.org. To report any other bug, fill out the form below and e-mail it to pgsql-bugs@postgresql.org. If you not only found the problem but solved it and generated a patch then e-mail it to pgsql-patches@postgresql.org instead. Please use the command "diff -c" to generate the patch. You may also enter a bug report at http://www.postgresql.org/ instead of e-mail-ing this form. =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D POSTGRESQL BUG REPORT TEMPLATE =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D Your name : Vivien MALERBA Your email address : malerba@gnome-db.org System Configuration --------------------- Architecture (example: Intel Pentium) :Intel Pentium Operating System (example: Linux 2.0.26 ELF) :Linux 2.4.7-10 PostgreSQL version (example: PostgreSQL-7.3): PostgreSQL-7.4 Beta 4 Compiler used (example: gcc 2.95.2) :gcc 3.0.2 Please enter a FULL description of your problem: ------------------------------------------------ The information_schema.referential_constraints retuns wrong data because there is an incomplete joining condition in the WHERE clause. Please describe a way to repeat the problem. Please try to provide a concise reproducible example, if at all possible: ---------------------------------------------------------------------- If you know how this problem might be fixed, list the solution below: --------------------------------------------------------------------- Fix the buggy joining condition in the view itself. Here is the working view (probably to be integrated into backend/catalog/information_schema.sql). Sorry, I did not have the time to produce a patch... CREATE VIEW referential_constraints AS SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog, CAST(ncon.nspname AS sql_identifier) AS constraint_schema, CAST(con.conname AS sql_identifier) AS constraint_name, CAST(current_database() AS sql_identifier) AS unique_constraint_catalog, CAST(npkc.nspname AS sql_identifier) AS unique_constraint_schema, CAST(pkc.conname AS sql_identifier) AS unique_constraint_name, CAST( CASE con.confmatchtype WHEN 'f' THEN 'FULL' WHEN 'p' THEN 'PARTIAL' WHEN 'u' THEN 'NONE' END AS character_data) AS match_option, CAST( CASE con.confupdtype WHEN 'c' THEN 'CASCADE' WHEN 'n' THEN 'SET NULL' WHEN 'd' THEN 'SET DEFAULT' WHEN 'r' THEN 'RESTRICT' WHEN 'a' THEN 'NOACTION' END AS character_data) AS update_rule, CAST( CASE con.confdeltype WHEN 'c' THEN 'CASCADE' WHEN 'n' THEN 'SET NULL' WHEN 'd' THEN 'SET DEFAULT' WHEN 'r' THEN 'RESTRICT' WHEN 'a' THEN 'NOACTION' END AS character_data) AS delete_rule FROM pg_namespace ncon, pg_constraint con, pg_class c, pg_constraint pkc, pg_namespace npkc, pg_user u WHERE ncon.oid =3D con.connamespace AND con.conrelid =3D c.oid AND con.confkey =3D pkc.conkey AND pkc.connamespace =3D npkc.oid AND c.relowner =3D u.usesysid AND c.relkind =3D 'r' AND con.contype =3D 'f' AND con.confrelid =3D pkc.conrelid AND u.usename =3D current_user;
malerba@gnome-db.org writes:
> The information_schema.referential_constraints retuns wrong data because
> there is an incomplete joining condition in the WHERE clause.
> [ these two conditions need to be added: ]
> AND con.contype = 'f'
> AND con.confrelid = pkc.conrelid
I think this is correct as far as it goes, but there are more problems.
For one, I believe we also need to check the contype of the pkc row;
otherwise matches against check constraints are possible.
Another problem is that the view will fail to list FK constraints at all
if it cannot identify a matching unique constraint. Which there may not
be (the backend code for creating an FK checks for a matching unique
index, quite a different animal). And the check for match is inadequate
anyway, because it is using "con.confkey = pkc.conkey", which only
matches if the unique constraint lists the same columns *in the same
order* as the FK constraint does. The backend code does not require
that.
A more robust way to handle things would be to make use of pg_depend to
find the index the FK constraint depends on and then chain to the unique
constraint associated with that index. However, we need to decide what
to do if there is no such unique constraint. I don't think "omit the FK
constraint from the view" is the right answer. We could return nulls
for the unique_constraint_schema and unique_constraint_name, or we could
return the name of the index itself (not standard, but then the
underlying situation isn't standard either).
Comments?
Another question is whether to force an initdb after making this change.
If we don't, existing beta testers may continue to use the incorrect
view definition.
regards, tom lane
Tom Lane writes: > > AND con.contype = 'f' > > AND con.confrelid = pkc.conrelid > > I think this is correct as far as it goes, but there are more problems. Added. > For one, I believe we also need to check the contype of the pkc row; > otherwise matches against check constraints are possible. Done. > Another problem is that the view will fail to list FK constraints at all > if it cannot identify a matching unique constraint. If you want information under those conditions, you're looking at the wrong view. table_constraints gives you general information about constraints. > Which there may not be (the backend code for creating an FK checks for a > matching unique index, quite a different animal). I think that should be changed. > And the check for match is inadequate anyway, because it is using > "con.confkey = pkc.conkey", which only matches if the unique constraint > lists the same columns *in the same order* as the FK constraint does. > The backend code does not require that. OK, that is indeed a problem. I'll see if I can up with a solution. > A more robust way to handle things would be to make use of pg_depend to > find the index the FK constraint depends on and then chain to the unique > constraint associated with that index. I've used pg_depend for some other views, but that entails problems as well, for example, because they don't track system tables. It might be worth a shot in this particular case, though. > Another question is whether to force an initdb after making this change. > If we don't, existing beta testers may continue to use the incorrect > view definition. I think we will have to. -- Peter Eisentraut peter_e@gmx.net
Peter Eisentraut <peter_e@gmx.net> writes:
> Tom Lane writes:
>> Another problem is that the view will fail to list FK constraints at all
>> if it cannot identify a matching unique constraint.
> If you want information under those conditions, you're looking at the
> wrong view. table_constraints gives you general information about
> constraints.
Mmm ... can't say that I agree. The FK constraint itself is a perfectly
good constraint. It may be that ignoring such constraints is okay per
the letter of the spec, but given that we have the extension to support
FK constraints on non-constraint-associated indexes, it seems to me that
this view should cope too.
>> Which there may not be (the backend code for creating an FK checks for a
>> matching unique index, quite a different animal).
> I think that should be changed.
No, because that would entail a genuine loss of capability: FK
constraints couldn't be built using indexes that were made by CREATE
UNIQUE INDEX rather than through the unique/pk constraint syntax.
In particular this would mean that non-btree indexes could not be used.
(Yes, I know that as of today we don't have UNIQUE support in any of the
non-btree index types, but that will change. IIRC Neil Conway has
already been working on unique hashes, and I'm sure GIST will support it
eventually as well.)
>> A more robust way to handle things would be to make use of pg_depend to
> I've used pg_depend for some other views, but that entails problems as
> well, for example, because they don't track system tables.
Good point. But we don't support explicit foreign key constraints on
system tables, and probably aren't going to start anytime soon, so I
think this is probably okay.
>> Another question is whether to force an initdb after making this change.
>> If we don't, existing beta testers may continue to use the incorrect
>> view definition.
> I think we will have to.
Okay. I've been reviewing the rest of information_schema and have found
some other issues, but am not all the way through yet. Please hold off
the initdb force until I've reported on the other stuff.
regards, tom lane
I have fixed the problem with the keys being in different order and the problem of missing unique constraints. Tom Lane writes: > >> Which there may not be (the backend code for creating an FK checks for a > >> matching unique index, quite a different animal). > > > I think that should be changed. > > No, because that would entail a genuine loss of capability: FK > constraints couldn't be built using indexes that were made by CREATE > UNIQUE INDEX rather than through the unique/pk constraint syntax. > In particular this would mean that non-btree indexes could not be used. But that means the deficiency is elsewhere, namely that you cannot build non-btree indexes for primary key or unique constraints. > (Yes, I know that as of today we don't have UNIQUE support in any of the > non-btree index types, but that will change. IIRC Neil Conway has > already been working on unique hashes, and I'm sure GIST will support it > eventually as well.) Isn't the whole unique index thing a dead end anyway? How are we ever going to get deferrable unique constraints that way? -- Peter Eisentraut peter_e@gmx.net
Peter Eisentraut <peter_e@gmx.net> writes:
> Tom Lane writes:
>> No, because that would entail a genuine loss of capability: FK
>> constraints couldn't be built using indexes that were made by CREATE
>> UNIQUE INDEX rather than through the unique/pk constraint syntax.
>> In particular this would mean that non-btree indexes could not be used.
> But that means the deficiency is elsewhere, namely that you cannot build
> non-btree indexes for primary key or unique constraints.
So are you proposing we extend the constraint syntax instead? I think
it's better to keep our nonstandard index support in a separate,
nonstandard statement (CREATE INDEX) rather than mash it together with
spec-mandated syntax. That seems like a recipe for getting stuck when
the spec adds extensions.
> Isn't the whole unique index thing a dead end anyway? How are we ever
> going to get deferrable unique constraints that way?
The way that was just discussed --- with a deferrable constraint, you
don't elog immediately when the index detects a collision, but make a
note to recheck that particular key value at the time the constraint
should be enforced. I can't imagine that we'd want to do unique
constraints without any index support. How would you avoid having to
check lots and lots of uninteresting rows?
regards, tom lane