Обсуждение: BUG #2750: information_schema broken with primary and foreign key on the same column

The following bug has been logged online:

Bug reference:      2750
Logged by:          Stephen haberman
Email address:      stephen@exigencecorp.com
PostgreSQL version: 8.2 beta 3
Operating system:   win32
Description:        information_schema broken with primary and foreign key
on the same column
Details:

Just tried postgresql 8.2 beta 3 and it is missing a patch I had sent to
pgsql-patches after trying 8.2 beta 2 a few weeks ago.

The information_schema `position_in_unique_constraint` is broken when a
column has both a primary key and a foreign key. Both constraints match in
the `SELECT a FROM generate_series` and caused a "subquery returns multiple
results for an expression" error.

(Or something like that--I don't have the exact error message handy.)

This patch fixes it:

--- information_schema-original.sql    Tue Oct 24 11:10:16 2006
+++ information_schema.sql    Fri Oct 27 02:24:52 2006
@@ -928,6 +928,7 @@
                (SELECT a FROM generate_series(1, array_upper(ss.confkey,1))
a) AS foo
              WHERE conrelid = ss.confrelid
              AND conkey[foo.a] = ss.confkey[(ss.x).n]
+             AND contype = 'f'
            ) AS position_in_unique_constraint
     FROM pg_attribute a,
          (SELECT r.oid, r.relname, nc.nspname AS nc_nspname,

"Stephen haberman" <stephen@exigencecorp.com> writes:
> Just tried postgresql 8.2 beta 3 and it is missing a patch I had sent to
> pgsql-patches after trying 8.2 beta 2 a few weeks ago.

Hm, there is no such message in the archives.

> The information_schema `position_in_unique_constraint` is broken when a
> column has both a primary key and a foreign key. Both constraints match in
> the `SELECT a FROM generate_series` and caused a "subquery returns multiple
> results for an expression" error.

I see the problem too, but your description and patch are both wrong:
the case occurs when the column *referenced* by an FK has multiple
relevant entries in pg_constraint.  They don't even have to be
primary/unique keys.  Test case:

regression=# create table foo(f1 int primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
CREATE TABLE
regression=# create table bar(fx int references foo);
CREATE TABLE
regression=# select * from information_schema.key_column_usage where table_name = 'bar';
[ ...ok... ]
regression=# alter table foo add constraint c1 check(f1 > 0);
ALTER TABLE
regression=# select * from information_schema.key_column_usage where table_name = 'bar';
ERROR:  more than one row returned by a subquery used as an expression
regression=#

I think the correct patch would enforce contype IN ('p','u') not
contype = 'f'.  Also, there's still an issue: at least theoretically,
the referenced column could be in more than one unique constraint,
so the query could fail even with that restriction.

Probably what we want to do to really fix this right is to look into
pg_depend to dig out the OID of the unique constraint the FK constraint
is dependent on, and report the correct column from that.

            regards, tom lane