src/test/regress/expected/alter_table_set_am.out

-- Check changing table access method
\set HIDE_TABLEAM off
-- Scenario 1: Changing to the same AM: it should have no effect but
-- make sure it doesn't rewrite table or blow up existing reloptions:
CREATE TABLE sameam_heap(a int, b int) WITH (fillfactor=70) DISTRIBUTED BY (a);
CREATE TABLE sameam_heap2(a int, b int) WITH (fillfactor=70) DISTRIBUTED BY (a);
CREATE TABLE sameam_ao(a int, b int) WITH (appendoptimized=true, orientation=row, compresstype=zlib, compresslevel=3);
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.
CREATE TABLE sameam_co(a int, b int) WITH (appendoptimized=true, orientation=column, compresstype=rle_type, compresslevel=3);
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.
INSERT INTO sameam_heap SELECT i,i FROM generate_series(1,5) i;
INSERT INTO sameam_heap2 SELECT i,i FROM generate_series(1,5) i;
INSERT INTO sameam_ao SELECT i,i FROM generate_series(1,5) i;
INSERT INTO sameam_co SELECT i,i FROM generate_series(1,5) i;
CREATE TEMP TABLE relfilebeforesameam AS
    SELECT -1 segid, relfilenode FROM pg_class WHERE relname LIKE 'sameam_%'
    UNION SELECT gp_segment_id segid, relfilenode FROM gp_dist_random('pg_class')
    WHERE relname LIKE 'sameam_%' ORDER BY segid;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'segid' 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.
-- changing to the same access method shouldn't rewrite the table
-- (i.e. the relfilenodes shouldn't change)
ALTER TABLE sameam_heap SET ACCESS METHOD heap;
ALTER TABLE sameam_heap2 SET WITH (appendoptimized=false); -- Alternative syntax of ATSETAM
ALTER TABLE sameam_ao SET ACCESS METHOD ao_row;
ALTER TABLE sameam_co SET ACCESS METHOD ao_column;
CREATE TEMP TABLE relfileaftersameam AS
    SELECT -1 segid, relfilenode FROM pg_class WHERE relname LIKE 'sameam_%'
    UNION SELECT gp_segment_id segid, relfilenode FROM gp_dist_random('pg_class')
    WHERE relname LIKE 'sameam_%' ORDER BY segid;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'segid' 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.
-- relfilenodes shouldn't change
SELECT * FROM relfilebeforesameam EXCEPT SELECT * FROM relfileaftersameam;
 segid | relfilenode 
-------+-------------
(0 rows)

-- reloptions should remain the same
SELECT relname, reloptions FROM pg_class WHERE relname LIKE 'sameam_%';
   relname    |               reloptions                
--------------+-----------------------------------------
 sameam_ao    | {compresstype=zlib,compresslevel=3}
 sameam_co    | {compresstype=rle_type,compresslevel=3}
 sameam_heap  | {fillfactor=70}
 sameam_heap2 | {fillfactor=70}
(4 rows)

-- Scenario 2: Heap to AO
CREATE TABLE heap2ao(a int, b int) WITH (fillfactor=70) DISTRIBUTED BY (a);
CREATE TABLE heap2ao2(a int, b int) DISTRIBUTED BY (a);
CREATE INDEX heapi ON heap2ao(b);
ALTER TABLE heap2ao2 ADD CONSTRAINT unique_constraint UNIQUE (a);
INSERT INTO heap2ao SELECT i,i FROM generate_series(1,5) i;
INSERT INTO heap2ao2 SELECT i,i FROM generate_series(1,5) i;
-- Check reloptions once before altering.
SELECT reloptions from pg_class where relname in ('heap2ao', 'heap2ao2');
   reloptions    
-----------------
 
 {fillfactor=70}
(2 rows)

CREATE TEMP TABLE relfilebeforeao AS
    SELECT -1 segid, relfilenode FROM pg_class WHERE relname in ('heap2ao', 'heap2ao2', 'heapi')
    UNION SELECT gp_segment_id segid, relfilenode FROM gp_dist_random('pg_class')
    WHERE relname in ('heap2ao', 'heap2ao2', 'heapi') ORDER BY segid;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'segid' 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.
-- Set default storage options for the table to inherit from
SET gp_default_storage_options = 'blocksize=65536, compresstype=zlib, compresslevel=5, checksum=true';
-- Alter table heap to AO should work, even if heap table has unique indexes.
ALTER TABLE heap2ao SET ACCESS METHOD ao_row;
ALTER TABLE heap2ao2 SET WITH (appendoptimized=true);
-- The altered tables should have AO AM
-- The altered tables should inherit storage options from gp_default_storage_options
-- And, the original heap reloptions are gone (in this case, 'fillfactor').
-- Also, equivalent indexes have been created
\d+ heap2ao
                                  Table "public.heap2ao"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
--------+---------+-----------+----------+---------+---------+--------------+-------------
 a      | integer |           |          |         | plain   |              | 
 b      | integer |           |          |         | plain   |              | 
Compression Type: zlib
Compression Level: 5
Block Size: 65536
Checksum: t
Indexes:
    "heapi" btree (b)
Distributed by: (a)
Access method: ao_row
Options: blocksize=65536, compresslevel=5

\d+ heap2ao2
                                 Table "public.heap2ao2"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
--------+---------+-----------+----------+---------+---------+--------------+-------------
 a      | integer |           |          |         | plain   |              | 
 b      | integer |           |          |         | plain   |              | 
Compression Type: zlib
Compression Level: 5
Block Size: 65536
Checksum: t
Indexes:
    "unique_constraint" UNIQUE CONSTRAINT, btree (a)
Distributed by: (a)
Access method: ao_row
Options: blocksize=65536, compresslevel=5

-- Check data is intact
SELECT * FROM heap2ao;
 a | b 
---+---
 1 | 1
 2 | 2
 3 | 3
 4 | 4
 5 | 5
(5 rows)

SELECT * FROM heap2ao2;
 a | b 
---+---
 1 | 1
 2 | 2
 3 | 3
 4 | 4
 5 | 5
(5 rows)

-- The tables and indexes should have been rewritten (should have different relfilenodes)
CREATE TEMP TABLE relfileafterao AS
    SELECT -1 segid, relfilenode FROM pg_class WHERE relname in ('heap2ao', 'heap2ao2', 'heapi')
    UNION SELECT gp_segment_id segid, relfilenode FROM gp_dist_random('pg_class')
    WHERE relname in ('heap2ao', 'heap2ao2', 'heapi') ORDER BY segid;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'segid' 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.
SELECT * FROM relfilebeforeao INTERSECT SELECT * FROM relfileafterao;
 segid | relfilenode 
-------+-------------
(0 rows)

-- aux tables are created, pg_appendonly row is created
SELECT * FROM gp_toolkit.__gp_aoseg('heap2ao');
 segment_id | segno | eof | tupcount | varblockcount | eof_uncompressed | modcount | formatversion | state 
------------+-------+-----+----------+---------------+------------------+----------+---------------+-------
          2 |     0 |  40 |        1 |             1 |               40 |        1 |             3 |     1
          0 |     0 |  72 |        3 |             1 |               88 |        1 |             3 |     1
          1 |     0 |  40 |        1 |             1 |               40 |        1 |             3 |     1
(3 rows)

SELECT gp_segment_id, (gp_toolkit.__gp_aovisimap('heap2ao')).* FROM gp_dist_random('gp_id');
 gp_segment_id | tid | segno | row_num 
---------------+-----+-------+---------
(0 rows)

SELECT gp_segment_id, (gp_toolkit.__gp_aoblkdir('heap2ao')).* FROM gp_dist_random('gp_id');
 gp_segment_id | tupleid | segno | columngroup_no | entry_no | first_row_no | file_offset | row_count 
---------------+---------+-------+----------------+----------+--------------+-------------+-----------
             0 | (0,1)   |     0 |              0 |        0 |            1 |           0 |         3
             1 | (0,1)   |     0 |              0 |        0 |            1 |           0 |         1
             2 | (0,1)   |     0 |              0 |        0 |            1 |           0 |         1
(3 rows)

SELECT * FROM gp_toolkit.__gp_aoseg('heap2ao2');
 segment_id | segno | eof | tupcount | varblockcount | eof_uncompressed | modcount | formatversion | state 
------------+-------+-----+----------+---------------+------------------+----------+---------------+-------
          1 |     0 |  40 |        1 |             1 |               40 |        1 |             3 |     1
          2 |     0 |  40 |        1 |             1 |               40 |        1 |             3 |     1
          0 |     0 |  72 |        3 |             1 |               88 |        1 |             3 |     1
(3 rows)

SELECT gp_segment_id, (gp_toolkit.__gp_aovisimap('heap2ao2')).* FROM gp_dist_random('gp_id');
 gp_segment_id | tid | segno | row_num 
---------------+-----+-------+---------
(0 rows)

-- pg_appendonly should have entries associated with the new AO tables
SELECT c.relname FROM pg_class c, pg_appendonly p WHERE c.relname LIKE 'heap2ao%' AND c.oid = p.relid;
 relname  
----------
 heap2ao
 heap2ao2
(2 rows)

-- check inherited tables
CREATE TABLE heapbase (a int, b int);
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.
CREATE TABLE heapchild (c int) INHERITS (heapbase);
NOTICE:  table has parent, setting distribution columns to match parent table
CREATE TABLE heapbase2 (a int, b int);
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.
CREATE TABLE heapchild2 (c int) INHERITS (heapbase2);
NOTICE:  table has parent, setting distribution columns to match parent table
INSERT INTO heapbase SELECT i,i FROM generate_series(1,5) i;
INSERT INTO heapchild SELECT i,i,i FROM generate_series(1,5) i;
INSERT INTO heapbase2 SELECT i,i FROM generate_series(1,5) i;
INSERT INTO heapchild2 SELECT i,i,i FROM generate_series(1,5) i;
CREATE TEMP TABLE inheritrelfilebefore AS
    SELECT -1 segid, relfilenode FROM pg_class WHERE relname in ('heapbase', 'heapbase2')
    UNION SELECT gp_segment_id segid, relfilenode FROM gp_dist_random('pg_class')
    WHERE relname in ('heapbase', 'heapbase2') ORDER BY segid;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'segid' 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.
CREATE TEMP TABLE inheritchildrelfilebefore AS
    SELECT -1 segid, relfilenode FROM pg_class WHERE relname in ('heapchild', 'heapchild2')
    UNION SELECT gp_segment_id segid, relfilenode FROM gp_dist_random('pg_class')
    WHERE relname in ('heapchild', 'heapchild2') ORDER BY segid;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'segid' 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 heapbase SET ACCESS METHOD ao_row;
ALTER TABLE heapbase2 SET WITH (appendoptimized=true);
-- The altered tables should inherit storage options from gp_default_storage_options
show gp_default_storage_options;
                   gp_default_storage_options                    
-----------------------------------------------------------------
 blocksize=65536,compresstype=zlib,compresslevel=5,checksum=true
(1 row)

SELECT blocksize,compresslevel,checksum,compresstype,columnstore
FROM pg_appendonly WHERE relid in ('heapbase'::regclass::oid, 'heapbase2'::regclass::oid);
 blocksize | compresslevel | checksum | compresstype | columnstore 
-----------+---------------+----------+--------------+-------------
     65536 |             5 | t        | zlib         | f
     65536 |             5 | t        | zlib         | f
(2 rows)

SELECT reloptions from pg_class where relname in ('heapbase','heapbase2');
            reloptions             
-----------------------------------
 {blocksize=65536,compresslevel=5}
 {blocksize=65536,compresslevel=5}
(2 rows)

SELECT blocksize,compresslevel,checksum,compresstype,columnstore
FROM pg_appendonly WHERE relid in ('heapchild'::regclass::oid, 'heapchild2'::regclass::oid);
 blocksize | compresslevel | checksum | compresstype | columnstore 
-----------+---------------+----------+--------------+-------------
(0 rows)

SELECT reloptions from pg_class where relname in ('heapchild','heapchild2');
 reloptions 
------------
 
 
(2 rows)

-- The altered parent tables should have AO AM but child tables are still heap
SELECT c.relname, a.amname FROM pg_class c JOIN pg_am a ON c.relam = a.oid WHERE c.relname LIKE 'heapbase%' OR c.relname LIKE 'heapchild%';
  relname   | amname 
------------+--------
 heapbase   | ao_row
 heapbase2  | ao_row
 heapchild  | heap
 heapchild2 | heap
(4 rows)

-- Check data is intact
SELECT * FROM heapbase;
 a | b 
---+---
 5 | 5
 5 | 5
 1 | 1
 1 | 1
 2 | 2
 3 | 3
 4 | 4
 2 | 2
 3 | 3
 4 | 4
(10 rows)

SELECT * FROM heapbase2;
 a | b 
---+---
 2 | 2
 3 | 3
 4 | 4
 2 | 2
 3 | 3
 4 | 4
 5 | 5
 5 | 5
 1 | 1
 1 | 1
(10 rows)

-- relfile node should change for base table set to AO
CREATE TEMP TABLE inheritrelfileafter AS
    SELECT -1 segid, relfilenode FROM pg_class WHERE relname in ('heapbase', 'heapbase2')
    UNION SELECT gp_segment_id segid, relfilenode FROM gp_dist_random('pg_class')
    WHERE relname in ('heapbase', 'heapbase2') ORDER BY segid;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'segid' 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.
SELECT * FROM inheritrelfilebefore INTERSECT SELECT * FROM inheritrelfileafter;
 segid | relfilenode 
-------+-------------
(0 rows)

-- relfile node should not change for child table
CREATE TEMP TABLE inheritchildrelfileafter AS
    SELECT -1 segid, relfilenode FROM pg_class WHERE relname in ('heapchild', 'heapchild2')
    UNION SELECT gp_segment_id segid, relfilenode FROM gp_dist_random('pg_class')
    WHERE relname in ('heapchild', 'heapchild2') ORDER BY segid;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'segid' 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.
SELECT count(*) FROM (SELECT * FROM inheritchildrelfilebefore UNION SELECT * FROM inheritchildrelfileafter)a;
 count 
-------
     8
(1 row)

-- aux tables are created, pg_appendonly row is created
SELECT * FROM gp_toolkit.__gp_aoseg('heapbase');
 segment_id | segno | eof | tupcount | varblockcount | eof_uncompressed | modcount | formatversion | state 
------------+-------+-----+----------+---------------+------------------+----------+---------------+-------
          0 |     0 |  72 |        3 |             1 |               88 |        1 |             3 |     1
          1 |     0 |  40 |        1 |             1 |               40 |        1 |             3 |     1
          2 |     0 |  40 |        1 |             1 |               40 |        1 |             3 |     1
(3 rows)

SELECT gp_segment_id, (gp_toolkit.__gp_aovisimap('heapbase')).* FROM gp_dist_random('gp_id');
 gp_segment_id | tid | segno | row_num 
---------------+-----+-------+---------
(0 rows)

SELECT * FROM gp_toolkit.__gp_aoseg('heapbase2');
 segment_id | segno | eof | tupcount | varblockcount | eof_uncompressed | modcount | formatversion | state 
------------+-------+-----+----------+---------------+------------------+----------+---------------+-------
          1 |     0 |  40 |        1 |             1 |               40 |        1 |             3 |     1
          2 |     0 |  40 |        1 |             1 |               40 |        1 |             3 |     1
          0 |     0 |  72 |        3 |             1 |               88 |        1 |             3 |     1
(3 rows)

SELECT gp_segment_id, (gp_toolkit.__gp_aovisimap('heapbase2')).* FROM gp_dist_random('gp_id');
 gp_segment_id | tid | segno | row_num 
---------------+-----+-------+---------
(0 rows)

SELECT c.relname FROM pg_class c, pg_appendonly p WHERE c.relname LIKE 'heapbase%' AND c.oid = p.relid;
  relname  
-----------
 heapbase
 heapbase2
(2 rows)

-- aux tables are not created for child table
SELECT * FROM gp_toolkit.__gp_aoseg('heapchild');
ERROR:  Relation 'heapchild' does not have appendoptimized row-oriented storage  (seg0 slice1 127.0.1.1:7002 pid=26472)
SELECT gp_segment_id, (gp_toolkit.__gp_aovisimap('heapchild')).* FROM gp_dist_random('gp_id');
ERROR:  function not supported on relation  (seg0 slice1 192.168.0.148:7002 pid=674753)
SELECT * FROM gp_toolkit.__gp_aoseg('heapchild2');
ERROR:  Relation 'heapchild2' does not have appendoptimized row-oriented storage  (seg0 slice1 127.0.1.1:7002 pid=26472)
SELECT gp_segment_id, (gp_toolkit.__gp_aovisimap('heapchild2')).* FROM gp_dist_random('gp_id');
ERROR:  function not supported on relation  (seg0 slice1 192.168.0.148:7002 pid=674753)
SELECT c.relname FROM pg_class c, pg_appendonly p WHERE c.relname LIKE 'heapchild%' AND c.oid = p.relid;
 relname 
---------
(0 rows)

-- Scenario 3: AO to Heap
SET gp_default_storage_options = 'blocksize=65536, compresstype=zlib, compresslevel=5, checksum=true';
CREATE TABLE ao2heap(a int, b int) WITH (appendonly=true);
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.
CREATE TABLE ao2heap2(a int, b int) WITH (appendonly=true);
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.
CREATE INDEX aoi ON ao2heap(b);
INSERT INTO ao2heap SELECT i,i FROM generate_series(1,5) i;
INSERT INTO ao2heap2 SELECT i,i FROM generate_series(1,5) i;
-- Check once that the AO tables have the custom reloptions 
SELECT relname, reloptions FROM pg_class WHERE relname LIKE 'ao2heap%';
 relname  |            reloptions             
----------+-----------------------------------
 ao2heap  | {blocksize=65536,compresslevel=5}
 ao2heap2 | {blocksize=65536,compresslevel=5}
(2 rows)

-- Check once that the AO tables have relfrozenxid = 0
SELECT relname, relfrozenxid FROM pg_class WHERE relname LIKE 'ao2heap%';
 relname  | relfrozenxid 
----------+--------------
 ao2heap  |            0
 ao2heap2 |            0
(2 rows)

CREATE TEMP TABLE relfilebeforeao2heap AS
    SELECT -1 segid, relfilenode FROM pg_class WHERE relname in ('ao2heap', 'ao2heap2', 'aoi')
    UNION SELECT gp_segment_id segid, relfilenode FROM gp_dist_random('pg_class')
    WHERE relname in ('ao2heap', 'ao2heap2', 'aoi') ORDER BY segid;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'segid' 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.
-- Altering AO to heap
ALTER TABLE ao2heap SET ACCESS METHOD heap;
ALTER TABLE ao2heap2 SET WITH (appendoptimized=false);
-- The tables and indexes should have been rewritten (should have different relfilenodes)
CREATE TEMP TABLE relfileafterao2heap AS
    SELECT -1 segid, relfilenode FROM pg_class WHERE relname in ('ao2heap', 'ao2heap2', 'aoi')
    UNION SELECT gp_segment_id segid, relfilenode FROM gp_dist_random('pg_class')
    WHERE relname in ('ao2heap', 'ao2heap2', 'aoi') ORDER BY segid;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'segid' 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.
SELECT * FROM relfilebeforeao2heap INTERSECT SELECT * FROM relfileafterao2heap;
 segid | relfilenode 
-------+-------------
(0 rows)

-- Check data is intact
SELECT * FROM ao2heap;
 a | b 
---+---
 2 | 2
 3 | 3
 4 | 4
 1 | 1
 5 | 5
(5 rows)

SELECT * FROM ao2heap2;
 a | b 
---+---
 2 | 2
 3 | 3
 4 | 4
 1 | 1
 5 | 5
(5 rows)

-- No AO aux tables should be left
SELECT * FROM gp_toolkit.__gp_aoseg('ao2heap');
ERROR:  Relation 'ao2heap' does not have appendoptimized row-oriented storage  (seg0 slice1 127.0.1.1:7002 pid=26472)
SELECT gp_segment_id, (gp_toolkit.__gp_aovisimap('ao2heap')).* FROM gp_dist_random('gp_id');
ERROR:  function not supported on relation  (seg0 slice1 192.168.0.148:7002 pid=674753)
SELECT gp_segment_id, (gp_toolkit.__gp_aoblkdir('ao2heap')).* FROM gp_dist_random('gp_id');
ERROR:  function not supported on non append-optimized relation  (seg2 slice1 192.168.0.148:7004 pid=1401173)
SELECT * FROM gp_toolkit.__gp_aoseg('ao2heap2');
ERROR:  Relation 'ao2heap2' does not have appendoptimized row-oriented storage  (seg0 slice1 127.0.1.1:7002 pid=26472)
SELECT gp_segment_id, (gp_toolkit.__gp_aovisimap('ao2heap2')).* FROM gp_dist_random('gp_id');
ERROR:  function not supported on relation  (seg0 slice1 192.168.0.148:7002 pid=674753)
-- No pg_appendonly entries should be left too
SELECT c.relname FROM pg_class c, pg_appendonly p WHERE c.relname LIKE 'ao2heap%' AND c.oid = p.relid;
 relname 
---------
(0 rows)

-- The altered tables should have heap AM.
SELECT c.relname, a.amname FROM pg_class c JOIN pg_am a ON c.relam = a.oid WHERE c.relname LIKE 'ao2heap%';
 relname  | amname 
----------+--------
 ao2heap  | heap
 ao2heap2 | heap
(2 rows)

-- The new heap tables shouldn't have the old AO table's reloptions
SELECT relname, reloptions FROM pg_class WHERE relname LIKE 'ao2heap%';
 relname  | reloptions 
----------+------------
 ao2heap  | 
 ao2heap2 | 
(2 rows)

-- The new heap tables should have a valid relfrozenxid
SELECT relname, relfrozenxid <> '0' FROM pg_class WHERE relname LIKE 'ao2heap%';
 relname  | ?column? 
----------+----------
 ao2heap  | t
 ao2heap2 | t
(2 rows)

DROP TABLE ao2heap;
DROP TABLE ao2heap2;
-- Scenario 4: Set reloptions along with change of AM.
CREATE TABLE ataoset(a int);
CREATE TABLE ataoset2(a int);
INSERT INTO ataoset select * from generate_series(1, 5);
INSERT INTO ataoset2 select * from generate_series(1, 5);
-- Error: user specifies a different AM than the one indicated in the WITH clause
ALTER TABLE ataoset SET ACCESS METHOD ao_row WITH(appendonly=true, orientation=column);
ERROR:  ACCESS METHOD is specified as "ao_row" but the WITH option indicates it to be "ao_column"
LINE 1: ALTER TABLE ataoset SET ACCESS METHOD ao_row WITH(appendonly...
                                                     ^
-- Error: user specifiies AO reloption when altering an AO table to heap.
CREATE TABLE ao2heaperror (a int) WITH (appendonly=true);
ALTER TABLE ao2heaperror SET ACCESS METHOD heap WITH (blocksize=65536);
ERROR:  unrecognized parameter "blocksize"
DROP TABLE ao2heaperror;
-- Scenario 4.1: change from heap to AO with customized storage options
CREATE TEMP TABLE relfilebeforeat AS
    SELECT -1 segid, relname, relfilenode FROM pg_class WHERE relname LIKE 'ataoset%'
    UNION SELECT gp_segment_id segid, relname, relfilenode FROM gp_dist_random('pg_class')
    WHERE relname LIKE 'ataoset%' ORDER BY segid;
ALTER TABLE ataoset SET WITH (appendonly=true, blocksize=65536, compresslevel=7);
ALTER TABLE ataoset2 SET ACCESS METHOD ao_row WITH (blocksize=65536, compresslevel=7);
CREATE TEMP TABLE relfileafterat AS
    SELECT -1 segid, relname, relfilenode FROM pg_class WHERE relname LIKE 'ataoset%'
    UNION SELECT gp_segment_id segid, relname, relfilenode FROM gp_dist_random('pg_class')
    WHERE relname LIKE 'ataoset%' ORDER BY segid;
-- relfilenode changed
SELECT * FROM relfilebeforeat INTERSECT SELECT * FROM relfileafterat;
 segid | relname | relfilenode 
-------+---------+-------------
(0 rows)

DROP TABLE relfilebeforeat;
DROP TABLE relfileafterat;
-- table AMs are changed to AO, and reloptions changed to what we set
SELECT c.relname, a.amname, c.reloptions FROM pg_class c JOIN pg_am a ON c.relam = a.oid WHERE c.relname LIKE 'ataoset%';
 relname  | amname |            reloptions             
----------+--------+-----------------------------------
 ataoset  | ao_row | {blocksize=65536,compresslevel=7}
 ataoset2 | ao_row | {blocksize=65536,compresslevel=7}
(2 rows)

-- data are intact
SELECT count(*) FROM ataoset;
 count 
-------
     5
(1 row)

SELECT count(*) FROM ataoset2;
 count 
-------
     5
(1 row)

-- Scenario 4.2. Alter the table w/ the exact same reloptions. 
-- AM, relfilenodes and reloptions all should remain the same
CREATE TEMP TABLE relfilebeforeat AS
    SELECT -1 segid, relname, relfilenode FROM pg_class WHERE relname LIKE 'ataoset%'
    UNION SELECT gp_segment_id segid, relname, relfilenode FROM gp_dist_random('pg_class')
    WHERE relname LIKE 'ataoset%' ORDER BY segid;
-- Firstly alter them with the exact same options as case 1.
-- Secondly alter them with same option values but different order.
-- Neither should trigger table rewrite. 
ALTER TABLE ataoset SET WITH (appendonly=true, blocksize=65536, compresslevel=7);
ALTER TABLE ataoset SET WITH (appendonly=true, compresslevel=7, blocksize=65536);
ALTER TABLE ataoset2 SET ACCESS METHOD ao_row WITH (blocksize=65536, compresslevel=7);
ALTER TABLE ataoset2 SET ACCESS METHOD ao_row WITH (compresslevel=7, blocksize=65536);
CREATE TEMP TABLE relfileafterat AS
    SELECT -1 segid, relname, relfilenode FROM pg_class WHERE relname LIKE 'ataoset%'
    UNION SELECT gp_segment_id segid, relname, relfilenode FROM gp_dist_random('pg_class')
    WHERE relname LIKE 'ataoset%' ORDER BY segid;
-- no change to relfilenode
SELECT * FROM relfilebeforeat EXCEPT SELECT * FROM relfileafterat;
 segid | relname | relfilenode 
-------+---------+-------------
(0 rows)

DROP TABLE relfilebeforeat;
DROP TABLE relfileafterat;
-- table AMs are still AO, but reloptions should reflect the order of options in the most recent ALTER TABLE
SELECT c.relname, a.amname, c.reloptions FROM pg_class c JOIN pg_am a ON c.relam = a.oid WHERE c.relname LIKE 'ataoset%';
 relname  | amname |            reloptions             
----------+--------+-----------------------------------
 ataoset  | ao_row | {compresslevel=7,blocksize=65536}
 ataoset2 | ao_row | {compresslevel=7,blocksize=65536}
(2 rows)

-- data still intact
SELECT count(*) FROM ataoset;
 count 
-------
     5
(1 row)

SELECT count(*) FROM ataoset2;
 count 
-------
     5
(1 row)

-- Scenario 4.3. Use the same syntax to alter from AO to AO, but specifying different storage options.
--         Table should be rewritten.
CREATE TEMP TABLE relfilebeforeao AS
    SELECT -1 segid, relname, relfilenode FROM pg_class WHERE relname LIKE 'ataoset%'
    UNION SELECT gp_segment_id segid, relname, relfilenode FROM gp_dist_random('pg_class')
    WHERE relname LIKE 'ataoset%' ORDER BY segid;
ERROR:  relation "relfilebeforeao" already exists
ALTER TABLE ataoset SET WITH (appendonly=true, blocksize=32768);
ALTER TABLE ataoset2 SET ACCESS METHOD ao_row WITH (blocksize=32768);
CREATE TEMP TABLE relfileafterao AS
    SELECT -1 segid, relname, relfilenode FROM pg_class WHERE relname LIKE 'ataoset%'
    UNION SELECT gp_segment_id segid, relname, relfilenode FROM gp_dist_random('pg_class')
    WHERE relname LIKE 'ataoset%' ORDER BY segid;
ERROR:  relation "relfileafterao" already exists
-- table is rewritten
SELECT * FROM relfilebeforeao INTERSECT SELECT * FROM relfileafterao;
 segid | relfilenode 
-------+-------------
(0 rows)

-- reloptions changed too
SELECT c.relname, a.amname, c.reloptions FROM pg_class c JOIN pg_am a ON c.relam = a.oid WHERE c.relname LIKE 'ataoset%';
 relname  | amname |            reloptions             
----------+--------+-----------------------------------
 ataoset  | ao_row | {compresslevel=7,blocksize=32768}
 ataoset2 | ao_row | {compresslevel=7,blocksize=32768}
(2 rows)

DROP TABLE relfilebeforeao;
DROP TABLE relfileafterao;
-- Scenario 4.4. Alter the tables back to heap and set some reloptions too.
CREATE TEMP TABLE relfilebeforeat AS
    SELECT -1 segid, relname, relfilenode FROM pg_class WHERE relname LIKE 'ataoset%'
    UNION SELECT gp_segment_id segid, relname, relfilenode FROM gp_dist_random('pg_class')
    WHERE relname LIKE 'ataoset%' ORDER BY segid;
ALTER TABLE ataoset SET ACCESS METHOD heap WITH (fillfactor=70);
ALTER TABLE ataoset2 SET ACCESS METHOD heap WITH (fillfactor=70);
CREATE TEMP TABLE relfileafterat AS
    SELECT -1 segid, relname, relfilenode FROM pg_class WHERE relname LIKE 'ataoset%'
    UNION SELECT gp_segment_id segid, relname, relfilenode FROM gp_dist_random('pg_class')
    WHERE relname LIKE 'ataoset%' ORDER BY segid;
-- there's a table rewrite
SELECT * FROM relfilebeforeat INTERSECT SELECT * FROM relfileafterat;
 segid | relname | relfilenode 
-------+---------+-------------
(0 rows)

DROP TABLE relfilebeforeat;
DROP TABLE relfileafterat;
-- reloptions and AM also changed
SELECT c.relname, a.amname, c.reloptions FROM pg_class c JOIN pg_am a ON c.relam = a.oid WHERE c.relname LIKE 'ataoset%';
 relname  | amname |   reloptions    
----------+--------+-----------------
 ataoset  | heap   | {fillfactor=70}
 ataoset2 | heap   | {fillfactor=70}
(2 rows)

DROP TABLE ataoset;
DROP TABLE ataoset2;
-- Scenario 5: AO to AOCO 
SET gp_default_storage_options = 'blocksize=65536, compresstype=zlib, compresslevel=5, checksum=true';
CREATE TABLE ao2co(a int, b int) WITH (appendonly=true);
CREATE TABLE ao2co2(a int, b int) WITH (appendonly=true);
CREATE TABLE ao2co3(a int, b int) WITH (appendonly=true);
CREATE TABLE ao2co4(a int, b int) WITH (appendonly=true);
CREATE INDEX index_ao2co ON ao2co(b);
CREATE INDEX index_ao2co3 ON ao2co3(b);
INSERT INTO ao2co SELECT i,i FROM generate_series(1,5) i;
INSERT INTO ao2co2 SELECT i,i FROM generate_series(1,5) i;
INSERT INTO ao2co3 SELECT i,i FROM generate_series(1,5) i;
INSERT INTO ao2co4 SELECT i,i FROM generate_series(1,5) i;
-- ERROR: conflicting storage option specified.
ALTER TABLE ao2co SET ACCESS METHOD ao_column WITH (appendoptimized=true, orientation=row);
ERROR:  ACCESS METHOD is specified as "ao_column" but the WITH option indicates it to be "ao_row"
LINE 1: ALTER TABLE ao2co SET ACCESS METHOD ao_column WITH (appendop...
                                                      ^
-- Use of *both* ACCESS METHOD and WITH clauses is allowed, but we'll print a hint to indicate the redundancy.
ALTER TABLE ao2co SET ACCESS METHOD ao_row WITH (appendoptimized=true, orientation=row);
HINT:  Only one of these is needed to indicate access method: the SET ACCESS METHOD clause or the options in the WITH clause.
NOTICE:  Redundant clauses are used to indicate the access method.
CREATE TEMP TABLE relfilebeforeao AS
    SELECT -1 segid, relname, relfilenode FROM pg_class WHERE relname LIKE 'ao2co%'
    UNION SELECT gp_segment_id segid, relname, relfilenode FROM gp_dist_random('pg_class')
    WHERE relname LIKE 'ao2co%' ORDER BY segid;
-- Check once the reloptions
SELECT c.relname, a.amname, c.reloptions FROM pg_class c JOIN pg_am a ON c.relam = a.oid WHERE c.relname LIKE 'ao2co%';
 relname | amname |            reloptions             
---------+--------+-----------------------------------
 ao2co   | ao_row | {blocksize=65536,compresslevel=5}
 ao2co2  | ao_row | {blocksize=65536,compresslevel=5}
 ao2co3  | ao_row | {blocksize=65536,compresslevel=5}
 ao2co4  | ao_row | {blocksize=65536,compresslevel=5}
(4 rows)

-- Altering AO to AOCO with various syntaxes, reloptions:
ALTER TABLE ao2co SET ACCESS METHOD ao_column;
ALTER TABLE ao2co2 SET WITH (appendoptimized=true, orientation=column);
ALTER TABLE ao2co3 SET ACCESS METHOD ao_column WITH (blocksize=32768, compresstype=rle_type, compresslevel=3);
ALTER TABLE ao2co4 SET WITH (appendoptimized=true, orientation=column, blocksize=32768, compresstype=rle_type, compresslevel=3);
-- The tables are rewritten
CREATE TEMP TABLE relfileafterao AS
    SELECT -1 segid, relname, relfilenode FROM pg_class WHERE relname LIKE 'ao2co%'
    UNION SELECT gp_segment_id segid, relname, relfilenode FROM gp_dist_random('pg_class')
    WHERE relname LIKE 'ao2co%' ORDER BY segid;
SELECT * FROM relfilebeforeao INTERSECT SELECT * FROM relfileafterao;
 segid | relname | relfilenode 
-------+---------+-------------
(0 rows)

DROP TABLE relfilebeforeao;
DROP TABLE relfileafterao;
-- Check data is intact
SELECT count(*) FROM ao2co;
 count 
-------
     5
(1 row)

SELECT count(*) FROM ao2co2;
 count 
-------
     5
(1 row)

SELECT count(*) FROM ao2co3;
 count 
-------
     5
(1 row)

SELECT count(*) FROM ao2co4;
 count 
-------
     5
(1 row)

-- Aux tables should have been deleted for the old AO table and recreated for the new AOCO table
-- Only tested for 2 out of the 4 tables being created, where the tables were altered w/wo reloptions. 
-- No need to test the other ones created by the alternative syntax SET WITH().
SELECT * FROM gp_toolkit.__gp_aoseg('ao2co');
ERROR:  Relation 'ao2co' does not have appendoptimized row-oriented storage  (seg0 slice1 127.0.1.1:7002 pid=26472)
SELECT * FROM gp_toolkit.__gp_aovisimap('ao2co');
 tid | segno | row_num 
-----+-------+---------
(0 rows)

SELECT count(*) FROM gp_toolkit.__gp_aocsseg('ao2co');
 count 
-------
     6
(1 row)

SELECT * FROM gp_toolkit.__gp_aoblkdir('ao2co');
 tupleid | segno | columngroup_no | entry_no | first_row_no | file_offset | row_count 
---------+-------+----------------+----------+--------------+-------------+-----------
(0 rows)

SELECT * FROM gp_toolkit.__gp_aoseg('ao2co3');
ERROR:  Relation 'ao2co3' does not have appendoptimized row-oriented storage  (seg0 slice1 127.0.1.1:7002 pid=26472)
SELECT * FROM gp_toolkit.__gp_aovisimap('ao2co3');
 tid | segno | row_num 
-----+-------+---------
(0 rows)

SELECT count(*) FROM gp_toolkit.__gp_aocsseg('ao2co3');
 count 
-------
     6
(1 row)

SELECT * FROM gp_toolkit.__gp_aoblkdir('ao2co3');
 tupleid | segno | columngroup_no | entry_no | first_row_no | file_offset | row_count 
---------+-------+----------------+----------+--------------+-------------+-----------
(0 rows)

-- pg_attribute_encoding should have columns for the AOCO table
SELECT c.relname, a.attnum, a.attoptions FROM pg_attribute_encoding a, pg_class c WHERE a.attrelid = c.oid AND c.relname LIKE 'ao2co%';
 relname | attnum |                       attoptions                        
---------+--------+---------------------------------------------------------
 ao2co   |      1 | {compresstype=zlib,compresslevel=5,blocksize=65536}
 ao2co   |      2 | {compresstype=zlib,compresslevel=5,blocksize=65536}
 ao2co2  |      1 | {compresstype=zlib,compresslevel=5,blocksize=65536}
 ao2co2  |      2 | {compresstype=zlib,compresslevel=5,blocksize=65536}
 ao2co3  |      1 | {blocksize=32768,compresstype=rle_type,compresslevel=3}
 ao2co3  |      2 | {blocksize=32768,compresstype=rle_type,compresslevel=3}
 ao2co4  |      1 | {blocksize=32768,compresstype=rle_type,compresslevel=3}
 ao2co4  |      2 | {blocksize=32768,compresstype=rle_type,compresslevel=3}
(8 rows)

-- AM and reloptions changed accordingly
SELECT c.relname, a.amname, c.reloptions FROM pg_class c JOIN pg_am a ON c.relam = a.oid WHERE c.relname LIKE 'ao2co%';
 relname |  amname   |                       reloptions                        
---------+-----------+---------------------------------------------------------
 ao2co   | ao_column | {blocksize=65536,compresslevel=5}
 ao2co2  | ao_column | {blocksize=65536,compresslevel=5}
 ao2co3  | ao_column | {blocksize=32768,compresstype=rle_type,compresslevel=3}
 ao2co4  | ao_column | {blocksize=32768,compresstype=rle_type,compresslevel=3}
(4 rows)

-- pg_appendonly should reflect the changes in reloptions
SELECT c.relname,a.blocksize,a.compresslevel,a.checksum,a.compresstype,a.columnstore
FROM pg_appendonly a, pg_class c WHERE a.relid = c.oid AND relname like ('ao2co%');
 relname | blocksize | compresslevel | checksum | compresstype | columnstore 
---------+-----------+---------------+----------+--------------+-------------
 ao2co   |     65536 |             5 | t        | zlib         | t
 ao2co2  |     65536 |             5 | t        | zlib         | t
 ao2co3  |     32768 |             3 | t        | rle_type     | t
 ao2co4  |     32768 |             3 | t        | rle_type     | t
(4 rows)

DROP TABLE ao2co;
DROP TABLE ao2co2;
DROP TABLE ao2co3;
DROP TABLE ao2co4;
-- Scenario 6: AOCO to Heap
SET gp_default_storage_options = 'blocksize=65536, compresstype=zlib, compresslevel=5, checksum=true';
CREATE TABLE co2heap(a int, b int) WITH (appendonly=true, orientation=column);
CREATE TABLE co2heap2(a int, b int) WITH (appendonly=true, orientation=column);
CREATE TABLE co2heap3(a int, b int) WITH (appendonly=true, orientation=column);
CREATE TABLE co2heap4(a int, b int) WITH (appendonly=true, orientation=column);
CREATE INDEX aoi ON co2heap(b);
INSERT INTO co2heap SELECT i,i FROM generate_series(1,5) i;
INSERT INTO co2heap2 SELECT i,i FROM generate_series(1,5) i;
INSERT INTO co2heap3 SELECT i,i FROM generate_series(1,5) i;
INSERT INTO co2heap4 SELECT i,i FROM generate_series(1,5) i;
-- Prior-ATSETAM checks:
-- Check once that the AO tables have the custom reloptions 
SELECT relname, reloptions FROM pg_class WHERE relname LIKE 'co2heap%';
 relname  |            reloptions             
----------+-----------------------------------
 co2heap  | {blocksize=65536,compresslevel=5}
 co2heap2 | {blocksize=65536,compresslevel=5}
 co2heap3 | {blocksize=65536,compresslevel=5}
 co2heap4 | {blocksize=65536,compresslevel=5}
(4 rows)

-- Check once that the AO tables have relfrozenxid = 0
SELECT relname, relfrozenxid FROM pg_class WHERE relname LIKE 'co2heap%';
 relname  | relfrozenxid 
----------+--------------
 co2heap  |            0
 co2heap2 |            0
 co2heap3 |            0
 co2heap4 |            0
(4 rows)

-- Check once that the pg_attribute_encoding has entries for the AOCO tables.
SELECT c.relname, a.attnum, attoptions FROM pg_attribute_encoding a, pg_class c WHERE a.attrelid=c.oid AND c.relname LIKE 'co2heap%';
 relname  | attnum |                     attoptions                      
----------+--------+-----------------------------------------------------
 co2heap  |      1 | {compresstype=zlib,compresslevel=5,blocksize=65536}
 co2heap  |      2 | {compresstype=zlib,compresslevel=5,blocksize=65536}
 co2heap2 |      1 | {compresstype=zlib,compresslevel=5,blocksize=65536}
 co2heap2 |      2 | {compresstype=zlib,compresslevel=5,blocksize=65536}
 co2heap3 |      1 | {compresstype=zlib,compresslevel=5,blocksize=65536}
 co2heap3 |      2 | {compresstype=zlib,compresslevel=5,blocksize=65536}
 co2heap4 |      1 | {compresstype=zlib,compresslevel=5,blocksize=65536}
 co2heap4 |      2 | {compresstype=zlib,compresslevel=5,blocksize=65536}
(8 rows)

CREATE TEMP TABLE relfilebeforeco2heap AS
    SELECT -1 segid, relfilenode FROM pg_class WHERE relname LIKE 'co2heap%'
    UNION SELECT gp_segment_id segid, relfilenode FROM gp_dist_random('pg_class')
    WHERE relname LIKE 'co2heap%' ORDER BY segid;
-- Various cases of altering AOCO to AO:
-- 1. Basic ATSETAMs:
ALTER TABLE co2heap SET ACCESS METHOD heap;
ALTER TABLE co2heap2 SET WITH (appendoptimized=false);
-- 2. ATSETAM with reloptions:
ALTER TABLE co2heap3 SET ACCESS METHOD heap WITH (fillfactor=70);
ALTER TABLE co2heap4 SET WITH (appendoptimized=false, fillfactor=70);
-- The tables and indexes should have been rewritten (should have different relfilenodes)
CREATE TEMP TABLE relfileafterco2heap AS
    SELECT -1 segid, relfilenode FROM pg_class WHERE relname LIKE 'co2heap%'
    UNION SELECT gp_segment_id segid, relfilenode FROM gp_dist_random('pg_class')
    WHERE relname LIKE 'co2heap%' ORDER BY segid;
SELECT * FROM relfilebeforeco2heap INTERSECT SELECT * FROM relfileafterco2heap;
 segid | relfilenode 
-------+-------------
(0 rows)

-- Check data is intact
SELECT count(*) FROM co2heap;
 count 
-------
     5
(1 row)

SELECT count(*) FROM co2heap2;
 count 
-------
     5
(1 row)

SELECT count(*) FROM co2heap3;
 count 
-------
     5
(1 row)

SELECT count(*) FROM co2heap4;
 count 
-------
     5
(1 row)

-- No AO aux tables should be left.
-- Only testing 2 out of the 4 tables being created, where the tables were altered w/wo reloptions. 
-- No need to test the other ones created by the alternative syntax SET WITH().
SELECT * FROM gp_toolkit.__gp_aoseg('co2heap');
ERROR:  Relation 'co2heap' does not have appendoptimized row-oriented storage  (seg1 slice1 127.0.1.1:7003 pid=26473)
SELECT * FROM gp_toolkit.__gp_aovisimap('co2heap');
ERROR:  function not supported on relation
SELECT count(*) FROM gp_toolkit.__gp_aocsseg('co2heap');
ERROR:  Relation 'co2heap' does not have append-optimized column-oriented storage  (seg1 slice1 127.0.1.1:7003 pid=26473)
SELECT * FROM gp_toolkit.__gp_aoblkdir('co2heap');
ERROR:  function not supported on non append-optimized relation
SELECT * FROM gp_toolkit.__gp_aoseg('co2heap3');
ERROR:  Relation 'co2heap3' does not have appendoptimized row-oriented storage  (seg1 slice1 127.0.1.1:7003 pid=26473)
SELECT * FROM gp_toolkit.__gp_aovisimap('co2heap3');
ERROR:  function not supported on relation
SELECT count(*) FROM gp_toolkit.__gp_aocsseg('co2heap3');
ERROR:  Relation 'co2heap3' does not have append-optimized column-oriented storage  (seg1 slice1 127.0.1.1:7003 pid=26473)
SELECT * FROM gp_toolkit.__gp_aoblkdir('co2heap3');
ERROR:  function not supported on non append-optimized relation
-- No pg_appendonly entries should be left too
SELECT c.relname FROM pg_class c, pg_appendonly p WHERE c.relname LIKE 'co2heap%' AND c.oid = p.relid;
 relname 
---------
(0 rows)

-- The altered tables should have heap AM.
SELECT c.relname, a.amname FROM pg_class c JOIN pg_am a ON c.relam = a.oid WHERE c.relname LIKE 'co2heap%';
 relname  | amname 
----------+--------
 co2heap  | heap
 co2heap2 | heap
 co2heap3 | heap
 co2heap4 | heap
(4 rows)

-- The new heap tables shouldn't have the old AO table's reloptions
SELECT relname, reloptions FROM pg_class WHERE relname LIKE 'co2heap%';
 relname  |   reloptions    
----------+-----------------
 co2heap  | 
 co2heap2 | 
 co2heap3 | {fillfactor=70}
 co2heap4 | {fillfactor=70}
(4 rows)

-- The new heap tables should have a valid relfrozenxid
SELECT relname, relfrozenxid <> '0' FROM pg_class WHERE relname LIKE 'co2heap%';
 relname  | ?column? 
----------+----------
 co2heap  | t
 co2heap2 | t
 co2heap3 | t
 co2heap4 | t
(4 rows)

-- The pg_attribute_encoding entries for the altered tables should have all gone.
SELECT c.relname, a.attnum, attoptions FROM pg_attribute_encoding a, pg_class c WHERE a.attrelid=c.oid AND c.relname LIKE 'co2heap%';
 relname | attnum | attoptions 
---------+--------+------------
(0 rows)

DROP TABLE co2heap;
DROP TABLE co2heap2;
DROP TABLE co2heap3;
DROP TABLE co2heap4;
-- Scenario 7: AOCO to AO
CREATE TABLE co2ao(a int, b int) WITH (appendonly=true, orientation=column, compresstype=zlib, compresslevel=3);
CREATE TABLE co2ao2(a int, b int) WITH (appendonly=true, orientation=column, compresstype=zlib, compresslevel=3);
CREATE TABLE co2ao3(a int, b int) WITH (appendonly=true, orientation=column, compresstype=rle_type, compresslevel=3);
CREATE TABLE co2ao4(a int, b int) WITH (appendonly=true, orientation=column, compresstype=rle_type, compresslevel=3);
CREATE INDEX aoi ON co2ao(b);
CREATE INDEX aoi2 ON co2ao3(b);
INSERT INTO co2ao SELECT i,i FROM generate_series(1,5) i;
INSERT INTO co2ao2 SELECT i,i FROM generate_series(1,5) i;
INSERT INTO co2ao3 SELECT i,i FROM generate_series(1,5) i;
INSERT INTO co2ao4 SELECT i,i FROM generate_series(1,5) i;
-- Prior-ATSETAM checks:
-- Check once that the AOCO tables have the custom reloptions
SELECT relname, reloptions FROM pg_class WHERE relname LIKE 'co2ao%';
 relname |                       reloptions                        
---------+---------------------------------------------------------
 co2ao   | {compresstype=zlib,compresslevel=3,blocksize=65536}
 co2ao2  | {compresstype=zlib,compresslevel=3,blocksize=65536}
 co2ao3  | {compresstype=rle_type,compresslevel=3,blocksize=65536}
 co2ao4  | {compresstype=rle_type,compresslevel=3,blocksize=65536}
(4 rows)

-- Check once that pg_appendonly has expected entries.
SELECT c.relname, p.compresstype, p.compresslevel, p.blocksize FROM pg_class c, pg_appendonly p WHERE c.relname LIKE 'co2ao%' AND c.oid = p.relid;
 relname | compresstype | compresslevel | blocksize 
---------+--------------+---------------+-----------
 co2ao   | zlib         |             3 |     65536
 co2ao2  | zlib         |             3 |     65536
 co2ao3  | rle_type     |             3 |     65536
 co2ao4  | rle_type     |             3 |     65536
(4 rows)

-- Check once that the pg_attribute_encoding has entries for the AOCO tables.
SELECT c.relname, a.attnum, attoptions FROM pg_attribute_encoding a, pg_class c WHERE a.attrelid=c.oid AND c.relname LIKE 'co2ao%';
 relname | attnum |                       attoptions                        
---------+--------+---------------------------------------------------------
 co2ao   |      1 | {compresstype=zlib,compresslevel=3,blocksize=65536}
 co2ao   |      2 | {compresstype=zlib,compresslevel=3,blocksize=65536}
 co2ao2  |      1 | {compresstype=zlib,compresslevel=3,blocksize=65536}
 co2ao2  |      2 | {compresstype=zlib,compresslevel=3,blocksize=65536}
 co2ao3  |      1 | {compresstype=rle_type,compresslevel=3,blocksize=65536}
 co2ao3  |      2 | {compresstype=rle_type,compresslevel=3,blocksize=65536}
 co2ao4  |      1 | {compresstype=rle_type,compresslevel=3,blocksize=65536}
 co2ao4  |      2 | {compresstype=rle_type,compresslevel=3,blocksize=65536}
(8 rows)

-- Check once on the aoblkdirs
SELECT gp_segment_id, (gp_toolkit.__gp_aoblkdir('co2ao')).* FROM gp_dist_random('gp_id');
 gp_segment_id | tupleid | segno | columngroup_no | entry_no | first_row_no | file_offset | row_count 
---------------+---------+-------+----------------+----------+--------------+-------------+-----------
             0 | (0,1)   |     1 |              0 |        0 |            1 |           0 |         3
             0 | (0,2)   |     1 |              1 |        0 |            1 |           0 |         3
             1 | (0,1)   |     1 |              0 |        0 |            1 |           0 |         1
             1 | (0,2)   |     1 |              1 |        0 |            1 |           0 |         1
             2 | (0,1)   |     1 |              0 |        0 |            1 |           0 |         1
             2 | (0,2)   |     1 |              1 |        0 |            1 |           0 |         1
(6 rows)

SELECT gp_segment_id, (gp_toolkit.__gp_aoblkdir('co2ao3')).* FROM gp_dist_random('gp_id');
 gp_segment_id | tupleid | segno | columngroup_no | entry_no | first_row_no | file_offset | row_count 
---------------+---------+-------+----------------+----------+--------------+-------------+-----------
             0 | (0,1)   |     1 |              0 |        0 |            1 |           0 |         3
             0 | (0,2)   |     1 |              1 |        0 |            1 |           0 |         3
             1 | (0,1)   |     1 |              0 |        0 |            1 |           0 |         1
             1 | (0,2)   |     1 |              1 |        0 |            1 |           0 |         1
             2 | (0,1)   |     1 |              0 |        0 |            1 |           0 |         1
             2 | (0,2)   |     1 |              1 |        0 |            1 |           0 |         1
(6 rows)

CREATE TEMP TABLE relfilebeforeco2ao AS
    SELECT -1 segid, relfilenode FROM pg_class WHERE relname LIKE 'co2ao%'
    UNION SELECT gp_segment_id segid, relfilenode FROM gp_dist_random('pg_class')
    WHERE relname LIKE 'co2ao%' ORDER BY segid;
-- Various cases of altering AOCO to AO:
-- 1. Basic ATSETAMs:
ALTER TABLE co2ao SET ACCESS METHOD ao_row;
ALTER TABLE co2ao2 SET WITH (appendoptimized=true);
-- 2. ATSETAM with reloptions:
ALTER TABLE co2ao3 SET ACCESS METHOD ao_row WITH (compresstype=zlib, compresslevel=7);
ALTER TABLE co2ao4 SET WITH (appendoptimized=true, compresstype=zlib, compresslevel=7);
-- The tables and indexes should have been rewritten (should have different relfilenodes)
CREATE TEMP TABLE relfileafterco2ao AS
    SELECT -1 segid, relfilenode FROM pg_class WHERE relname LIKE 'co2ao%'
    UNION SELECT gp_segment_id segid, relfilenode FROM gp_dist_random('pg_class')
    WHERE relname LIKE 'co2ao%' ORDER BY segid;
SELECT * FROM relfilebeforeco2ao INTERSECT SELECT * FROM relfileafterco2ao;
 segid | relfilenode 
-------+-------------
(0 rows)

DROP TABLE relfilebeforeco2ao;
DROP TABLE relfileafterco2ao;
-- Check data is intact
SELECT count(*) FROM co2ao;
 count 
-------
     5
(1 row)

SELECT count(*) FROM co2ao2;
 count 
-------
     5
(1 row)

SELECT count(*) FROM co2ao3;
 count 
-------
     5
(1 row)

SELECT count(*) FROM co2ao4;
 count 
-------
     5
(1 row)

-- AO aux tables should still be there, but AOCO seg tables are not.
-- Only testing 2 out of the 4 tables being created, where the tables were altered w/wo reloptions.
-- No need to test the other ones created by the alternative syntax SET WITH().
SELECT * FROM gp_toolkit.__gp_aoseg('co2ao');
 segment_id | segno | eof | tupcount | varblockcount | eof_uncompressed | modcount | formatversion | state 
------------+-------+-----+----------+---------------+------------------+----------+---------------+-------
          0 |     0 |  72 |        3 |             1 |               88 |        1 |             3 |     1
          1 |     0 |  40 |        1 |             1 |               40 |        1 |             3 |     1
          2 |     0 |  40 |        1 |             1 |               40 |        1 |             3 |     1
(3 rows)

SELECT * FROM gp_toolkit.__gp_aocsseg('co2ao');
ERROR:  Relation 'co2ao' does not have append-optimized column-oriented storage  (seg2 slice1 127.0.1.1:7004 pid=26474)
SELECT gp_segment_id, (gp_toolkit.__gp_aovisimap('co2ao')).* FROM gp_dist_random('gp_id');
 gp_segment_id | tid | segno | row_num 
---------------+-----+-------+---------
(0 rows)

SELECT gp_segment_id, (gp_toolkit.__gp_aoblkdir('co2ao')).* FROM gp_dist_random('gp_id');
 gp_segment_id | tupleid | segno | columngroup_no | entry_no | first_row_no | file_offset | row_count 
---------------+---------+-------+----------------+----------+--------------+-------------+-----------
             0 | (0,1)   |     0 |              0 |        0 |            1 |           0 |         3
             1 | (0,1)   |     0 |              0 |        0 |            1 |           0 |         1
             2 | (0,1)   |     0 |              0 |        0 |            1 |           0 |         1
(3 rows)

SELECT * FROM gp_toolkit.__gp_aoseg('co2ao3');
 segment_id | segno | eof | tupcount | varblockcount | eof_uncompressed | modcount | formatversion | state 
------------+-------+-----+----------+---------------+------------------+----------+---------------+-------
          0 |     0 |  72 |        3 |             1 |               88 |        1 |             3 |     1
          1 |     0 |  40 |        1 |             1 |               40 |        1 |             3 |     1
          2 |     0 |  40 |        1 |             1 |               40 |        1 |             3 |     1
(3 rows)

SELECT * FROM gp_toolkit.__gp_aocsseg('co2ao3');
ERROR:  Relation 'co2ao3' does not have append-optimized column-oriented storage  (seg0 slice1 127.0.1.1:7002 pid=26472)
SELECT gp_segment_id, (gp_toolkit.__gp_aovisimap('co2ao3')).* FROM gp_dist_random('gp_id');
 gp_segment_id | tid | segno | row_num 
---------------+-----+-------+---------
(0 rows)

SELECT gp_segment_id, (gp_toolkit.__gp_aoblkdir('co2ao3')).* FROM gp_dist_random('gp_id');
 gp_segment_id | tupleid | segno | columngroup_no | entry_no | first_row_no | file_offset | row_count 
---------------+---------+-------+----------------+----------+--------------+-------------+-----------
             0 | (0,1)   |     0 |              0 |        0 |            1 |           0 |         3
             1 | (0,1)   |     0 |              0 |        0 |            1 |           0 |         1
             2 | (0,1)   |     0 |              0 |        0 |            1 |           0 |         1
(3 rows)

-- pg_appendonly entries should be still be there, but options has changed accordingly.
SELECT c.relname, p.compresstype, p.compresslevel, p.blocksize FROM pg_class c, pg_appendonly p WHERE c.relname LIKE 'co2ao%' AND c.oid = p.relid;
 relname | compresstype | compresslevel | blocksize 
---------+--------------+---------------+-----------
 co2ao   | zlib         |             3 |     65536
 co2ao2  | zlib         |             3 |     65536
 co2ao3  | zlib         |             7 |     32768
 co2ao4  | zlib         |             7 |     32768
(4 rows)

-- The altered tables should show AO AM.
SELECT c.relname, a.amname FROM pg_class c JOIN pg_am a ON c.relam = a.oid WHERE c.relname LIKE 'co2ao%';
 relname | amname 
---------+--------
 co2ao   | ao_row
 co2ao2  | ao_row
 co2ao3  | ao_row
 co2ao4  | ao_row
(4 rows)

-- Only the new tables altered w/ reloptions supplies should have reloptions.
SELECT relname, reloptions FROM pg_class WHERE relname LIKE 'co2ao%';
 relname |                     reloptions                      
---------+-----------------------------------------------------
 co2ao   | {compresstype=zlib,compresslevel=3,blocksize=65536}
 co2ao2  | {compresstype=zlib,compresslevel=3,blocksize=65536}
 co2ao3  | {compresstype=zlib,compresslevel=7}
 co2ao4  | {compresstype=zlib,compresslevel=7}
(4 rows)

-- The pg_attribute_encoding entries for the altered tables should have all gone.
SELECT c.relname, a.attnum, attoptions FROM pg_attribute_encoding a, pg_class c WHERE a.attrelid=c.oid AND c.relname LIKE 'co2ao%';
 relname | attnum | attoptions 
---------+--------+------------
(0 rows)

DROP TABLE co2ao;
DROP TABLE co2ao2;
DROP TABLE co2ao3;
DROP TABLE co2ao4;
-- Scenario 8: Heap to AOCO
SET gp_default_storage_options = 'blocksize=65536, compresstype=zlib, compresslevel=5, checksum=true';
CREATE TABLE heap2co(a int, b int);
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.
CREATE TABLE heap2co2(a int, b int);
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.
CREATE TABLE heap2co3(a int, b int);
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.
CREATE TABLE heap2co4(a int, b int);
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.
CREATE INDEX index_heap2co ON heap2co(b);
CREATE INDEX index_heap2co3 ON heap2co3(b);
INSERT INTO heap2co SELECT i,i FROM generate_series(1,5) i;
INSERT INTO heap2co2 SELECT i,i FROM generate_series(1,5) i;
INSERT INTO heap2co3 SELECT i,i FROM generate_series(1,5) i;
INSERT INTO heap2co4 SELECT i,i FROM generate_series(1,5) i;
CREATE TEMP TABLE relfilebeforeaoco AS
SELECT -1 segid, relname, relfilenode FROM pg_class WHERE relname LIKE 'heap2co%'
UNION SELECT gp_segment_id segid, relname, relfilenode FROM gp_dist_random('pg_class')
WHERE relname LIKE 'heap2co%' ORDER BY segid;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'segid' 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.
-- ERROR: conflicting storage option specified.
ALTER TABLE heap2co SET ACCESS METHOD ao_column WITH (appendoptimized=false);
ERROR:  ACCESS METHOD is specified as "ao_column" but the WITH option indicates it to be "heap"
LINE 1: ALTER TABLE heap2co SET ACCESS METHOD ao_column WITH (append...
                                                        ^
-- Use of *both* ACCESS METHOD and WITH clauses is allowed, but we'll print a hint to indicate the redundancy.
ALTER TABLE heap2co SET ACCESS METHOD ao_column WITH (appendoptimized=true, orientation=column);
NOTICE:  Redundant clauses are used to indicate the access method.
HINT:  Only one of these is needed to indicate access method: the SET ACCESS METHOD clause or the options in the WITH clause.
-- Check once the reloptions
SELECT c.relname, a.amname, c.reloptions FROM pg_class c JOIN pg_am a ON c.relam = a.oid WHERE c.relname LIKE 'heap2co%';
 relname  |  amname   |            reloptions             
----------+-----------+-----------------------------------
 heap2co  | ao_column | {blocksize=65536,compresslevel=5}
 heap2co2 | heap      | 
 heap2co3 | heap      | 
 heap2co4 | heap      | 
(4 rows)

-- Altering AO to AOCO with various syntaxes, reloptions:
ALTER TABLE heap2co SET ACCESS METHOD ao_column;
ALTER TABLE heap2co2 SET WITH (appendoptimized=true, orientation=column);
ALTER TABLE heap2co3 SET ACCESS METHOD ao_column WITH (blocksize=32768, compresslevel=3);
ALTER TABLE heap2co4 SET WITH (appendoptimized=true, orientation=column, blocksize=32768, compresslevel=3);
-- The tables are rewritten
CREATE TEMP TABLE relfileafteraoco AS
SELECT -1 segid, relname, relfilenode FROM pg_class WHERE relname LIKE 'heap2co%'
UNION SELECT gp_segment_id segid, relname, relfilenode FROM gp_dist_random('pg_class')
WHERE relname LIKE 'heap2co%' ORDER BY segid;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'segid' 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.
SELECT * FROM relfilebeforeaoco INTERSECT SELECT * FROM relfileafteraoco;
 segid | relname | relfilenode 
-------+---------+-------------
(0 rows)

DROP TABLE relfilebeforeaoco;
DROP TABLE relfileafteraoco;
-- Check data is intact
SELECT count(*) FROM heap2co;
 count 
-------
     5
(1 row)

SELECT count(*) FROM heap2co2;
 count 
-------
     5
(1 row)

SELECT count(*) FROM heap2co3;
 count 
-------
     5
(1 row)

SELECT count(*) FROM heap2co4;
 count 
-------
     5
(1 row)

-- Aux tables should have been created for the new AOCO table
-- Only tested for 2 out of the 4 tables being created, where the tables were altered w/wo reloptions.
SELECT gp_segment_id, (gp_toolkit.__gp_aovisimap('heap2co')).* FROM gp_dist_random('gp_id');
 gp_segment_id | tid | segno | row_num 
---------------+-----+-------+---------
(0 rows)

SELECT gp_segment_id, (gp_toolkit.__gp_aoblkdir('heap2co')).* FROM gp_dist_random('gp_id');
 gp_segment_id | tupleid | segno | columngroup_no | entry_no | first_row_no | file_offset | row_count 
---------------+---------+-------+----------------+----------+--------------+-------------+-----------
             2 | (0,1)   |     0 |              0 |        0 |            1 |           0 |         1
             2 | (0,2)   |     0 |              1 |        0 |            1 |           0 |         1
             0 | (0,1)   |     0 |              0 |        0 |            1 |           0 |         3
             0 | (0,2)   |     0 |              1 |        0 |            1 |           0 |         3
             1 | (0,1)   |     0 |              0 |        0 |            1 |           0 |         1
             1 | (0,2)   |     0 |              1 |        0 |            1 |           0 |         1
(6 rows)

SELECT count(*) FROM gp_toolkit.__gp_aocsseg('heap2co');
 count 
-------
     6
(1 row)

SELECT gp_segment_id, (gp_toolkit.__gp_aovisimap('heap2co3')).* FROM gp_dist_random('gp_id');
 gp_segment_id | tid | segno | row_num 
---------------+-----+-------+---------
(0 rows)

SELECT gp_segment_id, (gp_toolkit.__gp_aoblkdir('heap2co3')).* FROM gp_dist_random('gp_id');
 gp_segment_id | tupleid | segno | columngroup_no | entry_no | first_row_no | file_offset | row_count 
---------------+---------+-------+----------------+----------+--------------+-------------+-----------
             1 | (0,1)   |     0 |              0 |        0 |            1 |           0 |         1
             1 | (0,2)   |     0 |              1 |        0 |            1 |           0 |         1
             2 | (0,1)   |     0 |              0 |        0 |            1 |           0 |         1
             2 | (0,2)   |     0 |              1 |        0 |            1 |           0 |         1
             0 | (0,1)   |     0 |              0 |        0 |            1 |           0 |         3
             0 | (0,2)   |     0 |              1 |        0 |            1 |           0 |         3
(6 rows)

SELECT count(*) FROM gp_toolkit.__gp_aocsseg('heap2co3');
 count 
-------
     6
(1 row)

-- pg_attribute_encoding should have columns for the AOCO table
SELECT c.relname, a.attnum, a.attoptions FROM pg_attribute_encoding a, pg_class c WHERE a.attrelid = c.oid AND c.relname LIKE 'heap2co%';
 relname  | attnum |                     attoptions                      
----------+--------+-----------------------------------------------------
 heap2co  |      2 | {compresstype=zlib,compresslevel=5,blocksize=65536}
 heap2co  |      1 | {compresstype=zlib,compresslevel=5,blocksize=65536}
 heap2co2 |      2 | {compresstype=zlib,compresslevel=5,blocksize=65536}
 heap2co2 |      1 | {compresstype=zlib,compresslevel=5,blocksize=65536}
 heap2co3 |      2 | {blocksize=32768,compresslevel=3,compresstype=zlib}
 heap2co3 |      1 | {blocksize=32768,compresslevel=3,compresstype=zlib}
 heap2co4 |      2 | {blocksize=32768,compresslevel=3,compresstype=zlib}
 heap2co4 |      1 | {blocksize=32768,compresslevel=3,compresstype=zlib}
(8 rows)

-- AM and reloptions changed accordingly
SELECT c.relname, a.amname, c.reloptions FROM pg_class c JOIN pg_am a ON c.relam = a.oid WHERE c.relname LIKE 'heap2co%';
 relname  |  amname   |            reloptions             
----------+-----------+-----------------------------------
 heap2co  | ao_column | {blocksize=65536,compresslevel=5}
 heap2co2 | ao_column | {blocksize=65536,compresslevel=5}
 heap2co3 | ao_column | {blocksize=32768,compresslevel=3}
 heap2co4 | ao_column | {blocksize=32768,compresslevel=3}
(4 rows)

-- pg_appendonly should reflect the changes in reloptions
SELECT c.relname,a.blocksize,a.compresslevel,a.checksum,a.compresstype,a.columnstore
FROM pg_appendonly a, pg_class c WHERE a.relid = c.oid AND relname like ('heap2co%');
 relname  | blocksize | compresslevel | checksum | compresstype | columnstore 
----------+-----------+---------------+----------+--------------+-------------
 heap2co  |     65536 |             5 | t        | zlib         | t
 heap2co2 |     65536 |             5 | t        | zlib         | t
 heap2co3 |     32768 |             3 | t        | zlib         | t
 heap2co4 |     32768 |             3 | t        | zlib         | t
(4 rows)

DROP TABLE heap2co;
DROP TABLE heap2co2;
DROP TABLE heap2co3;
DROP TABLE heap2co4;
-- Final scenario: the iterations of altering table from storage type "A" to "B" and back to "A".
-- The following cases will cover all variations of such iterations:
-- 1. Heap->AO->Heap->AO
-- 2. AO->AOCO->AO->AOCO
-- 3. Heap->AOCO->Heap->AOCO
-- 1. Heap->AO->Heap->AO
CREATE TABLE heapao(a int, b int);
CREATE INDEX heapaoindex ON heapao(b);
INSERT INTO heapao SELECT i,i FROM generate_series(1,5) i;
ALTER TABLE heapao SET ACCESS METHOD ao_row;
ALTER TABLE heapao SET ACCESS METHOD heap;
ALTER TABLE heapao SET ACCESS METHOD ao_row;
-- Just checking data is intact. 
SELECT count(*) FROM heapao;
 count 
-------
     5
(1 row)

DROP TABLE heapao;
-- 2. AO->AOCO->AO->AOCO
CREATE TABLE aoco(a int, b int) with (appendoptimized=true);
CREATE INDEX aocoindex ON aoco(b);
INSERT INTO aoco SELECT i,i FROM generate_series(1,5) i;
ALTER TABLE aoco SET ACCESS METHOD ao_column;
ALTER TABLE aoco SET ACCESS METHOD ao_row;
ALTER TABLE aoco SET ACCESS METHOD ao_column;
-- Just checking data is intact.
SELECT count(*) FROM aoco;
 count 
-------
     5
(1 row)

DROP TABLE aoco;
-- 3. Heap->AOCO->Heap->AOCO
CREATE TABLE heapco(a int, b int);
CREATE INDEX heapcoindex ON heapco(b);
INSERT INTO heapco SELECT i,i FROM generate_series(1,5) i;
ALTER TABLE heapco SET ACCESS METHOD ao_column;
ALTER TABLE heapco SET ACCESS METHOD heap;
ALTER TABLE heapco SET ACCESS METHOD ao_column;
-- Just checking data is intact.
SELECT count(*) FROM heapco;
 count 
-------
     5
(1 row)

DROP TABLE heapco;
-- Misc cases
-- 
-- ATSETAM with dropped column:
-- The dropped column should still have an entry in pg_attribute_encoding if it is 
-- an AOCO table, just like pg_attribute.
CREATE TABLE atsetam_dropcol(a int, b int, c int, d int);
INSERT INTO atsetam_dropcol VALUES(1,1,1,1);
SELECT count(*) FROM pg_attribute WHERE attrelid = 'atsetam_dropcol'::regclass AND attnum > 0;
 count 
-------
     4
(1 row)

SELECT count(*) FROM pg_attribute_encoding WHERE attrelid = 'atsetam_dropcol'::regclass;
 count 
-------
     0
(1 row)

ALTER TABLE atsetam_dropcol DROP COLUMN b;
ALTER TABLE atsetam_dropcol SET ACCESS METHOD ao_row;
SELECT count(*) FROM pg_attribute WHERE attrelid = 'atsetam_dropcol'::regclass AND attnum > 0;
 count 
-------
     4
(1 row)

SELECT count(*) FROM pg_attribute_encoding WHERE attrelid = 'atsetam_dropcol'::regclass;
 count 
-------
     0
(1 row)

SELECT * FROM atsetam_dropcol;
 a | c | d 
---+---+---
 1 | 1 | 1
(1 row)

ALTER TABLE atsetam_dropcol DROP COLUMN c;
ALTER TABLE atsetam_dropcol SET ACCESS METHOD ao_column;
SELECT count(*) FROM pg_attribute WHERE attrelid = 'atsetam_dropcol'::regclass AND attnum > 0;
 count 
-------
     4
(1 row)

SELECT count(*) FROM pg_attribute_encoding WHERE attrelid = 'atsetam_dropcol'::regclass;
 count 
-------
     4
(1 row)

SELECT * FROM atsetam_dropcol;
 a | d 
---+---
 1 | 1
(1 row)

ALTER TABLE atsetam_dropcol DROP COLUMN d;
ALTER TABLE atsetam_dropcol SET ACCESS METHOD heap;
SELECT count(*) FROM pg_attribute WHERE attrelid = 'atsetam_dropcol'::regclass AND attnum > 0;
 count 
-------
     4
(1 row)

SELECT count(*) FROM pg_attribute_encoding WHERE attrelid = 'atsetam_dropcol'::regclass;
 count 
-------
     0
(1 row)

SELECT * FROM atsetam_dropcol;
 a 
---
 1
(1 row)