-- ALTER TABLE ... SET DISTRIBUTED BY -- This is the main interface for system expansion \set DATA values(1, 2), (2, 3), (3, 4) -- Basic sanity tests set optimizer_disable_missing_stats_collection = on; create table atsdb (i int, j text) distributed by (i); insert into atsdb :DATA; -- should fail alter table atsdb set distributed by (); ERROR: syntax error at or near ")" LINE 1: alter table atsdb set distributed by (); ^ alter table atsdb set distributed by (m); ERROR: column "m" of relation "atsdb" does not exist alter table atsdb set distributed by (i, i); ERROR: distribution policy must be a unique set of columns HINT: Column "i" appears more than once alter table atsdb set distributed by (i, m); ERROR: column "m" of relation "atsdb" does not exist alter table atsdb set distributed by (i); WARNING: distribution policy of relation "atsdb" already set to (i) HINT: Use ALTER TABLE "atsdb" SET WITH (REORGANIZE=TRUE) DISTRIBUTED BY (i) to force redistribution -- should work alter table atsdb set distributed randomly; select localoid::regclass, attrnums from gp_distribution_policy where localoid = 'atsdb'::regclass; localoid | attrnums ----------+---------- atsdb | (1 row) -- not possible to correctly verify random distribution alter table atsdb set distributed by (j); select localoid::regclass, attrnums from gp_distribution_policy where localoid = 'atsdb'::regclass; localoid | attrnums ----------+---------- atsdb | {2} (1 row) -- verify that the data is correctly redistributed by building a fresh -- table with the same policy create table ats_test (i int, j text) distributed by (j); insert into ats_test :DATA; select gp_segment_id, * from ats_test except select gp_segment_id, * from atsdb; gp_segment_id | i | j ---------------+---+--- (0 rows) drop table ats_test; alter table atsdb set distributed by (i, j); select localoid::regclass, attrnums from gp_distribution_policy where localoid = 'atsdb'::regclass; localoid | attrnums ----------+---------- atsdb | {1,2} (1 row) -- verify create table ats_test (i int, j text) distributed by (i, j); insert into ats_test :DATA; select gp_segment_id, * from ats_test except select gp_segment_id, * from atsdb; gp_segment_id | i | j ---------------+---+--- (0 rows) drop table ats_test; alter table atsdb set distributed by (j, i); select localoid::regclass, attrnums from gp_distribution_policy where localoid = 'atsdb'::regclass; localoid | attrnums ----------+---------- atsdb | {2,1} (1 row) -- verify create table ats_test (i int, j text) distributed by (j, i); insert into ats_test :DATA; select gp_segment_id, * from ats_test except select gp_segment_id, * from atsdb; gp_segment_id | i | j ---------------+---+--- (0 rows) drop table ats_test; -- Now make sure indexes work. create index atsdb_i_idx on atsdb(i); set enable_seqscan to off; explain select * from atsdb where i = 1; QUERY PLAN --------------------------------------------------------------------------------- Gather Motion 2:1 (slice1) (cost=0.00..200.27 rows=1 width=6) -> Index Scan using atsdb_i_idx on atsdb (cost=0.00..200.27 rows=1 width=6) Index Cond: i = 1 Settings: enable_seqscan=off (4 rows) select * from atsdb where i = 1; i | j ---+--- 1 | 2 (1 row) alter table atsdb set distributed by (i); explain select * from atsdb where i = 1; QUERY PLAN --------------------------------------------------------------------------------- Gather Motion 2:1 (slice1) (cost=0.00..200.27 rows=1 width=6) -> Index Scan using atsdb_i_idx on atsdb (cost=0.00..200.27 rows=1 width=6) Index Cond: i = 1 Settings: enable_seqscan=off (4 rows) select * from atsdb where i = 1; i | j ---+--- 1 | 2 (1 row) drop table atsdb; -- Now try AO create table atsdb_ao (i int, j text) with (appendonly=true) distributed by (i); insert into atsdb_ao select i, (i+1)::text from generate_series(1, 100) i; insert into atsdb_ao select i, (i+1)::text from generate_series(1, 100) i; -- check that we're an AO table select count(*) from pg_appendonly where relid='atsdb_ao'::regclass; count ------- 1 (1 row) select count(*) from atsdb_ao; count ------- 200 (1 row) alter table atsdb_ao set distributed by (j); -- Still AO? select count(*) from pg_appendonly where relid='atsdb_ao'::regclass; count ------- 1 (1 row) select count(*) from atsdb_ao; count ------- 200 (1 row) -- check alter, vacuum analyze, and then alter delete from atsdb_ao where i = any(array(select generate_series(1,90))); vacuum analyze atsdb_ao; alter table atsdb_ao set distributed randomly; select count(*) from atsdb_ao; count ------- 20 (1 row) drop table atsdb_ao; -- Check divergent distribution policies for partitioning. create table atsdb (i int, j int, k int) distributed by (i) partition by range(k) (start(1) end(4) every(1)); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database data distribution key for this table. HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "atsdb_1_prt_1" for table "atsdb" NOTICE: CREATE TABLE will create partition "atsdb_1_prt_2" for table "atsdb" NOTICE: CREATE TABLE will create partition "atsdb_1_prt_3" for table "atsdb" alter table atsdb_1_prt_2 set distributed by (j); ERROR: cannot SET DISTRIBUTED BY for atsdb_1_prt_2 HINT: Leaf distribution policy must be random or match that of the entire partitioned table. alter table atsdb_1_prt_3 set distributed randomly; -- test COPY copy atsdb from stdin delimiter '|'; select count(*) from atsdb; count ------- 18 (1 row) -- compare distribution: we create a table, identical to the partitioned table, -- and compare how the tuples have been distributed. create table atsdb_1 (like atsdb_1_prt_1) distributed by (i); copy atsdb_1 from stdin delimiter '|'; select gp_segment_id, * from atsdb where k = 1 except select gp_segment_id, * from atsdb_1; gp_segment_id | i | j | k ---------------+---+---+--- (0 rows) create table atsdb_2 (like atsdb_1_prt_2) distributed by (i); copy atsdb_2 from stdin delimiter '|'; select gp_segment_id, * from atsdb where k = 2 except select gp_segment_id, * from atsdb_2; gp_segment_id | i | j | k ---------------+---+---+--- (0 rows) -- Can't test randomly distributed -- Can't test INSERT (yet) drop table atsdb, atsdb_1, atsdb_2; -- Can't redistribute system catalogs alter table pg_class set distributed by (relname); ERROR: permission denied: "pg_class" is a system catalog alter table pg_class set with(appendonly = true); ERROR: permission denied: "pg_class" is a system catalog -- MPP-7770: allow testing of changing storage for now set gp_setwith_alter_storage = true; alter table pg_class set with(appendonly = true); ERROR: permission denied: "pg_class" is a system catalog -- WITH clause create table atsdb (i int, j text) distributed by (j); insert into atsdb select i, i::text from generate_series(1, 10) i; alter table atsdb set with(appendonly = true); select relname, segrelid != 0, reloptions from pg_class, pg_appendonly where pg_class.oid = 'atsdb'::regclass and relid = pg_class.oid; relname | ?column? | reloptions ---------+----------+------------------- atsdb | t | {appendonly=true} (1 row) select * from atsdb; i | j ----+---- 1 | 1 3 | 3 5 | 5 7 | 7 9 | 9 10 | 10 2 | 2 4 | 4 6 | 6 8 | 8 (10 rows) drop table atsdb; create view distcheck as select relname as rel, attname from gp_distribution_policy g, pg_attribute p, pg_class c where g.localoid = p.attrelid and attnum = any(g.attrnums) and c.oid = p.attrelid; -- dropped columns create table atsdb (i int, j int, t text, n numeric) distributed by (j); insert into atsdb select i, i+1, i+2, i+3 from generate_series(1, 100) i; alter table atsdb drop column i; select * from atsdb; j | t | n -----+-----+----- 2 | 3 | 4 4 | 5 | 6 6 | 7 | 8 8 | 9 | 10 10 | 11 | 12 12 | 13 | 14 14 | 15 | 16 16 | 17 | 18 18 | 19 | 20 20 | 21 | 22 22 | 23 | 24 24 | 25 | 26 26 | 27 | 28 28 | 29 | 30 30 | 31 | 32 32 | 33 | 34 34 | 35 | 36 36 | 37 | 38 38 | 39 | 40 40 | 41 | 42 42 | 43 | 44 44 | 45 | 46 46 | 47 | 48 48 | 49 | 50 50 | 51 | 52 52 | 53 | 54 54 | 55 | 56 56 | 57 | 58 58 | 59 | 60 60 | 61 | 62 62 | 63 | 64 64 | 65 | 66 66 | 67 | 68 68 | 69 | 70 70 | 71 | 72 72 | 73 | 74 74 | 75 | 76 76 | 77 | 78 78 | 79 | 80 80 | 81 | 82 82 | 83 | 84 84 | 85 | 86 86 | 87 | 88 88 | 89 | 90 90 | 91 | 92 92 | 93 | 94 94 | 95 | 96 96 | 97 | 98 98 | 99 | 100 100 | 101 | 102 3 | 4 | 5 5 | 6 | 7 7 | 8 | 9 9 | 10 | 11 11 | 12 | 13 13 | 14 | 15 15 | 16 | 17 17 | 18 | 19 19 | 20 | 21 21 | 22 | 23 23 | 24 | 25 25 | 26 | 27 27 | 28 | 29 29 | 30 | 31 31 | 32 | 33 33 | 34 | 35 35 | 36 | 37 37 | 38 | 39 39 | 40 | 41 41 | 42 | 43 43 | 44 | 45 45 | 46 | 47 47 | 48 | 49 49 | 50 | 51 51 | 52 | 53 53 | 54 | 55 55 | 56 | 57 57 | 58 | 59 59 | 60 | 61 61 | 62 | 63 63 | 64 | 65 65 | 66 | 67 67 | 68 | 69 69 | 70 | 71 71 | 72 | 73 73 | 74 | 75 75 | 76 | 77 77 | 78 | 79 79 | 80 | 81 81 | 82 | 83 83 | 84 | 85 85 | 86 | 87 87 | 88 | 89 89 | 90 | 91 91 | 92 | 93 93 | 94 | 95 95 | 96 | 97 97 | 98 | 99 99 | 100 | 101 101 | 102 | 103 (100 rows) alter table atsdb set distributed by (t); select * from distcheck where rel = 'atsdb'; rel | attname -------+--------- atsdb | t (1 row) alter table atsdb drop column n; alter table atsdb set with(appendonly = true, compresslevel = 3); select relname, segrelid != 0, reloptions from pg_class, pg_appendonly where pg_class.oid = 'atsdb'::regclass and relid = pg_class.oid; relname | ?column? | reloptions ---------+----------+----------------------------------- atsdb | t | {appendonly=true,compresslevel=3} (1 row) select * from distcheck where rel = 'atsdb'; rel | attname -------+--------- atsdb | t (1 row) select * from atsdb; j | t -----+----- 9 | 10 11 | 12 13 | 14 15 | 16 17 | 18 29 | 30 31 | 32 33 | 34 35 | 36 37 | 38 49 | 50 51 | 52 53 | 54 55 | 56 57 | 58 69 | 70 71 | 72 73 | 74 75 | 76 77 | 78 89 | 90 91 | 92 93 | 94 95 | 96 97 | 98 99 | 100 101 | 102 2 | 3 4 | 5 6 | 7 8 | 9 20 | 21 22 | 23 24 | 25 26 | 27 28 | 29 40 | 41 42 | 43 44 | 45 46 | 47 48 | 49 60 | 61 62 | 63 64 | 65 66 | 67 68 | 69 80 | 81 82 | 83 84 | 85 86 | 87 88 | 89 3 | 4 5 | 6 7 | 8 19 | 20 21 | 22 23 | 24 25 | 26 27 | 28 39 | 40 41 | 42 43 | 44 45 | 46 47 | 48 59 | 60 61 | 62 63 | 64 65 | 66 67 | 68 79 | 80 81 | 82 83 | 84 85 | 86 87 | 88 10 | 11 12 | 13 14 | 15 16 | 17 18 | 19 30 | 31 32 | 33 34 | 35 36 | 37 38 | 39 50 | 51 52 | 53 54 | 55 56 | 57 58 | 59 70 | 71 72 | 73 74 | 75 76 | 77 78 | 79 90 | 91 92 | 93 94 | 95 96 | 97 98 | 99 100 | 101 (100 rows) alter table atsdb set distributed by (j); select * from distcheck where rel = 'atsdb'; rel | attname -------+--------- atsdb | j (1 row) select relname, segrelid != 0, reloptions from pg_class, pg_appendonly where pg_class.oid = 'atsdb'::regclass and relid = pg_class.oid; relname | ?column? | reloptions ---------+----------+----------------------------------- atsdb | t | {appendonly=true,compresslevel=3} (1 row) select * from atsdb; j | t -----+----- 10 | 11 12 | 13 14 | 15 16 | 17 18 | 19 30 | 31 32 | 33 34 | 35 36 | 37 38 | 39 50 | 51 52 | 53 54 | 55 56 | 57 58 | 59 70 | 71 72 | 73 74 | 75 76 | 77 78 | 79 90 | 91 92 | 93 94 | 95 96 | 97 98 | 99 100 | 101 2 | 3 4 | 5 6 | 7 8 | 9 20 | 21 22 | 23 24 | 25 26 | 27 28 | 29 40 | 41 42 | 43 44 | 45 46 | 47 48 | 49 60 | 61 62 | 63 64 | 65 66 | 67 68 | 69 80 | 81 82 | 83 84 | 85 86 | 87 88 | 89 3 | 4 5 | 6 7 | 8 19 | 20 21 | 22 23 | 24 25 | 26 27 | 28 39 | 40 41 | 42 43 | 44 45 | 46 47 | 48 59 | 60 61 | 62 63 | 64 65 | 66 67 | 68 79 | 80 81 | 82 83 | 84 85 | 86 87 | 88 9 | 10 11 | 12 13 | 14 15 | 16 17 | 18 29 | 30 31 | 32 33 | 34 35 | 36 37 | 38 49 | 50 51 | 52 53 | 54 55 | 56 57 | 58 69 | 70 71 | 72 73 | 74 75 | 76 77 | 78 89 | 90 91 | 92 93 | 94 95 | 96 97 | 98 99 | 100 101 | 102 (100 rows) -- validate parameters alter table atsdb set with (appendonly = ff); ERROR: invalid parameter value for "appendonly": "ff" alter table atsdb set with (reorganize = true); alter table atsdb set with (fgdfgef = asds); ERROR: unrecognized parameter "fgdfgef" alter table atsdb set with(reorganize = true, reorganize = false) distributed randomly; ERROR: "reorganize" specified more than once drop table atsdb; -- Check that we correctly cascade for partitioned tables create table atsdb (i int, j int, k int) distributed by (i) partition by range(k) (start(1) end(10) every(1)); NOTICE: CREATE TABLE will create partition "atsdb_1_prt_1" for table "atsdb" NOTICE: CREATE TABLE will create partition "atsdb_1_prt_2" for table "atsdb" NOTICE: CREATE TABLE will create partition "atsdb_1_prt_3" for table "atsdb" NOTICE: CREATE TABLE will create partition "atsdb_1_prt_4" for table "atsdb" NOTICE: CREATE TABLE will create partition "atsdb_1_prt_5" for table "atsdb" NOTICE: CREATE TABLE will create partition "atsdb_1_prt_6" for table "atsdb" NOTICE: CREATE TABLE will create partition "atsdb_1_prt_7" for table "atsdb" NOTICE: CREATE TABLE will create partition "atsdb_1_prt_8" for table "atsdb" NOTICE: CREATE TABLE will create partition "atsdb_1_prt_9" for table "atsdb" insert into atsdb select i+2, i+1, i from generate_series(1, 9) i; select * from distcheck where rel like 'atsdb%'; rel | attname ---------------+--------- atsdb | i atsdb_1_prt_1 | i atsdb_1_prt_2 | i atsdb_1_prt_3 | i atsdb_1_prt_4 | i atsdb_1_prt_5 | i atsdb_1_prt_6 | i atsdb_1_prt_7 | i atsdb_1_prt_8 | i atsdb_1_prt_9 | i (10 rows) alter table atsdb set distributed by (j); select * from distcheck where rel like 'atsdb%'; rel | attname ---------------+--------- atsdb | j atsdb_1_prt_1 | j atsdb_1_prt_2 | j atsdb_1_prt_3 | j atsdb_1_prt_4 | j atsdb_1_prt_5 | j atsdb_1_prt_6 | j atsdb_1_prt_7 | j atsdb_1_prt_8 | j atsdb_1_prt_9 | j (10 rows) select * from atsdb order by 1, 2, 3; i | j | k ----+----+--- 3 | 2 | 1 4 | 3 | 2 5 | 4 | 3 6 | 5 | 4 7 | 6 | 5 8 | 7 | 6 9 | 8 | 7 10 | 9 | 8 11 | 10 | 9 (9 rows) alter table atsdb set with(appendonly = true); select relname, a.blocksize, compresslevel, compresstype, checksum from pg_class c, pg_appendonly a where relname like 'atsdb%' and c.oid = a.relid order by 1; relname | blocksize | compresslevel | compresstype | checksum ---------------+-----------+---------------+--------------+---------- atsdb | 32768 | 0 | | t atsdb_1_prt_1 | 32768 | 0 | | t atsdb_1_prt_2 | 32768 | 0 | | t atsdb_1_prt_3 | 32768 | 0 | | t atsdb_1_prt_4 | 32768 | 0 | | t atsdb_1_prt_5 | 32768 | 0 | | t atsdb_1_prt_6 | 32768 | 0 | | t atsdb_1_prt_7 | 32768 | 0 | | t atsdb_1_prt_8 | 32768 | 0 | | t atsdb_1_prt_9 | 32768 | 0 | | t (10 rows) select * from atsdb order by 1, 2, 3; i | j | k ----+----+--- 3 | 2 | 1 4 | 3 | 2 5 | 4 | 3 6 | 5 | 4 7 | 6 | 5 8 | 7 | 6 9 | 8 | 7 10 | 9 | 8 11 | 10 | 9 (9 rows) insert into atsdb select i+2, i+1, i from generate_series(1, 9) i; select * from atsdb order by 1, 2, 3; i | j | k ----+----+--- 3 | 2 | 1 3 | 2 | 1 4 | 3 | 2 4 | 3 | 2 5 | 4 | 3 5 | 4 | 3 6 | 5 | 4 6 | 5 | 4 7 | 6 | 5 7 | 6 | 5 8 | 7 | 6 8 | 7 | 6 9 | 8 | 7 9 | 8 | 7 10 | 9 | 8 10 | 9 | 8 11 | 10 | 9 11 | 10 | 9 (18 rows) drop table atsdb; drop view distcheck; -- MPP-5452 -- Should succeed create table atsdb (i int, k int) distributed by (i) partition by range(i) (start (1) end(10) every(1)); NOTICE: CREATE TABLE will create partition "atsdb_1_prt_1" for table "atsdb" NOTICE: CREATE TABLE will create partition "atsdb_1_prt_2" for table "atsdb" NOTICE: CREATE TABLE will create partition "atsdb_1_prt_3" for table "atsdb" NOTICE: CREATE TABLE will create partition "atsdb_1_prt_4" for table "atsdb" NOTICE: CREATE TABLE will create partition "atsdb_1_prt_5" for table "atsdb" NOTICE: CREATE TABLE will create partition "atsdb_1_prt_6" for table "atsdb" NOTICE: CREATE TABLE will create partition "atsdb_1_prt_7" for table "atsdb" NOTICE: CREATE TABLE will create partition "atsdb_1_prt_8" for table "atsdb" NOTICE: CREATE TABLE will create partition "atsdb_1_prt_9" for table "atsdb" alter table atsdb alter partition for(rank(5)) set distributed by (i); NOTICE: altering table "atsdb_1_prt_5" (partition for rank 5 of relation "atsdb") WARNING: distribution policy of relation "atsdb_1_prt_5" already set to (i) HINT: Use ALTER TABLE "atsdb_1_prt_5" SET WITH (REORGANIZE=TRUE) DISTRIBUTED BY (i) to force redistribution alter table atsdb alter partition for(rank(5)) set distributed by (i); NOTICE: altering table "atsdb_1_prt_5" (partition for rank 5 of relation "atsdb") WARNING: distribution policy of relation "atsdb_1_prt_5" already set to (i) HINT: Use ALTER TABLE "atsdb_1_prt_5" SET WITH (REORGANIZE=TRUE) DISTRIBUTED BY (i) to force redistribution alter table atsdb alter partition for(rank(5)) set distributed by (i); NOTICE: altering table "atsdb_1_prt_5" (partition for rank 5 of relation "atsdb") WARNING: distribution policy of relation "atsdb_1_prt_5" already set to (i) HINT: Use ALTER TABLE "atsdb_1_prt_5" SET WITH (REORGANIZE=TRUE) DISTRIBUTED BY (i) to force redistribution drop table atsdb; --MPP-5500 CREATE TABLE test_add_drop_rename_column_change_datatype( text_col text, bigint_col bigint, char_vary_col character varying(30), numeric_col numeric, int_col int4, float_col float4, int_array_col int[], drop_col numeric, before_rename_col int4, change_datatype_col numeric, a_ts_without timestamp without time zone, b_ts_with timestamp with time zone, date_column date) distributed randomly; insert into test_add_drop_rename_column_change_datatype values ('0_zero', 0, '0_zero', 0, 0, 0, '{0}', 0, 0, 0, '2004-10-19 10:23:54', '2004-10-19 10:23:54+02', '1-1-2000'); insert into test_add_drop_rename_column_change_datatype values ('1_zero', 1, '1_zero', 1, 1, 1, '{1}', 1, 1, 1, '2005-10-19 10:23:54', '2005-10-19 10:23:54+02', '1-1-2001'); insert into test_add_drop_rename_column_change_datatype values ('2_zero', 2, '2_zero', 2, 2, 2, '{2}', 2, 2, 2, '2006-10-19 10:23:54', '2006-10-19 10:23:54+02', '1-1-2002'); ALTER TABLE test_add_drop_rename_column_change_datatype ADD COLUMN added_col character varying(30); ALTER TABLE test_add_drop_rename_column_change_datatype DROP COLUMN drop_col ; ALTER TABLE test_add_drop_rename_column_change_datatype RENAME COLUMN before_rename_col TO after_rename_col; ALTER TABLE test_add_drop_rename_column_change_datatype ALTER COLUMN change_datatype_col TYPE int4; alter table test_add_drop_rename_column_change_datatype set with(reorganize = true) distributed randomly; select * from test_add_drop_rename_column_change_datatype ; text_col | bigint_col | char_vary_col | numeric_col | int_col | float_col | int_array_col | after_rename_col | change_datatype_col | a_ts_without | b_ts_with | date_column | added_col ----------+------------+---------------+-------------+---------+-----------+---------------+------------------+---------------------+--------------------------+------------------------------+-------------+----------- 1_zero | 1 | 1_zero | 1 | 1 | 1 | {1} | 1 | 1 | Wed Oct 19 10:23:54 2005 | Wed Oct 19 01:23:54 2005 PDT | 01-01-2001 | 2_zero | 2 | 2_zero | 2 | 2 | 2 | {2} | 2 | 2 | Thu Oct 19 10:23:54 2006 | Thu Oct 19 01:23:54 2006 PDT | 01-01-2002 | 0_zero | 0 | 0_zero | 0 | 0 | 0 | {0} | 0 | 0 | Tue Oct 19 10:23:54 2004 | Tue Oct 19 01:23:54 2004 PDT | 01-01-2000 | (3 rows) drop table test_add_drop_rename_column_change_datatype ; -- MPP-5501 -- should run without error create table atsdb with (appendonly=true) as select * from generate_series(1,1000); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'generate_series' as the Greenplum Database data distribution key for this table. HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. alter table only atsdb set with(reorganize=true) distributed by (generate_series); select count(*) from atsdb; count ------- 1000 (1 row) drop table atsdb; -- MPP-5746 create table mpp5746 (c int[], t text); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'c' as the Greenplum Database data distribution key for this table. HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. insert into mpp5746 select array[i], i from generate_series(1, 100) i; alter table mpp5746 set with (reorganize=true, appendonly = true); select * from mpp5746 order by 1; c | t -------+----- {1} | 1 {2} | 2 {3} | 3 {4} | 4 {5} | 5 {6} | 6 {7} | 7 {8} | 8 {9} | 9 {10} | 10 {11} | 11 {12} | 12 {13} | 13 {14} | 14 {15} | 15 {16} | 16 {17} | 17 {18} | 18 {19} | 19 {20} | 20 {21} | 21 {22} | 22 {23} | 23 {24} | 24 {25} | 25 {26} | 26 {27} | 27 {28} | 28 {29} | 29 {30} | 30 {31} | 31 {32} | 32 {33} | 33 {34} | 34 {35} | 35 {36} | 36 {37} | 37 {38} | 38 {39} | 39 {40} | 40 {41} | 41 {42} | 42 {43} | 43 {44} | 44 {45} | 45 {46} | 46 {47} | 47 {48} | 48 {49} | 49 {50} | 50 {51} | 51 {52} | 52 {53} | 53 {54} | 54 {55} | 55 {56} | 56 {57} | 57 {58} | 58 {59} | 59 {60} | 60 {61} | 61 {62} | 62 {63} | 63 {64} | 64 {65} | 65 {66} | 66 {67} | 67 {68} | 68 {69} | 69 {70} | 70 {71} | 71 {72} | 72 {73} | 73 {74} | 74 {75} | 75 {76} | 76 {77} | 77 {78} | 78 {79} | 79 {80} | 80 {81} | 81 {82} | 82 {83} | 83 {84} | 84 {85} | 85 {86} | 86 {87} | 87 {88} | 88 {89} | 89 {90} | 90 {91} | 91 {92} | 92 {93} | 93 {94} | 94 {95} | 95 {96} | 96 {97} | 97 {98} | 98 {99} | 99 {100} | 100 (100 rows) alter table mpp5746 drop column t; select * from mpp5746 order by 1; c ------- {1} {2} {3} {4} {5} {6} {7} {8} {9} {10} {11} {12} {13} {14} {15} {16} {17} {18} {19} {20} {21} {22} {23} {24} {25} {26} {27} {28} {29} {30} {31} {32} {33} {34} {35} {36} {37} {38} {39} {40} {41} {42} {43} {44} {45} {46} {47} {48} {49} {50} {51} {52} {53} {54} {55} {56} {57} {58} {59} {60} {61} {62} {63} {64} {65} {66} {67} {68} {69} {70} {71} {72} {73} {74} {75} {76} {77} {78} {79} {80} {81} {82} {83} {84} {85} {86} {87} {88} {89} {90} {91} {92} {93} {94} {95} {96} {97} {98} {99} {100} (100 rows) alter table mpp5746 set with (reorganize=true, appendonly = false); select * from mpp5746 order by 1; c ------- {1} {2} {3} {4} {5} {6} {7} {8} {9} {10} {11} {12} {13} {14} {15} {16} {17} {18} {19} {20} {21} {22} {23} {24} {25} {26} {27} {28} {29} {30} {31} {32} {33} {34} {35} {36} {37} {38} {39} {40} {41} {42} {43} {44} {45} {46} {47} {48} {49} {50} {51} {52} {53} {54} {55} {56} {57} {58} {59} {60} {61} {62} {63} {64} {65} {66} {67} {68} {69} {70} {71} {72} {73} {74} {75} {76} {77} {78} {79} {80} {81} {82} {83} {84} {85} {86} {87} {88} {89} {90} {91} {92} {93} {94} {95} {96} {97} {98} {99} {100} (100 rows) drop table mpp5746; -- MPP-5738 create table mpp5738 (a int, b int, c int, d int) partition by range(d) (start(1) end(10) inclusive every(1)); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database data distribution key for this table. HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "mpp5738_1_prt_1" for table "mpp5738" NOTICE: CREATE TABLE will create partition "mpp5738_1_prt_2" for table "mpp5738" NOTICE: CREATE TABLE will create partition "mpp5738_1_prt_3" for table "mpp5738" NOTICE: CREATE TABLE will create partition "mpp5738_1_prt_4" for table "mpp5738" NOTICE: CREATE TABLE will create partition "mpp5738_1_prt_5" for table "mpp5738" NOTICE: CREATE TABLE will create partition "mpp5738_1_prt_6" for table "mpp5738" NOTICE: CREATE TABLE will create partition "mpp5738_1_prt_7" for table "mpp5738" NOTICE: CREATE TABLE will create partition "mpp5738_1_prt_8" for table "mpp5738" NOTICE: CREATE TABLE will create partition "mpp5738_1_prt_9" for table "mpp5738" insert into mpp5738 select i, i+1, i+2, i from generate_series(1, 10) i; select * from mpp5738; a | b | c | d ----+----+----+---- 1 | 2 | 3 | 1 3 | 4 | 5 | 3 5 | 6 | 7 | 5 7 | 8 | 9 | 7 9 | 10 | 11 | 9 2 | 3 | 4 | 2 4 | 5 | 6 | 4 6 | 7 | 8 | 6 8 | 9 | 10 | 8 10 | 11 | 12 | 10 (10 rows) alter table mpp5738 alter partition for(rank(1)) set with (appendonly=true); NOTICE: altering table "mpp5738_1_prt_1" (partition for rank 1 of relation "mpp5738") select * from mpp5738; a | b | c | d ----+----+----+---- 1 | 2 | 3 | 1 3 | 4 | 5 | 3 5 | 6 | 7 | 5 7 | 8 | 9 | 7 9 | 10 | 11 | 9 2 | 3 | 4 | 2 4 | 5 | 6 | 4 6 | 7 | 8 | 6 8 | 9 | 10 | 8 10 | 11 | 12 | 10 (10 rows) drop table mpp5738; drop table if exists mpp5754; NOTICE: table "mpp5754" does not exist, skipping CREATE TABLE mpp5754 ( N_NATIONKEY INTEGER, N_NAME CHAR(25), N_REGIONKEY INTEGER, N_COMMENT VARCHAR(152) ) with (appendonly = true, checksum = true) distributed by (N_NATIONKEY); copy mpp5754 from stdin with delimiter '|'; select * from mpp5754 order by n_nationkey; n_nationkey | n_name | n_regionkey | n_comment -------------+---------------------------+-------------+----------------------------------------------------- 0 | ALGERIA | 0 | haggle. carefully final deposits detect slyly agai (1 row) alter table mpp5754 set distributed randomly; select count(*) from mpp5754; count ------- 1 (1 row) alter table mpp5754 set distributed by (n_nationkey); select * from mpp5754 order by n_nationkey; n_nationkey | n_name | n_regionkey | n_comment -------------+---------------------------+-------------+----------------------------------------------------- 0 | ALGERIA | 0 | haggle. carefully final deposits detect slyly agai (1 row) drop table mpp5754; -- MPP-5918 create role atsdb; NOTICE: resource queue required -- using default resource queue "pg_default" create table owner_test(i int, toast text) distributed randomly; alter table owner_test owner to atsdb; alter table owner_test set with (reorganize = true) distributed by (i); -- verify, atsdb should own all three select a.relname, x.rolname as relowner, y.rolname as toastowner, z.rolname as toastidxowner from pg_class a, pg_class b, pg_class c, pg_authid x, pg_authid y, pg_authid z where a.reltoastrelid = b.oid and b.reltoastidxid=c.oid and a.relname='owner_test' and x.oid = a.relowner and y.oid = b.relowner and z.oid = c.relowner; relname | relowner | toastowner | toastidxowner ------------+----------+------------+--------------- owner_test | atsdb | atsdb | atsdb (1 row) -- MPP-9663 - Check that the ownership is consistent on the segments as well select a.relname, x.rolname as relowner, y.rolname as toastowner, z.rolname as toastidxowner from gp_dist_random('pg_class') a, gp_dist_random('pg_class') b, gp_dist_random('pg_class') c, pg_authid x, pg_authid y, pg_authid z where a.reltoastrelid=b.oid and b.reltoastidxid=c.oid and a.relname='owner_test' and x.oid = a.relowner and y.oid = b.relowner and z.oid = c.relowner and a.gp_segment_id = 0 and b.gp_segment_id = 0 and c.gp_segment_id = 0; relname | relowner | toastowner | toastidxowner ------------+----------+------------+--------------- owner_test | atsdb | atsdb | atsdb (1 row) -- MPP-9663 - The code path is different when the table has dropped columns alter table owner_test add column d text; alter table owner_test drop column d; alter table owner_test set with (reorganize = true) distributed by (i); select a.relname, x.rolname as relowner, y.rolname as toastowner, z.rolname as toastidxowner from gp_dist_random('pg_class') a, gp_dist_random('pg_class') b, gp_dist_random('pg_class') c, pg_authid x, pg_authid y, pg_authid z where a.reltoastrelid=b.oid and b.reltoastidxid=c.oid and a.relname='owner_test' and x.oid = a.relowner and y.oid = b.relowner and z.oid = c.relowner and a.gp_segment_id = 0 and b.gp_segment_id = 0 and c.gp_segment_id = 0; relname | relowner | toastowner | toastidxowner ------------+----------+------------+--------------- owner_test | atsdb | atsdb | atsdb (1 row) drop table owner_test; drop role atsdb; -- MPP-6332 create table abc (a int, b int, c int) distributed by (a); Alter table abc set distributed randomly; Alter table abc set with (reorganize=false) distributed randomly; WARNING: distribution policy of relation "abc" already set to DISTRIBUTED RANDOMLY HINT: Use ALTER TABLE "abc" SET WITH (REORGANIZE=TRUE) DISTRIBUTED RANDOMLY to force a random redistribution drop table abc; -- MPP-7770: disable changing storage options (default, for now) set gp_setwith_alter_storage = false; -- disallow, so fails create table atsdb (i int, j text) distributed by (j); alter table atsdb set with(appendonly = true); ERROR: option "appendonly" not supported drop table atsdb; -- MPP-18660: duplicate entry in gp_distribution_policy set enable_indexscan=on; set enable_seqscan=off; drop table if exists distrib_index_test; create table distrib_index_test (a int, b text) distributed by (a); select count(*) from gp_distribution_policy where localoid in (select oid from pg_class where relname='distrib_index_test'); count ------- 1 (1 row) begin; drop table distrib_index_test; rollback; select count(*) from gp_distribution_policy where localoid in (select oid from pg_class where relname='distrib_index_test'); count ------- 1 (1 row) reset enable_indexscan; reset enable_seqscan; drop table distrib_index_test; -- alter partitioned table crash -- Alter partitioned table set distributed by will crash when: -- 1. reorganize = false. -- 2. table have index. -- 3. partition table have "with" option. drop index if exists distrib_part_test_idx; NOTICE: index "distrib_part_test_idx" does not exist, skipping drop table if exists distrib_part_test; NOTICE: table "distrib_part_test" does not exist, skipping CREATE TABLE distrib_part_test ( col1 int, col2 decimal, col3 text, col4 bool ) distributed by (col1) partition by list(col2) ( partition part1 values(1,2,3,4,5,6,7,8,9,10) WITH (appendonly=false ) ); NOTICE: CREATE TABLE will create partition "distrib_part_test_1_prt_part1" for table "distrib_part_test" create index distrib_part_test_idx on distrib_part_test(col1); NOTICE: building index for child partition "distrib_part_test_1_prt_part1" ALTER TABLE public.distrib_part_test SET with (reorganize=false) DISTRIBUTED RANDOMLY; -- MPP-23801 -- -- ALTER TABLE set distribution key should check compatible with unique index. -- case 1 CREATE TABLE t_dist1(col1 INTEGER, col2 INTEGER, CONSTRAINT pk_t_dist1 PRIMARY KEY(col2)) DISTRIBUTED BY(col2); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t_dist1_pkey" for table "t_dist1" ALTER TABLE t_dist1 SET DISTRIBUTED BY(col1); ERROR: UNIQUE INDEX and DISTRIBUTED BY definitions incompatible HINT: the DISTRIBUTED BY columns must be equal to or a left-subset of the UNIQUE INDEX columns. -- case 2 CREATE TABLE t_dist2(col1 INTEGER, col2 INTEGER, col3 INTEGER, col4 INTEGER) DISTRIBUTED BY(col1); CREATE UNIQUE INDEX idx1_t_dist2 ON t_dist2(col1, col2); CREATE UNIQUE INDEX idx2_t_dist2 ON t_dist2(col1, col2, col3); CREATE UNIQUE INDEX idx3_t_dist2 ON t_dist2(col1, col2, col4); ALTER TABLE t_dist2 SET DISTRIBUTED BY(col1); WARNING: distribution policy of relation "t_dist2" already set to (col1) HINT: Use ALTER TABLE "t_dist2" SET WITH (REORGANIZE=TRUE) DISTRIBUTED BY (col1) to force redistribution ALTER TABLE t_dist2 SET DISTRIBUTED BY(col2); ALTER TABLE t_dist2 SET DISTRIBUTED BY(col1, col2); ALTER TABLE t_dist2 SET DISTRIBUTED BY(col1, col2, col3); ERROR: UNIQUE INDEX and DISTRIBUTED BY definitions incompatible HINT: the DISTRIBUTED BY columns must be equal to or a left-subset of the UNIQUE INDEX columns. ALTER TABLE t_dist2 SET DISTRIBUTED BY(col3); ERROR: UNIQUE INDEX and DISTRIBUTED BY definitions incompatible HINT: the DISTRIBUTED BY columns must be equal to or a left-subset of the UNIQUE INDEX columns. ALTER TABLE t_dist2 SET DISTRIBUTED BY(col4); ERROR: UNIQUE INDEX and DISTRIBUTED BY definitions incompatible HINT: the DISTRIBUTED BY columns must be equal to or a left-subset of the UNIQUE INDEX columns.