From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Pg Hackers <pgsql-hackers@lists.postgresql.org>
Cc: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
Subject: Re: information_schema and not-null constraints
Date: Wed, 6 Sep 2023 19:52:37 +0200
Message-ID: <202309061752.v3kcmjdangok@alvherre.pgsql> (raw)
In-Reply-To: <202309041710.psytrxlsiqex@alvherre.pgsql>
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/Attachments:v3-0001-Update-information_schema-definition-for-not-null.patch (text/x-diff, 6.3K) download
From 870533e728cbbcc878cf10cef12b3357a51db5fd Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvherre@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.eisentraut@enterprisedb.com>
Co-authored-by: Álvaro Herrera <alvherre@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.sqlindex 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.hindex 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
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: pgsql-hackers@postgresql.org
Cc: alvherre@alvh.no-ip.org, pgsql-hackers@lists.postgresql.org, peter.eisentraut@enterprisedb.com
Subject: Re: information_schema and not-null constraints
In-Reply-To: <202309061752.v3kcmjdangok@alvherre.pgsql>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox