On 2017/05/03 6:30, Robert Haas wrote:
> On Tue, May 2, 2017 at 2:51 AM, Amit Langote
> <langote_amit...@lab.ntt.co.jp> wrote:
>> Per an off-list report from Olaf Gawenda (thanks Olaf), it seems that the
>> range partition's constraint is sometimes incorrect, at least in the case
>> of multi-column range partitioning. See below:
>>
>> create table p (a int, b int) partition by range (a, b);
>> create table p1 partition of p for values from (1, 1) to (10 ,10);
>> create table p2 partition of p for values from (11, 1) to (20, 10);
>>
>> Perhaps unusual, but it's still a valid definition. Tuple-routing puts
>> rows where they belong correctly.
>>
>> -- ok
>> insert into p values (10, 9);
>> select tableoid::regclass, * from p;
>> tableoid | a | b
>> ----------+----+---
>> p1 | 10 | 9
>> (1 row)
>>
>> -- but see this
>> select tableoid::regclass, * from p where a = 10;
>> tableoid | a | b
>> ----------+---+---
>> (0 rows)
>>
>> explain select tableoid::regclass, * from p where a = 10;
>> QUERY PLAN
>> -------------------------------------------
>> Result (cost=0.00..0.00 rows=0 width=12)
>> One-Time Filter: false
>> (2 rows)
>>
>> -- or this
>> insert into p1 values (10, 9);
>> ERROR: new row for relation "p1" violates partition constraint
>> DETAIL: Failing row contains (10, 9).
>>
>> This is because of the constraint being generated is not correct in this
>> case. p1's constraint is currently:
>>
>> a >= 1 and a < 10
>>
>> where it should really be the following:
>>
>> (a > 1 OR (a = 1 AND b >= 1))
>> AND
>> (a < 10 OR (a = 10 AND b < 10))
>>
>> Attached patch rewrites get_qual_for_range() for the same, along with some
>> code rearrangement for reuse. I also added some new tests to insert.sql
>> and inherit.sql, but wondered (maybe, too late now) whether there should
>> really be a declarative_partition.sql for these, moving in some of the old
>> tests too.
>>
>> Adding to the open items list.
>
> I think there are more problems here. With the patch:
>
> rhaas=# create table p (a int, b int) partition by range (a, b);
> CREATE TABLE
> rhaas=# create table p1 partition of p for values from (unbounded,0)
> to (unbounded,1);
> CREATE TABLE
> rhaas=# insert into p1 values (-2,-2);
> ERROR: new row for relation "p1" violates partition constraint
> DETAIL: Failing row contains (-2, -2).
> rhaas=# insert into p1 values (2,2);
> ERROR: new row for relation "p1" violates partition constraint
> DETAIL: Failing row contains (2, 2).
>
> Really, the whole CREATE TABLE .. PARTITION statement is meaningless
> and should be disallowed, because it's not meaningful to have a
> partition bound specification with a non-unbounded value following an
> unbounded value.
Yes, disallowing this in the first place is the best thing to do.
Attached patch 0001 implements that. With the patch:
create table p1 partition of p for values from (unbounded,0) to (unbounded,1);
ERROR: cannot specify finite value after UNBOUNDED
LINE 1: ...able p1 partition of p for values from (unbounded,0) to (unb...
^
> BTW, I think we should also add a function that prints the partition
> constraint, and have psql display that in the \d+ output, because
> people might need that - e.g. if you want to attach a partition
> without having to validate it, you need to be able to apply an
> appropriate constraint to it in advance, so you'll want to see what
> the existing partition constraints look like.
Agree that that would be helpful, so done in the attached 0003. With the
patch:
create table p (a int, b int) partition by range (a, b);
create table p1 partition of p for values from (1, 1) to (10 ,10);
create table p2 partition of p for values from (11, 1) to (20, 10);
\d p2
Table "public.p2"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | not null |
b | integer | | not null |
Partition of: p FOR VALUES FROM (11, 1) TO (20, 10)
Partition constraint: CHECK ((((a > 11) OR ((a = 11) AND (b >= 1))) AND
((a < 20) OR ((a = 20) AND (b < 10)))))
create table p3 (like p);
alter table p3 add constraint partcheck check ((((a > 21) OR ((a = 21) AND
(b >= 1))) AND ((a < 30) OR ((a = 30) AND (b < 5)))));
alter table p attach partition p3 for values from (21, 1) to (30, 10);
INFO: partition constraint for table "p3" is implied by existing constraints
ALTER TABLE
BTW, is it strange that the newly added pg_get_partition_constraintdef()
requires the relcache entry to be created for the partition and all of its
ancestor relations up to the root (I mean the fact that the relcache entry
needs to be created at all)? I can see only one other function,
set_relation_column_names(), creating the relcache entry at all.
> While I'm glad we have partitioning has a feature, I'm starting to get
> a bit depressed by the number of bugs that are turning up here. This
> was committed in early December, and ideally ought to have been stable
> long before now.
I would think so too. This bug was particularly unsettling for me,
although it is not to say that other bugs that turned up were any less
serious.
> Since Amit is back from vacation May 8th, I'll update no later than May 9th.
Attached updated 0002 and 0001, 0003 as mentioned above. Thanks a lot for
your patience.
Regards,
Amit
>From 2bca3f25e5ac17631200fb5d76684d5805a76d77 Mon Sep 17 00:00:00 2001
From: amit <amitlangot...@gmail.com>
Date: Mon, 8 May 2017 13:29:19 +0900
Subject: [PATCH 1/3] Disallow finite value after UNBOUNDED in range bounds
It does not make sense to have a finite lower or upper bound value
for a column, if an earlier column is unconstrained, i.e., has
UNBOUNDED specified.
---
src/backend/parser/parse_utilcmd.c | 34 ++++++++++++++++++++++++++++--
src/test/regress/expected/create_table.out | 7 ++++++
src/test/regress/sql/create_table.sql | 5 +++++
3 files changed, 44 insertions(+), 2 deletions(-)
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index e187409f6f..3abb655ea6 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -3358,6 +3358,9 @@ transformPartitionBound(ParseState *pstate, Relation parent, Node *bound)
int i,
j;
char *colname;
+ PartitionRangeDatum *ldatum,
+ *rdatum;
+ bool seen_unbounded;
if (spec->strategy != PARTITION_STRATEGY_RANGE)
ereport(ERROR,
@@ -3376,12 +3379,39 @@ transformPartitionBound(ParseState *pstate, Relation parent, Node *bound)
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
errmsg("TO must specify exactly one value per partitioning column")));
+ /*
+ * Check that no finite value follows a UNBOUNDED literal in either of
+ * lower and upper bound lists.
+ */
+ seen_unbounded = false;
+ foreach(cell1, spec->lowerdatums)
+ {
+ ldatum = (PartitionRangeDatum *) lfirst(cell1);
+ if (ldatum->infinite)
+ seen_unbounded = true;
+ else if (seen_unbounded)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("cannot specify finite value after UNBOUNDED"),
+ parser_errposition(pstate, exprLocation((Node *) ldatum))));
+ }
+ seen_unbounded = false;
+ foreach(cell1, spec->upperdatums)
+ {
+ rdatum = (PartitionRangeDatum *) lfirst(cell1);
+ if (rdatum->infinite)
+ seen_unbounded = true;
+ else if (seen_unbounded)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("cannot specify finite value after UNBOUNDED"),
+ parser_errposition(pstate, exprLocation((Node *) rdatum))));
+ }
+
i = j = 0;
result_spec->lowerdatums = result_spec->upperdatums = NIL;
forboth(cell1, spec->lowerdatums, cell2, spec->upperdatums)
{
- PartitionRangeDatum *ldatum,
- *rdatum;
Node *value;
A_Const *lcon = NULL,
*rcon = NULL;
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out
index dda0d7ee5d..15d4ce591c 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -505,6 +505,13 @@ ERROR: TO must specify exactly one value per partitioning column
-- cannot specify null values in range bounds
CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES FROM (null) TO (unbounded);
ERROR: cannot specify NULL in range bound
+-- cannot specify finite values after UNBOUNDED has been specified
+CREATE TABLE range_parted_multicol (a int, b int, c int) PARTITION BY RANGE (a, b, c);
+CREATE TABLE fail_part PARTITION OF range_parted_multicol FOR VALUES FROM (1, UNBOUNDED, 1) TO (UNBOUNDED, 1, 1);
+ERROR: cannot specify finite value after UNBOUNDED
+LINE 1: ...ge_parted_multicol FOR VALUES FROM (1, UNBOUNDED, 1) TO (UNB...
+ ^
+DROP TABLE range_parted_multicol;
-- check if compatible with the specified parent
-- cannot create as partition of a non-partitioned table
CREATE TABLE unparted (
diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql
index caf5ddb58b..95035c5947 100644
--- a/src/test/regress/sql/create_table.sql
+++ b/src/test/regress/sql/create_table.sql
@@ -473,6 +473,11 @@ CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES FROM ('a') TO ('z',
-- cannot specify null values in range bounds
CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES FROM (null) TO (unbounded);
+-- cannot specify finite values after UNBOUNDED has been specified
+CREATE TABLE range_parted_multicol (a int, b int, c int) PARTITION BY RANGE (a, b, c);
+CREATE TABLE fail_part PARTITION OF range_parted_multicol FOR VALUES FROM (1, UNBOUNDED, 1) TO (UNBOUNDED, 1, 1);
+DROP TABLE range_parted_multicol;
+
-- check if compatible with the specified parent
-- cannot create as partition of a non-partitioned table
--
2.11.0
>From 30a895dfc08f61c0c9b4d8be2456a0e095c04bf6 Mon Sep 17 00:00:00 2001
From: amit <amitlangot...@gmail.com>
Date: Tue, 2 May 2017 11:03:54 +0900
Subject: [PATCH 2/3] Emit "correct" range partition constraint expression
Currently emitted expression does not always describe the constraint
correctly, especially in the case of multi-column range key.
Code surrounding get_qual_for_*() has been rearranged a little to
move common code to a couple of new functions.
Original issue reported by Olaf Gawenda (olaf...@googlemail.com)
---
src/backend/catalog/partition.c | 625 ++++++++++++++++++++++------------
src/include/nodes/pg_list.h | 14 +
src/test/regress/expected/inherit.out | 90 +++++
src/test/regress/expected/insert.out | 59 ++++
src/test/regress/sql/inherit.sql | 18 +
src/test/regress/sql/insert.sql | 43 +++
6 files changed, 631 insertions(+), 218 deletions(-)
diff --git a/src/backend/catalog/partition.c b/src/backend/catalog/partition.c
index 8641ae16a2..bd4f0b67a4 100644
--- a/src/backend/catalog/partition.c
+++ b/src/backend/catalog/partition.c
@@ -118,10 +118,19 @@ static int32 qsort_partition_list_value_cmp(const void *a, const void *b,
static int32 qsort_partition_rbound_cmp(const void *a, const void *b,
void *arg);
-static List *get_qual_for_list(PartitionKey key, PartitionBoundSpec *spec);
-static List *get_qual_for_range(PartitionKey key, PartitionBoundSpec *spec);
static Oid get_partition_operator(PartitionKey key, int col,
StrategyNumber strategy, bool *need_relabel);
+static Expr* make_partition_op_expr(PartitionKey key, int keynum,
+ uint16 strategy, Expr *arg1, Expr *arg2);
+static void get_range_key_properties(PartitionKey key, int keynum,
+ PartitionRangeDatum *ldatum,
+ PartitionRangeDatum *udatum,
+ ListCell **partexprs_item,
+ Expr **keyCol,
+ Const **lower_val, Const **upper_val,
+ NullTest **nulltest);
+static List *get_qual_for_list(PartitionKey key, PartitionBoundSpec *spec);
+static List *get_qual_for_range(PartitionKey key, PartitionBoundSpec *spec);
static List *generate_partition_qual(Relation rel);
static PartitionRangeBound *make_one_range_bound(PartitionKey key, int index,
@@ -1146,6 +1155,123 @@ RelationGetPartitionDispatchInfo(Relation rel, int lockmode,
/* Module-local functions */
/*
+ * get_partition_operator
+ *
+ * Return oid of the operator of given strategy for a given partition key
+ * column.
+ */
+static Oid
+get_partition_operator(PartitionKey key, int col, StrategyNumber strategy,
+ bool *need_relabel)
+{
+ Oid operoid;
+
+ /*
+ * First check if there exists an operator of the given strategy, with
+ * this column's type as both its lefttype and righttype, in the
+ * partitioning operator family specified for the column.
+ */
+ operoid = get_opfamily_member(key->partopfamily[col],
+ key->parttypid[col],
+ key->parttypid[col],
+ strategy);
+
+ /*
+ * If one doesn't exist, we must resort to using an operator in the same
+ * opreator family but with the operator class declared input type. It is
+ * OK to do so, because the column's type is known to be binary-coercible
+ * with the operator class input type (otherwise, the operator class in
+ * question would not have been accepted as the partitioning operator
+ * class). We must however inform the caller to wrap the non-Const
+ * expression with a RelabelType node to denote the implicit coercion. It
+ * ensures that the resulting expression structurally matches similarly
+ * processed expressions within the optimizer.
+ */
+ if (!OidIsValid(operoid))
+ {
+ operoid = get_opfamily_member(key->partopfamily[col],
+ key->partopcintype[col],
+ key->partopcintype[col],
+ strategy);
+ *need_relabel = true;
+ }
+ else
+ *need_relabel = false;
+
+ if (!OidIsValid(operoid))
+ elog(ERROR, "could not find operator for partitioning");
+
+ return operoid;
+}
+
+/*
+ * make_partition_op_expr
+ * Returns an Expr for the given partition key column with arg1 and
+ * arg2 as its leftop and rightop, respectively
+ */
+static Expr*
+make_partition_op_expr(PartitionKey key, int keynum,
+ uint16 strategy, Expr *arg1, Expr *arg2)
+{
+ Oid operoid;
+ bool need_relabel = false;
+ Expr *result = NULL;
+
+ /* Get the correct btree operator for this partitioning column */
+ operoid = get_partition_operator(key, keynum, strategy, &need_relabel);
+
+ /*
+ * Chosen operator may be such that the non-Const operand needs to
+ * be coerced, so apply the same; see the comment in
+ * get_partition_operator().
+ */
+ if (!IsA(arg1, Const) &&
+ (need_relabel ||
+ key->partcollation[keynum] != key->parttypcoll[keynum]))
+ arg1 = (Expr *) makeRelabelType(arg1,
+ key->partopcintype[keynum],
+ -1,
+ key->partcollation[keynum],
+ COERCE_EXPLICIT_CAST);
+
+ /* Generate the actual expression */
+ switch (key->strategy)
+ {
+ case PARTITION_STRATEGY_LIST:
+ {
+ ScalarArrayOpExpr *saopexpr;
+
+ /* Build leftop = ANY (rightop) */
+ saopexpr = makeNode(ScalarArrayOpExpr);
+ saopexpr->opno = operoid;
+ saopexpr->opfuncid = get_opcode(operoid);
+ saopexpr->useOr = true;
+ saopexpr->inputcollid = key->partcollation[0];
+ saopexpr->args = list_make2(arg1, arg2);
+ saopexpr->location = -1;
+
+ result = (Expr *) saopexpr;
+ break;
+ }
+
+ case PARTITION_STRATEGY_RANGE:
+ result = make_opclause(operoid,
+ BOOLOID,
+ false,
+ arg1, arg2,
+ InvalidOid,
+ key->partcollation[keynum]);
+ break;
+
+ default:
+ elog(ERROR, "invalid partitioning strategy");
+ break;
+ }
+
+ return result;
+}
+
+/*
* get_qual_for_list
*
* Returns a list of expressions to use as a list partition's constraint.
@@ -1155,14 +1281,12 @@ get_qual_for_list(PartitionKey key, PartitionBoundSpec *spec)
{
List *result;
ArrayExpr *arr;
- ScalarArrayOpExpr *opexpr;
+ Expr *opexpr;
ListCell *cell,
*prev,
*next;
Expr *keyCol;
- Oid operoid;
- bool need_relabel,
- list_has_null = false;
+ bool list_has_null = false;
NullTest *nulltest1 = NULL,
*nulltest2 = NULL;
@@ -1233,24 +1357,9 @@ get_qual_for_list(PartitionKey key, PartitionBoundSpec *spec)
arr->multidims = false;
arr->location = -1;
- /* Get the correct btree equality operator */
- operoid = get_partition_operator(key, 0, BTEqualStrategyNumber,
- &need_relabel);
- if (need_relabel || key->partcollation[0] != key->parttypcoll[0])
- keyCol = (Expr *) makeRelabelType(keyCol,
- key->partopcintype[0],
- -1,
- key->partcollation[0],
- COERCE_EXPLICIT_CAST);
-
- /* Build leftop = ANY (rightop) */
- opexpr = makeNode(ScalarArrayOpExpr);
- opexpr->opno = operoid;
- opexpr->opfuncid = get_opcode(operoid);
- opexpr->useOr = true;
- opexpr->inputcollid = key->partcollation[0];
- opexpr->args = list_make2(keyCol, arr);
- opexpr->location = -1;
+ /* Generate the main expression, i.e., keyCol = ANY (arr) */
+ opexpr = make_partition_op_expr(key, 0, BTEqualStrategyNumber,
+ keyCol, (Expr *) arr);
if (nulltest1)
result = list_make2(nulltest1, opexpr);
@@ -1268,9 +1377,100 @@ get_qual_for_list(PartitionKey key, PartitionBoundSpec *spec)
}
/*
+ * get_range_key_properties
+ * Returns range partition key information for a given column
+ *
+ * On return, *lower_val and *upper_val contain either a valid Const
+ * expression or NULL. *nulltest contains a NullTest expression, or
+ * NULL.
+ */
+static void
+get_range_key_properties(PartitionKey key, int keynum,
+ PartitionRangeDatum *ldatum,
+ PartitionRangeDatum *udatum,
+ ListCell **partexprs_item,
+ Expr **keyCol,
+ Const **lower_val, Const **upper_val,
+ NullTest **nulltest)
+{
+ /* Partition key expression for this column */
+ if (key->partattrs[keynum] != 0)
+ {
+ *keyCol = (Expr *) makeVar(1,
+ key->partattrs[keynum],
+ key->parttypid[keynum],
+ key->parttypmod[keynum],
+ key->parttypcoll[keynum],
+ 0);
+ }
+ else
+ {
+ *keyCol = copyObject(lfirst(*partexprs_item));
+ *partexprs_item = lnext(*partexprs_item);
+ }
+
+ /*
+ * A range-partitioned table does not allow partition keys to be null.
+ * For simple columns, their NOT NULL constraint suffices for the
+ * enforcement of non-nullability. But for the expression keys, which
+ * are still nullable, we must emit a IS NOT NULL expression.
+ */
+ if (!IsA(*keyCol, Var))
+ {
+ *nulltest = makeNode(NullTest);
+ (*nulltest)->arg = *keyCol;
+ (*nulltest)->nulltesttype = IS_NOT_NULL;
+ (*nulltest)->argisrow = false;
+ (*nulltest)->location = -1;
+ }
+ else
+ *nulltest = NULL;
+
+ if (!ldatum->infinite)
+ *lower_val = (Const *) ldatum->value;
+ else
+ *lower_val = NULL;
+
+ if (!udatum->infinite)
+ *upper_val = (Const *) udatum->value;
+ else
+ *upper_val = NULL;
+}
+
+/*
* get_qual_for_range
*
- * Get a list of OpExpr's to use as a range partition's constraint.
+ * Get a list of expressions to use as a range partition's constraint.
+ * If there are multiple expressions, they are to be considered implicitly
+ * ANDed.
+ *
+ * For a multi-column range partition key, say (a, b, c), with (al, bl, cl)
+ * as the lower bound tuple and (au, bu, cu) as the upper bound tuple, we
+ * generate an expression tree of the following form:
+ *
+ * (a > al OR (a = al AND b > bl) OR (a = al AND b = bl AND c >= cl))
+ * AND
+ * (a < au OR (a = au AND b < bu) OR (a = au AND b = bu AND c < cu))
+ *
+ * If b were an expression key instead of a simple column, we also append
+ * (b IS NOT NULL) to the AND's argument list.
+ *
+ * It is often the case that a prefix of lower and upper bound tuples contains
+ * the same values, for example, (al = au), in which case, we will emit an
+ * expression tree of the following form:
+ *
+ * (a = al)
+ * AND
+ * (b > bl OR (b = bl AND c >= cl))
+ * AND
+ * (b < bu) OR (b = bu AND c < cu))
+ *
+ * In most common cases with only one partition column, say a, the following
+ * expression tree will be generated: a >= al AND a < au
+ *
+ * If all values of both lower and upper bounds are UNBOUNDED, the partition
+ * does not really have a constraint, except the IS NOT NULL constraint for
+ * any expression keys. We append a constant-true expression in that case.
*/
static List *
get_qual_for_range(PartitionKey key, PartitionBoundSpec *spec)
@@ -1278,239 +1478,228 @@ get_qual_for_range(PartitionKey key, PartitionBoundSpec *spec)
List *result = NIL;
ListCell *cell1,
*cell2,
- *partexprs_item;
- int i;
+ *partexprs_item,
+ partexprs_item_saved;
+ int i,
+ j;
+ PartitionRangeDatum *ldatum,
+ *udatum;
+ Expr *keyCol;
+ Const *lower_val,
+ *upper_val;
+ NullTest *nulltest;
+ List *lower_or_arms,
+ *upper_or_arms;
+ int maxnum_or_arms,
+ num_or_arms;
+ ListCell *lower_or_start_datum,
+ *upper_or_start_datum;
+
+ lower_or_start_datum = list_head(spec->lowerdatums);
+ upper_or_start_datum = list_head(spec->upperdatums);
+ maxnum_or_arms = key->partnatts;
/*
- * Iterate over columns of the key, emitting an OpExpr for each using the
- * corresponding lower and upper datums as constant operands.
+ * Iterate over the key columns and check if the corresponding lower and
+ * upper datums are equal using the btree equality operator for the
+ * column's type. If equal, we emit single keyCol = common_value
+ * expression. Starting from the first column for which the corresponding
+ * lower and upper bound datums are not equal, we generate OR expressions
+ * as shown in the function's header comment.
*/
i = 0;
partexprs_item = list_head(key->partexprs);
forboth(cell1, spec->lowerdatums, cell2, spec->upperdatums)
{
- PartitionRangeDatum *ldatum = lfirst(cell1),
- *udatum = lfirst(cell2);
- Expr *keyCol;
- Const *lower_val = NULL,
- *upper_val = NULL;
EState *estate;
MemoryContext oldcxt;
Expr *test_expr;
ExprState *test_exprstate;
Datum test_result;
bool isNull;
- bool need_relabel = false;
- Oid operoid;
- NullTest *nulltest;
- /* Left operand */
- if (key->partattrs[i] != 0)
- {
- keyCol = (Expr *) makeVar(1,
- key->partattrs[i],
- key->parttypid[i],
- key->parttypmod[i],
- key->parttypcoll[i],
- 0);
- }
- else
- {
- keyCol = copyObject(lfirst(partexprs_item));
- partexprs_item = lnext(partexprs_item);
- }
+ ldatum = lfirst(cell1);
+ udatum = lfirst(cell2);
/*
- * Emit a IS NOT NULL expression for non-Var keys, because whereas
- * simple attributes are covered by NOT NULL constraints, expression
- * keys are still nullable which is not acceptable in case of range
- * partitioning.
+ * Since get_range_key_properties() modifies partexprs_item, and we
+ * might need to start over from the previous expression in the
+ * later part of this functiom, save away the current value.
*/
- if (!IsA(keyCol, Var))
- {
- nulltest = makeNode(NullTest);
- nulltest->arg = keyCol;
- nulltest->nulltesttype = IS_NOT_NULL;
- nulltest->argisrow = false;
- nulltest->location = -1;
+ if (partexprs_item)
+ partexprs_item_saved = *partexprs_item;
+ get_range_key_properties(key, i, ldatum, udatum,
+ &partexprs_item,
+ &keyCol,
+ &lower_val, &upper_val,
+ &nulltest);
+
+ if (nulltest)
result = lappend(result, nulltest);
- }
/*
- * Stop at this column if either of lower or upper datum is infinite,
- * but do emit an OpExpr for the non-infinite datum.
+ * If either or both of lower_val and upper_val is NULL, they are
+ * unequal, because being NULL means the column is unbounded in the
+ * respective direction.
*/
- if (!ldatum->infinite)
- lower_val = (Const *) ldatum->value;
- if (!udatum->infinite)
- upper_val = (Const *) udatum->value;
+ if (!lower_val || !upper_val)
+ break;
- /*
- * If lower_val and upper_val are both finite and happen to be equal,
- * emit only (keyCol = lower_val) for this column, because all rows in
- * this partition could only ever contain this value (ie, lower_val)
- * in the current partitioning column. We must consider further
- * columns because the above condition does not fully constrain the
- * rows of this partition.
- */
- if (lower_val && upper_val)
- {
- /* Get the correct btree equality operator for the test */
- operoid = get_partition_operator(key, i, BTEqualStrategyNumber,
- &need_relabel);
-
- /* Create the test expression */
- estate = CreateExecutorState();
- oldcxt = MemoryContextSwitchTo(estate->es_query_cxt);
- test_expr = make_opclause(operoid,
- BOOLOID,
- false,
- (Expr *) lower_val,
- (Expr *) upper_val,
- InvalidOid,
- key->partcollation[i]);
- fix_opfuncids((Node *) test_expr);
- test_exprstate = ExecInitExpr(test_expr, NULL);
- test_result = ExecEvalExprSwitchContext(test_exprstate,
- GetPerTupleExprContext(estate),
- &isNull);
- MemoryContextSwitchTo(oldcxt);
- FreeExecutorState(estate);
+ /* Create the test expression */
+ estate = CreateExecutorState();
+ oldcxt = MemoryContextSwitchTo(estate->es_query_cxt);
+ test_expr = make_partition_op_expr(key, i, BTEqualStrategyNumber,
+ (Expr *) lower_val,
+ (Expr *) upper_val);
+ fix_opfuncids((Node *) test_expr);
+ test_exprstate = ExecInitExpr(test_expr, NULL);
+ test_result = ExecEvalExprSwitchContext(test_exprstate,
+ GetPerTupleExprContext(estate),
+ &isNull);
+ MemoryContextSwitchTo(oldcxt);
+ FreeExecutorState(estate);
+
+ /* If not equal, go generate the OR expressions */
+ if (!DatumGetBool(test_result))
+ break;
- if (DatumGetBool(test_result))
- {
- /* This can never be, but it's better to make sure */
- if (i == key->partnatts - 1)
- elog(ERROR, "invalid range bound specification");
-
- if (need_relabel || key->partcollation[i] != key->parttypcoll[i])
- keyCol = (Expr *) makeRelabelType(keyCol,
- key->partopcintype[i],
- -1,
- key->partcollation[i],
- COERCE_EXPLICIT_CAST);
- result = lappend(result,
- make_opclause(operoid,
- BOOLOID,
- false,
- keyCol,
- (Expr *) lower_val,
- InvalidOid,
- key->partcollation[i]));
-
- /* Go over to consider the next column. */
- i++;
- continue;
- }
- }
+ /* Equal, so generate keyCol = lower_val expression */
/*
- * We can say here that lower_val != upper_val. Emit expressions
- * (keyCol >= lower_val) and (keyCol < upper_val), then stop.
+ * This can never be true for the last key column, because such a
+ * range partition would never be allowed to be defined (it would
+ * have an empty range otherwise).
*/
- if (lower_val)
- {
- operoid = get_partition_operator(key, i,
- BTGreaterEqualStrategyNumber,
- &need_relabel);
-
- if (need_relabel || key->partcollation[i] != key->parttypcoll[i])
- keyCol = (Expr *) makeRelabelType(keyCol,
- key->partopcintype[i],
- -1,
- key->partcollation[i],
- COERCE_EXPLICIT_CAST);
- result = lappend(result,
- make_opclause(operoid,
- BOOLOID,
- false,
- keyCol,
- (Expr *) lower_val,
- InvalidOid,
- key->partcollation[i]));
- }
+ if (i == key->partnatts - 1)
+ elog(ERROR, "invalid range bound specification");
+
+ result = lappend(result,
+ make_partition_op_expr(key, i, BTEqualStrategyNumber,
+ keyCol, (Expr *) lower_val));
+
+ i++;
+ }
- if (upper_val)
+ /* First pair of lower_val and upper_val that are not equal. */
+ lower_or_start_datum = cell1;
+ upper_or_start_datum = cell2;
+
+ /* OR will have as many arms as there are key columns left. */
+ maxnum_or_arms = key->partnatts - i;
+
+ num_or_arms = 0;
+ lower_or_arms = upper_or_arms = NIL;
+ while (num_or_arms < maxnum_or_arms)
+ {
+ List *lower_or_arm_args = NIL,
+ *upper_or_arm_args = NIL;
+
+ j = i;
+ partexprs_item = &partexprs_item_saved;
+ for_both_cell(cell1, lower_or_start_datum, cell2, upper_or_start_datum)
{
- operoid = get_partition_operator(key, i,
- BTLessStrategyNumber,
- &need_relabel);
+ ldatum = lfirst(cell1);
+ udatum = lfirst(cell2);
+ get_range_key_properties(key, j, ldatum, udatum,
+ &partexprs_item,
+ &keyCol,
+ &lower_val, &upper_val,
+ &nulltest);
+
+ if (nulltest)
+ result = lappend(result, nulltest);
+
+ if (lower_val)
+ {
+ uint16 strategy;
- if (need_relabel || key->partcollation[i] != key->parttypcoll[i])
- keyCol = (Expr *) makeRelabelType(keyCol,
- key->partopcintype[i],
- -1,
- key->partcollation[i],
- COERCE_EXPLICIT_CAST);
+ /*
+ * For the non-last columns of this arm, use the equality
+ * operator.
+ */
+ if (j - i < num_or_arms)
+ strategy = BTEqualStrategyNumber;
+ else
+ /* Consider that the lower bound is inclusive */
+ strategy = (j == key->partnatts - 1)
+ ? BTGreaterEqualStrategyNumber
+ : BTGreaterStrategyNumber;
+
+ lower_or_arm_args = lappend(lower_or_arm_args,
+ make_partition_op_expr(key, j,
+ strategy,
+ keyCol,
+ (Expr *) lower_val));
+ }
- result = lappend(result,
- make_opclause(operoid,
- BOOLOID,
- false,
- keyCol,
- (Expr *) upper_val,
- InvalidOid,
- key->partcollation[i]));
- }
+ if (upper_val)
+ {
+ uint16 strategy;
- /*
- * We can stop at this column, because we would not have checked the
- * next column when routing a given row into this partition.
- */
- break;
- }
+ /*
+ * For the non-last columns of this arm, use the equality
+ * operator.
+ */
+ if (j - i < num_or_arms)
+ strategy = BTEqualStrategyNumber;
+ else
+ strategy = BTLessStrategyNumber;
- return result;
-}
+ upper_or_arm_args = lappend(upper_or_arm_args,
+ make_partition_op_expr(key, j,
+ strategy,
+ keyCol,
+ (Expr *) upper_val));
-/*
- * get_partition_operator
- *
- * Return oid of the operator of given strategy for a given partition key
- * column.
- */
-static Oid
-get_partition_operator(PartitionKey key, int col, StrategyNumber strategy,
- bool *need_relabel)
-{
- Oid operoid;
+ }
- /*
- * First check if there exists an operator of the given strategy, with
- * this column's type as both its lefttype and righttype, in the
- * partitioning operator family specified for the column.
- */
- operoid = get_opfamily_member(key->partopfamily[col],
- key->parttypid[col],
- key->parttypid[col],
- strategy);
+ /* Did we generate enough of OR's arguments? */
+ ++j;
+ if (j - i > num_or_arms)
+ break;
+ }
+
+ /* One arm of the OR expression for each of lower and upper bounds */
+ if (lower_or_arm_args != NIL)
+ lower_or_arms = lappend(lower_or_arms,
+ list_length(lower_or_arm_args) > 1
+ ? makeBoolExpr(AND_EXPR, lower_or_arm_args, -1)
+ : linitial(lower_or_arm_args));
+
+ if (upper_or_arm_args != NIL)
+ upper_or_arms = lappend(upper_or_arms,
+ list_length(upper_or_arm_args) > 1
+ ? makeBoolExpr(AND_EXPR, upper_or_arm_args, -1)
+ : linitial(upper_or_arm_args));
+ ++num_or_arms;
+ }
/*
- * If one doesn't exist, we must resort to using an operator in the same
- * opreator family but with the operator class declared input type. It is
- * OK to do so, because the column's type is known to be binary-coercible
- * with the operator class input type (otherwise, the operator class in
- * question would not have been accepted as the partitioning operator
- * class). We must however inform the caller to wrap the non-Const
- * expression with a RelabelType node to denote the implicit coercion. It
- * ensures that the resulting expression structurally matches similarly
- * processed expressions within the optimizer.
+ * Generate the OR expressions for each of lower and upper bounds (if
+ * required), and append to the list of implicitly ANDed list of
+ * expressions.
+ *
+ * If both lower_or_arms and upper_or_arms are empty, we append a
+ * constant-true expression. That happens if all of the literal values
+ * in both the lower and upper bound lists are UNBOUNDED.
*/
- if (!OidIsValid(operoid))
+ if (lower_or_arms == NIL && upper_or_arms == NIL)
+ result = lappend(result, makeBoolConst(true, false));
+ else
{
- operoid = get_opfamily_member(key->partopfamily[col],
- key->partopcintype[col],
- key->partopcintype[col],
- strategy);
- *need_relabel = true;
+ if (lower_or_arms != NIL)
+ result = lappend(result,
+ list_length(lower_or_arms) > 1
+ ? makeBoolExpr(OR_EXPR, lower_or_arms, -1)
+ : linitial(lower_or_arms));
+ if (upper_or_arms != NIL)
+ result = lappend(result,
+ list_length(upper_or_arms) > 1
+ ? makeBoolExpr(OR_EXPR, upper_or_arms, -1)
+ : linitial(upper_or_arms));
}
- else
- *need_relabel = false;
-
- if (!OidIsValid(operoid))
- elog(ERROR, "could not find operator for partitioning");
- return operoid;
+ return result;
}
/*
diff --git a/src/include/nodes/pg_list.h b/src/include/nodes/pg_list.h
index 9df7fb30d3..3313b051ce 100644
--- a/src/include/nodes/pg_list.h
+++ b/src/include/nodes/pg_list.h
@@ -183,6 +183,20 @@ list_length(const List *l)
(cell1) = lnext(cell1), (cell2) = lnext(cell2))
/*
+ * for_both_cell -
+ * a convenience macro which loops through two lists starting from the
+ * specified cells of each. This macro loops through both lists at the same
+ * time, stopping when either list runs out of elements. Depending on the
+ * requirements of the call site, it may also be wise to assert that the
+ * lengths of the two lists are equal, and initcell1 and initcell2 are at
+ * the same position in the respective lists.
+ */
+#define for_both_cell(cell1, initcell1, cell2, initcell2) \
+ for ((cell1) = (initcell1), (cell2) = (initcell2); \
+ (cell1) != NULL && (cell2) != NULL; \
+ (cell1) = lnext(cell1), (cell2) = lnext(cell2))
+
+/*
* forthree -
* the same for three lists
*/
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index 6163ed8117..af7090ba0d 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -1828,3 +1828,93 @@ explain (costs off) select * from range_list_parted where a >= 30;
drop table list_parted;
drop table range_list_parted;
+-- check that constraint exclusion is able to cope with the partition
+-- constraint emitted for multi-column range partitioned tables
+create table mcrparted (a int, b int, c int) partition by range (a, abs(b), c);
+create table mcrparted0 partition of mcrparted for values from (unbounded, unbounded, unbounded) to (1, 1, 1);
+create table mcrparted1 partition of mcrparted for values from (1, 1, 1) to (10, 5, 10);
+create table mcrparted2 partition of mcrparted for values from (10, 5, 10) to (10, 10, 10);
+create table mcrparted3 partition of mcrparted for values from (11, 1, 1) to (20, 10, 10);
+create table mcrparted4 partition of mcrparted for values from (20, 10, 10) to (20, 20, 20);
+create table mcrparted5 partition of mcrparted for values from (20, 20, 20) to (unbounded, unbounded, unbounded);
+explain (costs off) select * from mcrparted where a = 0; -- scans mcrparted0
+ QUERY PLAN
+------------------------------
+ Append
+ -> Seq Scan on mcrparted0
+ Filter: (a = 0)
+(3 rows)
+
+explain (costs off) select * from mcrparted where a = 10 and abs(b) < 5; -- scans mcrparted1
+ QUERY PLAN
+---------------------------------------------
+ Append
+ -> Seq Scan on mcrparted1
+ Filter: ((a = 10) AND (abs(b) < 5))
+(3 rows)
+
+explain (costs off) select * from mcrparted where a = 10 and abs(b) = 5; -- scans mcrparted1, mcrparted2
+ QUERY PLAN
+---------------------------------------------
+ Append
+ -> Seq Scan on mcrparted1
+ Filter: ((a = 10) AND (abs(b) = 5))
+ -> Seq Scan on mcrparted2
+ Filter: ((a = 10) AND (abs(b) = 5))
+(5 rows)
+
+explain (costs off) select * from mcrparted where abs(b) = 5; -- scans all partitions
+ QUERY PLAN
+------------------------------
+ Append
+ -> Seq Scan on mcrparted0
+ Filter: (abs(b) = 5)
+ -> Seq Scan on mcrparted1
+ Filter: (abs(b) = 5)
+ -> Seq Scan on mcrparted2
+ Filter: (abs(b) = 5)
+ -> Seq Scan on mcrparted3
+ Filter: (abs(b) = 5)
+ -> Seq Scan on mcrparted5
+ Filter: (abs(b) = 5)
+(11 rows)
+
+explain (costs off) select * from mcrparted where a > -1; -- scans all partitions
+ QUERY PLAN
+-------------------------------------
+ Append
+ -> Seq Scan on mcrparted0
+ Filter: (a > '-1'::integer)
+ -> Seq Scan on mcrparted1
+ Filter: (a > '-1'::integer)
+ -> Seq Scan on mcrparted2
+ Filter: (a > '-1'::integer)
+ -> Seq Scan on mcrparted3
+ Filter: (a > '-1'::integer)
+ -> Seq Scan on mcrparted4
+ Filter: (a > '-1'::integer)
+ -> Seq Scan on mcrparted5
+ Filter: (a > '-1'::integer)
+(13 rows)
+
+explain (costs off) select * from mcrparted where a = 20 and abs(b) = 10 and c > 10; -- scans mcrparted4
+ QUERY PLAN
+-----------------------------------------------------------
+ Append
+ -> Seq Scan on mcrparted4
+ Filter: ((c > 10) AND (a = 20) AND (abs(b) = 10))
+(3 rows)
+
+explain (costs off) select * from mcrparted where a = 20 and c > 20; -- scans mcrparted3, mcrparte4, mcrparte5
+ QUERY PLAN
+-----------------------------------------
+ Append
+ -> Seq Scan on mcrparted3
+ Filter: ((c > 20) AND (a = 20))
+ -> Seq Scan on mcrparted4
+ Filter: ((c > 20) AND (a = 20))
+ -> Seq Scan on mcrparted5
+ Filter: ((c > 20) AND (a = 20))
+(7 rows)
+
+drop table mcrparted;
diff --git a/src/test/regress/expected/insert.out b/src/test/regress/expected/insert.out
index 6f34b1c640..c68b31415d 100644
--- a/src/test/regress/expected/insert.out
+++ b/src/test/regress/expected/insert.out
@@ -435,3 +435,62 @@ revoke all on key_desc from someone_else;
revoke all on key_desc_1 from someone_else;
drop role someone_else;
drop table key_desc, key_desc_1;
+-- check multi-column range partitioning expression enforces the same
+-- constraint as what tuple-routing would determine it to be
+create table mcrparted (a int, b int, c int) partition by range (a, abs(b), c);
+create table mcrparted0 partition of mcrparted for values from (unbounded, unbounded, unbounded) to (1, 1, 1);
+create table mcrparted1 partition of mcrparted for values from (1, 1, 1) to (10, 5, 10);
+create table mcrparted2 partition of mcrparted for values from (10, 5, 10) to (10, 10, 10);
+create table mcrparted3 partition of mcrparted for values from (11, 1, 1) to (20, 10, 10);
+create table mcrparted4 partition of mcrparted for values from (20, 10, 10) to (20, 20, 20);
+create table mcrparted5 partition of mcrparted for values from (20, 20, 20) to (unbounded, unbounded, unbounded);
+-- routed to mcrparted0
+insert into mcrparted values (0, 1, 1);
+insert into mcrparted0 values (0, 1, 1);
+-- routed to mcparted1
+insert into mcrparted values (9, 1000, 1);
+insert into mcrparted1 values (9, 1000, 1);
+insert into mcrparted values (10, 5, 1);
+insert into mcrparted1 values (10, 5, 1);
+-- routed to mcparted2
+insert into mcrparted values (10, 6, 1000);
+insert into mcrparted2 values (10, 6, 1000);
+insert into mcrparted values (10, 10, 9);
+insert into mcrparted2 values (10, 10, 9);
+-- no partition exists, nor does mcrparted2 accept it
+insert into mcrparted values (10, 10, 1000);
+ERROR: no partition of relation "mcrparted" found for row
+DETAIL: Partition key of the failing row contains (a, abs(b), c) = (10, 10, 1000).
+insert into mcrparted2 values (10, 10, 1000);
+ERROR: new row for relation "mcrparted2" violates partition constraint
+DETAIL: Failing row contains (10, 10, 1000).
+-- nor does mcrparted3
+insert into mcrparted3 values (10, 10, 1000);
+ERROR: new row for relation "mcrparted3" violates partition constraint
+DETAIL: Failing row contains (10, 10, 1000).
+-- routed to mcrparted5
+insert into mcrparted values (20, 20, 20);
+insert into mcrparted5 values (20, 20, 20);
+insert into mcrparted4 values (20, 20, 20); -- error
+ERROR: new row for relation "mcrparted4" violates partition constraint
+DETAIL: Failing row contains (20, 20, 20).
+-- check rows
+select tableoid::regclass::text, * from mcrparted order by 1;
+ tableoid | a | b | c
+------------+----+------+------
+ mcrparted0 | 0 | 1 | 1
+ mcrparted0 | 0 | 1 | 1
+ mcrparted1 | 9 | 1000 | 1
+ mcrparted1 | 9 | 1000 | 1
+ mcrparted1 | 10 | 5 | 1
+ mcrparted1 | 10 | 5 | 1
+ mcrparted2 | 10 | 6 | 1000
+ mcrparted2 | 10 | 6 | 1000
+ mcrparted2 | 10 | 10 | 9
+ mcrparted2 | 10 | 10 | 9
+ mcrparted5 | 20 | 20 | 20
+ mcrparted5 | 20 | 20 | 20
+(12 rows)
+
+-- cleanup
+drop table mcrparted;
diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql
index d43b75c4a7..7f34f43ec0 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -643,3 +643,21 @@ explain (costs off) select * from range_list_parted where a >= 30;
drop table list_parted;
drop table range_list_parted;
+
+-- check that constraint exclusion is able to cope with the partition
+-- constraint emitted for multi-column range partitioned tables
+create table mcrparted (a int, b int, c int) partition by range (a, abs(b), c);
+create table mcrparted0 partition of mcrparted for values from (unbounded, unbounded, unbounded) to (1, 1, 1);
+create table mcrparted1 partition of mcrparted for values from (1, 1, 1) to (10, 5, 10);
+create table mcrparted2 partition of mcrparted for values from (10, 5, 10) to (10, 10, 10);
+create table mcrparted3 partition of mcrparted for values from (11, 1, 1) to (20, 10, 10);
+create table mcrparted4 partition of mcrparted for values from (20, 10, 10) to (20, 20, 20);
+create table mcrparted5 partition of mcrparted for values from (20, 20, 20) to (unbounded, unbounded, unbounded);
+explain (costs off) select * from mcrparted where a = 0; -- scans mcrparted0
+explain (costs off) select * from mcrparted where a = 10 and abs(b) < 5; -- scans mcrparted1
+explain (costs off) select * from mcrparted where a = 10 and abs(b) = 5; -- scans mcrparted1, mcrparted2
+explain (costs off) select * from mcrparted where abs(b) = 5; -- scans all partitions
+explain (costs off) select * from mcrparted where a > -1; -- scans all partitions
+explain (costs off) select * from mcrparted where a = 20 and abs(b) = 10 and c > 10; -- scans mcrparted4
+explain (costs off) select * from mcrparted where a = 20 and c > 20; -- scans mcrparted3, mcrparte4, mcrparte5
+drop table mcrparted;
diff --git a/src/test/regress/sql/insert.sql b/src/test/regress/sql/insert.sql
index 020854c960..88509f9cce 100644
--- a/src/test/regress/sql/insert.sql
+++ b/src/test/regress/sql/insert.sql
@@ -289,3 +289,46 @@ revoke all on key_desc from someone_else;
revoke all on key_desc_1 from someone_else;
drop role someone_else;
drop table key_desc, key_desc_1;
+
+-- check multi-column range partitioning expression enforces the same
+-- constraint as what tuple-routing would determine it to be
+create table mcrparted (a int, b int, c int) partition by range (a, abs(b), c);
+create table mcrparted0 partition of mcrparted for values from (unbounded, unbounded, unbounded) to (1, 1, 1);
+create table mcrparted1 partition of mcrparted for values from (1, 1, 1) to (10, 5, 10);
+create table mcrparted2 partition of mcrparted for values from (10, 5, 10) to (10, 10, 10);
+create table mcrparted3 partition of mcrparted for values from (11, 1, 1) to (20, 10, 10);
+create table mcrparted4 partition of mcrparted for values from (20, 10, 10) to (20, 20, 20);
+create table mcrparted5 partition of mcrparted for values from (20, 20, 20) to (unbounded, unbounded, unbounded);
+
+-- routed to mcrparted0
+insert into mcrparted values (0, 1, 1);
+insert into mcrparted0 values (0, 1, 1);
+
+-- routed to mcparted1
+insert into mcrparted values (9, 1000, 1);
+insert into mcrparted1 values (9, 1000, 1);
+insert into mcrparted values (10, 5, 1);
+insert into mcrparted1 values (10, 5, 1);
+
+-- routed to mcparted2
+insert into mcrparted values (10, 6, 1000);
+insert into mcrparted2 values (10, 6, 1000);
+insert into mcrparted values (10, 10, 9);
+insert into mcrparted2 values (10, 10, 9);
+
+-- no partition exists, nor does mcrparted2 accept it
+insert into mcrparted values (10, 10, 1000);
+insert into mcrparted2 values (10, 10, 1000);
+-- nor does mcrparted3
+insert into mcrparted3 values (10, 10, 1000);
+
+-- routed to mcrparted5
+insert into mcrparted values (20, 20, 20);
+insert into mcrparted5 values (20, 20, 20);
+insert into mcrparted4 values (20, 20, 20); -- error
+
+-- check rows
+select tableoid::regclass::text, * from mcrparted order by 1;
+
+-- cleanup
+drop table mcrparted;
--
2.11.0
>From bc5d684664e3269183057a8bbae13b37fe4bf6e2 Mon Sep 17 00:00:00 2001
From: amit <amitlangot...@gmail.com>
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
