Re: BUG #2750: information_schema broken with primary and foreign key on the same column

"Stephen haberman" <stephen(at)exigencecorp(dot)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