alter_distribution_policy_optimizer.out

-- 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;