Re: BUG #1006: information schema constraint information.

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: info(at)majolee(dot)info
Cc: "PostgreSQL Bugs List" <pgsql-bugs(at)postgresql(dot)org>, Peter Eisentraut <peter_e(at)gmx(dot)net>
Subject: Re: BUG #1006: information schema constraint information.
Date: 2003-12-15 01:22:53
Message-ID: 4715.1071451373@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

"PostgreSQL Bugs List" <pgsql-bugs(at)postgresql(dot)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

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2003-12-15 01:33:09 Re: libpq3 + ssl memory leak
Previous Message PostgreSQL Bugs List 2003-12-13 15:51:20 BUG #1006: information schema constraint information.