src/test/regress/output/appendonly.source

CREATE TABLE tenk_heap (
	unique1 	int4,
	unique2 	int4,
	two 	 	int4,
	four 		int4,
	ten			int4,
	twenty 		int4,
	hundred 	int4,
	thousand 	int4,
	twothousand int4,
	fivethous 	int4,
	tenthous	int4,
	odd			int4,
	even		int4,
	stringu1	name,
	stringu2	name,
	string4		name
) with (appendonly=false) distributed by(unique1);
--
-- create few AO tables. test various reloptions combinations. use a sample
-- of them (the first 4) for later testing.
--
-- valid
CREATE TABLE tenk_ao1 (like tenk_heap) with (appendonly=true, checksum=true) distributed by(unique1);
CREATE TABLE tenk_ao2 (like tenk_heap) with (appendonly=true, compresslevel=0, blocksize=262144) distributed by(unique1);
CREATE TABLE tenk_ao3 (like tenk_heap) with (appendonly=true, compresslevel=6, blocksize=1048576, checksum=true) distributed by(unique1);
CREATE TABLE tenk_ao4 (like tenk_heap) with (appendonly=true, compresslevel=1, compresstype=quicklz) distributed by(unique1);
CREATE TABLE tenk_ao5 (like tenk_heap) with (appendonly=true, compresslevel=6, compresstype=zlib, blocksize=1048576, checksum=true) distributed by(unique1);
-- invalid
CREATE TABLE tenk_ao6 (like tenk_heap) with (appendonly=false, compresslevel=6, checksum=true) distributed by(unique1);
ERROR:  invalid option 'compresslevel' for base relation. Only valid for Append Only relations
CREATE TABLE tenk_ao7 (like tenk_heap) with (appendonly=true, compresslevel=6, compresstype=quicklz) distributed by(unique1);
ERROR:  compresslevel=6 is out of range for quicklz (should be 1)
CREATE TABLE tenk_ao8 (like tenk_heap) with (appendonly=true, blocksize=100) distributed by(unique1);
ERROR:  block size must be between 8KB and 2MB and be an 8KB multiple. Got 100
CREATE TABLE tenk_ao9 (like tenk_heap) with (appendonly=true, compresslevel=0, compresstype=zlib) distributed by(unique1);
ERROR:  compresstype can't be used with compresslevel 0
-------------------- 
-- catalog checks
--------------------
-- check pg_appendonly
SELECT c.relname, a.blocksize, a.compresstype, a.compresslevel, a.checksum FROM pg_class c, pg_appendonly a
       WHERE c.relname LIKE 'tenk_ao%' AND c.oid=a.relid AND c.relname not like 'tenk_aocs%' ORDER BY c.relname;
 relname  | blocksize | compresstype | compresslevel | checksum 
----------+-----------+--------------+---------------+----------
 tenk_ao1 |     32768 |              |             0 | t
 tenk_ao2 |    262144 |              |             0 | f
 tenk_ao3 |   1048576 | zlib         |             6 | t
 tenk_ao4 |     32768 | quicklz      |             1 | f
 tenk_ao5 |   1048576 | zlib         |             6 | t
(5 rows)

--------------------
-- fn needed later
--------------------
create  or replace function aototal(relname text) returns float8 as $$
declare
  aosegname text;
  tupcount float8 := 0;
  rc int := 0;
