On 2017/05/10 12:08, Robert Haas wrote:
> On Mon, May 8, 2017 at 2:59 AM, Amit Langote
> <langote_amit...@lab.ntt.co.jp> wrote:
>> Yes, disallowing this in the first place is the best thing to do.
>> Attached patch 0001 implements that. With the patch:
>
> Committed.
Thanks.
> With regard to 0002, some of the resulting constraints are a bit more
> complicated than seems desirable:
>
> create table foo1 partition of foo for values from (unbounded,
> unbounded, unbounded) to (1, unbounded, unbounded);
> yields:
> Partition constraint: CHECK (((a < 1) OR (a = 1) OR (a = 1)))
>
> It would be better not to have (a = 1) in there twice, and better
> still to have the whole thing as (a <= 1).
>
> create table foo2 partition of foo for values from (3, 4, 5) to (6, 7,
> unbounded);
> yields:
> Partition constraint: CHECK ((((a > 3) OR ((a = 3) AND (b > 4)) OR ((a
> = 3) AND (b = 4) AND (c >= 5))) AND ((a < 6) OR ((a = 6) AND (b < 7))
> OR ((a = 6) AND (b = 7)))))
>
> The first half of that (for the lower bound) is of course fine, but
> the second half could be written better using <=, like instead of
>
> ((a = 6) AND (b < 7)) OR ((a = 6) AND (b = 7))
> you could have:
> ((a = 6) AND (b <= 7)
>
> This isn't purely cosmetic because the simpler constraint is probably
> noticeably faster to evaluate.
I think that makes sense. I modified things such that a simpler
constraint expression as you described above results in the presence of
UNBOUNDED values.
> I think you should have a few test cases like this in the patch - that
> is, cases where some but not all bounds are finite.
Added tests like this in insert.sql and then in the second patch as well.
>
>> 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.
>
> I suggest that you display this information only when "verbose" is set
> - i.e. \d+ not just \d. I don't intrinsically care think that forcing
> the relcache entry to be built here, but note that it means this will
> block when the parent is locked. Between that and the fact that this
> information will only sometimes be of interest, restricting it to \d+
> seems preferable.
OK, done.
> Next update on this issue by Thursday 5/11.
Attached updated patches.
Thanks,
Amit
>From 17ae801b3f3fa5e89ab9f2332a825382281522ad Mon Sep 17 00:00:00 2001
From: amit <amitlangot...@gmail.com>
Date: Tue, 2 May 2017 11:03:54 +0900
Subject: [PATCH 1/2] 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 | 733 ++++++++++++++++++++++++----------
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, 748 insertions(+), 209 deletions(-)
diff --git a/src/backend/catalog/partition.c b/src/backend/catalog/partition.c
index 8641ae16a2..b05ffd2d90 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,354 @@ 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,
+ k,
+ l;
+ PartitionRangeDatum *ldatum,
+ *udatum;
+ Expr *keyCol;
+ Const *lower_val,
+ *upper_val;
+ NullTest *nulltest;
+ List *lower_or_arms,
+ *upper_or_arms;
+ List *lower_or_prev_arm_args,
+ *upper_or_prev_arm_args;
+ int maxnum_or_arms,
+ num_or_arms;
+ ListCell *lower_or_start_datum,
+ *upper_or_start_datum;
+ bool nulltest_generated[PARTITION_MAX_KEYS];
+
+ lower_or_start_datum = list_head(spec->lowerdatums);
+ upper_or_start_datum = list_head(spec->upperdatums);
+ maxnum_or_arms = key->partnatts;
+ memset(nulltest_generated, 0, sizeof(nulltest_generated));
/*
- * 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))
+ 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 && !nulltest_generated[i])
{
- nulltest = makeNode(NullTest);
- nulltest->arg = keyCol;
- nulltest->nulltesttype = IS_NOT_NULL;
- nulltest->argisrow = false;
- nulltest->location = -1;
result = lappend(result, nulltest);
+ nulltest_generated[i] = true;
}
/*
- * 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;
+
+ /* 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;
+
+ /* Equal, so generate keyCol = lower_val expression */
/*
- * 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.
+ * 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 && upper_val)
+ 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++;
+ }
+
+ /* 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. */
+ k = l = i;
+ maxnum_or_arms = key->partnatts - i;
+ num_or_arms = 0;
+ lower_or_arms = upper_or_arms = NIL;
+ lower_or_prev_arm_args = upper_or_prev_arm_args = NIL;
+ while (num_or_arms < maxnum_or_arms)
+ {
+ List *lower_or_arm_args = NIL,
+ *upper_or_arm_args = NIL;
+ Expr *lower_or_arm,
+ *upper_or_arm;
+
+ j = i;
+ partexprs_item = &partexprs_item_saved;
+ for_both_cell(cell1, lower_or_start_datum, cell2, upper_or_start_datum)
{
- /* 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);
+ ldatum = lfirst(cell1);
+ udatum = lfirst(cell2);
+ get_range_key_properties(key, j, ldatum, udatum,
+ &partexprs_item,
+ &keyCol,
+ &lower_val, &upper_val,
+ &nulltest);
+
+ if (nulltest && !nulltest_generated[j])
+ {
+ result = lappend(result, nulltest);
+ nulltest_generated[j] = true;
+ }
- if (DatumGetBool(test_result))
+ if (lower_val)
{
- /* 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;
+ uint16 strategy;
+
+ /*
+ * 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));
}
+
+ if (upper_val)
+ {
+ uint16 strategy;
+
+ /*
+ * For the non-last columns of this arm, use the equality
+ * operator.
+ */
+ if (j - i < num_or_arms)
+ strategy = BTEqualStrategyNumber;
+ else
+ strategy = BTLessStrategyNumber;
+
+ upper_or_arm_args = lappend(upper_or_arm_args,
+ make_partition_op_expr(key, j,
+ strategy,
+ keyCol,
+ (Expr *) upper_val));
+
+ }
+
+ /* Did we generate enough of OR's arguments? */
+ ++j;
+ if (j - i > num_or_arms)
+ break;
}
/*
- * We can say here that lower_val != upper_val. Emit expressions
- * (keyCol >= lower_val) and (keyCol < upper_val), then stop.
+ * Check if we needn't really add these as separate arms of the
+ * respective OR expressions. For example, if this arm didn't add
+ * an OpExpr for the next column because it is unbounded, then we
+ * don't really need to add this as the new arm. Instead we modify
+ * the previous arm's last OpExpr to consider equality by replacing
+ * its opno with appropriate operator.
*/
- if (lower_val)
+ if (lower_or_arm_args != NIL)
{
- 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]));
- }
+ bool consider_cur_arm = true;
- if (upper_val)
- {
- operoid = get_partition_operator(key, i,
- BTLessStrategyNumber,
- &need_relabel);
+ /*
+ * List length comparison with previous suffices to conclude
+ * that no new column was introduced in this arm.
+ */
+ if (lower_or_prev_arm_args != NIL &&
+ list_length(lower_or_arm_args) ==
+ list_length(lower_or_prev_arm_args))
+ {
+ OpExpr *prev_arm_last,
+ *cur_arm_last;
+ List *op_infos;
+ ListCell *opic;
+ OpBtreeInterpretation *cur_op_intp;
+ Oid newopno;
- if (need_relabel || key->partcollation[i] != key->parttypcoll[i])
- keyCol = (Expr *) makeRelabelType(keyCol,
- key->partopcintype[i],
- -1,
- key->partcollation[i],
- COERCE_EXPLICIT_CAST);
+ prev_arm_last = (OpExpr *) llast(lower_or_prev_arm_args);
+ cur_arm_last = (OpExpr *) llast(lower_or_arm_args);
- result = lappend(result,
- make_opclause(operoid,
- BOOLOID,
- false,
- keyCol,
- (Expr *) upper_val,
- InvalidOid,
- key->partcollation[i]));
+ /*
+ * Must choose the correct replacement operator, that is,
+ * figure out the opfamily, lefttype and righttype of the
+ * current operator, which when fed into get_opfamily_member()
+ * with the new strategy will give us the intended operator.
+ */
+ op_infos = get_op_btree_interpretation(cur_arm_last->opno);
+ foreach(opic, op_infos)
+ {
+ OpBtreeInterpretation *intp = lfirst(opic);
+
+ if (intp->opfamily_id == key->partopfamily[k-1])
+ {
+ cur_op_intp = intp;
+ break;
+ }
+ }
+
+ newopno = get_opfamily_member(cur_op_intp->opfamily_id,
+ cur_op_intp->oplefttype,
+ cur_op_intp->oprighttype,
+ BTGreaterEqualStrategyNumber);
+ if (!OidIsValid(newopno))
+ elog(ERROR, "no >= operator found in opfamily %u",
+ cur_op_intp->opfamily_id);
+ prev_arm_last->opno = newopno;
+ consider_cur_arm = false;
+ }
+
+ lower_or_prev_arm_args = lower_or_arm_args;
+
+ if (consider_cur_arm)
+ {
+ lower_or_arm = list_length(lower_or_arm_args) > 1
+ ? makeBoolExpr(AND_EXPR, lower_or_arm_args, -1)
+ : linitial(lower_or_arm_args);
+ lower_or_arms = lappend(lower_or_arms, lower_or_arm);
+ ++k; /* latest column in the lower OR expression */
+ }
}
- /*
- * We can stop at this column, because we would not have checked the
- * next column when routing a given row into this partition.
- */
- break;
- }
+ if (upper_or_arm_args != NIL)
+ {
+ bool consider_cur_arm = true;
- return result;
-}
+ /* Check if the new arm didn't add check on a new column */
+ if (upper_or_prev_arm_args != NIL &&
+ list_length(upper_or_arm_args) ==
+ list_length(upper_or_prev_arm_args))
+ {
+ OpExpr *prev_arm_last,
+ *cur_arm_last;
+ List *op_infos;
+ ListCell *opic;
+ OpBtreeInterpretation *cur_op_intp;
+ Oid newopno;
+
+ prev_arm_last = (OpExpr *) llast(upper_or_prev_arm_args);
+ cur_arm_last = (OpExpr *) llast(upper_or_arm_args);
+ op_infos = get_op_btree_interpretation(cur_arm_last->opno);
+ foreach(opic, op_infos)
+ {
+ OpBtreeInterpretation *intp = lfirst(opic);
-/*
- * 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;
+ if (intp->opfamily_id == key->partopfamily[l-1])
+ {
+ cur_op_intp = intp;
+ break;
+ }
+ }
- /*
- * 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);
+ newopno = get_opfamily_member(cur_op_intp->opfamily_id,
+ cur_op_intp->oplefttype,
+ cur_op_intp->oprighttype,
+ BTLessEqualStrategyNumber);
+ if (!OidIsValid(newopno))
+ elog(ERROR, "no <= operator found in opfamily %u",
+ cur_op_intp->opfamily_id);
+ prev_arm_last->opno = newopno;
+ consider_cur_arm = false;
+ }
+ upper_or_prev_arm_args = upper_or_arm_args;
+
+ if (consider_cur_arm)
+ {
+ upper_or_arm = list_length(upper_or_arm_args) > 1
+ ? makeBoolExpr(AND_EXPR, upper_or_arm_args, -1)
+ : linitial(upper_or_arm_args);
+ upper_or_arms = lappend(upper_or_arms, upper_or_arm);
+ ++l; /* latest column in the upper OR expression */
+ }
+ }
+
+ ++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..02429a37e3 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, unbounded, unbounded);
+create table mcrparted1 partition of mcrparted for values from (2, 1, unbounded) to (10, 5, 10);
+create table mcrparted2 partition of mcrparted for values from (10, 6, unbounded) to (10, unbounded, unbounded);
+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 (21, unbounded, unbounded) to (30, 20, unbounded);
+create table mcrparted5 partition of mcrparted for values from (30, 21, 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);
+insert into mcrparted values (2, 1, 0);
+insert into mcrparted1 values (2, 1, 0);
+-- routed to mcparted2
+insert into mcrparted values (10, 6, 1000);
+insert into mcrparted2 values (10, 6, 1000);
+insert into mcrparted values (10, 1000, 1000);
+insert into mcrparted2 values (10, 1000, 1000);
+-- no partition exists, nor does mcrparted3 accept it
+insert into mcrparted values (11, 1, -1);
+ERROR: no partition of relation "mcrparted" found for row
+DETAIL: Partition key of the failing row contains (a, abs(b), c) = (11, 1, -1).
+insert into mcrparted3 values (11, 1, -1);
+ERROR: new row for relation "mcrparted3" violates partition constraint
+DETAIL: Failing row contains (11, 1, -1).
+-- routed to mcrparted5
+insert into mcrparted values (30, 21, 20);
+insert into mcrparted5 values (30, 21, 20);
+insert into mcrparted4 values (30, 21, 20); -- error
+ERROR: new row for relation "mcrparted4" violates partition constraint
+DETAIL: Failing row contains (30, 21, 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
+ mcrparted1 | 2 | 1 | 0
+ mcrparted1 | 2 | 1 | 0
+ mcrparted2 | 10 | 6 | 1000
+ mcrparted2 | 10 | 6 | 1000
+ mcrparted2 | 10 | 1000 | 1000
+ mcrparted2 | 10 | 1000 | 1000
+ mcrparted5 | 30 | 21 | 20
+ mcrparted5 | 30 | 21 | 20
+(14 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..db8967bad7 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, unbounded, unbounded);
+create table mcrparted1 partition of mcrparted for values from (2, 1, unbounded) to (10, 5, 10);
+create table mcrparted2 partition of mcrparted for values from (10, 6, unbounded) to (10, unbounded, unbounded);
+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 (21, unbounded, unbounded) to (30, 20, unbounded);
+create table mcrparted5 partition of mcrparted for values from (30, 21, 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);
+insert into mcrparted values (2, 1, 0);
+insert into mcrparted1 values (2, 1, 0);
+
+-- routed to mcparted2
+insert into mcrparted values (10, 6, 1000);
+insert into mcrparted2 values (10, 6, 1000);
+insert into mcrparted values (10, 1000, 1000);
+insert into mcrparted2 values (10, 1000, 1000);
+
+-- no partition exists, nor does mcrparted3 accept it
+insert into mcrparted values (11, 1, -1);
+insert into mcrparted3 values (11, 1, -1);
+
+-- routed to mcrparted5
+insert into mcrparted values (30, 21, 20);
+insert into mcrparted5 values (30, 21, 20);
+insert into mcrparted4 values (30, 21, 20); -- error
+
+-- check rows
+select tableoid::regclass::text, * from mcrparted order by 1;
+
+-- cleanup
+drop table mcrparted;
--
2.11.0
>From 17da90561abdf911e57e2be58d1fce4b1e62fbb3 Mon Sep 17 00:00:00 2001
From: amit <amitlangot...@gmail.com>
Date: Mon, 8 May 2017 10:21:19 +0900
Subject: [PATCH 2/2] 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 | 37 +++++++++++--
src/include/catalog/partition.h | 1 +
src/include/catalog/pg_proc.h | 2 +
src/test/regress/expected/create_table.out | 89 +++++++++++++++++++++++++-----
src/test/regress/expected/foreign_data.out | 3 +
src/test/regress/sql/create_table.sql | 20 ++++++-
8 files changed, 196 insertions(+), 21 deletions(-)
diff --git a/src/backend/catalog/partition.c b/src/backend/catalog/partition.c
index b05ffd2d90..801263100a 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..f7aeaaa26b 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -1858,13 +1858,26 @@ describeOneTableDetails(const char *schemaname,
PGresult *result;
char *parent_name;
char *partdef;
+ char *partconstraintdef = NULL;
- printfPQExpBuffer(&buf,
- "SELECT inhparent::pg_catalog.regclass, pg_get_expr(c.relpartbound, inhrelid)"
- " FROM pg_catalog.pg_class c"
- " JOIN pg_catalog.pg_inherits"
- " ON c.oid = inhrelid"
- " WHERE c.oid = '%s' AND c.relispartition;", oid);
+ /* If verbose, also request the partition constraint definition */
+ if (verbose)
+ printfPQExpBuffer(&buf,
+ "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"
+ " WHERE c.oid = '%s' AND c.relispartition;", oid);
+ else
+ printfPQExpBuffer(&buf,
+ "SELECT inhparent::pg_catalog.regclass,"
+ " pg_get_expr(c.relpartbound, inhrelid)"
+ " FROM pg_catalog.pg_class c"
+ " JOIN pg_catalog.pg_inherits"
+ " ON c.oid = inhrelid"
+ " WHERE c.oid = '%s' AND c.relispartition;", oid);
result = PSQLexec(buf.data);
if (!result)
goto error_return;
@@ -1873,9 +1886,21 @@ describeOneTableDetails(const char *schemaname,
{
parent_name = PQgetvalue(result, 0, 0);
partdef = PQgetvalue(result, 0, 1);
+
+ if (PQnfields(result) == 3)
+ partconstraintdef = PQgetvalue(result, 0, 2);
+
printfPQExpBuffer(&tmpbuf, _("Partition of: %s %s"), parent_name,
partdef);
printTableAddFooter(&cont, tmpbuf.data);
+
+ if (partconstraintdef)
+ {
+ 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..a4df1415df 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;
@@ -643,29 +644,43 @@ CREATE TABLE part_c PARTITION OF parted (b WITH OPTIONS NOT NULL DEFAULT 0) FOR
-- create a level-2 partition
CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES FROM (1) TO (10);
-- Partition bound in describe output
-\d part_b
- Table "public.part_b"
- Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+---------
- a | text | | |
- b | integer | | not null | 1
+\d+ part_b
+ Table "public.part_b"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ a | text | | | | extended | |
+ b | integer | | not null | 1 | plain | |
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)
-- Both partition bound and partition key in describe output
-\d part_c
- Table "public.part_c"
- Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+---------
- a | text | | |
- b | integer | | not null | 0
+\d+ part_c
+ Table "public.part_c"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ a | text | | | | extended | |
+ b | integer | | not null | 0 | plain | |
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.)
+Partitions: part_c_1_10 FOR VALUES FROM (1) TO (10)
+
+-- 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 | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ a | text | | | | extended | |
+ b | integer | | not null | 0 | plain | |
+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
@@ -682,6 +697,54 @@ 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, b int, c int) PARTITION BY RANGE (abs(a), abs(b), c);
+CREATE TABLE unbounded_range_part PARTITION OF range_parted4 FOR VALUES FROM (UNBOUNDED, UNBOUNDED, UNBOUNDED) TO (UNBOUNDED, UNBOUNDED, UNBOUNDED);
+\d+ unbounded_range_part
+ Table "public.unbounded_range_part"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a | integer | | | | plain | |
+ b | integer | | | | plain | |
+ c | integer | | not null | | plain | |
+Partition of: range_parted4 FOR VALUES FROM (UNBOUNDED, UNBOUNDED, UNBOUNDED) TO (UNBOUNDED, UNBOUNDED, UNBOUNDED)
+Partition constraint: CHECK (((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND true))
+
+DROP TABLE unbounded_range_part;
+CREATE TABLE range_parted4_1 PARTITION OF range_parted4 FOR VALUES FROM (UNBOUNDED, UNBOUNDED, UNBOUNDED) TO (1, UNBOUNDED, UNBOUNDED);
+\d+ range_parted4_1
+ Table "public.range_parted4_1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a | integer | | | | plain | |
+ b | integer | | | | plain | |
+ c | integer | | not null | | plain | |
+Partition of: range_parted4 FOR VALUES FROM (UNBOUNDED, UNBOUNDED, UNBOUNDED) TO (1, UNBOUNDED, UNBOUNDED)
+Partition constraint: CHECK (((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND (abs(a) <= 1)))
+
+CREATE TABLE range_parted4_2 PARTITION OF range_parted4 FOR VALUES FROM (3, 4, 5) TO (6, 7, UNBOUNDED);
+\d+ range_parted4_2
+ Table "public.range_parted4_2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a | integer | | | | plain | |
+ b | integer | | | | plain | |
+ c | integer | | not null | | plain | |
+Partition of: range_parted4 FOR VALUES FROM (3, 4, 5) TO (6, 7, UNBOUNDED)
+Partition constraint: CHECK (((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND ((abs(a) > 3) OR ((abs(a) = 3) AND (abs(b) > 4)) OR ((abs(a) = 3) AND (abs(b) = 4) AND (c >= 5))) AND ((abs(a) < 6) OR ((abs(a) = 6) AND (abs(b) <= 7)))))
+
+CREATE TABLE range_parted4_3 PARTITION OF range_parted4 FOR VALUES FROM (6, 8, UNBOUNDED) TO (9, UNBOUNDED, UNBOUNDED);
+\d+ range_parted4_3
+ Table "public.range_parted4_3"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a | integer | | | | plain | |
+ b | integer | | | | plain | |
+ c | integer | | not null | | plain | |
+Partition of: range_parted4 FOR VALUES FROM (6, 8, UNBOUNDED) TO (9, UNBOUNDED, UNBOUNDED)
+Partition constraint: CHECK (((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND ((abs(a) > 6) OR ((abs(a) = 6) AND (abs(b) >= 8))) AND (abs(a) <= 9)))
+
+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 6a1f22ebeb..8020745d97 100644
--- a/src/test/regress/expected/foreign_data.out
+++ b/src/test/regress/expected/foreign_data.out
@@ -1844,6 +1844,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')
@@ -1914,6 +1915,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')
@@ -1941,6 +1943,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..5cb2bb4813 100644
--- a/src/test/regress/sql/create_table.sql
+++ b/src/test/regress/sql/create_table.sql
@@ -598,10 +598,13 @@ CREATE TABLE part_c PARTITION OF parted (b WITH OPTIONS NOT NULL DEFAULT 0) FOR
CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES FROM (1) TO (10);
-- Partition bound in describe output
-\d part_b
+\d+ part_b
-- Both partition bound and partition key in describe output
-\d part_c
+\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
@@ -609,6 +612,19 @@ CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES FROM (1) TO (10);
-- returned.
\d parted
+-- show that an unbounded range partition has a CHECK (true) constraint
+CREATE TABLE range_parted4 (a int, b int, c int) PARTITION BY RANGE (abs(a), abs(b), c);
+CREATE TABLE unbounded_range_part PARTITION OF range_parted4 FOR VALUES FROM (UNBOUNDED, UNBOUNDED, UNBOUNDED) TO (UNBOUNDED, UNBOUNDED, UNBOUNDED);
+\d+ unbounded_range_part
+DROP TABLE unbounded_range_part;
+CREATE TABLE range_parted4_1 PARTITION OF range_parted4 FOR VALUES FROM (UNBOUNDED, UNBOUNDED, UNBOUNDED) TO (1, UNBOUNDED, UNBOUNDED);
+\d+ range_parted4_1
+CREATE TABLE range_parted4_2 PARTITION OF range_parted4 FOR VALUES FROM (3, 4, 5) TO (6, 7, UNBOUNDED);
+\d+ range_parted4_2
+CREATE TABLE range_parted4_3 PARTITION OF range_parted4 FOR VALUES FROM (6, 8, UNBOUNDED) TO (9, UNBOUNDED, UNBOUNDED);
+\d+ range_parted4_3
+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
