Lists: | pgsql-hackers |
---|
From: | Josh Berkus <josh(at)agliodbs(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Bug in information_schema: FK constraint is defined as against referenced table only |
Date: | 2008-12-14 21:18:52 |
Message-ID: | 4945783C.7070608@agliodbs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Folks,
I've been trying to extract some information about referencing tables
from information_schema, and discovering that it isn't there.
For example, take the following FK, from table Errors to table Files:
CONSTRAINT errors_file_fkey FOREIGN KEY (file)
REFERENCES files (id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
What's listed in referential_constraints is:
constraint_catalog | constraint_schema | constraint_name |
unique_constraint_catalog | unique_constraint_schema |
unique_constraint_name | match_option | update_rule | delete_rule
apple3 | public | errors_file_fkey | apple3
| public | files_pkey |
NONE | CASCADE | CASCADE
Ok, there's some useful information about the *referenced* table,
including its unique constraint. Doesn't tell us anything about the
*referencing* table, though.
Let's look up the data on errors_file_fkey constraint in
constrain_column_usage:
table_catalog | table_schema | table_name | column_name |
constraint_catalog | constraint_schema | constraint_name
apple3 | public | files | id | apple3
| public | errors_file_fkey
Huh? This shows errors_file_fkey constraint defined on the *referenced*
table only.
constraint_table_usage has this:
table_catalog | table_schema | table_name | constraint_catalog |
constraint_schema | constraint_name
---------------+--------------+------------+--------------------+-------------------+------------------
apple3 | public | files | apple3 |
public | errors_file_fkey
By information_schema, you wouldn't have any idea that errors_file_fkey
is defined on the table Errors, let alone what columns it's defined
against.
Poke around; you'll discover that there is no information about
referencing tables in information_schema at all.
This has got to be a bug, even if the SQL standard can be read to
support it.
I'm happy to write some code to fix it, if we can agree what these views
should show. I think constraint_column_usage and constraint_table_usage
should be showing the data of both the referenced and referencing tables.
--Josh Berkus
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Josh Berkus <josh(at)agliodbs(dot)com> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Bug in information_schema: FK constraint is defined as against referenced table only |
Date: | 2008-12-15 01:02:29 |
Message-ID: | 5130.1229302949@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Josh Berkus <josh(at)agliodbs(dot)com> writes:
> By information_schema, you wouldn't have any idea that errors_file_fkey
> is defined on the table Errors, let alone what columns it's defined
> against.
Look into key_column_usage.
> I'm happy to write some code to fix it, if we can agree what these views
> should show. I think constraint_column_usage and constraint_table_usage
> should be showing the data of both the referenced and referencing tables.
You might think that, but the specification is 100% clear that you're
wrong.
regards, tom lane