Обсуждение: BUG #1006: information schema constraint information.

The following bug has been logged online:

Bug reference:      1006
Logged by:          Majolee InfoTech
Email address:      info@majolee.info
PostgreSQL version: 7.4
Operating system:   Redhat 9.0
Description:        information schema constraint information.
Details:

Hello,

as per the documentation of information schema (constraint_column_usage) view should return exact column names for a
constraintcreated.  

Currently this view has a bug for foreign key constraints created for a table for more than one times. It shows first
insertedcolumn name for all of the following foreign keys defined for same table. 
#####################################################
CREATE TABLE public.test
(
  fld1 varchar(25) NOT NULL,
  fld2 varchar(25),
  fld3 varchar(25),
  CONSTRAINT pk1 PRIMARY KEY (fld1)
) WITH OIDS;
CREATE TABLE public.test2
(
  pk2 int8 NOT NULL,
  fk1 varchar(25),
  CONSTRAINT pk22 PRIMARY KEY (pk2),
  CONSTRAINT fk11 FOREIGN KEY (fk1) REFERENCES public.test (fld1) ON UPDATE RESTRICT ON DELETE RESTRICT
) WITH OIDS;
CREATE TABLE public.test3
(
  fld_1 varchar(25) NOT NULL,
  fld_2 varchar(25) NOT NULL,
  fld_3 varchar(25) NOT NULL,
  CONSTRAINT pk3 PRIMARY KEY (fld_1),
  CONSTRAINT fk3_1 FOREIGN KEY (fld_2) REFERENCES public.test (fld1) ON UPDATE RESTRICT ON DELETE RESTRICT,
  CONSTRAINT fk3_2 FOREIGN KEY (fld_3) REFERENCES public.test2 (pk2) ON UPDATE RESTRICT ON DELETE RESTRICT
) WITH OIDS;
#####################################################

This on querying
#####################################################
select * from information_schema.constraint_column_usage
#####################################################

gives following output

#####################################################
table_catalog | table_schema | table_name | column_name | constraint_catalog | constraint_schema | constraint_name
---------------+--------------+------------+-------------+--------------------+-------------------+-----------------
 ERP           | public       | test       | fld1        | ERP                | public            | pk1
 ERP           | public       | test2      | pk2         | ERP                | public            | pk22
 ERP           | public       | test2      | pk2         | ERP                | public            | fk11
 ERP           | public       | test3      | fld_1       | ERP                | public            | pk3
 ERP           | public       | test3      | fld_1       | ERP                | public            | fk3_1
 ERP           | public       | test3      | fld_1       | ERP                | public            | fk3_2
#####################################################

Which should show (Changes displayed within *CHANGE*)

#####################################################
table_catalog | table_schema | table_name | column_name | constraint_catalog | constraint_schema | constraint_name
---------------+--------------+------------+-------------+--------------------+-------------------+-----------------
 ERP           | public       | test       | fld1        | ERP                | public            | pk1
 ERP           | public       | test2      | pk2         | ERP                | public            | pk22
 ERP           | public       | test2      | *fk1*         | ERP                | public            | fk11
 ERP           | public       | test3      | fld_1       | ERP                | public            | pk3
 ERP           | public       | test3      | *fld_2*       | ERP                | public            | fk3_1
 ERP           | public       | test3      | *fld_3*       | ERP                | public            | fk3_2
#####################################################

Please update us on the same.

Thanks.....

Majolee InfoTech

"PostgreSQL Bugs List" <pgsql-bugs@postgresql.org> writes:
> [ information_schema.constraint_column_usage gives wrong answers ]

I think this part of the view's definition:

            AND (CASE WHEN c.contype = 'f' THEN c.confkey[pos.n] = a.attnum
                      ELSE c.conkey[pos.n] = a.attnum END)

should just be

            AND c.conkey[pos.n] = a.attnum

The confkey array shows column numbers of the referenced columns, which
is not the right thing to look at.

If the view were also supposed to show referenced columns, then I think
we'd need an additional UNION arm that joined on confrelid and confkey[]
instead of conrelid/conkey[].  But if I read SQL99 correctly, only
referencing not referenced columns are supposed to be shown.

BTW, I also recommend deleting the clause

            AND a.attnum > 0

since for instance a UNIQUE constraint on the OID column is legitimate.

Peter, does this change look right to you?

            regards, tom lane

Tom Lane wrote:
> If the view were also supposed to show referenced columns, then I
> think we'd need an additional UNION arm that joined on confrelid and
> confkey[] instead of conrelid/conkey[].  But if I read SQL99
> correctly, only referencing not referenced columns are supposed to be
> shown.

It is supposed to show the referenced (primary key) columns.

Both the current behavior and his proposed correction are incorrect.
Will investigate later.

Peter Eisentraut <peter_e@gmx.net> writes:
> Tom Lane wrote:
>> But if I read SQL99 correctly, only referencing not referenced
>> columns are supposed to be shown.

> It is supposed to show the referenced (primary key) columns.

[ reads spec more carefully... ]  Yeah, I think you are right.
We are going to need a separate UNION arm for the foreign-key case.

            regards, tom lane

Tom Lane wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
> > Tom Lane wrote:
> >> But if I read SQL99 correctly, only referencing not referenced
> >> columns are supposed to be shown.
> >
> > It is supposed to show the referenced (primary key) columns.
>
> [ reads spec more carefully... ]  Yeah, I think you are right.
> We are going to need a separate UNION arm for the foreign-key case.

Fixed.  The union branch was already trying to differentiate between
primary key and foreign key when joining the columns, but not when
joining the tables.  Just a thinko.