The following bug has been logged online:

Bug reference: 2750
Logged by: Stephen haberman
Email address: stephen (AT) exigencecorp (DOT) 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,

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

"Stephen haberman" <stephen (AT) exigencecorp (DOT) com> writes:
Hm, there is no such message in the archives.

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

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate