On 2017/01/19 14:15, Amit Langote wrote:
> So, here are all the patches I posted to date (and one new at the bottom)
> for reported and unreported bugs, excluding the one involving
> BulkInsertState for which you replied in a new thread.
>
> I'll describe the attached patches in brief:
Sorry, I forgot to mention that I have skipped the patch I proposed to
modify the committed approach [1] to get the correct tuple to show in the
constraint violation messages. It might be better to continue that
discussion at [2].
And because I skipped that patch, I should have removed the related logic
in ExecWithCheckOptions() that I added in one of the later patches (patch
0004), which I forgot to do. So, here are all the patches again with the
correct 0004 this time. Sigh.
Thanks,
Amit
[1]
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=f1b4c771ea74f42447dccaed42ffcdcccf3aa694
[2]
https://www.postgresql.org/message-id/CA%2BTgmoZjGzSM5WwnyapFaw3GxnDLWh7pm8Xiz8_QWQnUQy%3DSCA%40mail.gmail.com
>From 37c861d5eae1c8f11b3fae93cb209da262a13fbc Mon Sep 17 00:00:00 2001
From: amit <amitlangot...@gmail.com>
Date: Tue, 13 Dec 2016 15:07:41 +0900
Subject: [PATCH 1/8] Fix a bug of insertion into an internal partition.
Since implicit partition constraints are not inherited, an internal
partition's constraint was not being enforced when targeted directly.
So, include such constraint when setting up leaf partition result
relations for tuple-routing.
Reported by: n/a
Patch by: Amit Langote
Reports: n/a
---
src/backend/commands/copy.c | 1 -
src/backend/commands/tablecmds.c | 1 -
src/backend/executor/execMain.c | 42 ++++++++++++++++++++++++++++--------
src/include/executor/executor.h | 1 -
src/test/regress/expected/insert.out | 6 ++++++
src/test/regress/sql/insert.sql | 5 +++++
6 files changed, 44 insertions(+), 12 deletions(-)
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 1fd2162794..75386212e0 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -2432,7 +2432,6 @@ CopyFrom(CopyState cstate)
InitResultRelInfo(resultRelInfo,
cstate->rel,
1, /* dummy rangetable index */
- true, /* do load partition check expression */
NULL,
0);
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index e633a50dd2..06e43cbb3a 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -1324,7 +1324,6 @@ ExecuteTruncate(TruncateStmt *stmt)
InitResultRelInfo(resultRelInfo,
rel,
0, /* dummy rangetable index */
- false,
NULL,
0);
resultRelInfo++;
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index ff277d300a..5457f8fbde 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -824,10 +824,10 @@ InitPlan(QueryDesc *queryDesc, int eflags)
resultRelationOid = getrelid(resultRelationIndex, rangeTable);
resultRelation = heap_open(resultRelationOid, RowExclusiveLock);
+
InitResultRelInfo(resultRelInfo,
resultRelation,
resultRelationIndex,
- true,
NULL,
estate->es_instrument);
resultRelInfo++;
@@ -1218,10 +1218,11 @@ void
InitResultRelInfo(ResultRelInfo *resultRelInfo,
Relation resultRelationDesc,
Index resultRelationIndex,
- bool load_partition_check,
Relation partition_root,
int instrument_options)
{
+ List *partition_check = NIL;
+
MemSet(resultRelInfo, 0, sizeof(ResultRelInfo));
resultRelInfo->type = T_ResultRelInfo;
resultRelInfo->ri_RangeTableIndex = resultRelationIndex;
@@ -1257,13 +1258,38 @@ InitResultRelInfo(ResultRelInfo *resultRelInfo,
resultRelInfo->ri_ConstraintExprs = NULL;
resultRelInfo->ri_junkFilter = NULL;
resultRelInfo->ri_projectReturning = NULL;
- if (load_partition_check)
- resultRelInfo->ri_PartitionCheck =
- RelationGetPartitionQual(resultRelationDesc);
+
/*
- * The following gets set to NULL unless we are initializing leaf
- * partitions for tuple-routing.
+ * If partition_root has been specified, that means we are builiding the
+ * ResultRelationInfo for one of its leaf partitions. In that case, we
+ * need *not* initialize the leaf partition's constraint, but rather the
+ * the partition_root's (if any). We must do that explicitly like this,
+ * because implicit partition constraints are not inherited like user-
+ * defined constraints and would fail to be enforced by ExecConstraints()
+ * after a tuple is routed to a leaf partition.
*/
+ if (partition_root)
+ {
+ /*
+ * Root table itself may or may not be a partition; partition_check
+ * would be NIL in the latter case.
+ */
+ partition_check = RelationGetPartitionQual(partition_root);
+
+ /*
+ * This is not our own partition constraint, but rather an ancestor's.
+ * So any Vars in it bear the ancestor's attribute numbers. We must
+ * switch them to our own.
+ */
+ if (partition_check != NIL)
+ partition_check = map_partition_varattnos(partition_check,
+ resultRelationDesc,
+ partition_root);
+ }
+ else
+ partition_check = RelationGetPartitionQual(resultRelationDesc);
+
+ resultRelInfo->ri_PartitionCheck = partition_check;
resultRelInfo->ri_PartitionRoot = partition_root;
}
@@ -1327,7 +1353,6 @@ ExecGetTriggerResultRel(EState *estate, Oid relid)
InitResultRelInfo(rInfo,
rel,
0, /* dummy rangetable index */
- true,
NULL,
estate->es_instrument);
estate->es_trig_target_relations =
@@ -3132,7 +3157,6 @@ ExecSetupPartitionTupleRouting(Relation rel,
InitResultRelInfo(leaf_part_rri,
partrel,
1, /* dummy */
- false,
rel,
0);
diff --git a/src/include/executor/executor.h b/src/include/executor/executor.h
index d424031676..eb180fdb63 100644
--- a/src/include/executor/executor.h
+++ b/src/include/executor/executor.h
@@ -189,7 +189,6 @@ extern void CheckValidResultRel(Relation resultRel, CmdType operation);
extern void InitResultRelInfo(ResultRelInfo *resultRelInfo,
Relation resultRelationDesc,
Index resultRelationIndex,
- bool load_partition_check,
Relation partition_root,
int instrument_options);
extern ResultRelInfo *ExecGetTriggerResultRel(EState *estate, Oid relid);
diff --git a/src/test/regress/expected/insert.out b/src/test/regress/expected/insert.out
index 0382560d39..729d9ebbbc 100644
--- a/src/test/regress/expected/insert.out
+++ b/src/test/regress/expected/insert.out
@@ -358,5 +358,11 @@ alter table p add constraint check_b check (b = 3);
insert into p values (1, 2);
ERROR: new row for relation "p11" violates check constraint "check_b"
DETAIL: Failing row contains (1, 2).
+-- check that inserting into an internal partition successfully results in
+-- checking its partition constraint before inserting into the leaf partition
+-- selected by tuple-routing
+insert into p1 (a, b) values (2, 3);
+ERROR: new row for relation "p11" violates partition constraint
+DETAIL: Failing row contains (3, 2).
-- cleanup
drop table p, p1, p11;
diff --git a/src/test/regress/sql/insert.sql b/src/test/regress/sql/insert.sql
index a6eab8f365..5509555fc5 100644
--- a/src/test/regress/sql/insert.sql
+++ b/src/test/regress/sql/insert.sql
@@ -221,5 +221,10 @@ alter table p add constraint check_b check (b = 3);
-- after "(1, 2)" is routed to it
insert into p values (1, 2);
+-- check that inserting into an internal partition successfully results in
+-- checking its partition constraint before inserting into the leaf partition
+-- selected by tuple-routing
+insert into p1 (a, b) values (2, 3);
+
-- cleanup
drop table p, p1, p11;
--
2.11.0
>From 1a47d44891c50e406fb3e1d9ca4bc0f536782a2a Mon Sep 17 00:00:00 2001
From: amit <amitlangot...@gmail.com>
Date: Wed, 28 Dec 2016 10:10:26 +0900
Subject: [PATCH 2/8] Set ecxt_scantuple correctly for tuple-routing
In 2ac3ef7a01df859c62d0a02333b646d65eaec5ff, we changed things so that
it's possible for a different TupleTableSlot to be used for partitioned
tables at successively lower levels. If we do end up changing the slot
from the original, we must update ecxt_scantuple to point to the new one
for partition key of the tuple to be computed correctly.
Also update the regression tests so that the code manipulating
ecxt_scantuple is covered.
Reported by: Rajkumar Raghuwanshi
Patch by: Amit Langote
Reports: https://www.postgresql.org/message-id/CAKcux6%3Dm1qyqB2k6cjniuMMrYXb75O-MB4qGQMu8zg-iGGLjDw%40mail.gmail.com
---
src/backend/catalog/partition.c | 29 ++++++++++++++++++++++-------
src/backend/executor/execMain.c | 2 --
src/test/regress/expected/insert.out | 2 +-
src/test/regress/sql/insert.sql | 2 +-
4 files changed, 24 insertions(+), 11 deletions(-)
diff --git a/src/backend/catalog/partition.c b/src/backend/catalog/partition.c
index 874e69d8d6..e540fc16d0 100644
--- a/src/backend/catalog/partition.c
+++ b/src/backend/catalog/partition.c
@@ -1642,7 +1642,10 @@ get_partition_for_tuple(PartitionDispatch *pd,
bool isnull[PARTITION_MAX_KEYS];
int cur_offset,
cur_index;
- int i;
+ int i,
+ result;
+ ExprContext *ecxt = GetPerTupleExprContext(estate);
+ TupleTableSlot *ecxt_scantuple_old = ecxt->ecxt_scantuple;
/* start with the root partitioned table */
parent = pd[0];
@@ -1671,7 +1674,14 @@ get_partition_for_tuple(PartitionDispatch *pd,
slot = myslot;
}
- /* Extract partition key from tuple */
+ /*
+ * Extract partition key from tuple; FormPartitionKeyDatum() expects
+ * ecxt_scantuple to point to the correct tuple slot (which might be
+ * different from the slot we received from the caller if the
+ * partitioned table of the current level has different tuple
+ * descriptor from its parent).
+ */
+ ecxt->ecxt_scantuple = slot;
FormPartitionKeyDatum(parent, slot, estate, values, isnull);
if (key->strategy == PARTITION_STRATEGY_RANGE)
@@ -1726,16 +1736,21 @@ get_partition_for_tuple(PartitionDispatch *pd,
*/
if (cur_index < 0)
{
+ result = -1;
*failed_at = RelationGetRelid(parent->reldesc);
- return -1;
+ break;
}
- else if (parent->indexes[cur_index] < 0)
- parent = pd[-parent->indexes[cur_index]];
- else
+ else if (parent->indexes[cur_index] >= 0)
+ {
+ result = parent->indexes[cur_index];
break;
+ }
+ else
+ parent = pd[-parent->indexes[cur_index]];
}
- return parent->indexes[cur_index];
+ ecxt->ecxt_scantuple = ecxt_scantuple_old;
+ return result;
}
/*
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 5457f8fbde..67e46729f3 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -3191,9 +3191,7 @@ ExecFindPartition(ResultRelInfo *resultRelInfo, PartitionDispatch *pd,
{
int result;
Oid failed_at;
- ExprContext *econtext = GetPerTupleExprContext(estate);
- econtext->ecxt_scantuple = slot;
result = get_partition_for_tuple(pd, slot, estate, &failed_at);
if (result < 0)
{
diff --git a/src/test/regress/expected/insert.out b/src/test/regress/expected/insert.out
index 729d9ebbbc..b8e74caee8 100644
--- a/src/test/regress/expected/insert.out
+++ b/src/test/regress/expected/insert.out
@@ -320,7 +320,7 @@ drop table part_ee_ff, part_gg2_2, part_gg2_1, part_gg2, part_gg1, part_gg;
drop table part_aa_bb, part_cc_dd, part_null, list_parted;
-- more tests for certain multi-level partitioning scenarios
create table p (a int, b int) partition by range (a, b);
-create table p1 (b int, a int not null) partition by range (b);
+create table p1 (b int not null, a int not null) partition by range ((b+0));
create table p11 (like p1);
alter table p11 drop a;
alter table p11 add a int;
diff --git a/src/test/regress/sql/insert.sql b/src/test/regress/sql/insert.sql
index 5509555fc5..2154d01c56 100644
--- a/src/test/regress/sql/insert.sql
+++ b/src/test/regress/sql/insert.sql
@@ -193,7 +193,7 @@ drop table part_aa_bb, part_cc_dd, part_null, list_parted;
-- more tests for certain multi-level partitioning scenarios
create table p (a int, b int) partition by range (a, b);
-create table p1 (b int, a int not null) partition by range (b);
+create table p1 (b int not null, a int not null) partition by range ((b+0));
create table p11 (like p1);
alter table p11 drop a;
alter table p11 add a int;
--
2.11.0
>From 365145d11c28c63106f641995ed8c266b7e05184 Mon Sep 17 00:00:00 2001
From: amit <amitlangot...@gmail.com>
Date: Tue, 27 Dec 2016 16:56:58 +0900
Subject: [PATCH 3/8] Fix RETURNING to work correctly after tuple-routing
In ExecInsert(), do not switch back to the root partitioned table
ResultRelInfo until after we finish ExecProcessReturning(), so that
RETURNING projection is done using the partition's descriptor. For
the projection to work correctly, we must initialize the same for
each leaf partition during ModifyTableState initialization.
With this commit, map_partition_varattnos() now accepts one more
argument viz. target_varno. Previously, it assumed varno = 1 for
its input expressions, which was fine since its usage was limited to
partition constraints. To use it with expressions such as an INSERT
statement's returning list, we must be prepared for varnos != 1 as
in the change above.
Reported by: n/a
Patch by: Amit Langote
Reports: n/a
---
src/backend/catalog/partition.c | 8 ++++---
src/backend/commands/tablecmds.c | 1 +
src/backend/executor/execMain.c | 4 ++--
src/backend/executor/nodeModifyTable.c | 43 +++++++++++++++++++++++++++-------
src/include/catalog/partition.h | 3 ++-
src/test/regress/expected/insert.out | 24 ++++++++++++++++++-
src/test/regress/sql/insert.sql | 16 ++++++++++++-
7 files changed, 82 insertions(+), 17 deletions(-)
diff --git a/src/backend/catalog/partition.c b/src/backend/catalog/partition.c
index e540fc16d0..c0a5f98f3c 100644
--- a/src/backend/catalog/partition.c
+++ b/src/backend/catalog/partition.c
@@ -883,7 +883,8 @@ get_qual_from_partbound(Relation rel, Relation parent, Node *bound)
* different from the parent's.
*/
List *
-map_partition_varattnos(List *expr, Relation partrel, Relation parent)
+map_partition_varattnos(List *expr, int target_varno,
+ Relation partrel, Relation parent)
{
TupleDesc tupdesc = RelationGetDescr(parent);
AttrNumber attno;
@@ -908,7 +909,7 @@ map_partition_varattnos(List *expr, Relation partrel, Relation parent)
}
expr = (List *) map_variable_attnos((Node *) expr,
- 1, 0,
+ target_varno, 0,
part_attnos,
tupdesc->natts,
&found_whole_row);
@@ -1540,8 +1541,9 @@ generate_partition_qual(Relation rel)
* Change Vars to have partition's attnos instead of the parent's.
* We do this after we concatenate the parent's quals, because
* we want every Var in it to bear this relation's attnos.
+ * It's safe to assume varno = 1 here.
*/
- result = map_partition_varattnos(result, rel, parent);
+ result = map_partition_varattnos(result, 1, rel, parent);
/* Save a copy in the relcache */
oldcxt = MemoryContextSwitchTo(CacheMemoryContext);
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 06e43cbb3a..18cac9ad2d 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -13454,6 +13454,7 @@ ATExecAttachPartition(List **wqueue, Relation rel, PartitionCmd *cmd)
constr = linitial(partConstraint);
my_constr = make_ands_implicit((Expr *) constr);
tab->partition_constraint = map_partition_varattnos(my_constr,
+ 1,
part_rel,
rel);
/* keep our lock until commit */
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 67e46729f3..8cb9691056 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -1279,10 +1279,10 @@ InitResultRelInfo(ResultRelInfo *resultRelInfo,
/*
* This is not our own partition constraint, but rather an ancestor's.
* So any Vars in it bear the ancestor's attribute numbers. We must
- * switch them to our own.
+ * switch them to our own. (dummy varno = 1)
*/
if (partition_check != NIL)
- partition_check = map_partition_varattnos(partition_check,
+ partition_check = map_partition_varattnos(partition_check, 1,
resultRelationDesc,
partition_root);
}
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
index 4692427e60..982f15d490 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -262,7 +262,8 @@ ExecInsert(ModifyTableState *mtstate,
Relation resultRelationDesc;
Oid newId;
List *recheckIndexes = NIL;
- TupleTableSlot *oldslot = slot;
+ TupleTableSlot *oldslot = slot,
+ *result = NULL;
/*
* get the heap tuple out of the tuple table slot, making sure we have a
@@ -574,12 +575,6 @@ ExecInsert(ModifyTableState *mtstate,
list_free(recheckIndexes);
- if (saved_resultRelInfo)
- {
- resultRelInfo = saved_resultRelInfo;
- estate->es_result_relation_info = resultRelInfo;
- }
-
/*
* Check any WITH CHECK OPTION constraints from parent views. We are
* required to do this after testing all constraints and uniqueness
@@ -597,9 +592,12 @@ ExecInsert(ModifyTableState *mtstate,
/* Process RETURNING if present */
if (resultRelInfo->ri_projectReturning)
- return ExecProcessReturning(resultRelInfo, slot, planSlot);
+ result = ExecProcessReturning(resultRelInfo, slot, planSlot);
- return NULL;
+ if (saved_resultRelInfo)
+ estate->es_result_relation_info = saved_resultRelInfo;
+
+ return result;
}
/* ----------------------------------------------------------------
@@ -1786,6 +1784,7 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
{
TupleTableSlot *slot;
ExprContext *econtext;
+ List *returningList;
/*
* Initialize result tuple slot and assign its rowtype using the first
@@ -1818,6 +1817,32 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
resultRelInfo->ri_RelationDesc->rd_att);
resultRelInfo++;
}
+
+ /*
+ * Build a projection for each leaf partition rel. Note that we
+ * didn't build the returningList for each partition within the
+ * planner, but simple translation of the varattnos for each
+ * partition will suffice. This only occurs for the INSERT case;
+ * UPDATE/DELETE are handled above.
+ */
+ resultRelInfo = mtstate->mt_partitions;
+ returningList = linitial(node->returningLists);
+ for (i = 0; i < mtstate->mt_num_partitions; i++)
+ {
+ Relation partrel = resultRelInfo->ri_RelationDesc;
+ List *rlist,
+ *rliststate;
+
+ /* varno = node->nominalRelation */
+ rlist = map_partition_varattnos(returningList,
+ node->nominalRelation,
+ partrel, rel);
+ rliststate = (List *) ExecInitExpr((Expr *) rlist, &mtstate->ps);
+ resultRelInfo->ri_projectReturning =
+ ExecBuildProjectionInfo(rliststate, econtext, slot,
+ resultRelInfo->ri_RelationDesc->rd_att);
+ resultRelInfo++;
+ }
}
else
{
diff --git a/src/include/catalog/partition.h b/src/include/catalog/partition.h
index 537f0aad67..df7dcce331 100644
--- a/src/include/catalog/partition.h
+++ b/src/include/catalog/partition.h
@@ -77,7 +77,8 @@ extern bool partition_bounds_equal(PartitionKey key,
extern void check_new_partition_bound(char *relname, Relation parent, Node *bound);
extern Oid get_partition_parent(Oid relid);
extern List *get_qual_from_partbound(Relation rel, Relation parent, Node *bound);
-extern List *map_partition_varattnos(List *expr, Relation partrel, Relation parent);
+extern List *map_partition_varattnos(List *expr, int target_varno,
+ Relation partrel, Relation parent);
extern List *RelationGetPartitionQual(Relation rel);
/* For tuple routing */
diff --git a/src/test/regress/expected/insert.out b/src/test/regress/expected/insert.out
index b8e74caee8..81af3ef497 100644
--- a/src/test/regress/expected/insert.out
+++ b/src/test/regress/expected/insert.out
@@ -364,5 +364,27 @@ DETAIL: Failing row contains (1, 2).
insert into p1 (a, b) values (2, 3);
ERROR: new row for relation "p11" violates partition constraint
DETAIL: Failing row contains (3, 2).
+-- check that RETURNING works correctly with tuple-routing
+alter table p drop constraint check_b;
+create table p12 partition of p1 for values from (5) to (10);
+create table p2 (b int not null, a int not null);
+alter table p attach partition p2 for values from (1, 10) to (1, 20);
+create table p3 partition of p for values from (1, 20) to (1, 30);
+create table p4 (like p);
+alter table p4 drop a;
+alter table p4 add a int not null;
+alter table p attach partition p4 for values from (1, 30) to (1, 40);
+with ins (a, b, c) as
+ (insert into p (b, a) select s.a, 1 from generate_series(2, 39) s(a) returning tableoid::regclass, *)
+ select a, b, min(c), max(c) from ins group by a, b order by 1;
+ a | b | min | max
+-----+---+-----+-----
+ p11 | 1 | 2 | 4
+ p12 | 1 | 5 | 9
+ p2 | 1 | 10 | 19
+ p3 | 1 | 20 | 29
+ p4 | 1 | 30 | 39
+(5 rows)
+
-- cleanup
-drop table p, p1, p11;
+drop table p, p1, p11, p12, p2, p3, p4;
diff --git a/src/test/regress/sql/insert.sql b/src/test/regress/sql/insert.sql
index 2154d01c56..454e1ce2e7 100644
--- a/src/test/regress/sql/insert.sql
+++ b/src/test/regress/sql/insert.sql
@@ -226,5 +226,19 @@ insert into p values (1, 2);
-- selected by tuple-routing
insert into p1 (a, b) values (2, 3);
+-- check that RETURNING works correctly with tuple-routing
+alter table p drop constraint check_b;
+create table p12 partition of p1 for values from (5) to (10);
+create table p2 (b int not null, a int not null);
+alter table p attach partition p2 for values from (1, 10) to (1, 20);
+create table p3 partition of p for values from (1, 20) to (1, 30);
+create table p4 (like p);
+alter table p4 drop a;
+alter table p4 add a int not null;
+alter table p attach partition p4 for values from (1, 30) to (1, 40);
+with ins (a, b, c) as
+ (insert into p (b, a) select s.a, 1 from generate_series(2, 39) s(a) returning tableoid::regclass, *)
+ select a, b, min(c), max(c) from ins group by a, b order by 1;
+
-- cleanup
-drop table p, p1, p11;
+drop table p, p1, p11, p12, p2, p3, p4;
--
2.11.0
>From 25fb172bfd9c40c89c835e62610cbfb5852a9115 Mon Sep 17 00:00:00 2001
From: amit <amitlangot...@gmail.com>
Date: Fri, 6 Jan 2017 15:33:02 +0900
Subject: [PATCH 4/8] Fix some issues with views and partitioned tables
Automatically updatable views failed to handle partitioned tables.
Once that's fixed, WITH CHECK OPTIONS wouldn't work correctly without
the WCO expressions having been suitably converted for each partition
(think applying map_partition_varattnos to Vars in the WCO expressions
just like with partition constraint expressions).
Reported by: n/a
Patch by: Amit Langote
Reports: n/a
---
src/backend/executor/nodeModifyTable.c | 40 +++++++++++++++++++++++++++
src/backend/rewrite/rewriteHandler.c | 3 +-
src/test/regress/expected/updatable_views.out | 24 ++++++++++++++++
src/test/regress/sql/updatable_views.sql | 19 +++++++++++++
4 files changed, 85 insertions(+), 1 deletion(-)
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
index 982f15d490..57edfeab95 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -1778,6 +1778,46 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
}
/*
+ * Build WITH CHECK OPTION constraints for each leaf partition rel.
+ * Note that we didn't build the withCheckOptionList for each partition
+ * within the planner, but simple translation of the varattnos for each
+ * partition will suffice. This only occurs for the INSERT case;
+ * UPDATE/DELETE cases are handled above.
+ */
+ if (node->withCheckOptionLists != NIL && mtstate->mt_num_partitions > 0)
+ {
+ List *wcoList;
+
+ Assert(operation == CMD_INSERT);
+ resultRelInfo = mtstate->mt_partitions;
+ wcoList = linitial(node->withCheckOptionLists);
+ for (i = 0; i < mtstate->mt_num_partitions; i++)
+ {
+ Relation partrel = resultRelInfo->ri_RelationDesc;
+ List *mapped_wcoList;
+ List *wcoExprs = NIL;
+ ListCell *ll;
+
+ /* varno = node->nominalRelation */
+ mapped_wcoList = map_partition_varattnos(wcoList,
+ node->nominalRelation,
+ partrel, rel);
+ foreach(ll, mapped_wcoList)
+ {
+ WithCheckOption *wco = (WithCheckOption *) lfirst(ll);
+ ExprState *wcoExpr = ExecInitExpr((Expr *) wco->qual,
+ mtstate->mt_plans[i]);
+
+ wcoExprs = lappend(wcoExprs, wcoExpr);
+ }
+
+ resultRelInfo->ri_WithCheckOptions = mapped_wcoList;
+ resultRelInfo->ri_WithCheckOptionExprs = wcoExprs;
+ resultRelInfo++;
+ }
+ }
+
+ /*
* Initialize RETURNING projections if needed.
*/
if (node->returningLists)
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
index d1ff3b20b6..d3e44fb135 100644
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -2249,7 +2249,8 @@ view_query_is_auto_updatable(Query *viewquery, bool check_cols)
if (base_rte->rtekind != RTE_RELATION ||
(base_rte->relkind != RELKIND_RELATION &&
base_rte->relkind != RELKIND_FOREIGN_TABLE &&
- base_rte->relkind != RELKIND_VIEW))
+ base_rte->relkind != RELKIND_VIEW &&
+ base_rte->relkind != RELKIND_PARTITIONED_TABLE))
return gettext_noop("Views that do not select from a single table or view are not automatically updatable.");
if (base_rte->tablesample)
diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out
index 2da3c069e1..2ae3613cec 100644
--- a/src/test/regress/expected/updatable_views.out
+++ b/src/test/regress/expected/updatable_views.out
@@ -2367,3 +2367,27 @@ ERROR: new row violates check option for view "v1"
DETAIL: Failing row contains (-1, invalid).
DROP VIEW v1;
DROP TABLE t1;
+-- check that an auto-updatable view on a partitioned table works correctly
+create table p (a int, b int) partition by range (a, b);
+create table p1 (b int not null, a int not null) partition by range (b);
+create table p11 (like p1);
+alter table p11 drop a;
+alter table p11 add a int;
+alter table p11 drop a;
+alter table p11 add a int not null;
+alter table p1 attach partition p11 for values from (2) to (5);
+alter table p attach partition p1 for values from (1, 2) to (1, 10);
+create view pv as select * from p;
+insert into pv values (1, 2);
+select tableoid::regclass, * from p;
+ tableoid | a | b
+----------+---+---
+ p11 | 1 | 2
+(1 row)
+
+create view pv_wco as select * from p where a = 0 with check option;
+insert into pv_wco values (1, 2);
+ERROR: new row violates check option for view "pv_wco"
+DETAIL: Failing row contains (2, 1).
+drop view pv, pv_wco;
+drop table p, p1, p11;
diff --git a/src/test/regress/sql/updatable_views.sql b/src/test/regress/sql/updatable_views.sql
index ffc64d2de9..3c19edc8f7 100644
--- a/src/test/regress/sql/updatable_views.sql
+++ b/src/test/regress/sql/updatable_views.sql
@@ -1112,3 +1112,22 @@ INSERT INTO v1 VALUES (-1, 'invalid'); -- should fail
DROP VIEW v1;
DROP TABLE t1;
+
+-- check that an auto-updatable view on a partitioned table works correctly
+create table p (a int, b int) partition by range (a, b);
+create table p1 (b int not null, a int not null) partition by range (b);
+create table p11 (like p1);
+alter table p11 drop a;
+alter table p11 add a int;
+alter table p11 drop a;
+alter table p11 add a int not null;
+alter table p1 attach partition p11 for values from (2) to (5);
+alter table p attach partition p1 for values from (1, 2) to (1, 10);
+
+create view pv as select * from p;
+insert into pv values (1, 2);
+select tableoid::regclass, * from p;
+create view pv_wco as select * from p where a = 0 with check option;
+insert into pv_wco values (1, 2);
+drop view pv, pv_wco;
+drop table p, p1, p11;
--
2.11.0
>From 677f7cc8a7a337aee8baf11695783b28fc2308ba Mon Sep 17 00:00:00 2001
From: amit <amitlangot...@gmail.com>
Date: Tue, 10 Jan 2017 17:43:36 +0900
Subject: [PATCH 5/8] Fix some wrong thinking in check_new_partition_bound()
Because a given range bound in the PartitionBoundInfo.datums array
is sometimes a range lower bound and upper bound at other times, we
must be careful when assuming which, especially when interpreting
the result of partition_bound_bsearch which returns the index of the
greatest bound that is less than or equal to probe. Due to an error
in thinking about the same, the relevant code in
check_new_partition_bound() caused invalid partition (index = -1)
to be chosen as the partition being overlapped.
Also, we need not continue searching for even greater bound in
partition_bound_bsearch() once we find the first bound that is *equal*
to the probe, because we don't have duplicate datums. That spends
cycles needlessly. Per suggestion from Amul Sul.
Reported by: Amul Sul
Patch by: Amit Langote
Reports: https://www.postgresql.org/message-id/CAAJ_b94XgbqVoXMyxxs63CaqWoMS1o2gpHiU0F7yGnJBnvDc_A%40mail.gmail.com
---
src/backend/catalog/partition.c | 62 ++++++++++++++++++++++--------
src/test/regress/expected/create_table.out | 10 ++++-
src/test/regress/sql/create_table.sql | 4 ++
3 files changed, 60 insertions(+), 16 deletions(-)
diff --git a/src/backend/catalog/partition.c b/src/backend/catalog/partition.c
index c0a5f98f3c..5ca57f44fb 100644
--- a/src/backend/catalog/partition.c
+++ b/src/backend/catalog/partition.c
@@ -741,35 +741,64 @@ check_new_partition_bound(char *relname, Relation parent, Node *bound)
boundinfo->strategy == PARTITION_STRATEGY_RANGE);
/*
- * Find the greatest index of a range bound that is less
- * than or equal with the new lower bound.
+ * Firstly, find the greatest range bound that is less
+ * than or equal to the new lower bound.
*/
off1 = partition_bound_bsearch(key, boundinfo, lower, true,
&equal);
/*
- * If equal has been set to true, that means the new lower
- * bound is found to be equal with the bound at off1,
- * which clearly means an overlap with the partition at
- * index off1+1).
- *
- * Otherwise, check if there is a "gap" that could be
- * occupied by the new partition. In case of a gap, the
- * new upper bound should not cross past the upper
- * boundary of the gap, that is, off2 == off1 should be
- * true.
+ * off1 == -1 means that all existing bounds are greater
+ * than the new lower bound. In that case and the case
+ * where no partition is defined between the bounds at
+ * off1 and off1 + 1, we have a "gap" in the range that
+ * could be occupied by the new partition. We confirm if
+ * so by checking whether the new upper bound is confined
+ * within the gap.
*/
if (!equal && boundinfo->indexes[off1 + 1] < 0)
{
off2 = partition_bound_bsearch(key, boundinfo, upper,
true, &equal);
+ /*
+ * If the new upper bound is returned to be equal to
+ * the bound at off2, the latter must be the upper
+ * bound of some partition with which the new partition
+ * clearly overlaps.
+ *
+ * Also, if bound at off2 is not same as the one
+ * returned for the new lower bound (IOW,
+ * off1 != off2), then the new partition overlaps at
+ * least one partition.
+ */
if (equal || off1 != off2)
{
overlap = true;
- with = boundinfo->indexes[off2 + 1];
+ /*
+ * The bound at off2 could be the lower bound of
+ * the partition with which the new partition
+ * overlaps. In that case, use the upper bound
+ * (that is, the bound at off2 + 1) to get the
+ * index of that partition.
+ */
+ if (boundinfo->indexes[off2] < 0)
+ with = boundinfo->indexes[off2 + 1];
+ else
+ with = boundinfo->indexes[off2];
}
}
+ /*
+ * If equal has been set to true or if there is no "gap"
+ * between the bound at off1 and that at off1 + 1, the new
+ * partition will overlap some partition. In the former
+ * case, the new lower bound is found to be equal to the
+ * bound at off1, which could only ever be true if the
+ * latter is the lower bound of some partition. It's
+ * clear in such a case that the new partition overlaps
+ * that partition, whose index we get using its upper
+ * bound (that is, using the bound at off1 + 1).
+ */
else
{
overlap = true;
@@ -1972,8 +2001,8 @@ partition_bound_cmp(PartitionKey key, PartitionBoundInfo boundinfo,
}
/*
- * Binary search on a collection of partition bounds. Returns greatest index
- * of bound in array boundinfo->datums which is less or equal with *probe.
+ * Binary search on a collection of partition bounds. Returns greatest
+ * bound in array boundinfo->datums which is less than or equal to *probe
* If all bounds in the array are greater than *probe, -1 is returned.
*
* *probe could either be a partition bound or a Datum array representing
@@ -2005,6 +2034,9 @@ partition_bound_bsearch(PartitionKey key, PartitionBoundInfo boundinfo,
{
lo = mid;
*is_equal = (cmpval == 0);
+
+ if (*is_equal)
+ break;
}
else
hi = mid - 1;
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out
index 8a2818bf97..37edd6cde8 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -551,6 +551,12 @@ ERROR: partition "fail_part" would overlap partition "part0"
CREATE TABLE part1 PARTITION OF range_parted2 FOR VALUES FROM (1) TO (10);
CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (9) TO (unbounded);
ERROR: partition "fail_part" would overlap partition "part1"
+CREATE TABLE part2 PARTITION OF range_parted2 FOR VALUES FROM (20) TO (30);
+CREATE TABLE part3 PARTITION OF range_parted2 FOR VALUES FROM (30) TO (40);
+CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (10) TO (30);
+ERROR: partition "fail_part" would overlap partition "part2"
+CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (10) TO (50);
+ERROR: partition "fail_part" would overlap partition "part3"
-- now check for multi-column range partition key
CREATE TABLE range_parted3 (
a int,
@@ -655,13 +661,15 @@ table part_c depends on table parted
table part_c_1_10 depends on table part_c
HINT: Use DROP ... CASCADE to drop the dependent objects too.
DROP TABLE parted, list_parted, range_parted, list_parted2, range_parted2, range_parted3 CASCADE;
-NOTICE: drop cascades to 14 other objects
+NOTICE: drop cascades to 16 other objects
DETAIL: drop cascades to table part00
drop cascades to table part10
drop cascades to table part11
drop cascades to table part12
drop cascades to table part0
drop cascades to table part1
+drop cascades to table part2
+drop cascades to table part3
drop cascades to table part_null_z
drop cascades to table part_ab
drop cascades to table part_1
diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql
index 2ab652a055..f960d36fd0 100644
--- a/src/test/regress/sql/create_table.sql
+++ b/src/test/regress/sql/create_table.sql
@@ -519,6 +519,10 @@ CREATE TABLE part0 PARTITION OF range_parted2 FOR VALUES FROM (unbounded) TO (1)
CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (unbounded) TO (2);
CREATE TABLE part1 PARTITION OF range_parted2 FOR VALUES FROM (1) TO (10);
CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (9) TO (unbounded);
+CREATE TABLE part2 PARTITION OF range_parted2 FOR VALUES FROM (20) TO (30);
+CREATE TABLE part3 PARTITION OF range_parted2 FOR VALUES FROM (30) TO (40);
+CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (10) TO (30);
+CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (10) TO (50);
-- now check for multi-column range partition key
CREATE TABLE range_parted3 (
--
2.11.0
>From 230d8b1c96334d003b1e52da1843a7ecf61d9440 Mon Sep 17 00:00:00 2001
From: amit <amitlangot...@gmail.com>
Date: Mon, 16 Jan 2017 14:37:50 +0900
Subject: [PATCH 6/8] Avoid code duplication in map_partition_varattnos()
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Code to map attribute numbers in map_partition_varattnos() duplicates
what convert_tuples_by_name_map() does. Avoid that.
Amit Langote, pointed out by Ãlvaro Herrera.
---
src/backend/catalog/partition.c | 21 ++++-----------------
1 file changed, 4 insertions(+), 17 deletions(-)
diff --git a/src/backend/catalog/partition.c b/src/backend/catalog/partition.c
index 5ca57f44fb..0a50808945 100644
--- a/src/backend/catalog/partition.c
+++ b/src/backend/catalog/partition.c
@@ -915,32 +915,19 @@ List *
map_partition_varattnos(List *expr, int target_varno,
Relation partrel, Relation parent)
{
- TupleDesc tupdesc = RelationGetDescr(parent);
- AttrNumber attno;
AttrNumber *part_attnos;
bool found_whole_row;
if (expr == NIL)
return NIL;
- part_attnos = (AttrNumber *) palloc0(tupdesc->natts * sizeof(AttrNumber));
- for (attno = 1; attno <= tupdesc->natts; attno++)
- {
- Form_pg_attribute attribute = tupdesc->attrs[attno - 1];
- char *attname = NameStr(attribute->attname);
- AttrNumber part_attno;
-
- if (attribute->attisdropped)
- continue;
-
- part_attno = get_attnum(RelationGetRelid(partrel), attname);
- part_attnos[attno - 1] = part_attno;
- }
-
+ part_attnos = convert_tuples_by_name_map(RelationGetDescr(partrel),
+ RelationGetDescr(parent),
+ gettext_noop("could not convert row type"));
expr = (List *) map_variable_attnos((Node *) expr,
target_varno, 0,
part_attnos,
- tupdesc->natts,
+ RelationGetDescr(parent)->natts,
&found_whole_row);
/* There can never be a whole-row reference here */
if (found_whole_row)
--
2.11.0
>From b4b131bcef2a239811e56491faebb199eeff859c Mon Sep 17 00:00:00 2001
From: amit <amitlangot...@gmail.com>
Date: Mon, 26 Dec 2016 17:44:14 +0900
Subject: [PATCH 7/8] Avoid tuple coversion in common partitioning cases
Currently, the tuple conversion is performed after a tuple is routed,
even if the attributes of a target leaf partition map one-to-one with
those of the root table, which is wasteful. Avoid that by making
convert_tuples_by_name() return a NULL map for such cases.
Reported by: n/a
Patch by: Amit Langote
Reports: n/a
---
src/backend/access/common/tupconvert.c | 8 ++++++--
src/backend/catalog/partition.c | 5 ++---
src/backend/commands/analyze.c | 1 +
src/backend/executor/execMain.c | 1 +
src/backend/executor/execQual.c | 2 +-
src/include/access/tupconvert.h | 1 +
6 files changed, 12 insertions(+), 6 deletions(-)
diff --git a/src/backend/access/common/tupconvert.c b/src/backend/access/common/tupconvert.c
index b17ceafa6e..bbbd271e1f 100644
--- a/src/backend/access/common/tupconvert.c
+++ b/src/backend/access/common/tupconvert.c
@@ -202,6 +202,7 @@ convert_tuples_by_position(TupleDesc indesc,
TupleConversionMap *
convert_tuples_by_name(TupleDesc indesc,
TupleDesc outdesc,
+ bool consider_typeid,
const char *msg)
{
TupleConversionMap *map;
@@ -216,11 +217,14 @@ convert_tuples_by_name(TupleDesc indesc,
/*
* Check to see if the map is one-to-one and the tuple types are the same.
* (We check the latter because if they're not, we want to do conversion
- * to inject the right OID into the tuple datum.)
+ * to inject the right OID into the tuple datum. In the partitioning
+ * case (!consider_typeid), tdhasoids must always match between indesc
+ * and outdesc, so we need not require tdtypeid's to be the same.)
*/
if (indesc->natts == outdesc->natts &&
- indesc->tdtypeid == outdesc->tdtypeid)
+ (!consider_typeid || indesc->tdtypeid == outdesc->tdtypeid))
{
+ Assert(!consider_typeid && indesc->tdhasoid == outdesc->tdhasoid);
same = true;
for (i = 0; i < n; i++)
{
diff --git a/src/backend/catalog/partition.c b/src/backend/catalog/partition.c
index 0a50808945..a49a2110db 100644
--- a/src/backend/catalog/partition.c
+++ b/src/backend/catalog/partition.c
@@ -1071,7 +1071,7 @@ RelationGetPartitionDispatchInfo(Relation rel, int lockmode,
*/
pd[i]->tupslot = MakeSingleTupleTableSlot(tupdesc);
pd[i]->tupmap = convert_tuples_by_name(RelationGetDescr(parent),
- tupdesc,
+ tupdesc, false,
gettext_noop("could not convert row type"));
}
else
@@ -1681,12 +1681,11 @@ get_partition_for_tuple(PartitionDispatch *pd,
return -1;
}
- if (myslot != NULL)
+ if (myslot != NULL && map != NULL)
{
HeapTuple tuple = ExecFetchSlotTuple(slot);
ExecClearTuple(myslot);
- Assert(map != NULL);
tuple = do_convert_tuple(tuple, map);
ExecStoreTuple(tuple, myslot, InvalidBuffer, true);
slot = myslot;
diff --git a/src/backend/commands/analyze.c b/src/backend/commands/analyze.c
index e3e1a53072..31bec9f961 100644
--- a/src/backend/commands/analyze.c
+++ b/src/backend/commands/analyze.c
@@ -1419,6 +1419,7 @@ acquire_inherited_sample_rows(Relation onerel, int elevel,
map = convert_tuples_by_name(RelationGetDescr(childrel),
RelationGetDescr(onerel),
+ true,
gettext_noop("could not convert row type"));
if (map != NULL)
{
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 8cb9691056..410f08a2c7 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -3152,6 +3152,7 @@ ExecSetupPartitionTupleRouting(Relation rel,
* partition from the parent's type to the partition's.
*/
(*tup_conv_maps)[i] = convert_tuples_by_name(tupDesc, part_tupdesc,
+ false,
gettext_noop("could not convert row type"));
InitResultRelInfo(leaf_part_rri,
diff --git a/src/backend/executor/execQual.c b/src/backend/executor/execQual.c
index eed7e95c75..5f637006d1 100644
--- a/src/backend/executor/execQual.c
+++ b/src/backend/executor/execQual.c
@@ -2920,7 +2920,7 @@ ExecEvalConvertRowtype(ConvertRowtypeExprState *cstate,
/* prepare map from old to new attribute numbers */
cstate->map = convert_tuples_by_name(cstate->indesc,
- cstate->outdesc,
+ cstate->outdesc, true,
gettext_noop("could not convert row type"));
cstate->initialized = true;
diff --git a/src/include/access/tupconvert.h b/src/include/access/tupconvert.h
index e86cfd56c8..231fe872d7 100644
--- a/src/include/access/tupconvert.h
+++ b/src/include/access/tupconvert.h
@@ -36,6 +36,7 @@ extern TupleConversionMap *convert_tuples_by_position(TupleDesc indesc,
extern TupleConversionMap *convert_tuples_by_name(TupleDesc indesc,
TupleDesc outdesc,
+ bool consider_typeid,
const char *msg);
extern AttrNumber *convert_tuples_by_name_map(TupleDesc indesc,
--
2.11.0
>From 1c9fc0d70a66ff3a167a6e2ab0f8a3b48d1d969d Mon Sep 17 00:00:00 2001
From: amit <amitlangot...@gmail.com>
Date: Thu, 19 Jan 2017 13:35:18 +0900
Subject: [PATCH 8/8] Avoid DROP TABLE ... CASCADE in more partitioning tests
The output produced by specifying CASCADE is unreliable and causes
test failures occasionally. This time, fix create_table.sql and
alter_table.sql to do that.
Patch by: Amit Langote
---
src/test/regress/expected/alter_table.out | 18 ++++++----------
src/test/regress/expected/create_table.out | 34 ++++++------------------------
src/test/regress/sql/alter_table.sql | 10 +++++----
src/test/regress/sql/create_table.sql | 14 ++++++------
4 files changed, 26 insertions(+), 50 deletions(-)
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index c510761c68..76b9c2d372 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -3330,15 +3330,10 @@ ALTER TABLE list_parted2 DROP COLUMN b;
ERROR: cannot drop column named in partition key
ALTER TABLE list_parted2 ALTER COLUMN b TYPE text;
ERROR: cannot alter type of column named in partition key
--- cleanup
-DROP TABLE list_parted, list_parted2, range_parted CASCADE;
-NOTICE: drop cascades to 6 other objects
-DETAIL: drop cascades to table part1
-drop cascades to table part2
-drop cascades to table part_2
-drop cascades to table part_5
-drop cascades to table part_5_a
-drop cascades to table part_1
+-- cleanup: avoid using CASCADE
+DROP TABLE list_parted, part_1;
+DROP TABLE list_parted2, part_2, part_5, part_5_a;
+DROP TABLE range_parted, part1, part2;
-- more tests for certain multi-level partitioning scenarios
create table p (a int, b int) partition by range (a, b);
create table p1 (b int, a int not null) partition by range (b);
@@ -3367,6 +3362,5 @@ insert into p1 (a, b) values (2, 3);
-- check that partition validation scan correctly detects violating rows
alter table p attach partition p1 for values from (1, 2) to (1, 10);
ERROR: partition constraint is violated by some row
--- cleanup
-drop table p, p1 cascade;
-NOTICE: drop cascades to table p11
+-- cleanup: avoid using CASCADE
+drop table p, p1, p11;
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out
index 37edd6cde8..6caa9c2407 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -651,30 +651,10 @@ Check constraints:
"check_a" CHECK (length(a) > 0)
Number of partitions: 3 (Use \d+ to list them.)
--- partitions cannot be dropped directly
-DROP TABLE part_a;
--- need to specify CASCADE to drop partitions along with the parent
-DROP TABLE parted;
-ERROR: cannot drop table parted because other objects depend on it
-DETAIL: table part_b depends on table parted
-table part_c depends on table parted
-table part_c_1_10 depends on table part_c
-HINT: Use DROP ... CASCADE to drop the dependent objects too.
-DROP TABLE parted, list_parted, range_parted, list_parted2, range_parted2, range_parted3 CASCADE;
-NOTICE: drop cascades to 16 other objects
-DETAIL: drop cascades to table part00
-drop cascades to table part10
-drop cascades to table part11
-drop cascades to table part12
-drop cascades to table part0
-drop cascades to table part1
-drop cascades to table part2
-drop cascades to table part3
-drop cascades to table part_null_z
-drop cascades to table part_ab
-drop cascades to table part_1
-drop cascades to table part_2
-drop cascades to table part_null
-drop cascades to table part_b
-drop cascades to table part_c
-drop cascades to table part_c_1_10
+-- cleanup: avoid using CASCADE
+DROP TABLE parted, part_a, part_b, part_c, part_c_1_10;
+DROP TABLE list_parted, part_1, part_2, part_null;
+DROP TABLE range_parted;
+DROP TABLE list_parted2, part_ab, part_null_z;
+DROP TABLE range_parted2, part0, part1, part2, part3;
+DROP TABLE range_parted3, part00, part10, part11, part12;
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index 86d27ac35c..4611cbb731 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -2189,8 +2189,10 @@ ALTER TABLE part_2 INHERIT inh_test;
ALTER TABLE list_parted2 DROP COLUMN b;
ALTER TABLE list_parted2 ALTER COLUMN b TYPE text;
--- cleanup
-DROP TABLE list_parted, list_parted2, range_parted CASCADE;
+-- cleanup: avoid using CASCADE
+DROP TABLE list_parted, part_1;
+DROP TABLE list_parted2, part_2, part_5, part_5_a;
+DROP TABLE range_parted, part1, part2;
-- more tests for certain multi-level partitioning scenarios
create table p (a int, b int) partition by range (a, b);
@@ -2215,5 +2217,5 @@ insert into p1 (a, b) values (2, 3);
-- check that partition validation scan correctly detects violating rows
alter table p attach partition p1 for values from (1, 2) to (1, 10);
--- cleanup
-drop table p, p1 cascade;
+-- cleanup: avoid using CASCADE
+drop table p, p1, p11;
diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql
index f960d36fd0..8242e7328d 100644
--- a/src/test/regress/sql/create_table.sql
+++ b/src/test/regress/sql/create_table.sql
@@ -585,10 +585,10 @@ CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES FROM (1) TO (10);
-- returned.
\d parted
--- partitions cannot be dropped directly
-DROP TABLE part_a;
-
--- need to specify CASCADE to drop partitions along with the parent
-DROP TABLE parted;
-
-DROP TABLE parted, list_parted, range_parted, list_parted2, range_parted2, range_parted3 CASCADE;
+-- cleanup: avoid using CASCADE
+DROP TABLE parted, part_a, part_b, part_c, part_c_1_10;
+DROP TABLE list_parted, part_1, part_2, part_null;
+DROP TABLE range_parted;
+DROP TABLE list_parted2, part_ab, part_null_z;
+DROP TABLE range_parted2, part0, part1, part2, part3;
+DROP TABLE range_parted3, part00, part10, part11, part12;
--
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
