-- 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 3:1 (slice1; segments: 3) (cost=0.00..1.01 rows=1 width=6) -> Index Scan using atsdb_i_idx on atsdb (cost=0.00..0.01 rows=1 width=6) Index Cond: i = 1 Settings: enable_seqscan=off; optimizer=on (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 3:1 (slice1; segments: 3) (cost=0.00..1.01 rows=1 width=6) -> Index Scan using atsdb_i_idx on atsdb (cost=0.00..0.01 rows=1 width=6) Index Cond: i = 1 Settings: enable_seqscan=off; optimizer=on (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: 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 4 | 4 7 | 7 3 | 3 6 | 6 9 | 9 10 | 10 2 | 2 5 | 5 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 -----+-----+----- 3 | 4 | 5 4 | 5 | 6 5 | 6 | 7 6 | 7 | 8 7 | 8 | 9 18 | 19 | 20 19 | 20 | 21 20 | 21 | 22 21 | 22 | 23 22 | 23 | 24 38 | 39 | 40 39 | 40 | 41 40 | 41 | 42 41 | 42 | 43 42 | 43 | 44 53 | 54 | 55 54 | 55 | 56 55 | 56 | 57 56 | 57 | 58 57 | 58 | 59 73 | 74 | 75 74 | 75 | 76 75 | 76 | 77 76 | 77 | 78 77 | 78 | 79 87 | 88 | 89 88 | 89 | 90 89 | 90 | 91 90 | 91 | 92 91 | 92 | 93 96 | 97 | 98 97 | 98 | 99 8 | 9 | 10 9 | 10 | 11 10 | 11 | 12 11 | 12 | 13 12 | 13 | 14 23 | 24 | 25 24 | 25 | 26 25 | 26 | 27 26 | 27 | 28 27 | 28 | 29 32 | 33 | 34 43 | 44 | 45 44 | 45 | 46 45 | 46 | 47 46 | 47 | 48 47 | 48 | 49 58 | 59 | 60 59 | 60 | 61 60 | 61 | 62 61 | 62 | 63 62 | 63 | 64 64 | 65 | 66 65 | 66 | 67 66 | 67 | 68 67 | 68 | 69 78 | 79 | 80 79 | 80 | 81 80 | 81 | 82 81 | 82 | 83 82 | 83 | 84 92 | 93 | 94 93 | 94 | 95 94 | 95 | 96 95 | 96 | 97 98 | 99 | 100 99 | 100 | 101 100 | 101 | 102 101 | 102 | 103 2 | 3 | 4 13 | 14 | 15 14 | 15 | 16 15 | 16 | 17 16 | 17 | 18 17 | 18 | 19 28 | 29 | 30 29 | 30 | 31 30 | 31 | 32 31 | 32 | 33 33 | 34 | 35 34 | 35 | 36 35 | 36 | 37 36 | 37 | 38 37 | 38 | 39 48 | 49 | 50 49 | 50 | 51 50 | 51 | 52 51 | 52 | 53 52 | 53 | 54 63 | 64 | 65 68 | 69 | 70 69 | 70 | 71 70 | 71 | 72 71 | 72 | 73 72 | 73 | 74 83 | 84 | 85 84 | 85 | 86 85 | 86 | 87 86 | 87 | 88 (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 -----+----- 3 | 4 6 | 7 18 | 19 20 | 21 42 | 43 53 | 54 73 | 74 76 | 77 77 | 78 88 | 89 90 | 91 91 | 92 12 | 13 23 | 24 26 | 27 27 | 28 43 | 44 58 | 59 60 | 61 65 | 66 82 | 83 93 | 94 99 | 100 14 | 15 15 | 16 31 | 32 33 | 34 36 | 37 37 | 38 48 | 49 52 | 53 70 | 71 84 | 85 85 | 86 4 | 5 7 | 8 21 | 22 39 | 40 56 | 57 57 | 58 75 | 76 89 | 90 96 | 97 10 | 11 11 | 12 24 | 25 44 | 45 45 | 46 61 | 62 66 | 67 67 | 68 79 | 80 98 | 99 13 | 14 17 | 18 28 | 29 30 | 31 35 | 36 50 | 51 51 | 52 63 | 64 69 | 70 72 | 73 86 | 87 5 | 6 19 | 20 22 | 23 38 | 39 40 | 41 41 | 42 54 | 55 55 | 56 74 | 75 87 | 88 97 | 98 8 | 9 9 | 10 25 | 26 32 | 33 46 | 47 47 | 48 59 | 60 62 | 63 64 | 65 78 | 79 80 | 81 81 | 82 92 | 93 94 | 95 95 | 96 100 | 101 101 | 102 2 | 3 16 | 17 29 | 30 34 | 35 49 | 50 68 | 69 71 | 72 83 | 84 (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 11 | 12 24 | 25 44 | 45 45 | 46 61 | 62 66 | 67 67 | 68 79 | 80 98 | 99 12 | 13 23 | 24 26 | 27 27 | 28 43 | 44 58 | 59 60 | 61 65 | 66 82 | 83 93 | 94 99 | 100 8 | 9 9 | 10 25 | 26 32 | 33 46 | 47 47 | 48 59 | 60 62 | 63 64 | 65 78 | 79 80 | 81 81 | 82 92 | 93 94 | 95 95 | 96 100 | 101 101 | 102 4 | 5 7 | 8 21 | 22 39 | 40 56 | 57 57 | 58 75 | 76 89 | 90 96 | 97 3 | 4 6 | 7 18 | 19 20 | 21 42 | 43 53 | 54 73 | 74 76 | 77 77 | 78 88 | 89 90 | 91 91 | 92 5 | 6 19 | 20 22 | 23 38 | 39 40 | 41 41 | 42 54 | 55 55 | 56 74 | 75 87 | 88 97 | 98 13 | 14 17 | 18 28 | 29 30 | 31 35 | 36 50 | 51 51 | 52 63 | 64 69 | 70 72 | 73 86 | 87 14 | 15 15 | 16 31 | 32 33 | 34 36 | 37 37 | 38 48 | 49 52 | 53 70 | 71 84 | 85 85 | 86 2 | 3 16 | 17 29 | 30 34 | 35 49 | 50 68 | 69 71 | 72 83 | 84 (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_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 atsdb | 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 ----------+------------+---------------+-------------+---------+-----------+---------------+------------------+---------------------+--------------------------+------------------------------+-------------+----------- 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 | 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 | (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 ----+----+----+---- 8 | 9 | 10 | 8 9 | 10 | 11 | 9 10 | 11 | 12 | 10 1 | 2 | 3 | 1 2 | 3 | 4 | 2 3 | 4 | 5 | 3 4 | 5 | 6 | 4 5 | 6 | 7 | 5 6 | 7 | 8 | 6 7 | 8 | 9 | 7 (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 ----+----+----+---- 3 | 4 | 5 | 3 4 | 5 | 6 | 4 5 | 6 | 7 | 5 6 | 7 | 8 | 6 7 | 8 | 9 | 7 1 | 2 | 3 | 1 2 | 3 | 4 | 2 8 | 9 | 10 | 8 9 | 10 | 11 | 9 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-8474: Index relfilenode mismatch: entry db to segment db. -- -- XXX This really belongs in alter_table.sql but this is not -- in use in current_good_schedule. drop table if exists mpp8474 cascade; --ignore NOTICE: table "mpp8474" does not exist, skipping create table mpp8474(a int, b int, c text) with (appendonly=true) distributed by (b); create index mpp8474_a on mpp8474(a); alter table mpp8474 add column d int default 10; select 'Mismatched relfilenodes:' as oops, e.oid::regclass as entry_oid, e.relkind, e.relfilenode as entry_relfilenode, s.segid, s.segfilenode as segment_relfilenode from pg_class e, ( select gp_execution_segment(), oid, relfilenode from gp_dist_random('pg_class') ) s (segid, segoid, segfilenode) where e.oid = s.segoid and e.relfilenode != s.segfilenode and e.relname ~ '^mpp8474.*'; oops | entry_oid | relkind | entry_relfilenode | segid | segment_relfilenode ------+-----------+---------+-------------------+-------+--------------------- (0 rows) drop table mpp8474; -- MPP-18660: duplicate entry in gp_distribution_policy set enable_indexscan=on; set enable_seqscan=off; drop table if exists distrib_index_test; NOTICE: table "distrib_index_test" does not exist, skipping 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;