Thread: Primary key error in INFORMATION_SCHEMA views
Hi everyone, In the SQL standard, the INFORMATION_SCHEMA.TABLE_CONSTRAINTS has a primary key compound of: - CONSTRAINT_CATALOG: the database name - CONSTRAINT_SCHEMA: the constraint's SQL schema - CONSTRAINT_NAME; the name of the constraint This part of the standard ISO/IEC 9075-11:2003 Information technology -- Database languages -- SQL -- Part 11: Information and Definition Schemas (SQL/Schemata) is show bellow: <http://www.postgresql-archive.org/file/t343155/SQL_ISO_standard_WD_9075-11-Part_11_-_SQL_Schemata.jpg> In PostgreSQL VERSION() --> 10.3 version 64 bits on Windows (and probably in oldest versions) this PRIMARY KEY does not exists. The Following script demonstrate the bug: CREATE SCHEMA s; CREATE TABLE s.foo (C INT); ALTER TABLE s.foo ADD CONSTRAINT k CHECK(C>0); CREATE TABLE s.foo2 (C INT); ALTER TABLE s.foo2 ADD CONSTRAINT k CHECK(C>=1); This last SQL command should fail because the database name, the schema name and the constraint name is the same for those two constraints, but did'nt ! This is confirm by the query: SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = 's'; Such a bug introduce major confusion in scripting or using the metadata. Thanks for correcting this bug in order to make exploitable the information views of schemas that are stable in time because going through the systems views PG _... is subject to caution and has no guarantee of stability in time! Thanks ----- Frédéric BROUARD - Data Architect - Paris / PACA, France - SARL SQL SPOT Teacher : CNAM PACA, ISEN Toulon, CESI/EXIA Aix en Provence, EPITA Paris Expert on SQL and Relational DBMS : Microsoft SQL Server / PostGreSQL... My French site about SQL language and RDBMS http://sqlpro.developpez.com The books I wrote : https://www.amazon.fr/Frédéric-Brouard/e/B004MQ3NVQ -- Sent from: http://www.postgresql-archive.org/PostgreSQL-bugs-f2117394.html
SQLpro <sqlpro@sqlspot.com> writes:
> The Following script demonstrate the bug:
> CREATE SCHEMA s;
> CREATE TABLE s.foo (C INT);
> ALTER TABLE s.foo ADD CONSTRAINT k CHECK(C>0);
> CREATE TABLE s.foo2 (C INT);
> ALTER TABLE s.foo2 ADD CONSTRAINT k CHECK(C>=1);
> This last SQL command should fail because the database name, the schema name
> and the constraint name is the same for those two constraints, but did'nt !
Indeed, but Postgres does not require check constraints to be named
uniquely across the whole schema, only per-table. Tightening that would
be a cure worse than the disease, because it would break applications
that work today.
So, sorry, we do not consider this a bug, and it's highly unlikely that
we'd ever "fix" it. You're surely free to use only spec-compliant choices
of constraint names in your own databases if you wish, but PG won't
enforce that.
regards, tom lane
Hi Tom, For a DBMS which loudly proclaims to be the most respectful of the SQL standard, not being able to use the INFORMATION_SCHEMA views because of such an error seems to me to go against its philosophy. Can not you change that by adding a configuration parameter, for a future version, that would allow you to apply these views more strictly? More pragmatically, what good can these views serve if we can not use them properly without bug? Better to remove them if you choose not to fix this bug … A + ----- Frédéric BROUARD - Data Architect - Paris / PACA, France - SARL SQL SPOT Teacher : CNAM PACA, ISEN Toulon, CESI/EXIA Aix en Provence, EPITA Paris Expert on SQL and Relational DBMS : Microsoft SQL Server / PostGreSQL... My French site about SQL language and RDBMS http://sqlpro.developpez.com The books I wrote : https://www.amazon.fr/Frédéric-Brouard/e/B004MQ3NVQ -- Sent from: http://www.postgresql-archive.org/PostgreSQL-bugs-f2117394.html
On Thursday, May 24, 2018, SQLpro <sqlpro@sqlspot.com> wrote:
Hi Tom,
For a DBMS which loudly proclaims to be the most respectful of the SQL
standard, not being able to use the INFORMATION_SCHEMA views because of such
an error seems to me to go against its philosophy.
Can not you change that by adding a configuration parameter, for a future
version, that would allow you to apply these views more strictly?
More pragmatically, what good can these views serve if we can not use them
properly without bug? Better to remove them if you choose not to fix this
bug …
Views don't have primary keys, they just report what is already stored. If the database is standard conforming the view works perfectly. It also works just fine if you accept that table name is part of the uniqueness so it does provide useful and accurate data even though we don't conform to the standard in this way. And breaking working applications to meet standard conformance here is a losing cause.
Even with an option it would default to false and nearly everyone would leave it that way. Those that would change it to true can easily write a validation script of their own to check for duplicates that need fixing.
For better and worse this is what we have. Standard conformance is respected but we are not going to cut off our own for just to meet a marginal requirement that we don't actual prevent people from choosing to adhere to themselves.
You haven't actually described the problem you are running into so further insights that could alleviate your confusion are not possible.
David J.
> You haven't actually described the problem you are running into so further
insights that could alleviate your confusion are not possible.
Good evening everyone
I'm sorry because i've got a very poor english (i'm french). I'll try to
explain as soon as possible.
It's my originel fault. I wrote a software to analyze a database and
generate its CDM. This soft is based on the integrity constraints that bind
the tables together. My query that retrieves integrity constraints uses
postgres internal tables "pg_xxx".
For example, here is 4 tables
create table "join_toto"(
"id1_join_toto" integer not null,
"id2_join_toto" integer not null,
primary key("id1_join_toto", "id2_join_toto")
);
create table "base_toto"(
"id_base_toto" integer not null,
"ref1_base_toto" integer not null,
"ref2_base_toto" integer not null,
constraint "fk_xxx" foreign key ("ref1_base_toto", "ref2_base_toto")
references "join_toto"("id2_join_toto", "id1_join_toto") match full
on update cascade on delete cascade,
primary key("id_base_toto")
);
create table "join_titi"(
"id1_join_titi" integer not null,
"id2_join_titi" integer not null,
primary key("id1_join_titi", "id2_join_titi")
);
create table "base_titi"(
"id_base_titi" integer not null,
"ref1_base_titi" integer not null,
"ref2_base_titi" integer not null,
constraint "fk_xxx" foreign key ("ref1_base_titi", "ref2_base_titi")
references "join_titi"("id2_join_titi", "id1_join_titi") match full
on update cascade on delete cascade,
primary key("id_base_titi")
);
So, as you can see, "base_toto" is linked to only "join_toto" and
"base_titi" is linked to only "join_titi".
I want to know the link of "base_toto"
select
pg_constraint.conrelid,
pg_constraint.confrelid,
pg_constraint.conkey,
pg_constraint.confkey,
t2.schemaname,
t2.relname
from pg_constraint
inner join pg_stat_user_tables as t1 on (t1.relid=pg_constraint.conrelid)
inner join pg_stat_user_tables as t2 on (t2.relid=pg_constraint.confrelid)
where (pg_constraint.contype, t1.schemaname, t1.relname)=('f', 'public',
'base_toto')
It's ok. I can see "base_toto" linked to "join_toto". But this sql using
"pg" internal's tables. There tables can change in the future with the
Postgres's version.
SQLPro, in forum, says "you need use normalized views in information_schema
because they never change".
So i've tried long long time to find any request with information_schema's
views. Like that
select distinct
k.table_schema,
k.table_name,
t.table_schema,
t.table_name
from information_schema.table_constraints as t
inner join (information_schema.referential_constraints as r
inner join information_schema.key_column_usage as k
on (
(k.constraint_schema, k.constraint_name)=(r.unique_constraint_schema,
r.unique_constraint_name)
)
) on (
(r.constraint_schema, r.constraint_name)=(t.constraint_schema,
t.constraint_name)
)
where (t.table_schema, t.table_name)=('public', 'base_toto');
But this request can't returns true's result because the constraint's name
"fk_xxx" is not unique in schema. So this request returns "base_toto" is
linked to "join_toto" and "join_titi" and that's false.
It is therefore unfortunate that Postgres' normalized views can not return a
result that allows me to know exactly which table is linked to "base_toto".
Best regards
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-bugs-f2117394.html
On 24/05/18 10:01, SQLpro wrote: > Hi Tom, > > For a DBMS which loudly proclaims to be the most respectful of the SQL > standard, not being able to use the INFORMATION_SCHEMA views because of such > an error seems to me to go against its philosophy. > > Can not you change that by adding a configuration parameter, for a future > version, that would allow you to apply these views more strictly? No, we cannot. Table inheritance matches constraints based on name, and inherited tables are often in the same schema as the parent. -- Vik Fearing +33 6 46 75 15 36 http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support