On 2023-Sep-04, Alvaro Herrera wrote: > In reference to [1], 0001 attached to this email contains the updated > view definitions that I propose.
Given the downthread discussion, I propose the attached. There are no changes to v2, other than dropping the test part. We can improve the situation for domains separately and likewise for testing. -- Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
>From 870533e728cbbcc878cf10cef12b3357a51db5fd Mon Sep 17 00:00:00 2001 From: Alvaro Herrera <alvhe...@alvh.no-ip.org> Date: Mon, 4 Sep 2023 18:05:50 +0200 Subject: [PATCH v3] Update information_schema definition for not-null constraints MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit 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 <peter.eisentr...@enterprisedb.com> Co-authored-by: Álvaro Herrera <alvhe...@alvh.no-ip.org> Discussion: https://postgr.es/m/202309041710.psytrxlsiqex@alvherre.pgsql --- 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
