HAWQ-404. Close each partition after inserting when optimizer guarantees input is sorted on partition-id.
Project: http://git-wip-us.apache.org/repos/asf/incubator-hawq/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-hawq/commit/6e6890ae Tree: http://git-wip-us.apache.org/repos/asf/incubator-hawq/tree/6e6890ae Diff: http://git-wip-us.apache.org/repos/asf/incubator-hawq/diff/6e6890ae Branch: refs/heads/master Commit: 6e6890ae7886b050aeb9c9d7e885fc84eb99937c Parents: 2c38b42 Author: George Caragea <gcara...@pivotal.io> Authored: Mon Feb 29 22:21:13 2016 -0800 Committer: Haisheng Yuan <hy...@pivotal.io> Committed: Wed Mar 2 18:27:22 2016 -0800 ---------------------------------------------------------------------- src/backend/executor/execDML.c | 99 +++++++++++------- src/backend/executor/execMain.c | 13 +-- src/backend/executor/execUtils.c | 2 +- src/backend/executor/nodeDML.c | 2 +- src/backend/utils/misc/guc.c | 2 +- src/include/executor/execDML.h | 3 +- src/include/nodes/execnodes.h | 7 +- src/test/regress/expected/goh_partition.out | 126 +++++++++++++++++++++++ src/test/regress/sql/goh_partition.sql | 68 ++++++++++++ 9 files changed, 271 insertions(+), 51 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/6e6890ae/src/backend/executor/execDML.c ---------------------------------------------------------------------- diff --git a/src/backend/executor/execDML.c b/src/backend/executor/execDML.c index d36e9ed..d6f05de 100644 --- a/src/backend/executor/execDML.c +++ b/src/backend/executor/execDML.c @@ -158,10 +158,11 @@ reconstructMatchingTupleSlot(TupleTableSlot *slot, ResultRelInfo *resultRelInfo) */ void ExecInsert(TupleTableSlot *slot, - DestReceiver *dest, - EState *estate, - PlanGenerator planGen, - bool isUpdate) + DestReceiver *dest, + EState *estate, + PlanGenerator planGen, + bool isUpdate, + bool isInputSorted) { void *tuple = NULL; ResultRelInfo *resultRelInfo = NULL; @@ -184,7 +185,7 @@ ExecInsert(TupleTableSlot *slot, resultRelInfo = slot_get_partition(slot, estate); estate->es_result_relation_info = resultRelInfo; - if (NULL != resultRelInfo->ri_parquetSendBack) + if (NULL != resultRelInfo->ri_insertSendBack) { /* * The Parquet part we are about to insert into @@ -203,59 +204,82 @@ ExecInsert(TupleTableSlot *slot, * inserted into (GPSQL-2291). */ Oid new_part_oid = resultRelInfo->ri_RelationDesc->rd_id; - if (gp_parquet_insert_sort && + + if (isInputSorted && PLANGEN_OPTIMIZER == planGen && - InvalidOid != estate->es_last_parq_part && - new_part_oid != estate->es_last_parq_part) + InvalidOid != estate->es_last_inserted_part && + new_part_oid != estate->es_last_inserted_part) { Assert(NULL != estate->es_partition_state->result_partition_hash); ResultPartHashEntry *entry = hash_search(estate->es_partition_state->result_partition_hash, - &estate->es_last_parq_part, - HASH_FIND, - NULL /* found */); + &estate->es_last_inserted_part, + HASH_FIND, + NULL /* found */); Assert(NULL != entry); Assert(entry->offset < estate->es_num_result_relations); ResultRelInfo *oldResultRelInfo = & estate->es_result_relations[entry->offset]; + Assert(NULL != oldResultRelInfo); - elog(DEBUG1, "Switching from old part oid=%d name=[%s] to new part oid=%d name=[%s]", - estate->es_last_parq_part, - oldResultRelInfo->ri_RelationDesc->rd_rel->relname.data, - new_part_oid, - resultRelInfo->ri_RelationDesc->rd_rel->relname.data); - - /* - * We are opening a new partition, and the last partition we - * inserted into was a Parquet part. Let's close the old - * parquet insert descriptor to free the memory before - * opening the new one. - */ - ParquetInsertDescData *oldInsertDesc = oldResultRelInfo->ri_parquetInsertDesc; /* * We need to preserve the "sendback" information that needs to be * sent back to the QD process from this part. * Compute it here, and store it for later use. */ - QueryContextDispatchingSendBack sendback = - CreateQueryContextDispatchingSendBack(1); + QueryContextDispatchingSendBack sendback = CreateQueryContextDispatchingSendBack(1); sendback->relid = RelationGetRelid(oldResultRelInfo->ri_RelationDesc); - oldInsertDesc->sendback = sendback; - parquet_insert_finish(oldInsertDesc); - /* Store the sendback information in the resultRelInfo for this part */ - oldResultRelInfo->ri_parquetSendBack = sendback; + Relation oldRelation = oldResultRelInfo->ri_RelationDesc; + if (RelationIsAoRows(oldRelation)) + { + AppendOnlyInsertDescData *oldInsertDesc = oldResultRelInfo->ri_aoInsertDesc; + Assert(NULL != oldInsertDesc); + + elog(DEBUG1, "AO: Switching from old part oid=%d name=[%s] to new part oid=%d name=[%s]", + estate->es_last_inserted_part, + oldResultRelInfo->ri_RelationDesc->rd_rel->relname.data, + new_part_oid, + resultRelInfo->ri_RelationDesc->rd_rel->relname.data); - /* Record in the resultRelInfo that we closed the parquet insert descriptor */ - oldResultRelInfo->ri_parquetInsertDesc = NULL; + oldInsertDesc->sendback = sendback; - /* Reset the last parquet part Oid, it's now closed */ - estate->es_last_parq_part = InvalidOid; + appendonly_insert_finish(oldInsertDesc); + oldResultRelInfo->ri_aoInsertDesc = NULL; + + } + else if (RelationIsParquet(oldRelation)) + { + ParquetInsertDescData *oldInsertDesc = oldResultRelInfo->ri_parquetInsertDesc; + Assert(NULL != oldInsertDesc); + + elog(DEBUG1, "PARQ: Switching from old part oid=%d name=[%s] to new part oid=%d name=[%s]", + estate->es_last_inserted_part, + oldResultRelInfo->ri_RelationDesc->rd_rel->relname.data, + new_part_oid, + resultRelInfo->ri_RelationDesc->rd_rel->relname.data); + + oldInsertDesc->sendback = sendback; + + parquet_insert_finish(oldInsertDesc); + oldResultRelInfo->ri_parquetInsertDesc = NULL; + + } + else + { + Assert(false && "Unreachable"); + } + + /* Store the sendback information in the resultRelInfo for this part */ + oldResultRelInfo->ri_insertSendBack = sendback; + + estate->es_last_inserted_part = InvalidOid; } - } + + } else { resultRelInfo = estate->es_result_relation_info; @@ -362,7 +386,7 @@ ExecInsert(TupleTableSlot *slot, resultRelInfo->ri_aoInsertDesc = appendonly_insert_init(resultRelationDesc, segfileinfo); - + estate->es_last_inserted_part = resultRelationDesc->rd_id; } appendonly_insert(resultRelInfo->ri_aoInsertDesc, tuple, &newId, &aoTupleId); @@ -391,8 +415,7 @@ ExecInsert(TupleTableSlot *slot, * in estate, so that we can close it when switching to a * new partition (GPSQL-2291) */ - elog(DEBUG1, "Saving es_last_parq_part. Old=%d, new=%d", estate->es_last_parq_part, resultRelationDesc->rd_id); - estate->es_last_parq_part = resultRelationDesc->rd_id; + estate->es_last_inserted_part = resultRelationDesc->rd_id; } newId = parquet_insert(resultRelInfo->ri_parquetInsertDesc, partslot); http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/6e6890ae/src/backend/executor/execMain.c ---------------------------------------------------------------------- diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c index 1c215b0..6fa5cd2 100644 --- a/src/backend/executor/execMain.c +++ b/src/backend/executor/execMain.c @@ -2927,7 +2927,7 @@ ExecEndPlan(PlanState *planstate, EState *estate) { if (resultRelInfo->ri_aoInsertDesc) ++aocount; - if (resultRelInfo->ri_parquetInsertDesc || resultRelInfo->ri_parquetSendBack) + if (resultRelInfo->ri_parquetInsertDesc || resultRelInfo->ri_insertSendBack) ++aocount; resultRelInfo++; } @@ -2946,6 +2946,7 @@ ExecEndPlan(PlanState *planstate, EState *estate) /* end (flush) the INSERT operation in the access layer */ if (resultRelInfo->ri_aoInsertDesc) { + sendback = CreateQueryContextDispatchingSendBack(1); resultRelInfo->ri_aoInsertDesc->sendback = sendback; sendback->relid = RelationGetRelid(resultRelInfo->ri_RelationDesc); @@ -2956,9 +2957,9 @@ ExecEndPlan(PlanState *planstate, EState *estate) /*need add processing for parquet insert desc*/ if (resultRelInfo->ri_parquetInsertDesc){ - AssertImply(resultRelInfo->ri_parquetSendBack, gp_parquet_insert_sort); + AssertImply(resultRelInfo->ri_insertSendBack, gp_parquet_insert_sort); - if (NULL != resultRelInfo->ri_parquetSendBack) + if (NULL != resultRelInfo->ri_insertSendBack) { /* * The Parquet part we just finished inserting into already @@ -2984,10 +2985,10 @@ ExecEndPlan(PlanState *planstate, EState *estate) * in the resultRelInfo, since the ri_parquetInsertDesc is freed * (GPSQL-2291) */ - if (NULL != resultRelInfo->ri_parquetSendBack) + if (NULL != resultRelInfo->ri_insertSendBack) { Assert(NULL == sendback); - sendback = resultRelInfo->ri_parquetSendBack; + sendback = resultRelInfo->ri_insertSendBack; } if (resultRelInfo->ri_extInsertDesc) @@ -3390,7 +3391,7 @@ lmark: ; break; case CMD_INSERT: - ExecInsert(slot, dest, estate, PLANGEN_PLANNER, false /* isUpdate */); + ExecInsert(slot, dest, estate, PLANGEN_PLANNER, false /* isUpdate */, false /* isInputSorted */); result = NULL; break; http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/6e6890ae/src/backend/executor/execUtils.c ---------------------------------------------------------------------- diff --git a/src/backend/executor/execUtils.c b/src/backend/executor/execUtils.c index eb1124d..780fa72 100644 --- a/src/backend/executor/execUtils.c +++ b/src/backend/executor/execUtils.c @@ -278,7 +278,7 @@ InternalCreateExecutorState(MemoryContext qcontext, bool is_subquery) estate->es_result_relations = NULL; estate->es_num_result_relations = 0; estate->es_result_relation_info = NULL; - estate->es_last_parq_part = InvalidOid; + estate->es_last_inserted_part = InvalidOid; estate->es_junkFilter = NULL; http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/6e6890ae/src/backend/executor/nodeDML.c ---------------------------------------------------------------------- diff --git a/src/backend/executor/nodeDML.c b/src/backend/executor/nodeDML.c index db103ce..5ba48df 100644 --- a/src/backend/executor/nodeDML.c +++ b/src/backend/executor/nodeDML.c @@ -120,7 +120,7 @@ ExecDML(DMLState *node) */ ExecInsert(node->cleanedUpSlot, NULL /* destReceiver */, node->ps.state, PLANGEN_OPTIMIZER /* Plan origin */, - isUpdate); + isUpdate, plannode->inputSorted); } else /* DML_DELETE */ { http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/6e6890ae/src/backend/utils/misc/guc.c ---------------------------------------------------------------------- diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c index 4e54ae8..b9a5204 100644 --- a/src/backend/utils/misc/guc.c +++ b/src/backend/utils/misc/guc.c @@ -6150,7 +6150,7 @@ static struct config_int ConfigureNamesInt[] = {"optimizer_parts_to_force_sort_on_insert", PGC_USERSET, DEVELOPER_OPTIONS, gettext_noop("Minimum number of partitions required to force sorting tuples during insertion in an append only row-oriented partitioned table"), NULL, - GUC_NOT_IN_SAMPLE + GUC_NOT_IN_SAMPLE | GUC_GPDB_ADDOPT }, &optimizer_parts_to_force_sort_on_insert, INT_MAX, 0, INT_MAX, NULL, NULL http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/6e6890ae/src/include/executor/execDML.h ---------------------------------------------------------------------- diff --git a/src/include/executor/execDML.h b/src/include/executor/execDML.h index f1f45c8..886b585 100644 --- a/src/include/executor/execDML.h +++ b/src/include/executor/execDML.h @@ -54,7 +54,8 @@ ExecInsert(TupleTableSlot *slot, DestReceiver *dest, EState *estate, PlanGenerator planGen, - bool isUpdate); + bool isUpdate, + bool isInputSorted); extern void ExecDelete(ItemPointer tupleid, http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/6e6890ae/src/include/nodes/execnodes.h ---------------------------------------------------------------------- diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h index 98c2ee6..7a2e733 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -278,7 +278,7 @@ typedef struct JunkFilter * aoInsertDesc context for appendonly relation buffered INSERT * extInsertDesc context for external table INSERT * parquetInsertDesc context for parquet table INSERT - * parquetSendBack information to be sent back to dispatch after INSERT in a parquet table + * insertSendBack information to be sent back to dispatch after INSERT in a parquet or AO table * aosegno the AO segfile we inserted into. * aoprocessed tuples processed for AO * partInsertMap map input attrno to target attrno @@ -307,7 +307,8 @@ typedef struct ResultRelInfo struct ExternalInsertDescData *ri_extInsertDesc; struct ParquetInsertDescData *ri_parquetInsertDesc; - struct QueryContextDispatchingSendBackData *ri_parquetSendBack; + + struct QueryContextDispatchingSendBackData *ri_insertSendBack; List *ri_aosegnos; @@ -501,7 +502,7 @@ typedef struct EState ResultRelInfo *es_result_relation_info; /* currently active array elt */ JunkFilter *es_junkFilter; /* currently active junk filter */ - Oid es_last_parq_part; /* The Oid of the last parquet partition we opened for insertion */ + Oid es_last_inserted_part; /* The Oid of the last partition we opened for insertion */ /* partitioning info for target relation */ PartitionNode *es_result_partitions; http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/6e6890ae/src/test/regress/expected/goh_partition.out ---------------------------------------------------------------------- diff --git a/src/test/regress/expected/goh_partition.out b/src/test/regress/expected/goh_partition.out index d636126..b26a76d 100755 --- a/src/test/regress/expected/goh_partition.out +++ b/src/test/regress/expected/goh_partition.out @@ -2197,3 +2197,129 @@ NOTICE: CREATE TABLE will create partition "rank3_1_prt_girls_2_prt_jan04_3_prt NOTICE: CREATE TABLE will create partition "rank3_1_prt_girls_2_prt_jan05_3_prt_mass" for table "rank3_1_prt_girls_2_prt_jan05" NOTICE: CREATE TABLE will create partition "rank3_1_prt_girls_2_prt_jan05_3_prt_cali" for table "rank3_1_prt_girls_2_prt_jan05" NOTICE: CREATE TABLE will create partition "rank3_1_prt_girls_2_prt_jan05_3_prt_ohio" for table "rank3_1_prt_girls_2_prt_jan05" +-- Tests for sort operator before insert with AO and PARQUET tables (HAWQ-404) +-- A GUC's value is set to less than the number of partitions in the example table, so that sort is activated. +DROP TABLE IF EXISTS ch_sort_src, ch_sort_aodest, ch_sort_pqdest, ch_sort_aopqdest, ch_sort__pq_table; +SET optimizer_parts_to_force_sort_on_insert = 5; +CREATE TABLE ch_sort_src (id int, year int, month int, day int, region text) +DISTRIBUTED BY (month); +INSERT INTO ch_sort_src select i, 2000 + i, i % 12, (2*i) % 30, i::text from generate_series(0, 99) i; +-- AO partitioned table +CREATE TABLE ch_sort_aodest (id int, year int, month int, day int, region text) +WITH (APPENDONLY=TRUE) +DISTRIBUTED BY (id) +PARTITION BY RANGE (year) +( + START (2002) END (2010) EVERY (1), + DEFAULT PARTITION outlying_years +); +NOTICE: CREATE TABLE will create partition "ch_sort_aodest_1_prt_outlying_years" for table "ch_sort_aodest" +NOTICE: CREATE TABLE will create partition "ch_sort_aodest_1_prt_2" for table "ch_sort_aodest" +NOTICE: CREATE TABLE will create partition "ch_sort_aodest_1_prt_3" for table "ch_sort_aodest" +NOTICE: CREATE TABLE will create partition "ch_sort_aodest_1_prt_4" for table "ch_sort_aodest" +NOTICE: CREATE TABLE will create partition "ch_sort_aodest_1_prt_5" for table "ch_sort_aodest" +NOTICE: CREATE TABLE will create partition "ch_sort_aodest_1_prt_6" for table "ch_sort_aodest" +NOTICE: CREATE TABLE will create partition "ch_sort_aodest_1_prt_7" for table "ch_sort_aodest" +NOTICE: CREATE TABLE will create partition "ch_sort_aodest_1_prt_8" for table "ch_sort_aodest" +NOTICE: CREATE TABLE will create partition "ch_sort_aodest_1_prt_9" for table "ch_sort_aodest" +-- PARQUET partitioned table +CREATE TABLE ch_sort_pqdest (id int, year int, month int, day int, region text) +WITH (APPENDONLY=TRUE, ORIENTATION = PARQUET) +DISTRIBUTED BY (id) +PARTITION BY RANGE (year) +( + START (2002) END (2010) EVERY (1), + DEFAULT PARTITION outlying_years +); +NOTICE: CREATE TABLE will create partition "ch_sort_pqdest_1_prt_outlying_years" for table "ch_sort_pqdest" +NOTICE: CREATE TABLE will create partition "ch_sort_pqdest_1_prt_2" for table "ch_sort_pqdest" +NOTICE: CREATE TABLE will create partition "ch_sort_pqdest_1_prt_3" for table "ch_sort_pqdest" +NOTICE: CREATE TABLE will create partition "ch_sort_pqdest_1_prt_4" for table "ch_sort_pqdest" +NOTICE: CREATE TABLE will create partition "ch_sort_pqdest_1_prt_5" for table "ch_sort_pqdest" +NOTICE: CREATE TABLE will create partition "ch_sort_pqdest_1_prt_6" for table "ch_sort_pqdest" +NOTICE: CREATE TABLE will create partition "ch_sort_pqdest_1_prt_7" for table "ch_sort_pqdest" +NOTICE: CREATE TABLE will create partition "ch_sort_pqdest_1_prt_8" for table "ch_sort_pqdest" +NOTICE: CREATE TABLE will create partition "ch_sort_pqdest_1_prt_9" for table "ch_sort_pqdest" +-- AO/PARQUET mixed table +CREATE TABLE ch_sort_aopqdest (id int, year int, month int, day int, region text) +WITH (APPENDONLY=TRUE) +DISTRIBUTED BY (id) +PARTITION BY RANGE (year) +( + START (2002) END (2010) EVERY (1), + DEFAULT PARTITION outlying_years +); +NOTICE: CREATE TABLE will create partition "ch_sort_aopqdest_1_prt_outlying_years" for table "ch_sort_aopqdest" +NOTICE: CREATE TABLE will create partition "ch_sort_aopqdest_1_prt_2" for table "ch_sort_aopqdest" +NOTICE: CREATE TABLE will create partition "ch_sort_aopqdest_1_prt_3" for table "ch_sort_aopqdest" +NOTICE: CREATE TABLE will create partition "ch_sort_aopqdest_1_prt_4" for table "ch_sort_aopqdest" +NOTICE: CREATE TABLE will create partition "ch_sort_aopqdest_1_prt_5" for table "ch_sort_aopqdest" +NOTICE: CREATE TABLE will create partition "ch_sort_aopqdest_1_prt_6" for table "ch_sort_aopqdest" +NOTICE: CREATE TABLE will create partition "ch_sort_aopqdest_1_prt_7" for table "ch_sort_aopqdest" +NOTICE: CREATE TABLE will create partition "ch_sort_aopqdest_1_prt_8" for table "ch_sort_aopqdest" +NOTICE: CREATE TABLE will create partition "ch_sort_aopqdest_1_prt_9" for table "ch_sort_aopqdest" +CREATE TABLE ch_sort__pq_table (id int, year int, month int, day int, region text) +WITH (APPENDONLY=TRUE, ORIENTATION = PARQUET) +DISTRIBUTED BY (id); +ALTER TABLE ch_sort_aopqdest +EXCHANGE PARTITION FOR(2006) +WITH TABLE ch_sort__pq_table; +-- Test that inserts work +INSERT INTO ch_sort_aodest SELECT * FROM ch_sort_src; +SELECT COUNT(*) FROM ch_sort_aodest; + count +------- + 100 +(1 row) + +SELECT COUNT(*) FROM ch_sort_aodest_1_prt_6; + count +------- + 1 +(1 row) + +SELECT COUNT(*) FROM ch_sort_aodest_1_prt_outlying_years; + count +------- + 92 +(1 row) + +INSERT INTO ch_sort_pqdest SELECT * FROM ch_sort_src; +SELECT COUNT(*) FROM ch_sort_pqdest; + count +------- + 100 +(1 row) + +SELECT COUNT(*) FROM ch_sort_pqdest_1_prt_6; + count +------- + 1 +(1 row) + +SELECT COUNT(*) FROM ch_sort_pqdest_1_prt_outlying_years; + count +------- + 92 +(1 row) + +INSERT INTO ch_sort_aopqdest SELECT * FROM ch_sort_src; +SELECT COUNT(*) FROM ch_sort_aopqdest; + count +------- + 100 +(1 row) + +SELECT COUNT(*) FROM ch_sort_aopqdest_1_prt_6; + count +------- + 1 +(1 row) + +SELECT COUNT(*) FROM ch_sort_aopqdest_1_prt_outlying_years; + count +------- + 92 +(1 row) + +RESET optimizer_parts_to_force_sort_on_insert; http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/6e6890ae/src/test/regress/sql/goh_partition.sql ---------------------------------------------------------------------- diff --git a/src/test/regress/sql/goh_partition.sql b/src/test/regress/sql/goh_partition.sql index 8cea409..4c04bd8 100644 --- a/src/test/regress/sql/goh_partition.sql +++ b/src/test/regress/sql/goh_partition.sql @@ -1227,3 +1227,71 @@ subpartition ohio values ('OH') ) ) ); + +-- Tests for sort operator before insert with AO and PARQUET tables (HAWQ-404) +-- A GUC's value is set to less than the number of partitions in the example table, so that sort is activated. + +DROP TABLE IF EXISTS ch_sort_src, ch_sort_aodest, ch_sort_pqdest, ch_sort_aopqdest, ch_sort__pq_table; + +SET optimizer_parts_to_force_sort_on_insert = 5; + +CREATE TABLE ch_sort_src (id int, year int, month int, day int, region text) +DISTRIBUTED BY (month); +INSERT INTO ch_sort_src select i, 2000 + i, i % 12, (2*i) % 30, i::text from generate_series(0, 99) i; + +-- AO partitioned table +CREATE TABLE ch_sort_aodest (id int, year int, month int, day int, region text) +WITH (APPENDONLY=TRUE) +DISTRIBUTED BY (id) +PARTITION BY RANGE (year) +( + START (2002) END (2010) EVERY (1), + DEFAULT PARTITION outlying_years +); + +-- PARQUET partitioned table +CREATE TABLE ch_sort_pqdest (id int, year int, month int, day int, region text) +WITH (APPENDONLY=TRUE, ORIENTATION = PARQUET) +DISTRIBUTED BY (id) +PARTITION BY RANGE (year) +( + START (2002) END (2010) EVERY (1), + DEFAULT PARTITION outlying_years +); + +-- AO/PARQUET mixed table +CREATE TABLE ch_sort_aopqdest (id int, year int, month int, day int, region text) +WITH (APPENDONLY=TRUE) +DISTRIBUTED BY (id) +PARTITION BY RANGE (year) +( + START (2002) END (2010) EVERY (1), + DEFAULT PARTITION outlying_years +); + +CREATE TABLE ch_sort__pq_table (id int, year int, month int, day int, region text) +WITH (APPENDONLY=TRUE, ORIENTATION = PARQUET) +DISTRIBUTED BY (id); + +ALTER TABLE ch_sort_aopqdest +EXCHANGE PARTITION FOR(2006) +WITH TABLE ch_sort__pq_table; + + +-- Test that inserts work +INSERT INTO ch_sort_aodest SELECT * FROM ch_sort_src; +SELECT COUNT(*) FROM ch_sort_aodest; +SELECT COUNT(*) FROM ch_sort_aodest_1_prt_6; +SELECT COUNT(*) FROM ch_sort_aodest_1_prt_outlying_years; + +INSERT INTO ch_sort_pqdest SELECT * FROM ch_sort_src; +SELECT COUNT(*) FROM ch_sort_pqdest; +SELECT COUNT(*) FROM ch_sort_pqdest_1_prt_6; +SELECT COUNT(*) FROM ch_sort_pqdest_1_prt_outlying_years; + +INSERT INTO ch_sort_aopqdest SELECT * FROM ch_sort_src; +SELECT COUNT(*) FROM ch_sort_aopqdest; +SELECT COUNT(*) FROM ch_sort_aopqdest_1_prt_6; +SELECT COUNT(*) FROM ch_sort_aopqdest_1_prt_outlying_years; + +RESET optimizer_parts_to_force_sort_on_insert;
