From bc5d684664e3269183057a8bbae13b37fe4bf6e2 Mon Sep 17 00:00:00 2001 From: amit Date: Mon, 8 May 2017 10:21:19 +0900 Subject: [PATCH 3/3] Add pg_get_partition_constraintdef To externally display a partition's implicit constraint. --- src/backend/catalog/partition.c | 29 ++++++++++++++++++++++++ src/backend/utils/adt/ruleutils.c | 36 ++++++++++++++++++++++++++++++ src/bin/psql/describe.c | 8 ++++++- src/include/catalog/partition.h | 1 + src/include/catalog/pg_proc.h | 2 ++ src/test/regress/expected/create_table.out | 27 ++++++++++++++++++++++ src/test/regress/expected/foreign_data.out | 3 +++ src/test/regress/sql/create_table.sql | 9 ++++++++ 8 files changed, 114 insertions(+), 1 deletion(-) diff --git a/src/backend/catalog/partition.c b/src/backend/catalog/partition.c index bd4f0b67a4..5093b59391 100644 --- a/src/backend/catalog/partition.c +++ b/src/backend/catalog/partition.c @@ -978,6 +978,35 @@ RelationGetPartitionQual(Relation rel) } /* + * get_partition_qual_relid + * + * Returns an expression tree describing the passed-in relation's partition + * constraint. + */ +Expr * +get_partition_qual_relid(Oid relid) +{ + Relation rel = heap_open(relid, AccessShareLock); + Expr *result = NULL; + List *and_args; + + /* Do the work only if this relation is a partition. */ + if (rel->rd_rel->relispartition) + { + and_args = generate_partition_qual(rel); + if (list_length(and_args) > 1) + result = makeBoolExpr(AND_EXPR, and_args, -1); + else + result = linitial(and_args); + } + + /* Keep the lock. */ + heap_close(rel, NoLock); + + return result; +} + +/* * Append OIDs of rel's partitions to the list 'partoids' and for each OID, * append pointer rel to the list 'parents'. */ diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index cbde1fff01..0472cc5ac0 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -24,6 +24,7 @@ #include "access/sysattr.h" #include "catalog/dependency.h" #include "catalog/indexing.h" +#include "catalog/partition.h" #include "catalog/pg_aggregate.h" #include "catalog/pg_am.h" #include "catalog/pg_authid.h" @@ -1729,6 +1730,41 @@ pg_get_partkeydef_worker(Oid relid, int prettyFlags, } /* + * pg_get_partition_constraintdef + * + * Returns partition constraint expression as a string for the input relation + */ +Datum +pg_get_partition_constraintdef(PG_FUNCTION_ARGS) +{ + Oid relationId = PG_GETARG_OID(0); + Expr *constr_expr; + int prettyFlags; + List *context; + char *consrc; + StringInfoData buf; + + constr_expr = get_partition_qual_relid(relationId); + + /* Quick exit if not a partition */ + if (constr_expr == NULL) + PG_RETURN_NULL(); + + /* + * Deparse the constraint expression and return in in the form of a CHECK + * constraint. + */ + prettyFlags = PRETTYFLAG_INDENT; + context = deparse_context_for(get_relation_name(relationId), relationId); + consrc = deparse_expression_pretty((Node *) constr_expr, context, false, + false, prettyFlags, 0); + initStringInfo(&buf); + appendStringInfo(&buf, "CHECK (%s)", consrc); + + PG_RETURN_TEXT_P(string_to_text(buf.data)); +} + +/* * pg_get_constraintdef * * Returns the definition for the constraint, ie, everything that needs to diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index dbfc7339e5..d04f843d0e 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -1858,9 +1858,11 @@ describeOneTableDetails(const char *schemaname, PGresult *result; char *parent_name; char *partdef; + char *partconstraintdef; printfPQExpBuffer(&buf, - "SELECT inhparent::pg_catalog.regclass, pg_get_expr(c.relpartbound, inhrelid)" + "SELECT inhparent::pg_catalog.regclass, pg_get_expr(c.relpartbound, inhrelid)," + " pg_get_partition_constraintdef(inhrelid)" " FROM pg_catalog.pg_class c" " JOIN pg_catalog.pg_inherits" " ON c.oid = inhrelid" @@ -1873,9 +1875,13 @@ describeOneTableDetails(const char *schemaname, { parent_name = PQgetvalue(result, 0, 0); partdef = PQgetvalue(result, 0, 1); + partconstraintdef = PQgetvalue(result, 0, 2); printfPQExpBuffer(&tmpbuf, _("Partition of: %s %s"), parent_name, partdef); printTableAddFooter(&cont, tmpbuf.data); + printfPQExpBuffer(&tmpbuf, _("Partition constraint: %s"), + partconstraintdef); + printTableAddFooter(&cont, tmpbuf.data); PQclear(result); } } diff --git a/src/include/catalog/partition.h b/src/include/catalog/partition.h index 421644ca77..25fb0a0440 100644 --- a/src/include/catalog/partition.h +++ b/src/include/catalog/partition.h @@ -80,6 +80,7 @@ extern List *get_qual_from_partbound(Relation rel, Relation parent, Node *bound) extern List *map_partition_varattnos(List *expr, int target_varno, Relation partrel, Relation parent); extern List *RelationGetPartitionQual(Relation rel); +extern Expr *get_partition_qual_relid(Oid relid); /* For tuple routing */ extern PartitionDispatch *RelationGetPartitionDispatchInfo(Relation rel, diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index 82562add43..aa0ce791c0 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -1992,6 +1992,8 @@ DATA(insert OID = 3415 ( pg_get_statisticsextdef PGNSP PGUID 12 1 0 0 0 f f DESCR("index description"); DATA(insert OID = 3352 ( pg_get_partkeydef PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_partkeydef _null_ _null_ _null_ )); DESCR("partition key description"); +DATA(insert OID = 3403 ( pg_get_partition_constraintdef PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_partition_constraintdef _null_ _null_ _null_ )); +DESCR("partition constraint description"); DATA(insert OID = 1662 ( pg_get_triggerdef PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_triggerdef _null_ _null_ _null_ )); DESCR("trigger description"); DATA(insert OID = 1387 ( pg_get_constraintdef PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_constraintdef _null_ _null_ _null_ )); diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out index 15d4ce591c..cb8c86a018 100644 --- a/src/test/regress/expected/create_table.out +++ b/src/test/regress/expected/create_table.out @@ -546,6 +546,7 @@ CREATE TABLE part_forced_oids PARTITION OF oids_parted FOR VALUES FROM (1) TO (1 --------+---------+-----------+----------+---------+---------+--------------+------------- a | integer | | not null | | plain | | Partition of: oids_parted FOR VALUES FROM (1) TO (10) +Partition constraint: CHECK (((a >= 1) AND (a < 10))) Has OIDs: yes DROP TABLE oids_parted, part_forced_oids; @@ -650,6 +651,7 @@ CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES FROM (1) TO (10); a | text | | | b | integer | | not null | 1 Partition of: parted FOR VALUES IN ('b') +Partition constraint: CHECK (((a IS NOT NULL) AND (a = ANY (ARRAY['b'::text])))) Check constraints: "check_a" CHECK (length(a) > 0) "part_b_b_check" CHECK (b >= 0) @@ -662,11 +664,24 @@ Check constraints: a | text | | | b | integer | | not null | 0 Partition of: parted FOR VALUES IN ('c') +Partition constraint: CHECK (((a IS NOT NULL) AND (a = ANY (ARRAY['c'::text])))) Partition key: RANGE (b) Check constraints: "check_a" CHECK (length(a) > 0) Number of partitions: 1 (Use \d+ to list them.) +-- a level-2 partition's constraint will include the parent's expressions +\d part_c_1_10 + Table "public.part_c_1_10" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + a | text | | | + b | integer | | not null | 0 +Partition of: part_c FOR VALUES FROM (1) TO (10) +Partition constraint: CHECK (((a IS NOT NULL) AND (a = ANY (ARRAY['c'::text])) AND (b >= 1) AND (b < 10))) +Check constraints: + "check_a" CHECK (length(a) > 0) + -- Show partition count in the parent's describe output -- Tempted to include \d+ output listing partitions with bound info but -- output could vary depending on the order in which partition oids are @@ -682,6 +697,18 @@ Check constraints: "check_a" CHECK (length(a) > 0) Number of partitions: 3 (Use \d+ to list them.) +-- show that an unbounded range partition has a CHECK (true) constraint +CREATE TABLE range_parted4 (a int) PARTITION BY RANGE (a); +CREATE TABLE unbounded_range_part PARTITION OF range_parted4 FOR VALUES FROM (UNBOUNDED) TO (UNBOUNDED); +\d unbounded_range_part + Table "public.unbounded_range_part" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + a | integer | | not null | +Partition of: range_parted4 FOR VALUES FROM (UNBOUNDED) TO (UNBOUNDED) +Partition constraint: CHECK (true) + +drop table range_parted4; -- cleanup DROP TABLE parted, list_parted, range_parted, list_parted2, range_parted2, range_parted3; -- comments on partitioned tables columns diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out index 1c7a7593f9..0009173382 100644 --- a/src/test/regress/expected/foreign_data.out +++ b/src/test/regress/expected/foreign_data.out @@ -1783,6 +1783,7 @@ Partitions: pt2_1 FOR VALUES IN (1) c2 | text | | | | | extended | | c3 | date | | | | | plain | | Partition of: pt2 FOR VALUES IN (1) +Partition constraint: CHECK (((c1 IS NOT NULL) AND (c1 = ANY (ARRAY[1])))) Server: s0 FDW Options: (delimiter ',', quote '"', "be quoted" 'value') @@ -1853,6 +1854,7 @@ Partitions: pt2_1 FOR VALUES IN (1) c2 | text | | | | | extended | | c3 | date | | | | | plain | | Partition of: pt2 FOR VALUES IN (1) +Partition constraint: CHECK (((c1 IS NOT NULL) AND (c1 = ANY (ARRAY[1])))) Server: s0 FDW Options: (delimiter ',', quote '"', "be quoted" 'value') @@ -1880,6 +1882,7 @@ Partitions: pt2_1 FOR VALUES IN (1) c2 | text | | | | | extended | | c3 | date | | not null | | | plain | | Partition of: pt2 FOR VALUES IN (1) +Partition constraint: CHECK (((c1 IS NOT NULL) AND (c1 = ANY (ARRAY[1])))) Check constraints: "p21chk" CHECK (c2 <> ''::text) Server: s0 diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql index 95035c5947..76b079dc42 100644 --- a/src/test/regress/sql/create_table.sql +++ b/src/test/regress/sql/create_table.sql @@ -603,12 +603,21 @@ CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES FROM (1) TO (10); -- Both partition bound and partition key in describe output \d part_c +-- a level-2 partition's constraint will include the parent's expressions +\d part_c_1_10 + -- Show partition count in the parent's describe output -- Tempted to include \d+ output listing partitions with bound info but -- output could vary depending on the order in which partition oids are -- returned. \d parted +-- show that an unbounded range partition has a CHECK (true) constraint +CREATE TABLE range_parted4 (a int) PARTITION BY RANGE (a); +CREATE TABLE unbounded_range_part PARTITION OF range_parted4 FOR VALUES FROM (UNBOUNDED) TO (UNBOUNDED); +\d unbounded_range_part +drop table range_parted4; + -- cleanup DROP TABLE parted, list_parted, range_parted, list_parted2, range_parted2, range_parted3; -- 2.11.0