information_schema and not-null constraints
In reference to [1]/messages/by-id/81b461c4-edab-5d8c-2f88-203108425340@enterprisedb.com, 0001 attached to this email contains the updated
view definitions that I propose.
In 0002, I took the tests added by Peter's proposed patch and put them
in a separate test file that runs at the end. There are some issues,
however. One is that the ORDER BY clause in the check_constraints view
is not fully deterministic, because the table name is not part of the
view definition, so we cannot sort by table name. In the current
regression database there is only one case[2]select constraint_name, count(*), string_agg(distinct check_clause, E'\n') from information_schema.check_constraints group by constraint_name having count(*) > 1; where two constraints have
the same name and different definition:
inh_check_constraint │ 2 │ ((f1 > 0)) NOT VALID ↵
│ │ ((f1 > 0))
(on tables invalid_check_con and invalid_check_con_child). I assume
this is going to bite us at some point. We could just add a WHERE
clause to omit that one constraint.
Another issue I notice eyeballing at the results is that foreign keys on
partitioned tables are listing the rows used to implement the
constraints on partitions, which are sort-of "internal" constraints (and
are not displayed by psql's \d). I hope this is a relatively simple fix
that we could extract from the code used by psql.
Anyway, I think I'm going to get 0001 committed sometime tomorrow, and
then play a bit more with 0002 to try and get it pushed soon also.
Thanks
[1]: /messages/by-id/81b461c4-edab-5d8c-2f88-203108425340@enterprisedb.com
[2]: select constraint_name, count(*), string_agg(distinct check_clause, E'\n') from information_schema.check_constraints group by constraint_name having count(*) > 1;
select constraint_name, count(*),
string_agg(distinct check_clause, E'\n')
from information_schema.check_constraints
group by constraint_name
having count(*) > 1;
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
"You don't solve a bad join with SELECT DISTINCT" #CupsOfFail
https://twitter.com/connor_mc_d/status/1431240081726115845
Attachments:
v2-0001-update-information_schema-definition.patchtext/x-diff; charset=utf-8Download
From d8a5f8103934fe65a83a2ca44f6af72449cb6aa9 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 v2 1/2] update information_schema definition
---
src/backend/catalog/information_schema.sql | 74 ++++++++--------------
1 file changed, 27 insertions(+), 47 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')
--
2.39.2
v2-0002-add-information_schema-test.patchtext/x-diff; charset=utf-8Download
From b0c39ecaf6c4a1179d45fa9de7876b173c92d6ca Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
Date: Mon, 4 Sep 2023 18:05:33 +0200
Subject: [PATCH v2 2/2] add information_schema test
---
.../regress/expected/information_schema.out | 691 ++++++++++++++++++
src/test/regress/parallel_schedule | 5 +-
src/test/regress/sql/information_schema.sql | 41 ++
3 files changed, 735 insertions(+), 2 deletions(-)
create mode 100644 src/test/regress/expected/information_schema.out
create mode 100644 src/test/regress/sql/information_schema.sql
diff --git a/src/test/regress/expected/information_schema.out b/src/test/regress/expected/information_schema.out
new file mode 100644
index 0000000000..0b7b93eb69
--- /dev/null
+++ b/src/test/regress/expected/information_schema.out
@@ -0,0 +1,691 @@
+-- We test the information schema last, so that we examine
+-- database state at the end of the regression test run.
+--
+-- Table constraints
+--
+SELECT * FROM information_schema.check_constraints
+ WHERE constraint_schema = 'public'
+ ORDER BY constraint_name;
+ constraint_catalog | constraint_schema | constraint_name | check_clause
+--------------------+-------------------+--------------------------------------------+-----------------------------------------------------------------------
+ regression | public | atnotnull1_a_not_null | CHECK (a IS NOT NULL)
+ regression | public | atnotnull1_b_not_null | CHECK (b IS NOT NULL)
+ regression | public | bar1_b_not_null | CHECK (b IS NOT NULL)
+ regression | public | bar2_b_not_null | CHECK (b IS NOT NULL)
+ regression | public | blocal | (((b)::double precision < (1000)::double precision))
+ regression | public | bmerged | (((b)::double precision > (1)::double precision))
+ regression | public | bmerged | (((b)::double precision > (1)::double precision))
+ regression | public | bnoinherit | (((b)::double precision > (100)::double precision)) NO INHERIT
+ regression | public | boolvalues_value_not_null | CHECK (value IS NOT NULL)
+ regression | public | brinopers_bloom_check | ((cardinality(op) = cardinality(value)))
+ regression | public | brinopers_bloom_check1 | ((cardinality(op) = cardinality(matches)))
+ regression | public | brinopers_check | ((cardinality(op) = cardinality(value)))
+ regression | public | brinopers_check1 | ((cardinality(op) = cardinality(matches)))
+ regression | public | brinopers_multi_check | ((cardinality(op) = cardinality(value)))
+ regression | public | brinopers_multi_check1 | ((cardinality(op) = cardinality(matches)))
+ regression | public | cc1_f1_not_null | CHECK (f1 IS NOT NULL)
+ regression | public | cc1_f1_not_null | CHECK (f1 IS NOT NULL)
+ regression | public | check_b | (((b IS NULL) OR (b = 'a'::bpchar)))
+ regression | public | check_con | ((x > 3))
+ regression | public | check_con_tbl_check | (check_con_function(check_con_tbl.*))
+ regression | public | chk | ((b < 50)) NOT VALID
+ regression | public | clstr_tst_a_not_null | CHECK (a IS NOT NULL)
+ regression | public | clstr_tst_a_not_null | CHECK (a IS NOT NULL)
+ regression | public | clstr_tst_s_rf_a_not_null | CHECK (rf_a IS NOT NULL)
+ regression | public | cnn_child2_a_not_null | CHECK (a IS NOT NULL)
+ regression | public | cnn_child2_b_not_null | CHECK (b IS NOT NULL)
+ regression | public | cnn_child_b_not_null | CHECK (b IS NOT NULL)
+ regression | public | cnn_grandchild_b_not_null | CHECK (b IS NOT NULL)
+ regression | public | cnn_grandchild_b_not_null | CHECK (b IS NOT NULL)
+ regression | public | comment_test_positive_col_check | ((positive_col > 0))
+ regression | public | con_check | ((VALUE > 0))
+ regression | public | copy_con | (((x > 3) AND (y <> 'check failed'::text) AND (x < 7)))
+ regression | public | customer_name_not_null | CHECK (name IS NOT NULL)
+ regression | public | foo | ((xx = 'text'::text))
+ regression | public | gtest1_1_a_not_null | CHECK (a IS NOT NULL)
+ regression | public | gtest1_y_a_not_null | CHECK (a IS NOT NULL)
+ regression | public | gtest20_b_check | ((b < 50))
+ regression | public | gtest21a_b_not_null | CHECK (b IS NOT NULL)
+ regression | public | gtest_child3_f1_not_null | CHECK (f1 IS NOT NULL)
+ regression | public | gtest_parent_f1_not_null | CHECK (f1 IS NOT NULL)
+ regression | public | gtest_parent_f1_not_null | CHECK (f1 IS NOT NULL)
+ regression | public | gtest_parent_f1_not_null | CHECK (f1 IS NOT NULL)
+ regression | public | gtestdomain1_check | ((VALUE < 10))
+ regression | public | gtestx_a_not_null | CHECK (a IS NOT NULL)
+ regression | public | gtestxx_1_a_not_null | CHECK (a IS NOT NULL)
+ regression | public | gtestxx_3_a_not_null | CHECK (a IS NOT NULL)
+ regression | public | gtestxx_4_a_not_null | CHECK (a IS NOT NULL)
+ regression | public | inh_check_constraint | ((f1 > 0)) NOT VALID
+ regression | public | inh_check_constraint | ((f1 > 0))
+ regression | public | insert_tbl_check | (((x + z) = 0))
+ regression | public | insert_tbl_con | (((x >= 3) AND (y <> 'check failed'::text) AND (x < 8)))
+ regression | public | itest10_a_not_null | CHECK (a IS NOT NULL)
+ regression | public | itest11_a_not_null | CHECK (a IS NOT NULL)
+ regression | public | itest13_b_not_null | CHECK (b IS NOT NULL)
+ regression | public | itest13_c_not_null | CHECK (c IS NOT NULL)
+ regression | public | itest14_id_not_null | CHECK (id IS NOT NULL)
+ regression | public | itest1_a_not_null | CHECK (a IS NOT NULL)
+ regression | public | itest2_a_not_null | CHECK (a IS NOT NULL)
+ regression | public | itest3_a_not_null | CHECK (a IS NOT NULL)
+ regression | public | itest3_c_not_null | CHECK (c IS NOT NULL)
+ regression | public | itest5_a_not_null | CHECK (a IS NOT NULL)
+ regression | public | itest6_a_not_null | CHECK (a IS NOT NULL)
+ regression | public | itest7_a_not_null | CHECK (a IS NOT NULL)
+ regression | public | itest7_a_not_null | CHECK (a IS NOT NULL)
+ regression | public | itest7c_a_not_null | CHECK (a IS NOT NULL)
+ regression | public | itest7d_a_not_null | CHECK (a IS NOT NULL)
+ regression | public | itest7d_a_not_null | CHECK (a IS NOT NULL)
+ regression | public | itest9_a_not_null | CHECK (a IS NOT NULL)
+ regression | public | list_parted2_b_not_null | CHECK (b IS NOT NULL)
+ regression | public | meow | ((VALUE < 11))
+ regression | public | mlparted11_a_not_null | CHECK (a IS NOT NULL)
+ regression | public | mlparted1_a_not_null | CHECK (a IS NOT NULL)
+ regression | public | mlparted1_a_not_null | CHECK (a IS NOT NULL)
+ regression | public | mlparted1_b_not_null | CHECK (b IS NOT NULL)
+ regression | public | mlparted1_b_not_null | CHECK (b IS NOT NULL)
+ regression | public | mlparted1_b_not_null | CHECK (b IS NOT NULL)
+ regression | public | mlparted2_a_not_null | CHECK (a IS NOT NULL)
+ regression | public | mlparted2_b_not_null | CHECK (b IS NOT NULL)
+ regression | public | mlparted4_a_not_null | CHECK (a IS NOT NULL)
+ regression | public | mvtest_t_amt_not_null | CHECK (amt IS NOT NULL)
+ regression | public | mvtest_t_id_not_null | CHECK (id IS NOT NULL)
+ regression | public | mvtest_t_type_not_null | CHECK (type IS NOT NULL)
+ regression | public | notnull_tbl3_a_check | ((a IS NOT NULL))
+ regression | public | nv_child_2009_d_check | (((d >= '01-01-2009'::date) AND (d <= '12-31-2009'::date)))
+ regression | public | nv_child_2010_d_check | (((d >= '01-01-2010'::date) AND (d <= '12-31-2010'::date))) NOT VALID
+ regression | public | nv_child_2011_d_check | (((d >= '01-01-2011'::date) AND (d <= '12-31-2011'::date)))
+ regression | public | nv_parent_check | (false) NO INHERIT
+ regression | public | nv_parent_d_check | (((d >= '01-01-2001'::date) AND (d <= '12-31-2099'::date))) NOT VALID
+ regression | public | nv_parent_d_check | (((d >= '01-01-2001'::date) AND (d <= '12-31-2099'::date))) NOT VALID
+ regression | public | nv_parent_d_check | (((d >= '01-01-2001'::date) AND (d <= '12-31-2099'::date))) NOT VALID
+ regression | public | nv_parent_d_check | (((d >= '01-01-2001'::date) AND (d <= '12-31-2099'::date))) NOT VALID
+ regression | public | orderedpair_check | (((VALUE)[1] < (VALUE)[2]))
+ regression | public | part1_a_check | ((a = 1))
+ regression | public | part1_a_not_null | CHECK (a IS NOT NULL)
+ regression | public | part1_b_check | (((b >= 1) AND (b <= 10)))
+ regression | public | part1_b_not_null | CHECK (b IS NOT NULL)
+ regression | public | part1_self_fk_id_not_null | CHECK (id IS NOT NULL)
+ regression | public | part33_self_fk_id_not_null | CHECK (id IS NOT NULL)
+ regression | public | part3_self_fk_id_not_null | CHECK (id IS NOT NULL)
+ regression | public | part3_self_fk_id_not_null | CHECK (id IS NOT NULL)
+ regression | public | part_rp100_a_check | (((a >= 123) AND (a < 133) AND (a IS NOT NULL)))
+ regression | public | part_rp_a_check | (((a IS NOT NULL) AND (a >= 0) AND (a < 100)))
+ regression | public | parted_self_fk_id_not_null | CHECK (id IS NOT NULL)
+ regression | public | parted_self_fk_id_not_null | CHECK (id IS NOT NULL)
+ regression | public | persons3_name_not_null | CHECK (name IS NOT NULL)
+ regression | public | plpgsql_arr_domain_check | (plpgsql_arr_domain_check(VALUE))
+ regression | public | plpgsql_domain_check | (plpgsql_domain_check(VALUE))
+ regression | public | pos_int_check | ((VALUE > 0))
+ regression | public | reservations_room_id_not_null | CHECK (room_id IS NOT NULL)
+ regression | public | rules_log_id_not_null | CHECK (id IS NOT NULL)
+ regression | public | sequence_con | (((x > 3) AND (y <> 'check failed'::text) AND (z < 8)))
+ regression | public | sorted | (((VALUE)[1] < (VALUE)[2]))
+ regression | public | spgist_box_tbl_id_not_null | CHECK (id IS NOT NULL)
+ regression | public | spgist_unlogged_tbl_id_not_null | CHECK (id IS NOT NULL)
+ regression | public | str_domain2_check | ((VALUE <> 'foo'::text))
+ regression | public | test_inh_check_a_check | (((a)::double precision > (10.2)::double precision))
+ regression | public | test_inh_check_a_check | (((a)::double precision > (10.2)::double precision))
+ regression | public | transition_table_level1_level1_no_not_null | CHECK (level1_no IS NOT NULL)
+ regression | public | transition_table_level2_level2_no_not_null | CHECK (level2_no IS NOT NULL)
+ regression | public | transition_table_level2_parent_no_not_null | CHECK (parent_no IS NOT NULL)
+ regression | public | transition_table_status_level_not_null | CHECK (level IS NOT NULL)
+ regression | public | transition_table_status_node_no_not_null | CHECK (node_no IS NOT NULL)
+ regression | public | truncate_b_id_not_null | CHECK (id IS NOT NULL)
+ regression | public | tt0_x_not_null | CHECK (x IS NOT NULL)
+ regression | public | tt0_x_not_null | CHECK (x IS NOT NULL)
+(125 rows)
+
+SELECT * FROM information_schema.constraint_column_usage
+ WHERE constraint_schema = 'public'
+ ORDER BY table_name, column_name, constraint_name;
+ table_catalog | table_schema | table_name | column_name | constraint_catalog | constraint_schema | constraint_name
+---------------+--------------+-------------------------------------+--------------+--------------------+-------------------+--------------------------------------------
+ regression | public | alter_table_under_transition_tables | id | regression | public | alter_table_under_transition_tables_pkey
+ regression | public | atnotnull1 | a | regression | public | atnotnull1_a_not_null
+ regression | public | atnotnull1 | b | regression | public | atnotnull1_b_not_null
+ regression | public | atnotnull1 | c | regression | public | atnotnull1_pkey
+ regression | public | bar1 | b | regression | public | bar1_b_not_null
+ regression | public | bar2 | b | regression | public | bar2_b_not_null
+ regression | public | boolvalues | value | regression | public | boolvalues_value_not_null
+ regression | public | brinopers | matches | regression | public | brinopers_check1
+ regression | public | brinopers | op | regression | public | brinopers_check
+ regression | public | brinopers | op | regression | public | brinopers_check1
+ regression | public | brinopers | value | regression | public | brinopers_check
+ regression | public | brinopers_bloom | matches | regression | public | brinopers_bloom_check1
+ regression | public | brinopers_bloom | op | regression | public | brinopers_bloom_check
+ regression | public | brinopers_bloom | op | regression | public | brinopers_bloom_check1
+ regression | public | brinopers_bloom | value | regression | public | brinopers_bloom_check
+ regression | public | brinopers_multi | matches | regression | public | brinopers_multi_check1
+ regression | public | brinopers_multi | op | regression | public | brinopers_multi_check
+ regression | public | brinopers_multi | op | regression | public | brinopers_multi_check1
+ regression | public | brinopers_multi | value | regression | public | brinopers_multi_check
+ regression | public | cc1 | f1 | regression | public | cc1_f1_not_null
+ regression | public | cc2 | f1 | regression | public | cc1_f1_not_null
+ regression | public | check2_tbl | x | regression | public | sequence_con
+ regression | public | check2_tbl | y | regression | public | sequence_con
+ regression | public | check2_tbl | z | regression | public | sequence_con
+ regression | public | check_tbl | x | regression | public | check_con
+ regression | public | clstr_tst | a | regression | public | clstr_tst_a_not_null
+ regression | public | clstr_tst | a | regression | public | clstr_tst_pkey
+ regression | public | clstr_tst_inh | a | regression | public | clstr_tst_a_not_null
+ regression | public | clstr_tst_s | rf_a | regression | public | clstr_tst_con
+ regression | public | clstr_tst_s | rf_a | regression | public | clstr_tst_s_pkey
+ regression | public | clstr_tst_s | rf_a | regression | public | clstr_tst_s_rf_a_not_null
+ regression | public | cnn_child | b | regression | public | cnn_child_b_not_null
+ regression | public | cnn_child2 | a | regression | public | cnn_child2_a_not_null
+ regression | public | cnn_child2 | b | regression | public | cnn_child2_b_not_null
+ regression | public | cnn_grandchild | b | regression | public | cnn_grandchild_b_not_null
+ regression | public | cnn_grandchild2 | b | regression | public | cnn_grandchild_b_not_null
+ regression | public | cnn_parent | b | regression | public | b_uq
+ regression | public | comment_test | id | regression | public | comment_test_child_fk
+ regression | public | comment_test | id | regression | public | comment_test_pk
+ regression | public | comment_test | positive_col | regression | public | comment_test_positive_col_check
+ regression | public | copy_tbl | x | regression | public | copy_con
+ regression | public | copy_tbl | y | regression | public | copy_con
+ regression | public | customer | cid | regression | public | credit_card_cid_fkey
+ regression | public | customer | cid | regression | public | credit_usage_cid_fkey
+ regression | public | customer | cid | regression | public | customer_pkey
+ regression | public | customer | name | regression | public | customer_name_not_null
+ regression | public | delete_test_table | a | regression | public | delete_test_table_pkey
+ regression | public | delete_test_table | b | regression | public | delete_test_table_pkey
+ regression | public | delete_test_table | c | regression | public | delete_test_table_pkey
+ regression | public | delete_test_table | d | regression | public | delete_test_table_pkey
+ regression | public | ec0 | ff | regression | public | ec0_pkey
+ regression | public | ec1 | ff | regression | public | ec1_pkey
+ regression | public | ec2 | xf | regression | public | ec2_pkey
+ regression | public | fk_notpartitioned_pk | a | regression | public | fk_notpartitioned_pk_pkey
+ regression | public | fk_notpartitioned_pk | a | regression | public | fk_partitioned_fk_4_2_a_b_fkey
+ regression | public | fk_notpartitioned_pk | a | regression | public | fk_partitioned_fk_4_a_b_fkey
+ regression | public | fk_notpartitioned_pk | a | regression | public | fk_partitioned_fk_4_a_b_fkey
+ regression | public | fk_notpartitioned_pk | a | regression | public | fk_partitioned_fk_4_a_b_fkey
+ regression | public | fk_notpartitioned_pk | a | regression | public | fk_partitioned_fk_5_1_a_b_fkey
+ regression | public | fk_notpartitioned_pk | a | regression | public | fk_partitioned_fk_5_a_b_fkey
+ regression | public | fk_notpartitioned_pk | a | regression | public | fk_partitioned_fk_5_a_b_fkey
+ regression | public | fk_notpartitioned_pk | a | regression | public | fk_partitioned_fk_5_a_b_fkey1
+ regression | public | fk_notpartitioned_pk | a | regression | public | fk_partitioned_fk_5_a_b_fkey1
+ regression | public | fk_notpartitioned_pk | a | regression | public | fk_partitioned_fk_a_b_fkey
+ regression | public | fk_notpartitioned_pk | a | regression | public | fk_partitioned_fk_a_b_fkey
+ regression | public | fk_notpartitioned_pk | a | regression | public | fk_partitioned_fk_a_b_fkey
+ regression | public | fk_notpartitioned_pk | a | regression | public | fk_partitioned_fk_a_b_fkey
+ regression | public | fk_notpartitioned_pk | a | regression | public | fk_partitioned_fk_a_b_fkey
+ regression | public | fk_notpartitioned_pk | a | regression | public | fk_partitioned_fk_a_b_fkey
+ regression | public | fk_notpartitioned_pk | a | regression | public | fk_partitioned_fk_a_b_fkey
+ regression | public | fk_notpartitioned_pk | a | regression | public | fk_partitioned_fk_a_b_fkey
+ regression | public | fk_notpartitioned_pk | b | regression | public | fk_notpartitioned_pk_pkey
+ regression | public | fk_notpartitioned_pk | b | regression | public | fk_partitioned_fk_4_2_a_b_fkey
+ regression | public | fk_notpartitioned_pk | b | regression | public | fk_partitioned_fk_4_a_b_fkey
+ regression | public | fk_notpartitioned_pk | b | regression | public | fk_partitioned_fk_4_a_b_fkey
+ regression | public | fk_notpartitioned_pk | b | regression | public | fk_partitioned_fk_4_a_b_fkey
+ regression | public | fk_notpartitioned_pk | b | regression | public | fk_partitioned_fk_5_1_a_b_fkey
+ regression | public | fk_notpartitioned_pk | b | regression | public | fk_partitioned_fk_5_a_b_fkey
+ regression | public | fk_notpartitioned_pk | b | regression | public | fk_partitioned_fk_5_a_b_fkey
+ regression | public | fk_notpartitioned_pk | b | regression | public | fk_partitioned_fk_5_a_b_fkey1
+ regression | public | fk_notpartitioned_pk | b | regression | public | fk_partitioned_fk_5_a_b_fkey1
+ regression | public | fk_notpartitioned_pk | b | regression | public | fk_partitioned_fk_a_b_fkey
+ regression | public | fk_notpartitioned_pk | b | regression | public | fk_partitioned_fk_a_b_fkey
+ regression | public | fk_notpartitioned_pk | b | regression | public | fk_partitioned_fk_a_b_fkey
+ regression | public | fk_notpartitioned_pk | b | regression | public | fk_partitioned_fk_a_b_fkey
+ regression | public | fk_notpartitioned_pk | b | regression | public | fk_partitioned_fk_a_b_fkey
+ regression | public | fk_notpartitioned_pk | b | regression | public | fk_partitioned_fk_a_b_fkey
+ regression | public | fk_notpartitioned_pk | b | regression | public | fk_partitioned_fk_a_b_fkey
+ regression | public | fk_notpartitioned_pk | b | regression | public | fk_partitioned_fk_a_b_fkey
+ regression | public | gtest0 | a | regression | public | gtest0_pkey
+ regression | public | gtest1 | a | regression | public | gtest1_pkey
+ regression | public | gtest10 | a | regression | public | gtest10_pkey
+ regression | public | gtest10a | a | regression | public | gtest10a_pkey
+ regression | public | gtest1_1 | a | regression | public | gtest1_1_a_not_null
+ regression | public | gtest1_y | a | regression | public | gtest1_y_a_not_null
+ regression | public | gtest2 | a | regression | public | gtest2_pkey
+ regression | public | gtest20 | a | regression | public | gtest20_pkey
+ regression | public | gtest20 | b | regression | public | gtest20_b_check
+ regression | public | gtest20a | a | regression | public | gtest20a_pkey
+ regression | public | gtest20b | a | regression | public | gtest20b_pkey
+ regression | public | gtest20b | b | regression | public | chk
+ regression | public | gtest21a | a | regression | public | gtest21a_pkey
+ regression | public | gtest21a | b | regression | public | gtest21a_b_not_null
+ regression | public | gtest21b | a | regression | public | gtest21b_pkey
+ regression | public | gtest22a | a | regression | public | gtest22a_pkey
+ regression | public | gtest22a | b | regression | public | gtest22a_b_key
+ regression | public | gtest22b | a | regression | public | gtest22b_pkey
+ regression | public | gtest22b | b | regression | public | gtest22b_pkey
+ regression | public | gtest23p | y | regression | public | gtest23p_pkey
+ regression | public | gtest23p | y | regression | public | gtest23q_b_fkey
+ regression | public | gtest23q | a | regression | public | gtest23q_pkey
+ regression | public | gtest24 | a | regression | public | gtest24_pkey
+ regression | public | gtest25 | a | regression | public | gtest25_pkey
+ regression | public | gtest26 | a | regression | public | gtest26_pkey
+ regression | public | gtest_child | f1 | regression | public | gtest_parent_f1_not_null
+ regression | public | gtest_child2 | f1 | regression | public | gtest_parent_f1_not_null
+ regression | public | gtest_child3 | f1 | regression | public | gtest_child3_f1_not_null
+ regression | public | gtest_parent | f1 | regression | public | gtest_parent_f1_not_null
+ regression | public | gtest_tableoid | a | regression | public | gtest_tableoid_pkey
+ regression | public | gtestx | a | regression | public | gtestx_a_not_null
+ regression | public | gtestxx_1 | a | regression | public | gtestxx_1_a_not_null
+ regression | public | gtestxx_3 | a | regression | public | gtestxx_3_a_not_null
+ regression | public | gtestxx_4 | a | regression | public | gtestxx_4_a_not_null
+ regression | public | idxpart_another | a | regression | public | idxpart_another_pkey
+ regression | public | idxpart_another | b | regression | public | idxpart_another_pkey
+ regression | public | idxpart_another_1 | a | regression | public | idxpart_another_1_pkey
+ regression | public | idxpart_another_1 | b | regression | public | idxpart_another_1_pkey
+ regression | public | inh_test | b | regression | public | list_parted2_b_not_null
+ regression | public | inhx | xx | regression | public | foo
+ regression | public | inhx | xx | regression | public | inhx_pkey
+ regression | public | insert_tbl | x | regression | public | insert_tbl_check
+ regression | public | insert_tbl | x | regression | public | insert_tbl_con
+ regression | public | insert_tbl | y | regression | public | insert_tbl_con
+ regression | public | insert_tbl | z | regression | public | insert_tbl_check
+ regression | public | invalid_check_con | f1 | regression | public | inh_check_constraint
+ regression | public | invalid_check_con_child | f1 | regression | public | inh_check_constraint
+ regression | public | itest1 | a | regression | public | itest1_a_not_null
+ regression | public | itest10 | a | regression | public | itest10_a_not_null
+ regression | public | itest11 | a | regression | public | itest11_a_not_null
+ regression | public | itest13 | b | regression | public | itest13_b_not_null
+ regression | public | itest13 | c | regression | public | itest13_c_not_null
+ regression | public | itest14 | id | regression | public | itest14_id_not_null
+ regression | public | itest2 | a | regression | public | itest2_a_not_null
+ regression | public | itest3 | a | regression | public | itest3_a_not_null
+ regression | public | itest3 | c | regression | public | itest3_c_not_null
+ regression | public | itest5 | a | regression | public | itest5_a_not_null
+ regression | public | itest6 | a | regression | public | itest6_a_not_null
+ regression | public | itest7 | a | regression | public | itest7_a_not_null
+ regression | public | itest7a | a | regression | public | itest7_a_not_null
+ regression | public | itest7c | a | regression | public | itest7c_a_not_null
+ regression | public | itest7d | a | regression | public | itest7d_a_not_null
+ regression | public | itest7e | a | regression | public | itest7d_a_not_null
+ regression | public | itest9 | a | regression | public | itest9_a_not_null
+ regression | public | mlparted1 | a | regression | public | mlparted1_a_not_null
+ regression | public | mlparted1 | b | regression | public | mlparted1_b_not_null
+ regression | public | mlparted11 | a | regression | public | mlparted11_a_not_null
+ regression | public | mlparted11 | b | regression | public | mlparted1_b_not_null
+ regression | public | mlparted12 | a | regression | public | mlparted1_a_not_null
+ regression | public | mlparted12 | b | regression | public | mlparted1_b_not_null
+ regression | public | mlparted2 | a | regression | public | mlparted2_a_not_null
+ regression | public | mlparted2 | b | regression | public | mlparted2_b_not_null
+ regression | public | mlparted4 | a | regression | public | mlparted4_a_not_null
+ regression | public | mvtest_t | amt | regression | public | mvtest_t_amt_not_null
+ regression | public | mvtest_t | id | regression | public | mvtest_t_id_not_null
+ regression | public | mvtest_t | id | regression | public | mvtest_t_pkey
+ regression | public | mvtest_t | type | regression | public | mvtest_t_type_not_null
+ regression | public | notnull_tbl2 | a | regression | public | notnull_tbl2_pkey
+ regression | public | notnull_tbl3 | a | regression | public | notnull_tbl3_a_check
+ regression | public | nv_child_2009 | d | regression | public | nv_child_2009_d_check
+ regression | public | nv_child_2009 | d | regression | public | nv_parent_d_check
+ regression | public | nv_child_2010 | d | regression | public | nv_child_2010_d_check
+ regression | public | nv_child_2010 | d | regression | public | nv_parent_d_check
+ regression | public | nv_child_2011 | d | regression | public | nv_child_2011_d_check
+ regression | public | nv_child_2011 | d | regression | public | nv_parent_d_check
+ regression | public | nv_parent | d | regression | public | nv_parent_d_check
+ regression | public | part1_self_fk | id | regression | public | part1_self_fk_id_not_null
+ regression | public | part1_self_fk | id | regression | public | part1_self_fk_pkey
+ regression | public | part2_self_fk | id | regression | public | part2_self_fk_pkey
+ regression | public | part2_self_fk | id | regression | public | parted_self_fk_id_not_null
+ regression | public | part32_self_fk | id | regression | public | part32_self_fk_pkey
+ regression | public | part32_self_fk | id | regression | public | part3_self_fk_id_not_null
+ regression | public | part33_self_fk | id | regression | public | part33_self_fk_id_not_null
+ regression | public | part33_self_fk | id | regression | public | part33_self_fk_pkey
+ regression | public | part3_self_fk | id | regression | public | part3_self_fk_id_not_null
+ regression | public | part3_self_fk | id | regression | public | part3_self_fk_pkey
+ regression | public | part_7_a_null | b | regression | public | check_b
+ regression | public | part_rp | a | regression | public | part_rp_a_check
+ regression | public | part_rp100 | a | regression | public | part_rp100_a_check
+ regression | public | parted_self_fk | id | regression | public | parted_self_fk_id_abc_fkey
+ regression | public | parted_self_fk | id | regression | public | parted_self_fk_id_abc_fkey
+ regression | public | parted_self_fk | id | regression | public | parted_self_fk_id_abc_fkey
+ regression | public | parted_self_fk | id | regression | public | parted_self_fk_id_abc_fkey
+ regression | public | parted_self_fk | id | regression | public | parted_self_fk_id_abc_fkey
+ regression | public | parted_self_fk | id | regression | public | parted_self_fk_id_abc_fkey
+ regression | public | parted_self_fk | id | regression | public | parted_self_fk_id_not_null
+ regression | public | parted_self_fk | id | regression | public | parted_self_fk_pkey
+ regression | public | partr_def2 | a | regression | public | part1_a_check
+ regression | public | partr_def2 | a | regression | public | part1_a_not_null
+ regression | public | partr_def2 | b | regression | public | part1_b_check
+ regression | public | partr_def2 | b | regression | public | part1_b_not_null
+ regression | public | persons2 | id | regression | public | persons2_pkey
+ regression | public | persons2 | name | regression | public | persons2_name_key
+ regression | public | persons3 | id | regression | public | persons3_pkey
+ regression | public | persons3 | name | regression | public | persons3_name_not_null
+ regression | public | pp1 | f1 | regression | public | pp1_pkey
+ regression | public | reservations | room_id | regression | public | reservations_room_id_not_null
+ regression | public | rule_and_refint_t1 | id1a | regression | public | rule_and_refint_t1_pkey
+ regression | public | rule_and_refint_t1 | id1a | regression | public | rule_and_refint_t3_id3a_id3b_fkey
+ regression | public | rule_and_refint_t1 | id1b | regression | public | rule_and_refint_t1_pkey
+ regression | public | rule_and_refint_t1 | id1b | regression | public | rule_and_refint_t3_id3a_id3b_fkey
+ regression | public | rule_and_refint_t2 | id2a | regression | public | rule_and_refint_t2_pkey
+ regression | public | rule_and_refint_t2 | id2a | regression | public | rule_and_refint_t3_id3a_id3c_fkey
+ regression | public | rule_and_refint_t2 | id2c | regression | public | rule_and_refint_t2_pkey
+ regression | public | rule_and_refint_t2 | id2c | regression | public | rule_and_refint_t3_id3a_id3c_fkey
+ regression | public | rule_and_refint_t3 | id3a | regression | public | rule_and_refint_t3_pkey
+ regression | public | rule_and_refint_t3 | id3b | regression | public | rule_and_refint_t3_pkey
+ regression | public | rule_and_refint_t3 | id3c | regression | public | rule_and_refint_t3_pkey
+ regression | public | rules_log | id | regression | public | rules_log_id_not_null
+ regression | public | skip_wal_skip_rewrite_index | c | regression | public | skip_wal_skip_rewrite_index_pkey
+ regression | public | spgist_box_tbl | id | regression | public | spgist_box_tbl_id_not_null
+ regression | public | spgist_unlogged_tbl | id | regression | public | spgist_unlogged_tbl_id_not_null
+ regression | public | tbl_include_box | c1 | regression | public | tbl_include_box_idx_unique
+ regression | public | tbl_include_box | c2 | regression | public | tbl_include_box_idx_unique
+ regression | public | tbl_include_pk | c1 | regression | public | tbl_include_pk_pkey
+ regression | public | tbl_include_pk | c2 | regression | public | tbl_include_pk_pkey
+ regression | public | tbl_include_unique1 | c1 | regression | public | tbl_include_unique1_c1_c2_c3_c4_key
+ regression | public | tbl_include_unique1 | c1 | regression | public | tbl_include_unique1_idx_unique
+ regression | public | tbl_include_unique1 | c2 | regression | public | tbl_include_unique1_c1_c2_c3_c4_key
+ regression | public | tbl_include_unique1 | c2 | regression | public | tbl_include_unique1_idx_unique
+ regression | public | test_inh_check | a | regression | public | test_inh_check_a_check
+ regression | public | test_inh_check | b | regression | public | bmerged
+ regression | public | test_inh_check | b | regression | public | bnoinherit
+ regression | public | test_inh_check_child | a | regression | public | test_inh_check_a_check
+ regression | public | test_inh_check_child | b | regression | public | blocal
+ regression | public | test_inh_check_child | b | regression | public | bmerged
+ regression | public | transition_table_base | id | regression | public | transition_table_base_pkey
+ regression | public | transition_table_level1 | level1_no | regression | public | transition_table_level1_level1_no_not_null
+ regression | public | transition_table_level1 | level1_no | regression | public | transition_table_level1_pkey
+ regression | public | transition_table_level2 | level2_no | regression | public | transition_table_level2_level2_no_not_null
+ regression | public | transition_table_level2 | level2_no | regression | public | transition_table_level2_pkey
+ regression | public | transition_table_level2 | parent_no | regression | public | transition_table_level2_parent_no_not_null
+ regression | public | transition_table_status | level | regression | public | transition_table_status_level_not_null
+ regression | public | transition_table_status | level | regression | public | transition_table_status_pkey
+ regression | public | transition_table_status | node_no | regression | public | transition_table_status_node_no_not_null
+ regression | public | transition_table_status | node_no | regression | public | transition_table_status_pkey
+ regression | public | trigger_parted | a | regression | public | trigger_parted_pkey
+ regression | public | trigger_parted_p1 | a | regression | public | trigger_parted_p1_pkey
+ regression | public | trigger_parted_p1_1 | a | regression | public | trigger_parted_p1_1_pkey
+ regression | public | trigger_parted_p2 | a | regression | public | trigger_parted_p2_pkey
+ regression | public | trigger_parted_p2_2 | a | regression | public | trigger_parted_p2_2_pkey
+ regression | public | truncate_b | id | regression | public | truncate_b_id_not_null
+ regression | public | tt0 | x | regression | public | tt0_x_not_null
+ regression | public | tt6 | x | regression | public | tt0_x_not_null
+ regression | public | utf8_verification_inputs | description | regression | public | utf8_verification_inputs_pkey
+ regression | public | view_base_table | key | regression | public | view_base_table_pkey
+(255 rows)
+
+SELECT * FROM information_schema.table_constraints
+ WHERE constraint_schema = 'public'
+ ORDER BY table_name, constraint_name;
+ constraint_catalog | constraint_schema | constraint_name | table_catalog | table_schema | table_name | constraint_type | is_deferrable | initially_deferred | enforced | nulls_distinct
+--------------------+-------------------+--------------------------------------------+---------------+--------------+-------------------------------------+-----------------+---------------+--------------------+----------+----------------
+ regression | public | alter_table_under_transition_tables_pkey | regression | public | alter_table_under_transition_tables | PRIMARY KEY | NO | NO | YES |
+ regression | public | atnotnull1_a_not_null | regression | public | atnotnull1 | CHECK | NO | NO | YES |
+ regression | public | atnotnull1_b_not_null | regression | public | atnotnull1 | CHECK | NO | NO | YES |
+ regression | public | atnotnull1_pkey | regression | public | atnotnull1 | PRIMARY KEY | NO | NO | YES |
+ regression | public | bar1_b_not_null | regression | public | bar1 | CHECK | NO | NO | YES |
+ regression | public | bar2_b_not_null | regression | public | bar2 | CHECK | NO | NO | YES |
+ regression | public | boolvalues_value_not_null | regression | public | boolvalues | CHECK | NO | NO | YES |
+ regression | public | brinopers_check | regression | public | brinopers | CHECK | NO | NO | YES |
+ regression | public | brinopers_check1 | regression | public | brinopers | CHECK | NO | NO | YES |
+ regression | public | brinopers_bloom_check | regression | public | brinopers_bloom | CHECK | NO | NO | YES |
+ regression | public | brinopers_bloom_check1 | regression | public | brinopers_bloom | CHECK | NO | NO | YES |
+ regression | public | brinopers_multi_check | regression | public | brinopers_multi | CHECK | NO | NO | YES |
+ regression | public | brinopers_multi_check1 | regression | public | brinopers_multi | CHECK | NO | NO | YES |
+ regression | public | cc1_f1_not_null | regression | public | cc1 | CHECK | NO | NO | YES |
+ regression | public | cc1_f1_not_null | regression | public | cc2 | CHECK | NO | NO | YES |
+ regression | public | sequence_con | regression | public | check2_tbl | CHECK | NO | NO | YES |
+ regression | public | check_con_tbl_check | regression | public | check_con_tbl | CHECK | NO | NO | YES |
+ regression | public | check_con | regression | public | check_tbl | CHECK | NO | NO | YES |
+ regression | public | clstr_tst_a_not_null | regression | public | clstr_tst | CHECK | NO | NO | YES |
+ regression | public | clstr_tst_con | regression | public | clstr_tst | FOREIGN KEY | NO | NO | YES |
+ regression | public | clstr_tst_pkey | regression | public | clstr_tst | PRIMARY KEY | NO | NO | YES |
+ regression | public | clstr_tst_a_not_null | regression | public | clstr_tst_inh | CHECK | NO | NO | YES |
+ regression | public | clstr_tst_s_pkey | regression | public | clstr_tst_s | PRIMARY KEY | NO | NO | YES |
+ regression | public | clstr_tst_s_rf_a_not_null | regression | public | clstr_tst_s | CHECK | NO | NO | YES |
+ regression | public | cnn_child_b_not_null | regression | public | cnn_child | CHECK | NO | NO | YES |
+ regression | public | cnn_child2_a_not_null | regression | public | cnn_child2 | CHECK | NO | NO | YES |
+ regression | public | cnn_child2_b_not_null | regression | public | cnn_child2 | CHECK | NO | NO | YES |
+ regression | public | cnn_grandchild_b_not_null | regression | public | cnn_grandchild | CHECK | NO | NO | YES |
+ regression | public | cnn_grandchild_b_not_null | regression | public | cnn_grandchild2 | CHECK | NO | NO | YES |
+ regression | public | b_uq | regression | public | cnn_parent | PRIMARY KEY | NO | NO | YES |
+ regression | public | comment_test_pk | regression | public | comment_test | PRIMARY KEY | NO | NO | YES |
+ regression | public | comment_test_positive_col_check | regression | public | comment_test | CHECK | NO | NO | YES |
+ regression | public | comment_test_child_fk | regression | public | comment_test_child | FOREIGN KEY | NO | NO | YES |
+ regression | public | copy_con | regression | public | copy_tbl | CHECK | NO | NO | YES |
+ regression | public | credit_card_cid_fkey | regression | public | credit_card | FOREIGN KEY | NO | NO | YES |
+ regression | public | credit_usage_cid_fkey | regression | public | credit_usage | FOREIGN KEY | NO | NO | YES |
+ regression | public | customer_name_not_null | regression | public | customer | CHECK | NO | NO | YES |
+ regression | public | customer_pkey | regression | public | customer | PRIMARY KEY | NO | NO | YES |
+ regression | public | delete_test_table_pkey | regression | public | delete_test_table | PRIMARY KEY | NO | NO | YES |
+ regression | public | ec0_pkey | regression | public | ec0 | PRIMARY KEY | NO | NO | YES |
+ regression | public | ec1_pkey | regression | public | ec1 | PRIMARY KEY | NO | NO | YES |
+ regression | public | ec2_pkey | regression | public | ec2 | PRIMARY KEY | NO | NO | YES |
+ regression | public | fk_notpartitioned_pk_pkey | regression | public | fk_notpartitioned_pk | PRIMARY KEY | NO | NO | YES |
+ regression | public | fk_partitioned_fk_a_b_fkey | regression | public | fk_partitioned_fk | FOREIGN KEY | NO | NO | YES |
+ regression | public | fk_partitioned_fk_a_b_fkey | regression | public | fk_partitioned_fk_1 | FOREIGN KEY | NO | NO | YES |
+ regression | public | fk_partitioned_fk_a_b_fkey | regression | public | fk_partitioned_fk_2 | FOREIGN KEY | NO | NO | YES |
+ regression | public | fk_partitioned_fk_a_b_fkey | regression | public | fk_partitioned_fk_2_1 | FOREIGN KEY | NO | NO | YES |
+ regression | public | fk_partitioned_fk_a_b_fkey | regression | public | fk_partitioned_fk_2_2 | FOREIGN KEY | NO | NO | YES |
+ regression | public | fk_partitioned_fk_a_b_fkey | regression | public | fk_partitioned_fk_3 | FOREIGN KEY | NO | NO | YES |
+ regression | public | fk_partitioned_fk_4_a_b_fkey | regression | public | fk_partitioned_fk_4 | FOREIGN KEY | NO | NO | YES |
+ regression | public | fk_partitioned_fk_4_a_b_fkey | regression | public | fk_partitioned_fk_4_1 | FOREIGN KEY | NO | NO | YES |
+ regression | public | fk_partitioned_fk_4_2_a_b_fkey | regression | public | fk_partitioned_fk_4_2 | FOREIGN KEY | NO | NO | YES |
+ regression | public | fk_partitioned_fk_4_a_b_fkey | regression | public | fk_partitioned_fk_4_2 | FOREIGN KEY | NO | NO | YES |
+ regression | public | fk_partitioned_fk_5_a_b_fkey | regression | public | fk_partitioned_fk_5 | FOREIGN KEY | YES | NO | YES |
+ regression | public | fk_partitioned_fk_5_a_b_fkey1 | regression | public | fk_partitioned_fk_5 | FOREIGN KEY | NO | NO | YES |
+ regression | public | fk_partitioned_fk_a_b_fkey | regression | public | fk_partitioned_fk_5 | FOREIGN KEY | NO | NO | YES |
+ regression | public | fk_partitioned_fk_5_1_a_b_fkey | regression | public | fk_partitioned_fk_5_1 | FOREIGN KEY | NO | NO | YES |
+ regression | public | fk_partitioned_fk_5_a_b_fkey | regression | public | fk_partitioned_fk_5_1 | FOREIGN KEY | YES | NO | YES |
+ regression | public | fk_partitioned_fk_5_a_b_fkey1 | regression | public | fk_partitioned_fk_5_1 | FOREIGN KEY | NO | NO | YES |
+ regression | public | fk_partitioned_fk_a_b_fkey | regression | public | fk_partitioned_fk_5_1 | FOREIGN KEY | NO | NO | YES |
+ regression | public | gtest0_pkey | regression | public | gtest0 | PRIMARY KEY | NO | NO | YES |
+ regression | public | gtest1_pkey | regression | public | gtest1 | PRIMARY KEY | NO | NO | YES |
+ regression | public | gtest10_pkey | regression | public | gtest10 | PRIMARY KEY | NO | NO | YES |
+ regression | public | gtest10a_pkey | regression | public | gtest10a | PRIMARY KEY | NO | NO | YES |
+ regression | public | gtest1_1_a_not_null | regression | public | gtest1_1 | CHECK | NO | NO | YES |
+ regression | public | gtest1_y_a_not_null | regression | public | gtest1_y | CHECK | NO | NO | YES |
+ regression | public | gtest2_pkey | regression | public | gtest2 | PRIMARY KEY | NO | NO | YES |
+ regression | public | gtest20_b_check | regression | public | gtest20 | CHECK | NO | NO | YES |
+ regression | public | gtest20_pkey | regression | public | gtest20 | PRIMARY KEY | NO | NO | YES |
+ regression | public | gtest20a_pkey | regression | public | gtest20a | PRIMARY KEY | NO | NO | YES |
+ regression | public | chk | regression | public | gtest20b | CHECK | NO | NO | YES |
+ regression | public | gtest20b_pkey | regression | public | gtest20b | PRIMARY KEY | NO | NO | YES |
+ regression | public | gtest21a_b_not_null | regression | public | gtest21a | CHECK | NO | NO | YES |
+ regression | public | gtest21a_pkey | regression | public | gtest21a | PRIMARY KEY | NO | NO | YES |
+ regression | public | gtest21b_pkey | regression | public | gtest21b | PRIMARY KEY | NO | NO | YES |
+ regression | public | gtest22a_b_key | regression | public | gtest22a | UNIQUE | NO | NO | YES | YES
+ regression | public | gtest22a_pkey | regression | public | gtest22a | PRIMARY KEY | NO | NO | YES |
+ regression | public | gtest22b_pkey | regression | public | gtest22b | PRIMARY KEY | NO | NO | YES |
+ regression | public | gtest23p_pkey | regression | public | gtest23p | PRIMARY KEY | NO | NO | YES |
+ regression | public | gtest23q_b_fkey | regression | public | gtest23q | FOREIGN KEY | NO | NO | YES |
+ regression | public | gtest23q_pkey | regression | public | gtest23q | PRIMARY KEY | NO | NO | YES |
+ regression | public | gtest24_pkey | regression | public | gtest24 | PRIMARY KEY | NO | NO | YES |
+ regression | public | gtest25_pkey | regression | public | gtest25 | PRIMARY KEY | NO | NO | YES |
+ regression | public | gtest26_pkey | regression | public | gtest26 | PRIMARY KEY | NO | NO | YES |
+ regression | public | gtest_parent_f1_not_null | regression | public | gtest_child | CHECK | NO | NO | YES |
+ regression | public | gtest_parent_f1_not_null | regression | public | gtest_child2 | CHECK | NO | NO | YES |
+ regression | public | gtest_child3_f1_not_null | regression | public | gtest_child3 | CHECK | NO | NO | YES |
+ regression | public | gtest_parent_f1_not_null | regression | public | gtest_parent | CHECK | NO | NO | YES |
+ regression | public | gtest_tableoid_pkey | regression | public | gtest_tableoid | PRIMARY KEY | NO | NO | YES |
+ regression | public | gtestx_a_not_null | regression | public | gtestx | CHECK | NO | NO | YES |
+ regression | public | gtestxx_1_a_not_null | regression | public | gtestxx_1 | CHECK | NO | NO | YES |
+ regression | public | gtestxx_3_a_not_null | regression | public | gtestxx_3 | CHECK | NO | NO | YES |
+ regression | public | gtestxx_4_a_not_null | regression | public | gtestxx_4 | CHECK | NO | NO | YES |
+ regression | public | idxpart_another_pkey | regression | public | idxpart_another | PRIMARY KEY | NO | NO | YES |
+ regression | public | idxpart_another_1_pkey | regression | public | idxpart_another_1 | PRIMARY KEY | NO | NO | YES |
+ regression | public | list_parted2_b_not_null | regression | public | inh_test | CHECK | NO | NO | YES |
+ regression | public | foo | regression | public | inhx | CHECK | NO | NO | YES |
+ regression | public | inhx_pkey | regression | public | inhx | PRIMARY KEY | NO | NO | YES |
+ regression | public | insert_tbl_check | regression | public | insert_tbl | CHECK | NO | NO | YES |
+ regression | public | insert_tbl_con | regression | public | insert_tbl | CHECK | NO | NO | YES |
+ regression | public | inh_check_constraint | regression | public | invalid_check_con | CHECK | NO | NO | YES |
+ regression | public | inh_check_constraint | regression | public | invalid_check_con_child | CHECK | NO | NO | YES |
+ regression | public | itest1_a_not_null | regression | public | itest1 | CHECK | NO | NO | YES |
+ regression | public | itest10_a_not_null | regression | public | itest10 | CHECK | NO | NO | YES |
+ regression | public | itest11_a_not_null | regression | public | itest11 | CHECK | NO | NO | YES |
+ regression | public | itest13_b_not_null | regression | public | itest13 | CHECK | NO | NO | YES |
+ regression | public | itest13_c_not_null | regression | public | itest13 | CHECK | NO | NO | YES |
+ regression | public | itest14_id_not_null | regression | public | itest14 | CHECK | NO | NO | YES |
+ regression | public | itest2_a_not_null | regression | public | itest2 | CHECK | NO | NO | YES |
+ regression | public | itest3_a_not_null | regression | public | itest3 | CHECK | NO | NO | YES |
+ regression | public | itest3_c_not_null | regression | public | itest3 | CHECK | NO | NO | YES |
+ regression | public | itest5_a_not_null | regression | public | itest5 | CHECK | NO | NO | YES |
+ regression | public | itest6_a_not_null | regression | public | itest6 | CHECK | NO | NO | YES |
+ regression | public | itest7_a_not_null | regression | public | itest7 | CHECK | NO | NO | YES |
+ regression | public | itest7_a_not_null | regression | public | itest7a | CHECK | NO | NO | YES |
+ regression | public | itest7c_a_not_null | regression | public | itest7c | CHECK | NO | NO | YES |
+ regression | public | itest7d_a_not_null | regression | public | itest7d | CHECK | NO | NO | YES |
+ regression | public | itest7d_a_not_null | regression | public | itest7e | CHECK | NO | NO | YES |
+ regression | public | itest9_a_not_null | regression | public | itest9 | CHECK | NO | NO | YES |
+ regression | public | mlparted1_a_not_null | regression | public | mlparted1 | CHECK | NO | NO | YES |
+ regression | public | mlparted1_b_not_null | regression | public | mlparted1 | CHECK | NO | NO | YES |
+ regression | public | mlparted11_a_not_null | regression | public | mlparted11 | CHECK | NO | NO | YES |
+ regression | public | mlparted1_b_not_null | regression | public | mlparted11 | CHECK | NO | NO | YES |
+ regression | public | mlparted1_a_not_null | regression | public | mlparted12 | CHECK | NO | NO | YES |
+ regression | public | mlparted1_b_not_null | regression | public | mlparted12 | CHECK | NO | NO | YES |
+ regression | public | mlparted2_a_not_null | regression | public | mlparted2 | CHECK | NO | NO | YES |
+ regression | public | mlparted2_b_not_null | regression | public | mlparted2 | CHECK | NO | NO | YES |
+ regression | public | mlparted4_a_not_null | regression | public | mlparted4 | CHECK | NO | NO | YES |
+ regression | public | mvtest_t_amt_not_null | regression | public | mvtest_t | CHECK | NO | NO | YES |
+ regression | public | mvtest_t_id_not_null | regression | public | mvtest_t | CHECK | NO | NO | YES |
+ regression | public | mvtest_t_pkey | regression | public | mvtest_t | PRIMARY KEY | NO | NO | YES |
+ regression | public | mvtest_t_type_not_null | regression | public | mvtest_t | CHECK | NO | NO | YES |
+ regression | public | notnull_tbl2_pkey | regression | public | notnull_tbl2 | PRIMARY KEY | NO | NO | YES |
+ regression | public | notnull_tbl3_a_check | regression | public | notnull_tbl3 | CHECK | NO | NO | YES |
+ regression | public | nv_child_2009_d_check | regression | public | nv_child_2009 | CHECK | NO | NO | YES |
+ regression | public | nv_parent_d_check | regression | public | nv_child_2009 | CHECK | NO | NO | YES |
+ regression | public | nv_child_2010_d_check | regression | public | nv_child_2010 | CHECK | NO | NO | YES |
+ regression | public | nv_parent_d_check | regression | public | nv_child_2010 | CHECK | NO | NO | YES |
+ regression | public | nv_child_2011_d_check | regression | public | nv_child_2011 | CHECK | NO | NO | YES |
+ regression | public | nv_parent_d_check | regression | public | nv_child_2011 | CHECK | NO | NO | YES |
+ regression | public | nv_parent_check | regression | public | nv_parent | CHECK | NO | NO | YES |
+ regression | public | nv_parent_d_check | regression | public | nv_parent | CHECK | NO | NO | YES |
+ regression | public | part1_self_fk_id_not_null | regression | public | part1_self_fk | CHECK | NO | NO | YES |
+ regression | public | part1_self_fk_pkey | regression | public | part1_self_fk | PRIMARY KEY | NO | NO | YES |
+ regression | public | parted_self_fk_id_abc_fkey | regression | public | part1_self_fk | FOREIGN KEY | NO | NO | YES |
+ regression | public | part2_self_fk_pkey | regression | public | part2_self_fk | PRIMARY KEY | NO | NO | YES |
+ regression | public | parted_self_fk_id_abc_fkey | regression | public | part2_self_fk | FOREIGN KEY | NO | NO | YES |
+ regression | public | parted_self_fk_id_not_null | regression | public | part2_self_fk | CHECK | NO | NO | YES |
+ regression | public | part32_self_fk_pkey | regression | public | part32_self_fk | PRIMARY KEY | NO | NO | YES |
+ regression | public | part3_self_fk_id_not_null | regression | public | part32_self_fk | CHECK | NO | NO | YES |
+ regression | public | parted_self_fk_id_abc_fkey | regression | public | part32_self_fk | FOREIGN KEY | NO | NO | YES |
+ regression | public | part33_self_fk_id_not_null | regression | public | part33_self_fk | CHECK | NO | NO | YES |
+ regression | public | part33_self_fk_pkey | regression | public | part33_self_fk | PRIMARY KEY | NO | NO | YES |
+ regression | public | parted_self_fk_id_abc_fkey | regression | public | part33_self_fk | FOREIGN KEY | NO | NO | YES |
+ regression | public | part3_self_fk_id_not_null | regression | public | part3_self_fk | CHECK | NO | NO | YES |
+ regression | public | part3_self_fk_pkey | regression | public | part3_self_fk | PRIMARY KEY | NO | NO | YES |
+ regression | public | parted_self_fk_id_abc_fkey | regression | public | part3_self_fk | FOREIGN KEY | NO | NO | YES |
+ regression | public | check_b | regression | public | part_7_a_null | CHECK | NO | NO | YES |
+ regression | public | part_rp_a_check | regression | public | part_rp | CHECK | NO | NO | YES |
+ regression | public | part_rp100_a_check | regression | public | part_rp100 | CHECK | NO | NO | YES |
+ regression | public | parted_self_fk_id_abc_fkey | regression | public | parted_self_fk | FOREIGN KEY | NO | NO | YES |
+ regression | public | parted_self_fk_id_not_null | regression | public | parted_self_fk | CHECK | NO | NO | YES |
+ regression | public | parted_self_fk_pkey | regression | public | parted_self_fk | PRIMARY KEY | NO | NO | YES |
+ regression | public | part1_a_check | regression | public | partr_def2 | CHECK | NO | NO | YES |
+ regression | public | part1_a_not_null | regression | public | partr_def2 | CHECK | NO | NO | YES |
+ regression | public | part1_b_check | regression | public | partr_def2 | CHECK | NO | NO | YES |
+ regression | public | part1_b_not_null | regression | public | partr_def2 | CHECK | NO | NO | YES |
+ regression | public | persons2_name_key | regression | public | persons2 | UNIQUE | NO | NO | YES | YES
+ regression | public | persons2_pkey | regression | public | persons2 | PRIMARY KEY | NO | NO | YES |
+ regression | public | persons3_name_not_null | regression | public | persons3 | CHECK | NO | NO | YES |
+ regression | public | persons3_pkey | regression | public | persons3 | PRIMARY KEY | NO | NO | YES |
+ regression | public | pp1_pkey | regression | public | pp1 | PRIMARY KEY | NO | NO | YES |
+ regression | public | reservations_room_id_not_null | regression | public | reservations | CHECK | NO | NO | YES |
+ regression | public | rule_and_refint_t1_pkey | regression | public | rule_and_refint_t1 | PRIMARY KEY | NO | NO | YES |
+ regression | public | rule_and_refint_t2_pkey | regression | public | rule_and_refint_t2 | PRIMARY KEY | NO | NO | YES |
+ regression | public | rule_and_refint_t3_id3a_id3b_fkey | regression | public | rule_and_refint_t3 | FOREIGN KEY | NO | NO | YES |
+ regression | public | rule_and_refint_t3_id3a_id3c_fkey | regression | public | rule_and_refint_t3 | FOREIGN KEY | NO | NO | YES |
+ regression | public | rule_and_refint_t3_pkey | regression | public | rule_and_refint_t3 | PRIMARY KEY | NO | NO | YES |
+ regression | public | rules_log_id_not_null | regression | public | rules_log | CHECK | NO | NO | YES |
+ regression | public | skip_wal_skip_rewrite_index_pkey | regression | public | skip_wal_skip_rewrite_index | PRIMARY KEY | NO | NO | YES |
+ regression | public | spgist_box_tbl_id_not_null | regression | public | spgist_box_tbl | CHECK | NO | NO | YES |
+ regression | public | spgist_unlogged_tbl_id_not_null | regression | public | spgist_unlogged_tbl | CHECK | NO | NO | YES |
+ regression | public | tbl_include_box_idx_unique | regression | public | tbl_include_box | PRIMARY KEY | NO | NO | YES |
+ regression | public | tbl_include_pk_pkey | regression | public | tbl_include_pk | PRIMARY KEY | NO | NO | YES |
+ regression | public | tbl_include_unique1_c1_c2_c3_c4_key | regression | public | tbl_include_unique1 | UNIQUE | NO | NO | YES | YES
+ regression | public | tbl_include_unique1_idx_unique | regression | public | tbl_include_unique1 | UNIQUE | NO | NO | YES | YES
+ regression | public | bmerged | regression | public | test_inh_check | CHECK | NO | NO | YES |
+ regression | public | bnoinherit | regression | public | test_inh_check | CHECK | NO | NO | YES |
+ regression | public | test_inh_check_a_check | regression | public | test_inh_check | CHECK | NO | NO | YES |
+ regression | public | blocal | regression | public | test_inh_check_child | CHECK | NO | NO | YES |
+ regression | public | bmerged | regression | public | test_inh_check_child | CHECK | NO | NO | YES |
+ regression | public | test_inh_check_a_check | regression | public | test_inh_check_child | CHECK | NO | NO | YES |
+ regression | public | transition_table_base_pkey | regression | public | transition_table_base | PRIMARY KEY | NO | NO | YES |
+ regression | public | transition_table_level1_level1_no_not_null | regression | public | transition_table_level1 | CHECK | NO | NO | YES |
+ regression | public | transition_table_level1_pkey | regression | public | transition_table_level1 | PRIMARY KEY | NO | NO | YES |
+ regression | public | transition_table_level2_level2_no_not_null | regression | public | transition_table_level2 | CHECK | NO | NO | YES |
+ regression | public | transition_table_level2_parent_no_not_null | regression | public | transition_table_level2 | CHECK | NO | NO | YES |
+ regression | public | transition_table_level2_pkey | regression | public | transition_table_level2 | PRIMARY KEY | NO | NO | YES |
+ regression | public | transition_table_status_level_not_null | regression | public | transition_table_status | CHECK | NO | NO | YES |
+ regression | public | transition_table_status_node_no_not_null | regression | public | transition_table_status | CHECK | NO | NO | YES |
+ regression | public | transition_table_status_pkey | regression | public | transition_table_status | PRIMARY KEY | NO | NO | YES |
+ regression | public | trigger_parted_pkey | regression | public | trigger_parted | PRIMARY KEY | NO | NO | YES |
+ regression | public | trigger_parted_p1_pkey | regression | public | trigger_parted_p1 | PRIMARY KEY | NO | NO | YES |
+ regression | public | trigger_parted_p1_1_pkey | regression | public | trigger_parted_p1_1 | PRIMARY KEY | NO | NO | YES |
+ regression | public | trigger_parted_p2_pkey | regression | public | trigger_parted_p2 | PRIMARY KEY | NO | NO | YES |
+ regression | public | trigger_parted_p2_2_pkey | regression | public | trigger_parted_p2_2 | PRIMARY KEY | NO | NO | YES |
+ regression | public | truncate_b_id_not_null | regression | public | truncate_b | CHECK | NO | NO | YES |
+ regression | public | tt0_x_not_null | regression | public | tt0 | CHECK | NO | NO | YES |
+ regression | public | tt0_x_not_null | regression | public | tt6 | CHECK | NO | NO | YES |
+ regression | public | utf8_verification_inputs_pkey | regression | public | utf8_verification_inputs | PRIMARY KEY | NO | NO | YES |
+ regression | public | view_base_table_pkey | regression | public | view_base_table | PRIMARY KEY | NO | NO | YES |
+(211 rows)
+
+--
+-- Domain constraints
+--
+SELECT * FROM information_schema.column_domain_usage
+ WHERE domain_schema = 'public' AND table_schema = 'public'
+ ORDER BY domain_name;
+ domain_catalog | domain_schema | domain_name | table_catalog | table_schema | table_name | column_name
+----------------+---------------+--------------+---------------+--------------+-------------------+-------------
+ regression | public | con | regression | public | domcontest | col1
+ regression | public | dom | regression | public | domview | col1
+ regression | public | gtestdomain1 | regression | public | gtest24 | b
+ regression | public | spgist_text | regression | public | spgist_domain_tbl | f1
+ regression | public | things | regression | public | thethings | stuff
+(5 rows)
+
+SELECT * FROM information_schema.domain_constraints
+ WHERE domain_schema = 'public'
+ ORDER BY constraint_name;
+ constraint_catalog | constraint_schema | constraint_name | domain_catalog | domain_schema | domain_name | is_deferrable | initially_deferred
+--------------------+-------------------+--------------------------+----------------+---------------+--------------------+---------------+--------------------
+ regression | public | con_check | regression | public | con | NO | NO
+ regression | public | gtestdomain1_check | regression | public | gtestdomain1 | NO | NO
+ regression | public | meow | regression | public | things | NO | NO
+ regression | public | orderedpair_check | regression | public | orderedpair | NO | NO
+ regression | public | plpgsql_arr_domain_check | regression | public | plpgsql_arr_domain | NO | NO
+ regression | public | plpgsql_domain_check | regression | public | plpgsql_domain | NO | NO
+ regression | public | pos_int_check | regression | public | pos_int | NO | NO
+ regression | public | sorted | regression | public | orderedarray | NO | NO
+ regression | public | str_domain2_check | regression | public | str_domain2 | NO | NO
+(9 rows)
+
+SELECT * FROM information_schema.domains
+ WHERE domain_schema = 'public'
+ ORDER BY domain_name;
+ domain_catalog | domain_schema | domain_name | data_type | character_maximum_length | character_octet_length | character_set_catalog | character_set_schema | character_set_name | collation_catalog | collation_schema | collation_name | numeric_precision | numeric_precision_radix | numeric_scale | datetime_precision | interval_type | interval_precision | domain_default | udt_catalog | udt_schema | udt_name | scope_catalog | scope_schema | scope_name | maximum_cardinality | dtd_identifier
+----------------+---------------+--------------------+-------------------+--------------------------+------------------------+-----------------------+----------------------+--------------------+-------------------+------------------+----------------+-------------------+-------------------------+---------------+--------------------+---------------+--------------------+----------------+-------------+------------+----------+---------------+--------------+------------+---------------------+----------------
+ regression | public | con | integer | | | | | | | | | 32 | 2 | 0 | | | | | regression | pg_catalog | int4 | | | | | 1
+ regression | public | dom | integer | | | | | | | | | 32 | 2 | 0 | | | | | regression | pg_catalog | int4 | | | | | 1
+ regression | public | gtestdomain1 | integer | | | | | | | | | 32 | 2 | 0 | | | | | regression | pg_catalog | int4 | | | | | 1
+ regression | public | mynums | numeric[] | | | | | | | | | | | | | | | | regression | pg_catalog | _numeric | | | | | 1
+ regression | public | mynums2 | USER-DEFINED | | | | | | | | | | | | | | | | regression | public | mynums | | | | | 1
+ regression | public | orderedarray | integer[] | | | | | | | | | | | | | | | | regression | pg_catalog | _int4 | | | | | 1
+ regression | public | orderedpair | integer[] | | | | | | | | | | | | | | | | regression | pg_catalog | _int4 | | | | | 1
+ regression | public | plpgsql_arr_domain | integer[] | | | | | | | | | | | | | | | | regression | pg_catalog | _int4 | | | | | 1
+ regression | public | plpgsql_domain | integer | | | | | | | | | 32 | 2 | 0 | | | | | regression | pg_catalog | int4 | | | | | 1
+ regression | public | pos_int | integer | | | | | | | | | 32 | 2 | 0 | | | | | regression | pg_catalog | int4 | | | | | 1
+ regression | public | spgist_text | character varying | | 1073741824 | | | | | | | | | | | | | | regression | pg_catalog | varchar | | | | | 1
+ regression | public | str_domain | text | | 1073741824 | | | | | | | | | | | | | | regression | pg_catalog | text | | | | | 1
+ regression | public | str_domain2 | text | | 1073741824 | | | | | | | | | | | | | 'foo'::text | regression | pg_catalog | text | | | | | 1
+ regression | public | testboolxmldomain | boolean | | | | | | | | | | | | | | | | regression | pg_catalog | bool | | | | | 1
+ regression | public | testdatexmldomain | date | | | | | | | | | | | | 0 | | | | regression | pg_catalog | date | | | | | 1
+ regression | public | testxmldomain | character varying | | 1073741824 | | | | | | | | | | | | | | regression | pg_catalog | varchar | | | | | 1
+ regression | public | things | integer | | | | | | | | | 32 | 2 | 0 | | | | | regression | pg_catalog | int4 | | | | | 1
+(17 rows)
+
+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;
+ constraint_catalog | constraint_schema | constraint_name | check_clause
+--------------------+-------------------+--------------------------+-----------------------------------
+ regression | public | con_check | ((VALUE > 0))
+ regression | public | gtestdomain1_check | ((VALUE < 10))
+ regression | public | meow | ((VALUE < 11))
+ regression | public | orderedpair_check | (((VALUE)[1] < (VALUE)[2]))
+ regression | public | plpgsql_arr_domain_check | (plpgsql_arr_domain_check(VALUE))
+ regression | public | plpgsql_domain_check | (plpgsql_domain_check(VALUE))
+ regression | public | pos_int_check | ((VALUE > 0))
+ regression | public | sorted | (((VALUE)[1] < (VALUE)[2]))
+ regression | public | str_domain2_check | ((VALUE <> 'foo'::text))
+(9 rows)
+
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 4df9d8503b..22e9896f18 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -130,5 +130,6 @@ test: event_trigger oidjoins
test: fast_default
# run tablespace test at the end because it drops the tablespace created during
-# setup that other tests may use.
-test: tablespace
+# setup that other tests may use. This is also a good place to verify
+# our information_schema definitions.
+test: tablespace information_schema
diff --git a/src/test/regress/sql/information_schema.sql b/src/test/regress/sql/information_schema.sql
new file mode 100644
index 0000000000..a0a8b1ed58
--- /dev/null
+++ b/src/test/regress/sql/information_schema.sql
@@ -0,0 +1,41 @@
+-- We test the information schema last, so that we examine
+-- database state at the end of the regression test run.
+
+--
+-- Table constraints
+--
+
+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 table_name, constraint_name;
+
+--
+-- Domain constraints
+--
+
+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.39.2
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
In 0002, I took the tests added by Peter's proposed patch and put them
in a separate test file that runs at the end. There are some issues,
however. One is that the ORDER BY clause in the check_constraints view
is not fully deterministic, because the table name is not part of the
view definition, so we cannot sort by table name.
I object very very strongly to this proposed test method. It
completely undoes the work I did in v15 (cc50080a8 and related)
to make the core regression test scripts mostly independent of each
other. Even without considering the use-case of running a subset of
the tests, the new test's expected output will constantly be needing
updates as side effects of unrelated changes.
regards, tom lane
On 2023-Sep-05, Peter Eisentraut wrote:
The following information schema views are affected by the not-null
constraint catalog entries:1. CHECK_CONSTRAINTS
2. CONSTRAINT_COLUMN_USAGE
3. DOMAIN_CONSTRAINTS
4. TABLE_CONSTRAINTSNote that 1 and 3 also contain domain constraints.
After looking at what happens for domain constraints in older versions
(I tested 15, but I suppose this applies everywhere), I notice that we
don't seem to handle them anywhere that I can see. My quick exercise is
just
create domain nnint as int not null;
create table foo (a nnint);
and then verify that this constraint shows nowhere -- it's not in
DOMAIN_CONSTRAINTS for starters, which is I think the most obvious place.
And nothing is shown in CHECK_CONSTRAINTS nor TABLE_CONSTRAINTS either.
This did ever work in the past? I tested with 9.3 and didn't see
anything there either.
I am hesitant to try to add domain not-null constraint support to
information_schema in the same commit as these changes. I think this
should be fixed separately.
(Note that if, in older versions, you change the table to be
create table foo (a nnint NOT NULL);
then you do get a row in table_constraints, but nothing in
check_constraints. With my proposed definition this constraint appears
in check_constraints, table_constraints and constraint_column_usage.)
On 2023-Sep-04, Tom Lane wrote:
I object very very strongly to this proposed test method. It
completely undoes the work I did in v15 (cc50080a8 and related)
to make the core regression test scripts mostly independent of each
other. Even without considering the use-case of running a subset of
the tests, the new test's expected output will constantly be needing
updates as side effects of unrelated changes.
You're absolutely right, this would be disastrous. A better alternative
is that the new test file creates a few objects for itself, either by
using a separate role or by using a separate schema, and we examine the
information_schema display for those objects only. Then it'll be better
isolated.
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
Subversion to GIT: the shortest path to happiness I've ever heard of
(Alexey Klyukin)
Import Notes
Reply to msg id not found: 471cd41b-c345-3bee-fe97-061f74eba5b8@eisentraut.org1196274.1693860187@sss.pgh.pa.us202309041710.psytrxlsiqex@alvherre.pgsql | Resolved by subject fallback
On 2023-Sep-05, Alvaro Herrera wrote:
After looking at what happens for domain constraints in older versions
(I tested 15, but I suppose this applies everywhere), I notice that we
don't seem to handle them anywhere that I can see. My quick exercise is
justcreate domain nnint as int not null;
create table foo (a nnint);and then verify that this constraint shows nowhere -- it's not in
DOMAIN_CONSTRAINTS for starters, which is I think the most obvious place.
And nothing is shown in CHECK_CONSTRAINTS nor TABLE_CONSTRAINTS either.
Looking now at what to do for CHECK_CONSTRAINTS with domain constraints,
I admit I'm completely confused about what this view is supposed to
show. Currently, we show the constraint name and a definition like
"CHECK (column IS NOT NULL)". But since the table name is not given, it
is not possible to know to what table the column name refers to. For
domains, we could show "CHECK (VALUE IS NOT NULL)" but again with no
indication of what domain it applies to, or anything at all that would
make this useful in any way whatsoever.
So this whole thing seems pretty futile and I'm disinclined to waste
much time on it.
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
On 9/5/23 19:15, Alvaro Herrera wrote:
On 2023-Sep-05, Alvaro Herrera wrote:
Looking now at what to do for CHECK_CONSTRAINTS with domain constraints,
I admit I'm completely confused about what this view is supposed to
show. Currently, we show the constraint name and a definition like
"CHECK (column IS NOT NULL)". But since the table name is not given, it
is not possible to know to what table the column name refers to. For
domains, we could show "CHECK (VALUE IS NOT NULL)" but again with no
indication of what domain it applies to, or anything at all that would
make this useful in any way whatsoever.
Constraint names are supposed to be unique per schema[1]SQL:2023-2 11.4 <table constraint definition> Syntax Rule 4 -- Vik Fearing so the view
contains the minimum required information to identify the constraint.
So this whole thing seems pretty futile and I'm disinclined to waste
much time on it.
Until PostgreSQL either
A) obeys the spec on this uniqueness, or
B) decides to deviate from the information_schema spec;
this view will be completely useless for actually getting any useful
information.
I would like to see us do A because it is the right thing to do. Our
autogenerated names obey this rule, but who knows how many duplicate
names per schema are out there in the wild from people specifying their
own names.
I don't know what the project would think about doing B.
[1]: SQL:2023-2 11.4 <table constraint definition> Syntax Rule 4 -- Vik Fearing
--
Vik Fearing
On Tue, Sep 5, 2023 at 2:50 PM Vik Fearing <vik@postgresfriends.org> wrote:
On 9/5/23 19:15, Alvaro Herrera wrote:
On 2023-Sep-05, Alvaro Herrera wrote:
Looking now at what to do for CHECK_CONSTRAINTS with domain constraints,
I admit I'm completely confused about what this view is supposed to
show. Currently, we show the constraint name and a definition like
"CHECK (column IS NOT NULL)". But since the table name is not given, it
is not possible to know to what table the column name refers to. For
domains, we could show "CHECK (VALUE IS NOT NULL)" but again with no
indication of what domain it applies to, or anything at all that would
make this useful in any way whatsoever.Constraint names are supposed to be unique per schema[1] so the view
contains the minimum required information to identify the constraint.
I'm presuming that the view constraint_column_usage [1]https://www.postgresql.org/docs/current/infoschema-constraint-column-usage.html is an integral part
of all this though I haven't taken the time to figure out exactly how we
are implementing it today.
I'm not all that for either A or B since the status quo seems workable.
Though ideally if the system has unique names per schema then everything
should just work - having the views produce duplicated information (as
opposed to nothing) if they are used when the DBA doesn't enforce the
standard's requirements seems plausible.
David J.
[1]: https://www.postgresql.org/docs/current/infoschema-constraint-column-usage.html
https://www.postgresql.org/docs/current/infoschema-constraint-column-usage.html
On 9/6/23 00:14, David G. Johnston wrote:
I'm not all that for either A or B since the status quo seems workable.
Pray tell, how is it workable? The view does not identify a specific
constraint because we don't obey the rules on one side and we do obey
the rules on the other side. It is completely useless and unworkable.
Though ideally if the system has unique names per schema then everything
should just work - having the views produce duplicated information (as
opposed to nothing) if they are used when the DBA doesn't enforce the
standard's requirements seems plausible.
Let us not engage in victim blaming. Postgres is the problem here.
--
Vik Fearing
Vik Fearing <vik@postgresfriends.org> writes:
On 9/6/23 00:14, David G. Johnston wrote:
I'm not all that for either A or B since the status quo seems workable.
Pray tell, how is it workable? The view does not identify a specific
constraint because we don't obey the rules on one side and we do obey
the rules on the other side. It is completely useless and unworkable.
What solution do you propose? Starting to enforce the spec's rather
arbitrary requirement that constraint names be unique per-schema is
a complete nonstarter. Changing the set of columns in a spec-defined
view is also a nonstarter, or at least we've always taken it as such.
If you'd like to see some forward progress in this area, maybe you
could lobby the SQL committee to make constraint names unique per-table
not per-schema, and then make the information_schema changes that would
be required to support that.
In general though, the fact that we have any DDL extensions at all
compared to the standard means that there will be Postgres databases
that are not adequately represented by the information_schema views.
I'm not sure it's worth being more outraged about constraint names
than anything else. Or do you also want us to rip out (for starters)
unique indexes on expressions, or unique partial indexes?
regards, tom lane
On 9/6/23 02:53, Tom Lane wrote:
Vik Fearing <vik@postgresfriends.org> writes:
On 9/6/23 00:14, David G. Johnston wrote:
I'm not all that for either A or B since the status quo seems workable.
Pray tell, how is it workable? The view does not identify a specific
constraint because we don't obey the rules on one side and we do obey
the rules on the other side. It is completely useless and unworkable.What solution do you propose? Starting to enforce the spec's rather
arbitrary requirement that constraint names be unique per-schema is
a complete nonstarter. Changing the set of columns in a spec-defined
view is also a nonstarter, or at least we've always taken it as such.
I both semi-agree and semi-disagree that these are nonstarters. One of
them has to give.
If you'd like to see some forward progress in this area, maybe you
could lobby the SQL committee to make constraint names unique per-table
not per-schema, and then make the information_schema changes that would
be required to support that.
I could easily do that; but now you are asking to denormalize the
standard, because the constraints could be from tables, domains, or
assertions.
I don't think that will go over well, starting with my own opinion.
And for this reason, I do not believe that this is a "rather arbitrary
requirement".
In general though, the fact that we have any DDL extensions at all
compared to the standard means that there will be Postgres databases
that are not adequately represented by the information_schema views.
Sure.
I'm not sure it's worth being more outraged about constraint names
than anything else. Or do you also want us to rip out (for starters)
unique indexes on expressions, or unique partial indexes?
Indexes of any kind are not part of the standard so these examples are
basically invalid.
SQL:2023-11 Schemata is not the part I am most familiar with, but I
don't even see where regular multi-column unique constraints are listed
out, so that is both a lack in the standard and a knockdown of this
argument. I am happy to be shown wrong about this.
--
Vik Fearing
Vik Fearing <vik@postgresfriends.org> writes:
On 9/6/23 02:53, Tom Lane wrote:
What solution do you propose? Starting to enforce the spec's rather
arbitrary requirement that constraint names be unique per-schema is
a complete nonstarter. Changing the set of columns in a spec-defined
view is also a nonstarter, or at least we've always taken it as such.
I both semi-agree and semi-disagree that these are nonstarters. One of
them has to give.
[ shrug... ] if you stick to a SQL-compliant schema setup, then the
information_schema views will serve for introspection. If you don't,
they won't, and you'll need to look at Postgres-specific catalog data.
This compromise has served for twenty years or so, and I'm not in a
hurry to change it. I think the odds of changing to the spec's
restriction without enormous pushback are nil, and I do not think
that the benefit could possibly be worth the ensuing pain to users.
(It's not even the absolute pain level that is a problem, so much
as the asymmetry: the pain would fall exclusively on users who get
no benefit, because they weren't relying on these views anyway.
If you think that's an easy sell, you're mistaken.)
It could possibly be a little more palatable to add column(s) to the
information_schema views, but I'm having a hard time seeing how that
moves the needle. The situation would still be precisely describable
as "if you stick to a SQL-compliant schema setup, then the standard
columns of the information_schema views will serve for introspection.
If you don't, they won't, and you'll need to look at Postgres-specific
columns". That doesn't seem like a big improvement. Also, given your
point about normalization, how would we define the additions exactly?
regards, tom lane
On 05.09.23 18:24, Alvaro Herrera wrote:
On 2023-Sep-05, Peter Eisentraut wrote:
The following information schema views are affected by the not-null
constraint catalog entries:1. CHECK_CONSTRAINTS
2. CONSTRAINT_COLUMN_USAGE
3. DOMAIN_CONSTRAINTS
4. TABLE_CONSTRAINTSNote that 1 and 3 also contain domain constraints.
After looking at what happens for domain constraints in older versions
(I tested 15, but I suppose this applies everywhere), I notice that we
don't seem to handle them anywhere that I can see. My quick exercise is
justcreate domain nnint as int not null;
create table foo (a nnint);and then verify that this constraint shows nowhere -- it's not in
DOMAIN_CONSTRAINTS for starters, which is I think the most obvious place.
And nothing is shown in CHECK_CONSTRAINTS nor TABLE_CONSTRAINTS either.This did ever work in the past? I tested with 9.3 and didn't see
anything there either.
No, this was never implemented. (As I wrote in my other message on the
other thread, arguably a bit buggy.) We could fix this separately,
unless we are going to implement catalogued domain not-null constraints
soon.
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.patchtext/x-diff; charset=utf-8Download
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.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
On 9/6/23 05:40, Tom Lane wrote:
Vik Fearing <vik@postgresfriends.org> writes:
On 9/6/23 02:53, Tom Lane wrote:
What solution do you propose? Starting to enforce the spec's rather
arbitrary requirement that constraint names be unique per-schema is
a complete nonstarter. Changing the set of columns in a spec-defined
view is also a nonstarter, or at least we've always taken it as such.I both semi-agree and semi-disagree that these are nonstarters. One of
them has to give.[ shrug... ] if you stick to a SQL-compliant schema setup, then the
information_schema views will serve for introspection. If you don't,
they won't, and you'll need to look at Postgres-specific catalog data.
As someone who regularly asks people to cite chapter and verse of the
standard, do you not see this as a problem?
If there is /one thing/ I wish we were 100% compliant on, it's
information_schema.
This compromise has served for twenty years or so, and I'm not in a
hurry to change it.
Has it? Or is this just the first time someone has complained?
I think the odds of changing to the spec's
restriction without enormous pushback are nil, and I do not think
that the benefit could possibly be worth the ensuing pain to users.
That is a valid opinion, and probably one that will win out for quite a
while.
(It's not even the absolute pain level that is a problem, so much
as the asymmetry: the pain would fall exclusively on users who get
no benefit, because they weren't relying on these views anyway.
If you think that's an easy sell, you're mistaken.)
I am curious how many people we are selling this to. In my career as a
consultant, I have never once come across anyone specifying their own
constraint names. That is certainly anecdotal, and by no means means it
doesn't happen, but my personal experience says that it is very low.
And since our generated names obey the spec (see ChooseConstraintName()
which even says some apps depend on this), I don't see making this
change being a big problem in the real world.
Mind you, I am not pushing (right now) to make this change; I am just
saying that it is the right thing to do.
It could possibly be a little more palatable to add column(s) to the
information_schema views, but I'm having a hard time seeing how that
moves the needle. The situation would still be precisely describable
as "if you stick to a SQL-compliant schema setup, then the standard
columns of the information_schema views will serve for introspection.
If you don't, they won't, and you'll need to look at Postgres-specific
columns". That doesn't seem like a big improvement. Also, given your
point about normalization, how would we define the additions exactly?
This is precisely my point.
--
Vik Fearing
On 2023-Sep-06, Alvaro Herrera wrote:
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.
Pushed.
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
On 06.09.23 19:52, Alvaro Herrera wrote:
+ 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
Small correction here: This should be
pg_catalog.format('%s IS NOT NULL', at.attname)::information_schema.character_data AS check_clause
That is, the word "CHECK" and the parentheses should not be part of the
produced value.
On 14.09.23 10:20, Peter Eisentraut wrote:
On 06.09.23 19:52, Alvaro Herrera wrote:
+ 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_clauseSmall correction here: This should be
pg_catalog.format('%s IS NOT NULL',
at.attname)::information_schema.character_data AS check_clauseThat is, the word "CHECK" and the parentheses should not be part of the
produced value.
I have committed this fix.
On 2023-Sep-18, Peter Eisentraut wrote:
On 14.09.23 10:20, Peter Eisentraut wrote:
Small correction here: This should be
pg_catalog.format('%s IS NOT NULL',
at.attname)::information_schema.character_data AS check_clauseThat is, the word "CHECK" and the parentheses should not be part of the
produced value.I have committed this fix.
Thanks.
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
On 14.09.23 10:20, Peter Eisentraut wrote:
On 06.09.23 19:52, Alvaro Herrera wrote:
+ 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_clauseSmall correction here: This should be
pg_catalog.format('%s IS NOT NULL',
at.attname)::information_schema.character_data AS check_clauseThat is, the word "CHECK" and the parentheses should not be part of the
produced value.
Slightly related, so let's just tack it on here:
While testing this, I noticed that the way the check_clause of regular
check constraints is computed appears to be suboptimal. It currently does
CAST(substring(pg_get_constraintdef(con.oid) from 7) AS character_data)
which ends up with an extra set of parentheses, which is ignorable, but
it also leaves in suffixes like "NOT VALID", which don't belong into
that column. Earlier in this thread I had contemplated a fix for the
first issue, but that wouldn't address the second issue. I think we can
fix this quite simply by using pg_get_expr() instead. I don't know why
it wasn't done like that to begin with, maybe it was just a (my?)
mistake. See attached patch.
Attachments:
0001-Simplify-information-schema-check-constraint-deparsi.patchtext/plain; charset=UTF-8; name=0001-Simplify-information-schema-check-constraint-deparsi.patchDownload
From 4483d1f5c6c6aac047f12a36cc4a9e69d4e912f6 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter@eisentraut.org>
Date: Tue, 19 Sep 2023 08:46:47 +0200
Subject: [PATCH] Simplify information schema check constraint deparsing
The computation of the column
information_schema.check_constraints.check_clause used
pg_get_constraintdef() plus some string manipulation to get the check
clause back out. This ended up with an extra pair of parentheses,
which is only an aesthetic problem, but also with suffixes like "NOT
VALID", which don't belong into that column. We can fix both of these
problems and simplify the code by just using pg_get_expr() instead.
---
src/backend/catalog/information_schema.sql | 3 +--
1 file changed, 1 insertion(+), 2 deletions(-)
diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql
index 7f7de91cc2..10b34c3c5b 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -435,8 +435,7 @@ 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)
- AS check_clause
+ CAST(pg_get_expr(con.conbin, coalesce(c.oid, 0)) 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)
--
2.42.0
On 19.09.23 09:01, Peter Eisentraut wrote:
While testing this, I noticed that the way the check_clause of regular
check constraints is computed appears to be suboptimal. It currently doesCAST(substring(pg_get_constraintdef(con.oid) from 7) AS character_data)
which ends up with an extra set of parentheses, which is ignorable, but
it also leaves in suffixes like "NOT VALID", which don't belong into
that column. Earlier in this thread I had contemplated a fix for the
first issue, but that wouldn't address the second issue. I think we can
fix this quite simply by using pg_get_expr() instead. I don't know why
it wasn't done like that to begin with, maybe it was just a (my?)
mistake. See attached patch.
committed