Bug in the information_schema.referential_constraints view - Mailing list pgsql-bugs

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;