agora inbox for [email protected]
help / color / mirror / Atom feedFrom: Alvaro Herrera <[email protected]>
Subject: [PATCH v3] Update information_schema definition for not-null constraints
Date: Mon, 4 Sep 2023 18:05:50 +0200
Now that we have catalogued not-null constraints, our information_schema
definition must be updated to grab those rather than fabricate synthetic
definitions.
Note that we still don't have catalog rows for not-null constraints on
domains, but we've never had not-null constraints listed in
information_schema, so that's a problem to be solved separately.
Co-authored-by: Peter Eisentraut <[email protected]>
Co-authored-by: Álvaro Herrera <[email protected]>
Discussion: https://postgr.es/m/[email protected]
---
src/backend/catalog/information_schema.sql | 74 ++++++++--------------
src/include/catalog/catversion.h | 2 +-
2 files changed, 28 insertions(+), 48 deletions(-)
diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql
index a06ec7a0a8..c402cca7f4 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -444,22 +444,19 @@ CREATE VIEW check_constraints AS
WHERE pg_has_role(coalesce(c.relowner, t.typowner), 'USAGE')
AND con.contype = 'c'
- UNION
+ UNION ALL
-- 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');
+ SELECT current_database()::information_schema.sql_identifier AS constraint_catalog,
+ rs.nspname::information_schema.sql_identifier AS constraint_schema,
+ con.conname::information_schema.sql_identifier AS constraint_name,
+ format('CHECK (%s IS NOT NULL)', at.attname)::information_schema.character_data AS check_clause
+ FROM pg_constraint con
+ LEFT JOIN pg_namespace rs ON rs.oid = con.connamespace
+ LEFT JOIN pg_class c ON c.oid = con.conrelid
+ LEFT JOIN pg_type t ON t.oid = con.contypid
+ LEFT JOIN pg_attribute at ON (con.conrelid = at.attrelid AND con.conkey[1] = at.attnum)
+ WHERE pg_has_role(coalesce(c.relowner, t.typowner), 'USAGE'::text)
+ AND con.contype = 'n';
GRANT SELECT ON check_constraints TO PUBLIC;
@@ -826,6 +823,20 @@ CREATE VIEW constraint_column_usage AS
AND r.relkind IN ('r', 'p')
AND NOT a.attisdropped
+ UNION ALL
+
+ /* not-null constraints */
+ SELECT DISTINCT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname
+ FROM pg_namespace nr, pg_class r, pg_attribute a, pg_namespace nc, pg_constraint c
+ WHERE nr.oid = r.relnamespace
+ AND r.oid = a.attrelid
+ AND r.oid = c.conrelid
+ AND a.attnum = c.conkey[1]
+ AND c.connamespace = nc.oid
+ AND c.contype = 'n'
+ AND r.relkind in ('r', 'p')
+ AND not a.attisdropped
+
UNION ALL
/* unique/primary key/foreign key constraints */
@@ -1828,6 +1839,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
@@ -1852,38 +1864,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')
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index ab9a7ac1f7..4eaef54d0c 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -57,6 +57,6 @@
*/
/* yyyymmddN */
-#define CATALOG_VERSION_NO 202308251
+#define CATALOG_VERSION_NO 202309061
#endif
--
2.30.2
--ljgpw53wkr76ezax--
view thread (4859+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: [PATCH v3] Update information_schema definition for not-null constraints
In-Reply-To: <no-message-id-1853582@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox