| -- start_ignore |
| create extension if not exists gp_debug_numsegments; |
| -- end_ignore |
| drop schema if exists test_expand_table_ao cascade; |
| create schema test_expand_table_ao; |
| set search_path=test_expand_table_ao,public; |
| set default_table_access_method = ao_row; |
| set allow_system_table_mods=true; |
| -- Hash distributed tables |
| select gp_debug_set_create_table_default_numsegments(2); |
| gp_debug_set_create_table_default_numsegments |
| ----------------------------------------------- |
| 2 |
| (1 row) |
| |
| Create table t1(a int, b int, c int) distributed by (a); |
| insert into t1 select i,i,0 from generate_series(1,100) I; |
| Update t1 set c = gp_segment_id; |
| Select gp_segment_id, count(*) from t1 group by gp_segment_id; |
| gp_segment_id | count |
| ---------------+------- |
| 1 | 47 |
| 0 | 53 |
| (2 rows) |
| |
| begin; |
| Alter table t1 expand table; |
| Select gp_segment_id, count(*) from t1 group by gp_segment_id; |
| gp_segment_id | count |
| ---------------+------- |
| 0 | 38 |
| 2 | 25 |
| 1 | 37 |
| (3 rows) |
| |
| abort; |
| Select gp_segment_id, count(*) from t1 group by gp_segment_id; |
| gp_segment_id | count |
| ---------------+------- |
| 1 | 47 |
| 0 | 53 |
| (2 rows) |
| |
| Alter table t1 expand table; |
| Select gp_segment_id, count(*) from t1 group by gp_segment_id; |
| gp_segment_id | count |
| ---------------+------- |
| 0 | 38 |
| 2 | 25 |
| 1 | 37 |
| (3 rows) |
| |
| select numsegments from gp_distribution_policy where localoid='t1'::regclass; |
| numsegments |
| ------------- |
| 3 |
| (1 row) |
| |
| drop table t1; |
| select gp_debug_set_create_table_default_numsegments(1); |
| gp_debug_set_create_table_default_numsegments |
| ----------------------------------------------- |
| 1 |
| (1 row) |
| |
| Create table t1(a int, b int, c int) distributed by (a,b); |
| insert into t1 select i,i,0 from generate_series(1,100) I; |
| Update t1 set c = gp_segment_id; |
| Select gp_segment_id, count(*) from t1 group by gp_segment_id; |
| gp_segment_id | count |
| ---------------+------- |
| 0 | 100 |
| (1 row) |
| |
| begin; |
| Alter table t1 expand table; |
| Select gp_segment_id, count(*) from t1 group by gp_segment_id; |
| gp_segment_id | count |
| ---------------+------- |
| 0 | 33 |
| 2 | 37 |
| 1 | 30 |
| (3 rows) |
| |
| abort; |
| Select gp_segment_id, count(*) from t1 group by gp_segment_id; |
| gp_segment_id | count |
| ---------------+------- |
| 0 | 100 |
| (1 row) |
| |
| Alter table t1 expand table; |
| Select gp_segment_id, count(*) from t1 group by gp_segment_id; |
| gp_segment_id | count |
| ---------------+------- |
| 0 | 33 |
| 2 | 37 |
| 1 | 30 |
| (3 rows) |
| |
| select numsegments from gp_distribution_policy where localoid='t1'::regclass; |
| numsegments |
| ------------- |
| 3 |
| (1 row) |
| |
| drop table t1; |
| -- Test NULLs. |
| select gp_debug_set_create_table_default_numsegments(2); |
| gp_debug_set_create_table_default_numsegments |
| ----------------------------------------------- |
| 2 |
| (1 row) |
| |
| Create table t1(a int, b int, c int) distributed by (a,b,c); |
| insert into t1 values |
| (1, 1, 1 ), |
| (null, 2, 2 ), |
| (3, null, 3 ), |
| (4, 4, null), |
| (null, null, 5 ), |
| (null, 6, null), |
| (7, null, null), |
| (null, null, null); |
| Select gp_segment_id, count(*) from t1 group by gp_segment_id; |
| gp_segment_id | count |
| ---------------+------- |
| 1 | 4 |
| 0 | 4 |
| (2 rows) |
| |
| begin; |
| Alter table t1 expand table; |
| Select gp_segment_id, count(*) from t1 group by gp_segment_id; |
| gp_segment_id | count |
| ---------------+------- |
| 0 | 3 |
| 2 | 4 |
| 1 | 1 |
| (3 rows) |
| |
| abort; |
| Select gp_segment_id, count(*) from t1 group by gp_segment_id; |
| gp_segment_id | count |
| ---------------+------- |
| 1 | 4 |
| 0 | 4 |
| (2 rows) |
| |
| Alter table t1 expand table; |
| Select gp_segment_id, count(*) from t1 group by gp_segment_id; |
| gp_segment_id | count |
| ---------------+------- |
| 0 | 3 |
| 1 | 1 |
| 2 | 4 |
| (3 rows) |
| |
| select numsegments from gp_distribution_policy where localoid='t1'::regclass; |
| numsegments |
| ------------- |
| 3 |
| (1 row) |
| |
| drop table t1; |
| select gp_debug_set_create_table_default_numsegments(1); |
| gp_debug_set_create_table_default_numsegments |
| ----------------------------------------------- |
| 1 |
| (1 row) |
| |
| Create table t1(a int, b int, c int) distributed by (a) partition by list(b) (partition t1_1 values(1), partition t1_2 values(2), default partition other); |
| insert into t1 select i,i,0 from generate_series(1,100) I; |
| Select gp_segment_id, count(*) from t1 group by gp_segment_id; |
| gp_segment_id | count |
| ---------------+------- |
| 0 | 100 |
| (1 row) |
| |
| begin; |
| Alter table t1 expand table; |
| Select gp_segment_id, count(*) from t1 group by gp_segment_id; |
| gp_segment_id | count |
| ---------------+------- |
| 0 | 38 |
| 1 | 37 |
| 2 | 25 |
| (3 rows) |
| |
| abort; |
| Select gp_segment_id, count(*) from t1 group by gp_segment_id; |
| gp_segment_id | count |
| ---------------+------- |
| 0 | 100 |
| (1 row) |
| |
| Alter table t1 expand table; |
| Select gp_segment_id, count(*) from t1 group by gp_segment_id; |
| gp_segment_id | count |
| ---------------+------- |
| 2 | 25 |
| 1 | 37 |
| 0 | 38 |
| (3 rows) |
| |
| select numsegments from gp_distribution_policy where localoid='t1'::regclass; |
| numsegments |
| ------------- |
| 3 |
| (1 row) |
| |
| drop table t1; |
| -- Random distributed tables |
| select gp_debug_set_create_table_default_numsegments(2); |
| gp_debug_set_create_table_default_numsegments |
| ----------------------------------------------- |
| 2 |
| (1 row) |
| |
| Create table r1(a int, b int, c int) distributed randomly; |
| insert into r1 select i,i,0 from generate_series(1,100) I; |
| Update r1 set c = gp_segment_id; |
| Select count(*) from r1; |
| count |
| ------- |
| 100 |
| (1 row) |
| |
| Select count(*) > 0 from r1 where gp_segment_id=2; |
| ?column? |
| ---------- |
| f |
| (1 row) |
| |
| begin; |
| Alter table r1 expand table; |
| Select count(*) from r1; |
| count |
| ------- |
| 100 |
| (1 row) |
| |
| Select count(*) > 0 from r1 where gp_segment_id=2; |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| abort; |
| Select count(*) from r1; |
| count |
| ------- |
| 100 |
| (1 row) |
| |
| Select count(*) > 0 from r1 where gp_segment_id=2; |
| ?column? |
| ---------- |
| f |
| (1 row) |
| |
| Alter table r1 expand table; |
| Select count(*) from r1; |
| count |
| ------- |
| 100 |
| (1 row) |
| |
| Select count(*) > 0 from r1 where gp_segment_id=2; |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| select numsegments from gp_distribution_policy where localoid='r1'::regclass; |
| numsegments |
| ------------- |
| 3 |
| (1 row) |
| |
| drop table r1; |
| select gp_debug_set_create_table_default_numsegments(2); |
| gp_debug_set_create_table_default_numsegments |
| ----------------------------------------------- |
| 2 |
| (1 row) |
| |
| Create table r1(a int, b int, c int) distributed randomly; |
| insert into r1 select i,i,0 from generate_series(1,100) I; |
| Update r1 set c = gp_segment_id; |
| Select count(*) from r1; |
| count |
| ------- |
| 100 |
| (1 row) |
| |
| Select count(*) > 0 from r1 where gp_segment_id=2; |
| ?column? |
| ---------- |
| f |
| (1 row) |
| |
| begin; |
| Alter table r1 expand table; |
| Select count(*) from r1; |
| count |
| ------- |
| 100 |
| (1 row) |
| |
| Select count(*) > 0 from r1 where gp_segment_id=2; |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| abort; |
| Select count(*) from r1; |
| count |
| ------- |
| 100 |
| (1 row) |
| |
| Select count(*) > 0 from r1 where gp_segment_id=2; |
| ?column? |
| ---------- |
| f |
| (1 row) |
| |
| Alter table r1 expand table; |
| Select count(*) from r1; |
| count |
| ------- |
| 100 |
| (1 row) |
| |
| Select count(*) > 0 from r1 where gp_segment_id=2; |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| select numsegments from gp_distribution_policy where localoid='r1'::regclass; |
| numsegments |
| ------------- |
| 3 |
| (1 row) |
| |
| drop table r1; |
| select gp_debug_set_create_table_default_numsegments(2); |
| gp_debug_set_create_table_default_numsegments |
| ----------------------------------------------- |
| 2 |
| (1 row) |
| |
| Create table r1(a int, b int, c int) distributed randomly partition by list(b) (partition r1_1 values(1), partition r1_2 values(2), default partition other); |
| insert into r1 select i,i,0 from generate_series(1,100) I; |
| Select count(*) from r1; |
| count |
| ------- |
| 100 |
| (1 row) |
| |
| Select count(*) > 0 from r1 where gp_segment_id=2; |
| ?column? |
| ---------- |
| f |
| (1 row) |
| |
| begin; |
| Alter table r1 expand table; |
| Select count(*) from r1; |
| count |
| ------- |
| 100 |
| (1 row) |
| |
| Select count(*) > 0 from r1 where gp_segment_id=2; |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| abort; |
| Select count(*) from r1; |
| count |
| ------- |
| 100 |
| (1 row) |
| |
| Select count(*) > 0 from r1 where gp_segment_id=2; |
| ?column? |
| ---------- |
| f |
| (1 row) |
| |
| Alter table r1 expand table; |
| Select count(*) from r1; |
| count |
| ------- |
| 100 |
| (1 row) |
| |
| Select count(*) > 0 from r1 where gp_segment_id=2; |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| select numsegments from gp_distribution_policy where localoid='r1'::regclass; |
| numsegments |
| ------------- |
| 3 |
| (1 row) |
| |
| drop table r1; |
| -- Replicated tables |
| select gp_debug_set_create_table_default_numsegments(1); |
| gp_debug_set_create_table_default_numsegments |
| ----------------------------------------------- |
| 1 |
| (1 row) |
| |
| Create table r1(a int, b int, c int) distributed replicated; |
| insert into r1 select i,i,0 from generate_series(1,100) I; |
| Select count(*) from gp_dist_random('r1'); |
| count |
| ------- |
| 100 |
| (1 row) |
| |
| begin; |
| Alter table r1 expand table; |
| Select count(*) from gp_dist_random('r1'); |
| count |
| ------- |
| 300 |
| (1 row) |
| |
| abort; |
| Select count(*) from gp_dist_random('r1'); |
| count |
| ------- |
| 100 |
| (1 row) |
| |
| Alter table r1 expand table; |
| Select count(*) from gp_dist_random('r1'); |
| count |
| ------- |
| 300 |
| (1 row) |
| |
| select numsegments from gp_distribution_policy where localoid='r1'::regclass; |
| numsegments |
| ------------- |
| 3 |
| (1 row) |
| |
| drop table r1; |
| -- |
| select gp_debug_set_create_table_default_numsegments(1); |
| gp_debug_set_create_table_default_numsegments |
| ----------------------------------------------- |
| 1 |
| (1 row) |
| |
| Create table r1(a int, b int, c int) distributed replicated; |
| insert into r1 select i,i,0 from generate_series(1,100) I; |
| Select count(*) from gp_dist_random('r1'); |
| count |
| ------- |
| 100 |
| (1 row) |
| |
| begin; |
| Alter table r1 expand table; |
| Select count(*) from gp_dist_random('r1'); |
| count |
| ------- |
| 300 |
| (1 row) |
| |
| abort; |
| Select count(*) from gp_dist_random('r1'); |
| count |
| ------- |
| 100 |
| (1 row) |
| |
| Alter table r1 expand table; |
| Select count(*) from gp_dist_random('r1'); |
| count |
| ------- |
| 300 |
| (1 row) |
| |
| select numsegments from gp_distribution_policy where localoid='r1'::regclass; |
| numsegments |
| ------------- |
| 3 |
| (1 row) |
| |
| drop table r1; |
| -- multi-level partition tables |
| select gp_debug_set_create_table_default_numsegments(2); |
| gp_debug_set_create_table_default_numsegments |
| ----------------------------------------------- |
| 2 |
| (1 row) |
| |
| CREATE TABLE part_t1(a int, b int, c int, d int, e int) |
| DISTRIBUTED BY(a) |
| PARTITION BY RANGE (b) |
| SUBPARTITION BY RANGE (c) |
| SUBPARTITION TEMPLATE ( |
| START(1) END (3) EVERY(1), |
| DEFAULT SUBPARTITION others_c) |
| SUBPARTITION BY LIST (d) |
| SUBPARTITION TEMPLATE ( |
| SUBPARTITION one VALUES (1), |
| SUBPARTITION two VALUES (2), |
| SUBPARTITION three VALUES (3), |
| DEFAULT SUBPARTITION others_d) |
| ( START (1) END (2) EVERY (1), |
| DEFAULT PARTITION other_b); |
| insert into part_t1 select i,i%3,i%4,i%5,i from generate_series(1,100) I; |
| Update part_t1 set e = gp_segment_id; |
| Select gp_segment_id, count(*) from part_t1 group by gp_segment_id; |
| gp_segment_id | count |
| ---------------+------- |
| 1 | 47 |
| 0 | 53 |
| (2 rows) |
| |
| begin; |
| Alter table part_t1 expand table; |
| Select gp_segment_id, count(*) from part_t1 group by gp_segment_id; |
| gp_segment_id | count |
| ---------------+------- |
| 0 | 38 |
| 2 | 25 |
| 1 | 37 |
| (3 rows) |
| |
| abort; |
| Select gp_segment_id, count(*) from part_t1 group by gp_segment_id; |
| gp_segment_id | count |
| ---------------+------- |
| 1 | 47 |
| 0 | 53 |
| (2 rows) |
| |
| Alter table part_t1 expand table; |
| Select gp_segment_id, count(*) from part_t1 group by gp_segment_id; |
| gp_segment_id | count |
| ---------------+------- |
| 0 | 38 |
| 2 | 25 |
| 1 | 37 |
| (3 rows) |
| |
| select numsegments from gp_distribution_policy where localoid='part_t1'::regclass; |
| numsegments |
| ------------- |
| 3 |
| (1 row) |
| |
| drop table part_t1; |
| -- |
| select gp_debug_set_create_table_default_numsegments(2); |
| gp_debug_set_create_table_default_numsegments |
| ----------------------------------------------- |
| 2 |
| (1 row) |
| |
| CREATE TABLE part_t1(a int, b int, c int, d int, e int) |
| DISTRIBUTED RANDOMLY |
| PARTITION BY RANGE (b) |
| SUBPARTITION BY RANGE (c) |
| SUBPARTITION TEMPLATE ( |
| START(1) END (3) EVERY(1), |
| DEFAULT SUBPARTITION others_c) |
| SUBPARTITION BY LIST (d) |
| SUBPARTITION TEMPLATE ( |
| SUBPARTITION one VALUES (1), |
| SUBPARTITION two VALUES (2), |
| SUBPARTITION three VALUES (3), |
| DEFAULT SUBPARTITION others_d) |
| ( START (1) END (2) EVERY (1), |
| DEFAULT PARTITION other_b); |
| insert into part_t1 select i,i%3,i%4,i%5,i from generate_series(1,100) I; |
| Update part_t1 set e = gp_segment_id; |
| Select count(*) from part_t1; |
| count |
| ------- |
| 100 |
| (1 row) |
| |
| Select count(*) > 0 from part_t1 where gp_segment_id=2; |
| ?column? |
| ---------- |
| f |
| (1 row) |
| |
| begin; |
| Alter table part_t1 expand table; |
| Select count(*) from part_t1; |
| count |
| ------- |
| 100 |
| (1 row) |
| |
| Select count(*) > 0 from part_t1 where gp_segment_id=2; |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| abort; |
| Select count(*) from part_t1; |
| count |
| ------- |
| 100 |
| (1 row) |
| |
| Select count(*) > 0 from part_t1 where gp_segment_id=2; |
| ?column? |
| ---------- |
| f |
| (1 row) |
| |
| Alter table part_t1 expand table; |
| Select count(*) from part_t1; |
| count |
| ------- |
| 100 |
| (1 row) |
| |
| Select count(*) > 0 from part_t1 where gp_segment_id=2; |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| select numsegments from gp_distribution_policy where localoid='part_t1'::regclass; |
| numsegments |
| ------------- |
| 3 |
| (1 row) |
| |
| drop table part_t1; |
| -- only expand leaf partitions, not allowed now |
| select gp_debug_set_create_table_default_numsegments(2); |
| gp_debug_set_create_table_default_numsegments |
| ----------------------------------------------- |
| 2 |
| (1 row) |
| |
| CREATE TABLE part_t1(a int, b int, c int, d int, e int) |
| DISTRIBUTED BY(a) |
| PARTITION BY RANGE (b) |
| SUBPARTITION BY RANGE (c) |
| SUBPARTITION TEMPLATE ( |
| START(1) END (3) EVERY(1), |
| DEFAULT SUBPARTITION others_c) |
| SUBPARTITION BY LIST (d) |
| SUBPARTITION TEMPLATE ( |
| SUBPARTITION one VALUES (1), |
| SUBPARTITION two VALUES (2), |
| SUBPARTITION three VALUES (3), |
| DEFAULT SUBPARTITION others_d) |
| ( START (1) END (2) EVERY (1), |
| DEFAULT PARTITION other_b); |
| insert into part_t1 select i,i%3,i%4,i%5,i from generate_series(1,100) I; |
| Update part_t1 set e = gp_segment_id; |
| select gp_segment_id, * from part_t1_1_prt_other_b_2_prt_2_3_prt_others_d; |
| gp_segment_id | a | b | c | d | e |
| ---------------+----+---+---+---+--- |
| 0 | 9 | 0 | 1 | 4 | 0 |
| 0 | 29 | 2 | 1 | 4 | 0 |
| 0 | 45 | 0 | 1 | 0 | 0 |
| 0 | 65 | 2 | 1 | 0 | 0 |
| 1 | 5 | 2 | 1 | 0 | 1 |
| 1 | 69 | 0 | 1 | 4 | 1 |
| 1 | 89 | 2 | 1 | 4 | 1 |
| (7 rows) |
| |
| alter table part_t1_1_prt_other_b_2_prt_2_3_prt_others_d expand table; |
| ERROR: cannot expand leaf or interior partition "part_t1_1_prt_other_b_2_prt_2_3_prt_others_d" |
| DETAIL: Root/leaf/interior partitions need to have same numsegments |
| HINT: Call ALTER TABLE EXPAND TABLE on the root table instead |
| select gp_segment_id, * from part_t1_1_prt_other_b_2_prt_2_3_prt_others_d; |
| gp_segment_id | a | b | c | d | e |
| ---------------+----+---+---+---+--- |
| 0 | 9 | 0 | 1 | 4 | 0 |
| 0 | 29 | 2 | 1 | 4 | 0 |
| 0 | 45 | 0 | 1 | 0 | 0 |
| 0 | 65 | 2 | 1 | 0 | 0 |
| 1 | 5 | 2 | 1 | 0 | 1 |
| 1 | 69 | 0 | 1 | 4 | 1 |
| 1 | 89 | 2 | 1 | 4 | 1 |
| (7 rows) |
| |
| -- try to expand root partition, should success |
| Alter table part_t1 expand table; |
| Select gp_segment_id, count(*) from part_t1 group by gp_segment_id; |
| gp_segment_id | count |
| ---------------+------- |
| 2 | 25 |
| 0 | 38 |
| 1 | 37 |
| (3 rows) |
| |
| drop table part_t1; |
| -- Test expanding an AO table without index, and current gp_default_storage_options is different |
| -- from the table created. |
| select gp_debug_set_create_table_default_numsegments(2); |
| gp_debug_set_create_table_default_numsegments |
| ----------------------------------------------- |
| 2 |
| (1 row) |
| |
| set gp_default_storage_options = ''; |
| -- Create a AO table with default row-oriented storage |
| create table t_9526 (a int, b text) WITH (appendoptimized=true) distributed by (a); |
| -- Set default storage method to column-oriented, and change the default value of blocksize, compresstype, etc. |
| SET gp_default_storage_options = 'blocksize=65536, compresstype=zlib, compresslevel=5, checksum=true'; |
| set default_table_access_method = ao_column; |
| -- Should successful and the table is still row-oriented |
| alter table t_9526 expand table; |
| show gp_default_storage_options; |
| gp_default_storage_options |
| ----------------------------------------------------------------- |
| blocksize=65536,compresstype=zlib,compresslevel=5,checksum=true |
| (1 row) |
| |
| select localoid::regclass::name, policytype, numsegments, distkey, distclass |
| from gp_distribution_policy where localoid = 't_9526'::regclass::oid; |
| localoid | policytype | numsegments | distkey | distclass |
| ----------+------------+-------------+---------+----------- |
| t_9526 | p | 3 | 1 | 10020 |
| (1 row) |
| |
| -- storage paramter should be same with before |
| select blocksize,compresslevel,checksum,compresstype,columnstore from pg_appendonly where relid='t_9526'::regclass::oid; |
| blocksize | compresslevel | checksum | compresstype | columnstore |
| -----------+---------------+----------+--------------+------------- |
| 32768 | 0 | t | none | f |
| (1 row) |
| |
| -- set back |
| set gp_default_storage_options = ''; |
| drop table t_9526; |
| -- Test expanding an AO table with index, and current gp_default_storage_options is different |
| -- from the table created. |
| select gp_debug_set_create_table_default_numsegments(2); |
| gp_debug_set_create_table_default_numsegments |
| ----------------------------------------------- |
| 2 |
| (1 row) |
| |
| set gp_default_storage_options = ''; |
| -- Create a AO table with default row-oriented storage and build index for it |
| create table t_9527 (a int, b text) WITH (appendoptimized=true) distributed by (a); |
| create index idx_9527 on t_9527(b); |
| -- Set default storage method to column-oriented, and change the default value of blocksize, compresstype, etc. |
| SET gp_default_storage_options = 'blocksize=65536, compresstype=zlib, compresslevel=5, checksum=true'; |
| set default_table_access_method = ao_column; |
| -- Should successful and the table is still row-oriented |
| alter table t_9527 expand table; |
| show gp_default_storage_options; |
| gp_default_storage_options |
| ----------------------------------------------------------------- |
| blocksize=65536,compresstype=zlib,compresslevel=5,checksum=true |
| (1 row) |
| |
| select localoid::regclass::name, policytype, numsegments, distkey, distclass |
| from gp_distribution_policy where localoid = 't_9527'::regclass::oid; |
| localoid | policytype | numsegments | distkey | distclass |
| ----------+------------+-------------+---------+----------- |
| t_9527 | p | 3 | 1 | 10020 |
| (1 row) |
| |
| -- storage paramter should be same with before |
| select blocksize,compresslevel,checksum,compresstype,columnstore from pg_appendonly where relid='t_9527'::regclass::oid; |
| blocksize | compresslevel | checksum | compresstype | columnstore |
| -----------+---------------+----------+--------------+------------- |
| 32768 | 0 | t | none | f |
| (1 row) |
| |
| -- set back |
| set gp_default_storage_options = ''; |
| drop table t_9527; |
| -- Test change the distribution policy of an AO table with index, and current gp_default_storage_options is different |
| -- from the table created. |
| select gp_debug_set_create_table_default_numsegments(2); |
| gp_debug_set_create_table_default_numsegments |
| ----------------------------------------------- |
| 2 |
| (1 row) |
| |
| set gp_default_storage_options = ''; |
| -- Create a AO table with default row-oriented storage and build index for it |
| create table t_9528 (a int, b text) WITH (appendoptimized=true) distributed by (a); |
| create index idx_9528 on t_9528(b); |
| SET gp_default_storage_options = 'blocksize=65536, compresstype=zlib, compresslevel=5, checksum=true'; |
| set default_table_access_method = ao_column; |
| -- Should successful and the table is still row-oriented |
| alter table t_9528 set distributed by (b); |
| show gp_default_storage_options; |
| gp_default_storage_options |
| ----------------------------------------------------------------- |
| blocksize=65536,compresstype=zlib,compresslevel=5,checksum=true |
| (1 row) |
| |
| select localoid::regclass::name, policytype, numsegments, distkey, distclass |
| from gp_distribution_policy where localoid = 't_9528'::regclass::oid; |
| localoid | policytype | numsegments | distkey | distclass |
| ----------+------------+-------------+---------+----------- |
| t_9528 | p | 2 | 2 | 10039 |
| (1 row) |
| |
| -- storage paramter should be same with before |
| select blocksize,compresslevel,checksum,compresstype,columnstore from pg_appendonly where relid='t_9528'::regclass::oid; |
| blocksize | compresslevel | checksum | compresstype | columnstore |
| -----------+---------------+----------+--------------+------------- |
| 32768 | 0 | t | none | f |
| (1 row) |
| |
| -- set back |
| set gp_default_storage_options = ''; |
| drop table t_9528; |
| -- start_ignore |
| -- We need to do a cluster expansion which will check if there are partial |
| -- tables, we need to drop the partial tables to keep the cluster expansion |
| -- run correctly. |
| reset search_path; |
| drop schema test_reshuffle_ao cascade; |
| -- end_ignore |
