On 27.03.23 15:55, Peter Eisentraut wrote:
The information schema should be updated. I think the following views:
- CHECK_CONSTRAINTS
- CONSTRAINT_COLUMN_USAGE
- DOMAIN_CONSTRAINTS
- TABLE_CONSTRAINTS
It looks like these have no test coverage; maybe that could be addressed
at the same time.
Here are patches for this. I haven't included the expected files for
the tests; this should be checked again that output is correct or the
changes introduced by this patch set are as expected.
The reason we didn't have tests for this before was probably in part
because the information schema made up names for not-null constraints
involving OIDs, so the test wouldn't have been stable.
Feel free to integrate this, or we can add it on afterwards.
From 8cb5f81176e06dded88d49179debddab992ff1ce Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Wed, 29 Mar 2023 16:42:16 +0200
Subject: [PATCH 1/2] Add tests for information schema constraints views
---
src/test/regress/sql/constraints.sql | 16 ++++++++++++++++
src/test/regress/sql/domain.sql | 24 ++++++++++++++++++++++++
2 files changed, 40 insertions(+)
diff --git a/src/test/regress/sql/constraints.sql
b/src/test/regress/sql/constraints.sql
index ae427d25e9..0c8f681b51 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -589,6 +589,22 @@ CREATE TABLE notnull_tbl3 (a INTEGER NOT NULL, CHECK (a IS
NOT NULL));
ALTER TABLE notnull_tbl3 DROP CONSTRAINT pk;
\d notnull_tbl3
+--
+-- Information schema
+--
+
+SELECT * FROM information_schema.check_constraints
+ WHERE constraint_schema = 'public'
+ ORDER BY constraint_name;
+
+SELECT * FROM information_schema.constraint_column_usage
+ WHERE constraint_schema = 'public'
+ ORDER BY table_name, column_name, constraint_name;
+
+SELECT * FROM information_schema.table_constraints
+ WHERE constraint_schema = 'public'
+ ORDER BY constraint_name;
+
-- Comments
-- Setup a low-level role to enforce non-superuser checks.
CREATE ROLE regress_constraint_comments;
diff --git a/src/test/regress/sql/domain.sql b/src/test/regress/sql/domain.sql
index 75703940f9..3096b377eb 100644
--- a/src/test/regress/sql/domain.sql
+++ b/src/test/regress/sql/domain.sql
@@ -812,3 +812,27 @@ CREATE TABLE thethings (stuff things);
alter domain testdomain1 rename constraint unsigned to unsigned_foo;
alter domain testdomain1 drop constraint unsigned_foo;
drop domain testdomain1;
+
+
+--
+-- Information schema
+--
+
+SELECT * FROM information_schema.column_domain_usage
+ WHERE domain_schema = 'public' AND table_schema = 'public'
+ ORDER BY domain_name;
+
+SELECT * FROM information_schema.domain_constraints
+ WHERE domain_schema = 'public'
+ ORDER BY constraint_name;
+
+SELECT * FROM information_schema.domains
+ WHERE domain_schema = 'public'
+ ORDER BY domain_name;
+
+SELECT * FROM information_schema.check_constraints
+ WHERE (constraint_schema, constraint_name)
+ IN (SELECT constraint_schema, constraint_name
+ FROM information_schema.domain_constraints
+ WHERE domain_schema = 'public')
+ ORDER BY constraint_name;
--
2.40.0
From 093905fb9bddd073b93128893ecceae5da6801d5 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Wed, 29 Mar 2023 16:42:37 +0200
Subject: [PATCH 2/2] Update information schema for catalogued not-null
constraints
---
src/backend/catalog/information_schema.sql | 57 ++--------------------
1 file changed, 5 insertions(+), 52 deletions(-)
diff --git a/src/backend/catalog/information_schema.sql
b/src/backend/catalog/information_schema.sql
index 0555e9bc03..414fd0c6ba 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -435,31 +435,15 @@ CREATE VIEW check_constraints AS
SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
CAST(rs.nspname AS sql_identifier) AS constraint_schema,
CAST(con.conname AS sql_identifier) AS constraint_name,
- CAST(substring(pg_get_constraintdef(con.oid) from 7) AS
character_data)
+ CAST(CASE con.contype WHEN 'c' THEN
left(substring(pg_get_constraintdef(con.oid) from 8), -1)
+ WHEN 'n' THEN
substring(pg_get_constraintdef(con.oid) from 10) || ' IS NOT NULL' END AS
character_data)
AS check_clause
FROM pg_constraint con
LEFT OUTER JOIN pg_namespace rs ON (rs.oid = con.connamespace)
LEFT OUTER JOIN pg_class c ON (c.oid = con.conrelid)
LEFT OUTER JOIN pg_type t ON (t.oid = con.contypid)
WHERE pg_has_role(coalesce(c.relowner, t.typowner), 'USAGE')
- AND con.contype = 'c'
-
- UNION
- -- not-null constraints
-
- SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
- CAST(n.nspname AS sql_identifier) AS constraint_schema,
- CAST(CAST(n.oid AS text) || '_' || CAST(r.oid AS text) || '_' ||
CAST(a.attnum AS text) || '_not_null' AS sql_identifier) AS constraint_name, --
XXX
- CAST(a.attname || ' IS NOT NULL' AS character_data)
- AS check_clause
- FROM pg_namespace n, pg_class r, pg_attribute a
- WHERE n.oid = r.relnamespace
- AND r.oid = a.attrelid
- AND a.attnum > 0
- AND NOT a.attisdropped
- AND a.attnotnull
- AND r.relkind IN ('r', 'p')
- AND pg_has_role(r.relowner, 'USAGE');
+ AND con.contype IN ('c', 'n');
GRANT SELECT ON check_constraints TO PUBLIC;
@@ -822,7 +806,7 @@ CREATE VIEW constraint_column_usage AS
AND d.classid = 'pg_catalog.pg_constraint'::regclass
AND d.objid = c.oid
AND c.connamespace = nc.oid
- AND c.contype = 'c'
+ AND c.contype IN ('c', 'n')
AND r.relkind IN ('r', 'p')
AND NOT a.attisdropped
@@ -1832,6 +1816,7 @@ CREATE VIEW table_constraints AS
CAST(r.relname AS sql_identifier) AS table_name,
CAST(
CASE c.contype WHEN 'c' THEN 'CHECK'
+ WHEN 'n' THEN 'CHECK'
WHEN 'f' THEN 'FOREIGN KEY'
WHEN 'p' THEN 'PRIMARY KEY'
WHEN 'u' THEN 'UNIQUE' END
@@ -1856,38 +1841,6 @@ CREATE VIEW table_constraints AS
AND c.contype NOT IN ('t', 'x') -- ignore nonstandard constraints
AND r.relkind IN ('r', 'p')
AND (NOT pg_is_other_temp_schema(nr.oid))
- AND (pg_has_role(r.relowner, 'USAGE')
- -- SELECT privilege omitted, per SQL standard
- OR has_table_privilege(r.oid, 'INSERT, UPDATE, DELETE,
TRUNCATE, REFERENCES, TRIGGER')
- OR has_any_column_privilege(r.oid, 'INSERT, UPDATE,
REFERENCES') )
-
- UNION ALL
-
- -- not-null constraints
-
- SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
- CAST(nr.nspname AS sql_identifier) AS constraint_schema,
- CAST(CAST(nr.oid AS text) || '_' || CAST(r.oid AS text) || '_' ||
CAST(a.attnum AS text) || '_not_null' AS sql_identifier) AS constraint_name, --
XXX
- CAST(current_database() AS sql_identifier) AS table_catalog,
- CAST(nr.nspname AS sql_identifier) AS table_schema,
- CAST(r.relname AS sql_identifier) AS table_name,
- CAST('CHECK' AS character_data) AS constraint_type,
- CAST('NO' AS yes_or_no) AS is_deferrable,
- CAST('NO' AS yes_or_no) AS initially_deferred,
- CAST('YES' AS yes_or_no) AS enforced,
- CAST(NULL AS yes_or_no) AS nulls_distinct
-
- FROM pg_namespace nr,
- pg_class r,
- pg_attribute a
-
- WHERE nr.oid = r.relnamespace
- AND r.oid = a.attrelid
- AND a.attnotnull
- AND a.attnum > 0
- AND NOT a.attisdropped
- AND r.relkind IN ('r', 'p')
- AND (NOT pg_is_other_temp_schema(nr.oid))
AND (pg_has_role(r.relowner, 'USAGE')
-- SELECT privilege omitted, per SQL standard
OR has_table_privilege(r.oid, 'INSERT, UPDATE, DELETE,
TRUNCATE, REFERENCES, TRIGGER')
--
2.40.0