begin

  execute 'select relname from pg_class where oid=(select segrelid from pg_class, pg_appendonly where relname=''' || relname || ''' and relid = pg_class.oid)' into aosegname;
  if aosegname > 0 then
          execute 'select tupcount from pg_aoseg.' || aosegname into tupcount;
  end if;
  return tupcount;
end; $$ language plpgsql volatile;
-------------------- 
-- supported sql
--------------------
-- COPY
COPY tenk_heap FROM '@abs_srcdir@/data/tenk.data';
COPY tenk_ao1 FROM '@abs_srcdir@/data/tenk.data';
COPY tenk_ao2 FROM '@abs_srcdir@/data/tenk.data';
COPY tenk_ao3 FROM '@abs_srcdir@/data/tenk.data';
COPY tenk_ao4 FROM '@abs_srcdir@/data/tenk.data';
-- SELECT
SELECT count(*) FROM tenk_heap;
 count 
-------
 10000
(1 row)

SELECT count(*) FROM tenk_ao1;
 count 
-------
 10000
(1 row)

SELECT count(*) FROM tenk_ao2;
 count 
-------
 10000
(1 row)

SELECT count(*) FROM tenk_ao3;
 count 
-------
 10000
(1 row)

SELECT count(*) FROM tenk_ao4;
 count 
-------
 10000
(1 row)

SELECT aototal('tenk_ao1'), aototal('tenk_ao2'), aototal('tenk_ao3'), aototal('tenk_ao4');
 aototal | aototal | aototal | aototal 
---------+---------+---------+---------
   10000 |   10000 |   10000 |   10000
(1 row)

-- INSERT SELECT
INSERT INTO tenk_ao1 SELECT * FROM tenk_heap;
INSERT INTO tenk_ao2 SELECT * FROM tenk_heap;
INSERT INTO tenk_ao3 SELECT * FROM tenk_heap;
INSERT INTO tenk_ao4 SELECT * FROM tenk_heap;
-- mix and match some
INSERT INTO tenk_ao1 SELECT * FROM tenk_ao1;
INSERT INTO tenk_ao2 SELECT * FROM tenk_ao3;
INSERT INTO tenk_ao3 SELECT * FROM tenk_ao2;
INSERT INTO tenk_ao4 SELECT * FROM tenk_ao3;
SELECT aototal('tenk_ao1'), aototal('tenk_ao2'), aototal('tenk_ao3'), aototal('tenk_ao4');
 aototal | aototal | aototal | aototal 
---------+---------+---------+---------
   40000 |   40000 |   60000 |   80000
(1 row)

-- SELECT
SELECT count(*) FROM tenk_heap;
 count 
-------
 10000
(1 row)

SELECT count(*) FROM tenk_ao1;
 count 
-------
 40000
(1 row)

SELECT count(*) FROM tenk_ao2;
 count 
-------
 40000
(1 row)

SELECT count(*) FROM tenk_ao3;
 count 
-------
 60000
(1 row)

SELECT count(*) FROM tenk_ao4;
 count 
-------
 80000
(1 row)

--
-- Test that the catalog eof entry doesn't change even if the file gets
-- larger due to bad data that isn't cleaned up until the next VACUUM. 
-- make sure the SELECT stops at eof (count is the same). 
-- The first row is good (so it grows the file), the second is bad.
--
COPY tenk_ao1 FROM STDIN;
ERROR:  missing data for column "unique2"
CONTEXT:  COPY tenk_ao1, line 2: "bad data row"
COPY tenk_ao2 FROM STDIN;
ERROR:  missing data for column "unique2"
CONTEXT:  COPY tenk_ao2, line 2: "bad data row"
COPY tenk_ao3 FROM STDIN;
ERROR:  missing data for column "unique2"
CONTEXT:  COPY tenk_ao3, line 2: "bad data row"
COPY tenk_ao4 FROM STDIN;
ERROR:  missing data for column "unique2"
CONTEXT:  COPY tenk_ao4, line 2: "bad data row"
SELECT count(*) FROM tenk_ao1;
 count 
-------
 40000
(1 row)

SELECT count(*) FROM tenk_ao2;
 count 
-------
 40000
(1 row)

SELECT count(*) FROM tenk_ao3;
 count 
-------
 60000
(1 row)

SELECT count(*) FROM tenk_ao4;
 count 
-------
 80000
(1 row)

SELECT aototal('tenk_ao1'), aototal('tenk_ao2'), aototal('tenk_ao3'), aototal('tenk_ao4');
 aototal | aototal | aototal | aototal 
---------+---------+---------+---------
   40000 |   40000 |   60000 |   80000
(1 row)

-------------------- 
-- transactionality
--------------------
-- rollback
BEGIN;
INSERT INTO tenk_ao1 SELECT * FROM tenk_heap;
SELECT count(*) FROM tenk_ao1; -- should show new count
 count 
-------
 50000
(1 row)

ROLLBACK;
SELECT count(*) FROM tenk_ao1; -- should show previous count
 count 
-------
 40000
(1 row)

SELECT aototal('tenk_ao1');
 aototal 
---------
   40000
(1 row)

-- commit
BEGIN;
INSERT INTO tenk_ao1 SELECT * FROM tenk_heap;
SELECT count(*) FROM tenk_ao1; -- should show new count
 count 
-------
 50000
(1 row)

COMMIT;
SELECT count(*) FROM tenk_ao1; -- should show new count
 count 
-------
 50000
(1 row)

SELECT aototal('tenk_ao1');
 aototal 
---------
   50000
(1 row)

-- same txn inserts
BEGIN;
INSERT INTO tenk_ao1(unique1) VALUES(12345678);
INSERT INTO tenk_ao1(unique1) VALUES(12345678);
INSERT INTO tenk_ao1(unique1) VALUES(12345678);
INSERT INTO tenk_ao1(unique1) VALUES(12345678);
INSERT INTO tenk_ao1(unique1) VALUES(12345678);
ROLLBACK;
BEGIN;
INSERT INTO tenk_ao1(unique1) VALUES(87654321);
INSERT INTO tenk_ao1(unique1) VALUES(87654321);
INSERT INTO tenk_ao1(unique1) VALUES(87654321);
INSERT INTO tenk_ao1(unique1) VALUES(87654321);
INSERT INTO tenk_ao1(unique1) VALUES(87654321);
COMMIT;
SELECT count(*) FROM tenk_ao1 WHERE unique1 = 12345678; -- should be 0
 count 
-------
     0
(1 row)

SELECT count(*) FROM tenk_ao1 WHERE unique1 = 87654321; -- should be 5
 count 
-------
     5
(1 row)

--------------------
-- cursors (basic)
--------------------
BEGIN;
DECLARE foo1 CURSOR FOR SELECT * FROM tenk_ao1 ORDER BY 1,2,3,4;
DECLARE foo2 CURSOR FOR SELECT * FROM tenk_ao2 ORDER BY 1,2,3,4;
FETCH 1 in foo1;
 unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 
---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
       0 |    9998 |   0 |    0 |   0 |      0 |       0 |        0 |           0 |         0 |        0 |   0 |    1 | AAAAAA   | OUOAAA   | OOOOxx
(1 row)

FETCH 2 in foo2;
 unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 
---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
       0 |    9998 |   0 |    0 |   0 |      0 |       0 |        0 |           0 |         0 |        0 |   0 |    1 | AAAAAA   | OUOAAA   | OOOOxx
       0 |    9998 |   0 |    0 |   0 |      0 |       0 |        0 |           0 |         0 |        0 |   0 |    1 | AAAAAA   | OUOAAA   | OOOOxx
(2 rows)

FETCH 1 in foo1;
 unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 
---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
       0 |    9998 |   0 |    0 |   0 |      0 |       0 |        0 |           0 |         0 |        0 |   0 |    1 | AAAAAA   | OUOAAA   | OOOOxx
(1 row)

FETCH 2 in foo2;
 unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 
---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
       0 |    9998 |   0 |    0 |   0 |      0 |       0 |        0 |           0 |         0 |        0 |   0 |    1 | AAAAAA   | OUOAAA   | OOOOxx
       0 |    9998 |   0 |    0 |   0 |      0 |       0 |        0 |           0 |         0 |        0 |   0 |    1 | AAAAAA   | OUOAAA   | OOOOxx
(2 rows)

CLOSE foo1;
CLOSE foo2;
END;
BEGIN;
DECLARE foo3 NO SCROLL CURSOR FOR SELECT * FROM tenk_ao1 ORDER BY 1,2,3,4;
FETCH 1 FROM foo3;
 unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 
---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
       0 |    9998 |   0 |    0 |   0 |      0 |       0 |        0 |           0 |         0 |        0 |   0 |    1 | AAAAAA   | OUOAAA   | OOOOxx
(1 row)

FETCH BACKWARD 1 FROM foo3; -- should fail
ERROR:  backward scan is not supported in this version of Greenplum Database
END;
-- Cursors outside transaction blocks
BEGIN;
DECLARE foo4 CURSOR WITH HOLD FOR SELECT * FROM tenk_ao1 ORDER BY 1,2,3,4;
FETCH FROM foo4;
 unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 
---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
       0 |    9998 |   0 |    0 |   0 |      0 |       0 |        0 |           0 |         0 |        0 |   0 |    1 | AAAAAA   | OUOAAA   | OOOOxx
(1 row)

FETCH FROM foo4;
 unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 
---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
       0 |    9998 |   0 |    0 |   0 |      0 |       0 |        0 |           0 |         0 |        0 |   0 |    1 | AAAAAA   | OUOAAA   | OOOOxx
(1 row)

COMMIT;
FETCH FROM foo4;
 unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 
---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
       0 |    9998 |   0 |    0 |   0 |      0 |       0 |        0 |           0 |         0 |        0 |   0 |    1 | AAAAAA   | OUOAAA   | OOOOxx
(1 row)

SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors ORDER BY name;
 name |                                 statement                                  | is_holdable | is_binary | is_scrollable 
------+----------------------------------------------------------------------------+-------------+-----------+---------------
 foo4 | DECLARE foo4 CURSOR WITH HOLD FOR SELECT * FROM tenk_ao1 ORDER BY 1,2,3,4; | t           | f         | f
(1 row)

CLOSE foo4;
-- DROP
DROP TABLE tenk_ao1;
DROP TABLE tenk_ao2;
DROP TABLE tenk_ao3;
DROP TABLE tenk_ao4;
-- CTAS
CREATE TABLE tenk_ao1 with(appendonly=true, checksum=true) AS SELECT * FROM tenk_heap;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'unique1' 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 tenk_ao2 with(appendonly=true, compresslevel=0, blocksize=262144) AS SELECT * FROM tenk_heap;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'unique1' 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 tenk_ao3 with(appendonly=true, compresslevel=6, blocksize=1048576, checksum=true) AS SELECT * FROM tenk_heap;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'unique1' 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 tenk_ao4 with(appendonly=true, compresslevel=1, compresstype=quicklz) AS SELECT * FROM tenk_heap;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'unique1' 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 c.relname, a.blocksize, a.compresstype, a.compresslevel, a.checksum FROM pg_class c, pg_appendonly a
       WHERE c.relname LIKE 'tenk_ao%' AND c.oid=a.relid AND c.relname not like 'tenk_aocs%' ORDER BY c.relname;
 relname  | blocksize | compresstype | compresslevel | checksum 
----------+-----------+--------------+---------------+----------
 tenk_ao1 |     32768 |              |             0 | t
 tenk_ao2 |    262144 |              |             0 | f
 tenk_ao3 |   1048576 | zlib         |             6 | t
 tenk_ao4 |     32768 | quicklz      |             1 | f
 tenk_ao5 |   1048576 | zlib         |             6 | t
(5 rows)

SELECT count(*) FROM tenk_ao1;
 count 
-------
 10000
(1 row)

SELECT count(*) FROM tenk_ao2;
 count 
-------
 10000
(1 row)

SELECT count(*) FROM tenk_ao3;
 count 
-------
 10000
(1 row)

SELECT count(*) FROM tenk_ao4;
 count 
-------
 10000
(1 row)

-- test get_ao_compression_ratio. use uncompressed table, so result is always 1.
SELECT get_ao_compression_ratio('tenk_ao2');
 get_ao_compression_ratio 
--------------------------
                        1
(1 row)

-- VACUUM
VACUUM tenk_ao1;
VACUUM tenk_ao2;
VACUUM tenk_ao3;
VACUUM tenk_ao4;
VACUUM FULL tenk_ao1;
NOTICE:  'VACUUM FULL' is not safe for large tables and has been known to yield unpredictable runtimes.
HINT:  Use 'VACUUM' instead.
ANALYZE tenk_ao2;
ANALYZE tenk_ao4;
VACUUM ANALYZE tenk_ao3;
SELECT count(*) FROM tenk_ao1;
 count 
-------
 10000
(1 row)

SELECT count(*) FROM tenk_ao2;
 count 
-------
 10000
(1 row)

SELECT count(*) FROM tenk_ao3;
 count 
-------
 10000
(1 row)

SELECT count(*) FROM tenk_ao4;
 count 
-------
 10000
(1 row)

-- JOIN
SELECT count(*) FROM tenk_ao1 t1, tenk_ao2 t2 where t1.unique1 = t2.unique2;
 count 
-------
 10000
(1 row)

SELECT count(*) FROM tenk_ao1 t1, tenk_heap t2 where t1.unique1 = t2.unique2;
 count 
-------
 10000
(1 row)

-- EXCEPT
SELECT unique1 FROM tenk_ao1 EXCEPT SELECT unique1 FROM tenk_ao1;
 unique1 
---------
(0 rows)

SELECT unique1 FROM tenk_heap EXCEPT SELECT unique1 FROM tenk_ao3;
 unique1 
---------
(0 rows)

-- TRUNCATE
TRUNCATE tenk_ao2;
-- OIDS
CREATE TABLE aowithoids(a int, b int) WITH (appendonly=true,oids=true);
COPY aowithoids FROM STDIN;
SELECT * FROM aowithoids ORDER BY a; -- this should show a,b only
 a | b 
---+---
 1 | 1
 2 | 2
(2 rows)

SELECT oideq(oid,-1) FROM aowithoids; -- kind of stupid but checks that oid actually exists and is queriable. should always be 'f'
 oideq 
-------
 f
 f
(2 rows)

-- CREATE INDEX
CREATE INDEX tenk_ao1_unique1 ON tenk_ao1 USING btree(unique1 int4_ops);
drop table if exists ao;
create table ao (i int, j int, k varchar) with(appendonly=true);
insert into ao values (1,1,'a'), (2,2,'aa'), (3,3,'aaa'), (4,4,'aaaa'),
	(5,5,'aaaaa'), (6,6,'aaaaaa'), (7,7,'aaaaaaa'), (8,8,'aaaaaaaa');
create index ao_j on ao using btree(j);
create index ao_k on ao using btree(k);
create index ao_jk on ao using btree((j + length(k)));
set enable_seqscan=off;
select * from ao where j = 2;
 i | j | k  
---+---+----
 2 | 2 | aa
(1 row)

insert into ao values (9,1,'b'), (10,2,'bb'), (11,3,'bbb'), (12,4,'bbbb'),
	(13,5,'aaaaa'), (14,6,'aaaaaa'), (15,7,'aaaaaaa'), (16,8,'aaaaaaaa');
select * from ao where j = 2;
 i  | j | k  
----+---+----
  2 | 2 | aa
 10 | 2 | bb
(2 rows)

insert into ao values (9,2,'b'), (10,2,'bb'), (11,2,'bbb'), (12,2,'bbbb'),
	(13,5,'aaaaa'), (14,6,'aaaaaa'), (15,7,'aaaaaaa'), (16,8,'aaaaaaaa');
select * from ao where j = 2;
 i  | j |  k   
----+---+------
 11 | 2 | bbb
  9 | 2 | b
 12 | 2 | bbbb
  2 | 2 | aa
 10 | 2 | bb
 10 | 2 | bb
(6 rows)

drop table if exists ao;
create table ao (i int, j int, k varchar) with(appendonly=true);
insert into ao values (1,1,'a'), (2,2,'aa'), (3,3,'aaa'), (4,4,'aaaa'),
	(5,5,'aaaaa'), (6,6,'aaaaaa'), (7,7,'aaaaaaa'), (8,8,'aaaaaaaa');
create index ao_j on ao using bitmap(j);
create index ao_k on ao using bitmap(k);
create index ao_jk on ao using bitmap((j + length(k)));
set enable_seqscan=off;
select * from ao where j = 2;
 i | j | k  
---+---+----
 2 | 2 | aa
(1 row)

insert into ao values (9,1,'b'), (10,2,'bb'), (11,3,'bbb'), (12,4,'bbbb'),
	(13,5,'aaaaa'), (14,6,'aaaaaa'), (15,7,'aaaaaaa'), (16,8,'aaaaaaaa');
select * from ao where j = 2;
 i  | j | k  
----+---+----
  2 | 2 | aa
 10 | 2 | bb
(2 rows)

insert into ao values (9,2,'b'), (10,2,'bb'), (11,2,'bbb'), (12,2,'bbbb'),
	(13,5,'aaaaa'), (14,6,'aaaaaa'), (15,7,'aaaaaaa'), (16,8,'aaaaaaaa');
select * from ao where j = 2;
 i  | j |  k   
----+---+------
 11 | 2 | bbb
  9 | 2 | b
 12 | 2 | bbbb
  2 | 2 | aa
 10 | 2 | bb
 10 | 2 | bb
(6 rows)

-------------------- 
-- unsupported sql 
--------------------
-- DELETE (not supported)
DELETE FROM tenk_ao1 WHERE unique1 = 1;
ERROR:  Append-only tables are not updatable. Operation not permitted.
-- UPDATE (not supported)
UPDATE tenk_ao1 SET unique2 = 1 WHERE unique2 = 2;
ERROR:  Append-only tables are not updatable. Operation not permitted.
-- ALTER
ALTER TABLE tenk_ao1 RENAME TO tenk_renamed;
ALTER TABLE tenk_renamed ADD COLUMN newcol int default 10;
ALTER TABLE tenk_renamed ALTER COLUMN twothousand SET NOT NULL;
ALTER TABLE tenk_renamed ADD COLUMN sercol serial; -- MPP-10015
ALTER TABLE tenk_renamed ADD COLUMN newcol2 int NOT NULL; -- should fail
ERROR:  ADD COLUMN with no default value in append-only tables is not yet supported.
HINT:  Use a DEFAULT value, or use INSERT...SELECT to movedata to a newly created table with the new column.
SELECT count(*) FROM tenk_renamed;
 count 
-------
 10000
(1 row)

ALTER TABLE tenk_renamed RENAME TO tenk_ao1;
--------------------
-- system columns
--------------------
CREATE TABLE syscoltest(a 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.
INSERT INTO syscoltest VALUES(1);
SELECT ctid FROM syscoltest;
   ctid   
------------------
 (33554432,32769)
(1 row)

DROP TABLE syscoltest;
--------------------
-- relation size tests -- make sure can execute without block directory, sanity checks on relative sizes
--
--
--------------------
CREATE TABLE aosizetest_1(a 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 aosizetest_2(a 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.
--
-- size will be < total size because of segrelid
--
SELECT pg_relation_size('aosizetest_1') < pg_total_relation_size('aosizetest_1') as total_exceeds_regular;
 total_exceeds_regular
-----------------------
 t
(1 row)

--
-- create currently build block directory, but dropping last index does not delete block directory...
--  so we will verify that the size of _2 is greater than the size of _1 after this
--
CREATE INDEX aosizetest_2_idx on aosizetest_2(a);
DROP INDEX aosizetest_2_idx;
SELECT pg_total_relation_size('aosizetest_1') < pg_total_relation_size('aosizetest_2') as with_block_dir_exceeds_without;
 with_block_dir_exceeds_without
--------------------------------
 t
(1 row)

DROP TABLE aosizetest_1;
DROP TABLE aosizetest_2;
-------------------- 
-- supported sql 
--------------------
DROP TABLE tenk_heap;
DROP TABLE tenk_ao1;
DROP TABLE tenk_ao2;
DROP TABLE tenk_ao3;
DROP TABLE tenk_ao4;
DROP TABLE tenk_ao5;
DROP TABLE aowithoids;