src/test/regress/expected/alter_table_aocs2.out

-- Test ALTER TABLE ADD COLUMN / DROP COLUMN, on AOCO tables, with different
-- storage options.
-- Setup test tables
-- AOCO multiple insert to create multiple var-block
DROP TABLE IF EXISTS multivarblock_tab;
CREATE TABLE multivarblock_tab (
    c_custkey integer,
    c_name character varying(25),
    c_comment text,
    c_rating float,
    c_phone character(15),
    c_acctbal numeric(15,2),
    c_date date,
    c_timestamp timestamp
)
WITH (checksum=true, appendonly=true, orientation=column) DISTRIBUTED BY (c_custkey);
insert into multivarblock_tab values( 1, 'aa','this is a looong text' , 3.5, '12121212',1000.34,'2015/10/10',now());
insert into multivarblock_tab values( 2, 'ab','this is also a looong text' , 4.5, '3456789',3000.45,'2014/08/10',now());
insert into multivarblock_tab values( 3, 'ac','this  too is a looong text' , 1.5, '878787',500.54,'2014/04/04',now());
-- AOCO multiple insert to create multiple var-block for table with btree index
DROP TABLE IF EXISTS multivarblock_bitab;
CREATE TABLE multivarblock_bitab (
    c_custkey integer,
    c_name character varying(25),
    c_comment text,
    c_rating float,
    c_phone character(15),
    c_acctbal numeric(15,2),
    c_date date,
    c_timestamp timestamp
)
WITH (checksum=true, appendonly=true, orientation=column) DISTRIBUTED BY (c_custkey);
CREATE INDEX multivarblock_btree_idx ON multivarblock_bitab USING btree (c_custkey);
insert into multivarblock_bitab values( 1, 'aa','this is a looong text' , 3.5, '12121212',1000.34,'2015/10/10',now());
insert into multivarblock_bitab values( 2, 'ab','this is also a looong text' , 4.5, '3456789',3000.45,'2014/08/10',now());
insert into multivarblock_bitab values( 3, 'ac','this  too is a looong text' , 1.5, '878787',500.54,'2014/04/04',now());
-- AOCO multiple insert to create multiple var-block for table with partitions
DROP TABLE IF EXISTS multivarblock_parttab;
CREATE TABLE multivarblock_parttab (
    c_custkey integer,
    c_name character varying(25),
    c_comment text,
    c_rating float,
    c_phone character(15),
    c_acctbal numeric(15,2),
    c_date date,
    c_timestamp timestamp
)
WITH (checksum=true, appendonly=true, orientation=column) DISTRIBUTED BY (c_custkey)
partition by range(c_custkey)  subpartition by range( c_rating)
subpartition template ( default subpartition subothers,start (0.0) end(1.9) every (2.0) )
(default partition others, partition p1 start(1) end(5000), partition p2 start(5000) end(10000), partition p3 start(10000) end(15000));
insert into multivarblock_parttab values( 1, 'aa','this is a looong text' , 4.5, '12121212',1000.34,'2015/10/10',now());
insert into multivarblock_parttab values( 2, 'ab','this is also a looong text' , 7.5, '3456789',3000.45,'2014/08/10',now());
insert into multivarblock_parttab values( 3, 'ac','this  too is a looong text' , 1.5, '878787',4000.25,'2014/08/10',now());
-- AOCO multiple insert to create multiple var-block
DROP TABLE IF EXISTS multivarblock_toast;
CREATE TABLE multivarblock_toast (
    c_custkey integer,
    c_name character varying(25),
    c_comment text,
    c_rating float,
    c_phone character(15),
    c_acctbal numeric(15,2),
    c_date date,
    c_timestamp timestamp
)
WITH (checksum=true, appendonly=true, orientation=column) DISTRIBUTED BY (c_custkey);
insert into multivarblock_toast values( 1, 'aa','this is a looong text' , 3.5, '12121212',1000.34,'2015/10/10',now());
insert into multivarblock_toast values( 2, 'ab','this is also a looong text' , 4.5, '3456789',3000.45,'2014/08/10',now());
insert into multivarblock_toast values( 3, 'ac','this  too is a looong text' , 1.5, '878787',500.54,'2014/04/04',now());
-- AOCO multiple insert to create multiple var-block
DROP TABLE IF EXISTS multivarblock_zlibtab;
CREATE TABLE multivarblock_zlibtab (
    c_custkey integer,
    c_name character varying(25),
    c_comment text,
    c_rating float,
    c_phone character(15),
    c_acctbal numeric(15,2),
    c_date date,
    c_timestamp timestamp
)
WITH (checksum=true, appendonly=true, orientation=column, compresstype=zlib, compresslevel=9) DISTRIBUTED BY (c_custkey);
insert into multivarblock_zlibtab values( 1, 'aa','this is a looong text' , 3.5, '12121212',1000.34,'2015/10/10',now());
insert into multivarblock_zlibtab values( 2, 'ab','this is also a looong text' , 4.5, '3456789',3000.45,'2014/08/10',now());
insert into multivarblock_zlibtab values( 3, 'ac','this  too is a looong text' , 1.5, '878787',500.54,'2014/04/04',now());
DROP TABLE IF EXISTS multi_segfile_tab;
CREATE TABLE multi_segfile_tab (
    c_custkey integer,
    c_name character varying(25),
    c_comment text,
    c_rating float,
    c_phone character(15),
    c_acctbal numeric(15,2),
    c_date date,
    c_timestamp timestamp
)
WITH (checksum=true, appendonly=true, orientation=column, compresstype=zlib, compresslevel=1) DISTRIBUTED BY (c_custkey);
insert into multi_segfile_tab values( 1, 'aa','this is a looong text' , 3.5, '12121212',1000.34,'2015/10/10',now());
insert into multi_segfile_tab values( 2, 'ab','this is also a looong text' , 4.5, '3456789',3000.45,'2014/08/10',now());
update multi_segfile_tab set c_name = 'bcx' where c_custkey = 2;
vacuum multi_segfile_tab;
insert into multi_segfile_tab values( 3, 'ac','this  too is a looong text' , 1.5, '878787',500.54,'2014/04/04',now());
DROP TABLE IF EXISTS multi_segfile_bitab;
CREATE TABLE multi_segfile_bitab (
    c_custkey integer,
    c_name character varying(25),
    c_comment text,
    c_rating float,
    c_phone character(15),
    c_acctbal numeric(15,2),
    c_date date,
    c_timestamp timestamp
)
WITH (checksum=true, appendonly=true, orientation=column, compresstype=zlib, compresslevel=1) DISTRIBUTED BY (c_custkey);
CREATE INDEX multi_segfile_btree_idx ON multi_segfile_bitab USING btree (c_custkey);
insert into multi_segfile_bitab values( 1, 'aa','this is a looong text' , 3.5, '12121212',1000.34,'2015/10/10',now());
insert into multi_segfile_bitab values( 2, 'ab','this is also a looong text' , 4.5, '3456789',3000.45,'2014/08/10',now());
update multi_segfile_bitab set c_name = 'bcx' where c_custkey = 2;
vacuum multi_segfile_bitab;
insert into multi_segfile_bitab values( 3, 'ac','this  too is a looong text' , 1.5, '878787',500.54,'2014/04/04',now());
DROP TABLE IF EXISTS multi_segfile_zlibtab;
CREATE TABLE multi_segfile_zlibtab (
    c_custkey integer,
    c_name character varying(25),
    c_comment text,
    c_rating float,
    c_phone character(15),
    c_acctbal numeric(15,2),
    c_date date,
    c_timestamp timestamp
)
WITH (checksum=true, appendonly=true, orientation=column, compresstype=zlib, compresslevel=9) DISTRIBUTED BY (c_custkey);
insert into multi_segfile_zlibtab values( 1, 'aa','this is a looong text' , 3.5, '12121212',1000.34,'2015/10/10',now());
insert into multi_segfile_zlibtab values( 2, 'ab','this is also a looong text' , 4.5, '3456789',3000.45,'2014/08/10',now());
update multi_segfile_zlibtab set c_name = 'bcx' where c_custkey = 2;
vacuum multi_segfile_zlibtab;
insert into multi_segfile_zlibtab values( 3, 'ac','this  too is a looong text' , 1.5, '878787',500.54,'2014/04/04',now());
DROP TABLE IF EXISTS multi_segfile_parttab;
CREATE TABLE multi_segfile_parttab (
    c_custkey integer,
    c_name character varying(25),
    c_comment text,
    c_rating float,
    c_phone character(15),
    c_acctbal numeric(15,2),
    c_date date,
    c_timestamp timestamp
)
WITH (checksum=true, appendonly=true, orientation=column, compresstype=zlib, compresslevel=1) DISTRIBUTED BY (c_custkey)
partition by range(c_custkey)  subpartition by range( c_rating)
subpartition template ( default subpartition subothers,start (0.0) end(1.9) every (2.0) )
(default partition others, partition p1 start(1) end(5000), partition p2 start(5000) end(10000), partition p3 start(10000) end(15000));
insert into multi_segfile_parttab values( 1, 'aa','this is a looong text' , 4.5, '12121212',1000.34,'2015/10/10',now());
insert into multi_segfile_parttab values( 2, 'ab','this is also a looong text' , 7.5, '3456789',3000.45,'2014/08/10',now());
update multi_segfile_parttab set c_name = 'bcx' where c_custkey = 2;
vacuum multi_segfile_parttab;
insert into multi_segfile_parttab values( 3, 'ac','this  too is a looong text' , 1.5, '878787',4000.25,'2014/08/10',now());
DROP TABLE IF EXISTS multi_segfile_toast;
CREATE TABLE multi_segfile_toast (
    c_custkey integer,
    c_name character varying(25),
    c_comment text,
    c_rating float,
    c_phone character(15),
    c_acctbal numeric(15,2),
    c_date date,
    c_timestamp timestamp
)
WITH (checksum=true, appendonly=true, orientation=column, compresstype=zlib, compresslevel=1) DISTRIBUTED BY (c_custkey);
insert into multi_segfile_toast values( 1, 'aa','this is a looong text' , 3.5, '12121212',1000.34,'2015/10/10',now());
insert into multi_segfile_toast values( 2, 'ab','this is also a looong text' , 4.5, '3456789',3000.45,'2014/08/10',now());
update multi_segfile_toast set c_name = 'bcx' where c_custkey = 2;
vacuum multi_segfile_toast;
insert into multi_segfile_toast values( 3, 'ac','this  too is a looong text' , 1.5, '878787',500.54,'2014/04/04',now());
--
-- @description AOCO multi_segfile table : add column with default value NULL
alter table multi_segfile_tab ADD COLUMN added_col3 character varying(35) default NULL;
select count(*) as added_col3 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multi_segfile_tab' and attname='added_col3';
 added_col3 
------------
          1
(1 row)

--
-- @description AOCO multi_segfile table : add column with default value non NULL
alter table multi_segfile_tab ADD COLUMN added_col1 character varying(35) default 'this is default value of non null';
select count(*) as added_col1 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multi_segfile_tab' and attname='added_col1';
 added_col1 
------------
          1
(1 row)

--
-- @description AOCO multivarblock table : add column with default value non NULL then add index on new column
alter table multi_segfile_tab ADD COLUMN added_col50 TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ;
select count(*) as added_col50 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multi_segfile_tab' and attname='added_col50';
 added_col50 
-------------
           1
(1 row)

insert into multi_segfile_tab (c_custkey, c_name, c_comment, c_rating, c_phone, c_acctbal,c_date, c_timestamp)
                     values( 500, 'acz','yet another  looong text' , 11.5, '778777',550.54,'2014/05/04',now());
create index multi_segfile_tab_idx2 on multi_segfile_tab(added_col50);
--
-- @description AOCO multi_segfile table : add column with default value NULL
alter table multi_segfile_bitab ADD COLUMN added_col2 character varying(35) default NULL;
select count(*) as added_col2 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multi_segfile_bitab' and attname='added_col2';
 added_col2 
------------
          1
(1 row)

--
-- @description AOCO multi_segfile table : add column with default value non NULL
alter table multi_segfile_bitab ADD COLUMN added_col1 character varying(35) default 'this is default value of non null';
select count(*) as added_col1 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multi_segfile_bitab' and attname='added_col1';
 added_col1 
------------
          1
(1 row)

--
-- @description AOCO multi_segfile table : drop column with default value non NULL
alter table multi_segfile_bitab ADD COLUMN added_col22 character varying(35) default NULL;
select count(*) as added_col22 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multi_segfile_bitab' and attname='added_col22';
 added_col22 
-------------
           1
(1 row)

alter table multi_segfile_bitab DROP COLUMN added_col22;
select count(*) as added_col22 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multi_segfile_bitab' and attname='added_col22';
 added_col22 
-------------
           0
(1 row)

VACUUM multi_segfile_bitab;
--
-- @description AOCO multi_segfile table : drop column with default value non NULL
alter table multi_segfile_bitab ADD COLUMN added_col11 character varying(35) default 'this is default value of non null';
select count(*) as added_col11 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multi_segfile_bitab' and attname='added_col11';
 added_col11 
-------------
           1
(1 row)

alter table multi_segfile_bitab DROP COLUMN added_col11;
select count(*) as added_col11 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multi_segfile_bitab' and attname='added_col11';
 added_col11 
-------------
           0
(1 row)

VACUUM multi_segfile_bitab;
--
-- @description AOCO multi_segfile table : drop column with default value NULL
alter table multi_segfile_tab ADD COLUMN added_col33 character varying(35) default NULL;
select count(*) as added_col33 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multi_segfile_tab' and attname='added_col33';
 added_col33 
-------------
           1
(1 row)

alter table multi_segfile_tab DROP COLUMN added_col33;
select count(*) as added_col33 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multi_segfile_tab' and attname='added_col33';
 added_col33 
-------------
           0
(1 row)

VACUUM multi_segfile_tab;
--
-- @description AOCO multi_segfile table : drop column with default value non NULL
alter table multi_segfile_tab ADD COLUMN added_col11 character varying(35) default 'this is default value of non null';
select count(*) as added_col11 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multi_segfile_tab' and attname='added_col11';
 added_col11 
-------------
           1
(1 row)

alter table multi_segfile_tab DROP COLUMN added_col11;
select count(*) as added_col11 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multi_segfile_tab' and attname='added_col11';
 added_col11 
-------------
           0
(1 row)

VACUUM multi_segfile_tab;
--
-- @description AOCO multi_segfile table : add column with default value NULL
alter table multi_segfile_parttab ADD COLUMN added_col2 character varying(35) default NULL;
select count(*) as added_col2 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multi_segfile_parttab' and attname='added_col2';
 added_col2 
------------
          1
(1 row)

--
-- @description AOCO multi_segfile table : add column with default value non NULL
alter table multi_segfile_parttab ADD COLUMN added_col1 character varying(35) default 'this is default value of non null';
select count(*) as added_col1 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multi_segfile_parttab' and attname='added_col1';
 added_col1 
------------
          1
(1 row)

--
-- @description AOCO multi_segfile table : drop column with default value NULL
alter table multi_segfile_parttab ADD COLUMN added_col22 character varying(35) default NULL;
select count(*) as added_col22 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multi_segfile_parttab' and attname='added_col22';
 added_col22 
-------------
           1
(1 row)

alter table multi_segfile_parttab DROP COLUMN added_col22;
select count(*) as added_col22 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multi_segfile_parttab' and attname='added_col22';
 added_col22 
-------------
           0
(1 row)

VACUUM multi_segfile_parttab;
--
-- @description AOCO multi_segfile table : drop column with default value non NULL
alter table multi_segfile_parttab ADD COLUMN added_col11 character varying(35) default 'this is default value of non null';
select count(*) as added_col11 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multi_segfile_parttab' and attname='added_col11';
 added_col11 
-------------
           1
(1 row)

alter table multi_segfile_parttab DROP COLUMN added_col11;
select count(*) as added_col11 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multi_segfile_parttab' and attname='added_col11';
 added_col11 
-------------
           0
(1 row)

VACUUM multi_segfile_parttab;
--
-- @description AOCO multi_segfile table : add column toast with default value NULL
alter table multi_segfile_toast ADD COLUMN added_col3 bytea  default NULL;
select count(*) as added_col3 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multi_segfile_toast' and attname='added_col3';
 added_col3 
------------
          1
(1 row)

--
-- @description AOCO multi_segfile table : add column with default value non NULL
alter table multi_segfile_toast ADD COLUMN added_col4  bytea default ("decode"(repeat('1234567890',10000),'escape'));
select count(*) as added_col4 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multi_segfile_toast' and attname='added_col4';
 added_col4 
------------
          1
(1 row)

--
-- @description AOCO multi_segfile table : drop column with default value NULL
alter table multi_segfile_toast ADD COLUMN added_col33 bytea  default NULL;
select count(*) as added_col33 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multi_segfile_toast' and attname='added_col33';
 added_col33 
-------------
           1
(1 row)

alter table multi_segfile_toast DROP COLUMN added_col33;
select count(*) as added_col33 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multi_segfile_toast' and attname='added_col33';
 added_col33 
-------------
           0
(1 row)

VACUUM multi_segfile_toast;
--
-- @description AOCO multi_segfile table : drop column with default value non NULL
alter table multi_segfile_toast ADD COLUMN added_col44  bytea default ("decode"(repeat('1234567890',10000),'escape'));
select count(*) as added_col44 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multi_segfile_toast' and attname='added_col44';
 added_col44 
-------------
           1
(1 row)

alter table multi_segfile_toast DROP COLUMN added_col44;
select count(*) as added_col44 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multi_segfile_toast' and attname='added_col44';
 added_col44 
-------------
           0
(1 row)

VACUUM multi_segfile_toast;
--
-- @description AOCO multi_segfile table : add column with default value NULL
alter table multi_segfile_zlibtab ADD COLUMN added_col2 character varying(35) default NULL;
select count(*) as added_col2 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multi_segfile_zlibtab' and attname='added_col2';
 added_col2 
------------
          1
(1 row)

--
-- @description AOCO multi_segfile table : add column with default value non NULL
alter table multi_segfile_zlibtab ADD COLUMN added_col1 character varying(35) default 'this is default value of non null';
select count(*) as added_col1 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multi_segfile_zlibtab' and attname='added_col1';
 added_col1 
------------
          1
(1 row)

--
-- @description AOCO multi_segfile table : drop column with default value NULL
alter table multi_segfile_zlibtab ADD COLUMN added_col22 character varying(35) default NULL;
select count(*) as added_col22 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multi_segfile_zlibtab' and attname='added_col22';
 added_col22 
-------------
           1
(1 row)

alter table multi_segfile_zlibtab DROP COLUMN added_col22;
select count(*) as added_col22 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multi_segfile_zlibtab' and attname='added_col22';
 added_col22 
-------------
           0
(1 row)

VACUUM multi_segfile_zlibtab;
--
-- @description AOCO multi_segfile table : drop column with default value non NULL
alter table multi_segfile_zlibtab ADD COLUMN added_col11 character varying(35) default 'this is default value of non null';
select count(*) as added_col11 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multi_segfile_zlibtab' and attname='added_col11';
 added_col11 
-------------
           1
(1 row)

alter table multi_segfile_zlibtab DROP COLUMN added_col11;
select count(*) as added_col11 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multi_segfile_zlibtab' and attname='added_col11';
 added_col11 
-------------
           0
(1 row)

VACUUM multi_segfile_zlibtab;
--
-- @description AOCO multivarblock table : add column with default value NULL
alter table multivarblock_tab ADD COLUMN added_col2 character varying(35) default NULL;
select count(*) as added_col2 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multivarblock_tab' and attname='added_col2';
 added_col2 
------------
          1
(1 row)

--
-- @description AOCO multivarblock table : add column with default value non NULL
alter table multivarblock_tab ADD COLUMN added_col5 TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ;
select count(*) as added_col5 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multivarblock_tab' and attname='added_col5';
 added_col5 
------------
          1
(1 row)

--
-- @description AOCO multivarblock table : add column with constraint and default value non NULL
-- Negative test
alter table multivarblock_tab add column added_col66 int CONSTRAINT multivarblock_tab_check1 CHECK (added_col66 < 10)  default 30;
ERROR:  check constraint "multivarblock_tab_check1" of relation "multivarblock_tab" is violated by some row  (seg0 127.0.1.1:7002 pid=1938609)
select count(*) as added_col66 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multivarblock_tab' and attname='added_col66';
 added_col66 
-------------
           0
(1 row)

-- Positive test
alter table multivarblock_tab add column added_col66 int CONSTRAINT multivarblock_tab_check1 CHECK (added_col66 < 10)  default 5;
select count(*) as added_col66 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multivarblock_tab' and attname='added_col66';
 added_col66 
-------------
           1
(1 row)

--
-- @description AOCO multivarblock table : add column with default value non NULL then add index on new column
alter table multivarblock_tab ADD COLUMN added_col50 TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ;
select count(*) as added_col50 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multivarblock_tab' and attname='added_col50';
 added_col50 
-------------
           1
(1 row)

insert into multivarblock_tab (c_custkey, c_name, c_comment, c_rating, c_phone, c_acctbal,c_date, c_timestamp)
                     values( 500, 'acz','yet another  looong text' , 11.5, '778777',550.54,'2014/05/04',now());
create index multivarblock_tab_idx2 on multivarblock_tab(added_col50);
--
-- @description AOCO multivarblock table : add column with default value NULL
alter table multivarblock_bitab ADD COLUMN added_col2 character varying(35) default NULL;
select count(*) as added_col2 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multivarblock_bitab' and attname='added_col2';
 added_col2 
------------
          1
(1 row)

--
-- @description AOCO multivarblock table : add column with default value non NULL
alter table multivarblock_bitab ADD COLUMN added_col1 character varying(35) default 'this is default value of non null';
select count(*) as added_col1 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multivarblock_bitab' and attname='added_col1';
 added_col1 
------------
          1
(1 row)

--
-- @description AOCO multivarblock table : drop column with default value NULL
alter table multivarblock_bitab ADD COLUMN added_col22 character varying(35) default NULL;
select count(*) as added_col22 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multivarblock_bitab' and attname='added_col22';
 added_col22 
-------------
           1
(1 row)

alter table multivarblock_bitab DROP COLUMN added_col22;
select count(*) as added_col22 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multivarblock_bitab' and attname='added_col22';
 added_col22 
-------------
           0
(1 row)

VACUUM multivarblock_bitab;
--
-- @description AOCO multivarblock table : drop column with default value non NULL
alter table multivarblock_bitab ADD COLUMN added_col11 character varying(35) default 'this is default value of non null';
select count(*) as added_col11 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multivarblock_bitab' and attname='added_col11';
 added_col11 
-------------
           1
(1 row)

alter table multivarblock_bitab DROP COLUMN added_col11;
select count(*) as added_col11 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multivarblock_bitab' and attname='added_col11';
 added_col11 
-------------
           0
(1 row)

VACUUM multivarblock_bitab;
--
-- @description AOCO multivarblock table : drop column with default value NULL
alter table multivarblock_tab ADD COLUMN added_col22 character varying(35) default NULL;
select count(*) as added_col22 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multivarblock_tab' and attname='added_col22';
 added_col22 
-------------
           1
(1 row)

alter table multivarblock_tab DROP COLUMN added_col22;
select count(*) as added_col22 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multivarblock_tab' and attname='added_col22';
 added_col22 
-------------
           0
(1 row)

VACUUM multivarblock_tab;
--
-- @description AOCO multivarblock table : drop column with default value non NULL
alter table multivarblock_tab ADD COLUMN added_col55 TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ;
select count(*) as added_col55 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multivarblock_tab' and attname='added_col55';
 added_col55 
-------------
           1
(1 row)

alter table multivarblock_tab DROP COLUMN added_col55;
select count(*) as added_col55 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multivarblock_tab' and attname='added_col55';
 added_col55 
-------------
           0
(1 row)

VACUUM multivarblock_tab;
--
-- @description AOCO multivarblock table : add column with default value NULL
alter table multivarblock_parttab ADD COLUMN added_col2 character varying(35) default NULL;
select count(*) as added_col2 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multivarblock_parttab' and attname='added_col2';
 added_col2 
------------
          1
(1 row)

--
-- @description AOCO multivarblock table : add column with default value non NULL
alter table multivarblock_parttab ADD COLUMN added_col1 character varying(35) default 'this is default value of non null';
select count(*) as added_col1 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multivarblock_parttab' and attname='added_col1';
 added_col1 
------------
          1
(1 row)

--
-- @description AOCO multivarblock table : drop column with default value NULL
alter table multivarblock_parttab ADD COLUMN added_col22 character varying(35) default NULL;
select count(*) as added_col22 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multivarblock_parttab' and attname='added_col22';
 added_col22 
-------------
           1
(1 row)

alter table multivarblock_parttab DROP COLUMN added_col22;
select count(*) as added_col22 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multivarblock_parttab' and attname='added_col22';
 added_col22 
-------------
           0
(1 row)

VACUUM multivarblock_parttab;
--
-- @description AOCO multivarblock partition table : drop column with default value non NULL
alter table multivarblock_parttab ADD COLUMN added_col11 character varying(35) default 'this is default value of non null';
select count(*) as added_col11 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multivarblock_parttab' and attname='added_col11';
 added_col11 
-------------
           1
(1 row)

alter table multivarblock_parttab DROP COLUMN added_col11;
select count(*) as added_col11 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multivarblock_parttab' and attname='added_col11';
 added_col11 
-------------
           0
(1 row)

VACUUM multivarblock_parttab;
--
-- @description AOCO multivarblock table : add column toast with default value NULL
alter table multivarblock_toast ADD COLUMN added_col3 bytea  default NULL;
select count(*) as added_col3 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multivarblock_toast' and attname='added_col3';
 added_col3 
------------
          1
(1 row)

--
-- @description AOCO multivarblock table : add column with default value non NULL
alter table multivarblock_toast ADD COLUMN added_col4  bytea default ("decode"(repeat('1234567890',10000),'escape'));
select count(*) as added_col4 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multivarblock_toast' and attname='added_col4';
 added_col4 
------------
          1
(1 row)

--
-- @description AOCO multivarblock table : drop column with default value NULL
alter table multivarblock_toast ADD COLUMN added_col33 bytea  default NULL;
select count(*) as added_col33 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multivarblock_toast' and attname='added_col33';
 added_col33 
-------------
           1
(1 row)

alter table multivarblock_toast DROP COLUMN added_col33;
select count(*) as added_col33 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multivarblock_toast' and attname='added_col33';
 added_col33 
-------------
           0
(1 row)

VACUUM multivarblock_toast;
--
-- @description AOCO multivarblock table : drop column with default value non NULL
alter table multivarblock_toast ADD COLUMN added_col44  bytea default ("decode"(repeat('1234567890',10000),'escape'));
select count(*) as added_col44 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multivarblock_toast' and attname='added_col44';
 added_col44 
-------------
           1
(1 row)

alter table multivarblock_toast DROP COLUMN added_col44;
select count(*) as added_col44 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multivarblock_toast' and attname='added_col44';
 added_col44 
-------------
           0
(1 row)

VACUUM multivarblock_toast;
--
-- @description AOCO multivarblock table : add column with default value NULL
alter table multivarblock_zlibtab ADD COLUMN added_col2 character varying(35) default NULL;
select count(*) as added_col2 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multivarblock_zlibtab' and attname='added_col2';
 added_col2 
------------
          1
(1 row)

--
-- @description AOCO multivarblock table : add column with default value non NULL
alter table multivarblock_zlibtab ADD COLUMN added_col1 character varying(35) default 'this is default value of non null';
select count(*) as added_col1 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multivarblock_zlibtab' and attname='added_col1';
 added_col1 
------------
          1
(1 row)

--
-- @description AOCO multivarblock table : drop column with default value NULL
alter table multivarblock_zlibtab ADD COLUMN added_col22 character varying(35) default NULL;
select count(*) as added_col22 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multivarblock_zlibtab' and attname='added_col22';
 added_col22 
-------------
           1
(1 row)

alter table multivarblock_zlibtab DROP COLUMN added_col22;
select count(*) as added_col22 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multivarblock_zlibtab' and attname='added_col22';
 added_col22 
-------------
           0
(1 row)

VACUUM multivarblock_zlibtab;
--
-- @description AOCO multivarblock zlib compressed table : drop column with default value non NULL
alter table multivarblock_zlibtab ADD COLUMN added_col11 character varying(35) default 'this is default value of non null';
select count(*) as added_col11 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multivarblock_zlibtab' and attname='added_col11';
 added_col11 
-------------
           1
(1 row)

alter table multivarblock_zlibtab DROP COLUMN added_col11;
select count(*) as added_col11 from pg_attribute pa, pg_class pc where pa.attrelid = pc.oid and pc.relname='multivarblock_zlibtab' and attname='added_col11';
 added_col11 
-------------
           0
(1 row)

VACUUM multivarblock_zlibtab;
--
-- Non-bulk dense content header with RLE compression
-- This will  insert more rows than a small content header can accommodate in the same insert statement
drop table if exists nonbulk_rle_tab;
NOTICE:  table "nonbulk_rle_tab" does not exist, skipping
create table nonbulk_rle_tab (a int) with (appendonly=true, orientation=column, compresstype='rle_type', checksum=true);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry 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 nonbulk_rle_tab select i/50 from generate_series(1, 1000000)i;
alter table nonbulk_rle_tab add column b int default round(random()*100);
insert into nonbulk_rle_tab values (-1,-5);
ANALYZE nonbulk_rle_tab; -- To avoid NOTICE about missing stats with ORCA.
update nonbulk_rle_tab set b = b + 3 where a = -1;
CREATE FUNCTION descendants_of(rel regclass) RETURNS TABLE(descendant regclass)
SET gp_recursive_cte=ON
LANGUAGE SQL STABLE AS $fn$
WITH RECURSIVE w AS (
  SELECT rel AS descendant
  UNION ALL
  SELECT inhrelid
  FROM pg_inherits JOIN w ON inhparent = descendant
)
SELECT * FROM w;
$fn$;
-- Scenario 1: Parent is AOCO, 1 child is AO, 1 child is heap. AO children require full table rewrite
CREATE TABLE rewrite_optimization_aoco_parent(a int, b int, c int)
WITH (APPENDONLY = true, ORIENTATION = column)
DISTRIBUTED BY (a)
    PARTITION BY RANGE (b)
        (START (0) END (1) EVERY (1) WITH (APPENDONLY = true),
        START (1) END (2) EVERY (1) WITH (APPENDONLY = false));
SELECT $$
SELECT -1 AS segno, oid::regclass AS rel, relfilenode, (select amname from pg_am where pg_am.oid = relam)
FROM pg_class c
WHERE c.oid IN (SELECT descendant FROM descendants_of(:'ROOT_PARTITION_UNDER_TEST'))
UNION ALL
SELECT gp_segment_id, oid::regclass, relfilenode, (select amname from pg_am where pg_am.oid = relam)
FROM gp_dist_random('pg_class') c
WHERE c.oid IN (SELECT descendant FROM descendants_of(:'ROOT_PARTITION_UNDER_TEST'))
$$ AS qry \gset
SELECT $$
SELECT t.segno,
       t.rel,
       t.amname,
       CASE
           WHEN table_relfilenode.segno IS NULL THEN 'full table rewritten'
           ELSE 'ADD COLUMN optimized for table' END AS aoco_add_col_optimized
FROM (:qry) t
         LEFT JOIN table_relfilenode USING (segno, rel, relfilenode)
WHERE t.segno IN (-1, 0)
ORDER BY 1, 2;
$$ AS chk_co_opt_qry \gset
\set ROOT_PARTITION_UNDER_TEST rewrite_optimization_aoco_parent
CREATE MATERIALIZED VIEW table_relfilenode (segno, rel, relfilenode)
AS
:qry
;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'segno' as the Apache Cloudberry 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 rewrite_optimization_aoco_parent SELECT i, i % 2, i FROM generate_series(1,10)i;
ALTER TABLE rewrite_optimization_aoco_parent ADD COLUMN new_col int DEFAULT 1;
SELECT * FROM rewrite_optimization_aoco_parent;
 a  | b | c  | new_col 
----+---+----+---------
  1 | 1 |  1 |       1
  6 | 0 |  6 |       1
 10 | 0 | 10 |       1
  5 | 1 |  5 |       1
  9 | 1 |  9 |       1
  2 | 0 |  2 |       1
  4 | 0 |  4 |       1
  8 | 0 |  8 |       1
  3 | 1 |  3 |       1
  7 | 1 |  7 |       1
(10 rows)

:chk_co_opt_qry;
 segno |                   rel                    |  amname   |     aoco_add_col_optimized     
-------+------------------------------------------+-----------+--------------------------------
    -1 | rewrite_optimization_aoco_parent         | ao_column | ADD COLUMN optimized for table
    -1 | rewrite_optimization_aoco_parent_1_prt_1 | ao_row    | full table rewritten
    -1 | rewrite_optimization_aoco_parent_1_prt_2 | heap      | ADD COLUMN optimized for table
     0 | rewrite_optimization_aoco_parent         | ao_column | ADD COLUMN optimized for table
     0 | rewrite_optimization_aoco_parent_1_prt_1 | ao_row    | full table rewritten
     0 | rewrite_optimization_aoco_parent_1_prt_2 | heap      | ADD COLUMN optimized for table
(6 rows)

-- ADD COLUMN for AOCO partition children should not trigger full table rewrite
-- Scenario 2: Parent is heap, 1 child is AO, 1 child is AOCO. AO child requires full table rewrite. AOCO child does not.
CREATE TABLE rewrite_optimization_heap_parent(a int, b int, c int) DISTRIBUTED BY (a)
    PARTITION BY RANGE (b)
        (START (0) END (1) EVERY (1) WITH (APPENDONLY = true),
        START (1) END (2) EVERY (1) WITH (APPENDONLY = true, ORIENTATION = column));
\set ROOT_PARTITION_UNDER_TEST rewrite_optimization_heap_parent
DROP MATERIALIZED VIEW table_relfilenode;
CREATE MATERIALIZED VIEW table_relfilenode (segno, rel, relfilenode)
AS
:qry
;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'segno' as the Apache Cloudberry 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 rewrite_optimization_heap_parent SELECT i, i % 2, i FROM generate_series(1,10)i;
ALTER TABLE rewrite_optimization_heap_parent ADD COLUMN new_col int DEFAULT 1;
SELECT * FROM rewrite_optimization_heap_parent;
 a  | b | c  | new_col 
----+---+----+---------
  6 | 0 |  6 |       1
 10 | 0 | 10 |       1
  5 | 1 |  5 |       1
  9 | 1 |  9 |       1
  2 | 0 |  2 |       1
  4 | 0 |  4 |       1
  8 | 0 |  8 |       1
  3 | 1 |  3 |       1
  7 | 1 |  7 |       1
  1 | 1 |  1 |       1
(10 rows)

:chk_co_opt_qry;
 segno |                   rel                    |  amname   |     aoco_add_col_optimized     
-------+------------------------------------------+-----------+--------------------------------
    -1 | rewrite_optimization_heap_parent         |           | ADD COLUMN optimized for table
    -1 | rewrite_optimization_heap_parent_1_prt_1 | ao_row    | full table rewritten
    -1 | rewrite_optimization_heap_parent_1_prt_2 | ao_column | ADD COLUMN optimized for table
     0 | rewrite_optimization_heap_parent         |           | ADD COLUMN optimized for table
     0 | rewrite_optimization_heap_parent_1_prt_1 | ao_row    | full table rewritten
     0 | rewrite_optimization_heap_parent_1_prt_2 | ao_column | ADD COLUMN optimized for table
(6 rows)

REFRESH MATERIALIZED VIEW table_relfilenode;
ALTER TABLE rewrite_optimization_heap_parent ADD COLUMN new_col2 int DEFAULT 1, ALTER COLUMN c TYPE bigint;
SELECT * FROM rewrite_optimization_heap_parent;
 a  | b | c  | new_col | new_col2 
----+---+----+---------+----------
  1 | 1 |  1 |       1 |        1
  6 | 0 |  6 |       1 |        1
 10 | 0 | 10 |       1 |        1
  5 | 1 |  5 |       1 |        1
  9 | 1 |  9 |       1 |        1
  2 | 0 |  2 |       1 |        1
  4 | 0 |  4 |       1 |        1
  8 | 0 |  8 |       1 |        1
  3 | 1 |  3 |       1 |        1
  7 | 1 |  7 |       1 |        1
(10 rows)

:chk_co_opt_qry;
 segno |                   rel                    |  amname   |     aoco_add_col_optimized     
-------+------------------------------------------+-----------+--------------------------------
    -1 | rewrite_optimization_heap_parent         |           | ADD COLUMN optimized for table
    -1 | rewrite_optimization_heap_parent_1_prt_1 | ao_row    | full table rewritten
    -1 | rewrite_optimization_heap_parent_1_prt_2 | ao_column | full table rewritten
     0 | rewrite_optimization_heap_parent         |           | ADD COLUMN optimized for table
     0 | rewrite_optimization_heap_parent_1_prt_1 | ao_row    | full table rewritten
     0 | rewrite_optimization_heap_parent_1_prt_2 | ao_column | full table rewritten
(6 rows)

-- Parent is heap, child is heap, grandchild is AOCO.
CREATE TABLE subpartition_aoco_leaf(a int, b int, c int)
DISTRIBUTED BY (a)
  PARTITION BY RANGE(b) SUBPARTITION BY RANGE(c) 
      (PARTITION intermediate START (0) END (2)
        (SUBPARTITION leaf START (0) END(2) WITH (APPENDONLY = true, ORIENTATION = column))
      );
INSERT INTO subpartition_aoco_leaf SELECT i, i % 2, i % 2 FROM generate_series(1,10)i;
\set ROOT_PARTITION_UNDER_TEST subpartition_aoco_leaf
DROP MATERIALIZED VIEW table_relfilenode;
CREATE MATERIALIZED VIEW table_relfilenode (segno, rel, relfilenode)
AS
:qry
;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'segno' as the Apache Cloudberry 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.
-- Scenario 1: ADD COLUMN for AOCO subpartitioned table should not trigger full
-- table rewrite. AOCO leaf only writes new column
ALTER TABLE subpartition_aoco_leaf ADD COLUMN new_col int DEFAULT 1;
SELECT * FROM subpartition_aoco_leaf;
 a  | b | c | new_col 
----+---+---+---------
  1 | 1 | 1 |       1
  5 | 1 | 1 |       1
  6 | 0 | 0 |       1
  9 | 1 | 1 |       1
 10 | 0 | 0 |       1
  2 | 0 | 0 |       1
  3 | 1 | 1 |       1
  4 | 0 | 0 |       1
  7 | 1 | 1 |       1
  8 | 0 | 0 |       1
(10 rows)

:chk_co_opt_qry;
 segno |                         rel                          |  amname   |     aoco_add_col_optimized     
-------+------------------------------------------------------+-----------+--------------------------------
    -1 | subpartition_aoco_leaf                               |           | ADD COLUMN optimized for table
    -1 | subpartition_aoco_leaf_1_prt_intermediate            | heap      | ADD COLUMN optimized for table
    -1 | subpartition_aoco_leaf_1_prt_intermediate_2_prt_leaf | ao_column | ADD COLUMN optimized for table
     0 | subpartition_aoco_leaf                               |           | ADD COLUMN optimized for table
     0 | subpartition_aoco_leaf_1_prt_intermediate            | heap      | ADD COLUMN optimized for table
     0 | subpartition_aoco_leaf_1_prt_intermediate_2_prt_leaf | ao_column | ADD COLUMN optimized for table
(6 rows)

REFRESH MATERIALIZED VIEW table_relfilenode;
-- Scenario 2: mixing ADD COLUMN with ALTER COLUMN TYPE should trigger full on aoco
-- table rewrite for every level
ALTER TABLE subpartition_aoco_leaf ADD COLUMN new_col2 int DEFAULT 1, ALTER COLUMN new_col TYPE bigint;
SELECT * FROM subpartition_aoco_leaf;
 a  | b | c | new_col | new_col2 
----+---+---+---------+----------
  1 | 1 | 1 |       1 |        1
  2 | 0 | 0 |       1 |        1
  3 | 1 | 1 |       1 |        1
  4 | 0 | 0 |       1 |        1
  7 | 1 | 1 |       1 |        1
  8 | 0 | 0 |       1 |        1
  5 | 1 | 1 |       1 |        1
  6 | 0 | 0 |       1 |        1
  9 | 1 | 1 |       1 |        1
 10 | 0 | 0 |       1 |        1
(10 rows)

:chk_co_opt_qry;
 segno |                         rel                          |  amname   |     aoco_add_col_optimized     
-------+------------------------------------------------------+-----------+--------------------------------
    -1 | subpartition_aoco_leaf                               |           | ADD COLUMN optimized for table
    -1 | subpartition_aoco_leaf_1_prt_intermediate            | heap      | ADD COLUMN optimized for table
    -1 | subpartition_aoco_leaf_1_prt_intermediate_2_prt_leaf | ao_column | full table rewritten
     0 | subpartition_aoco_leaf                               |           | ADD COLUMN optimized for table
     0 | subpartition_aoco_leaf_1_prt_intermediate            | heap      | ADD COLUMN optimized for table
     0 | subpartition_aoco_leaf_1_prt_intermediate_2_prt_leaf | ao_column | full table rewritten
(6 rows)

-- Check if add column doesn't rewrite the table
CREATE TABLE testaddcol(i int) WITH (appendonly=true, orientation=column);
INSERT INTO testaddcol SELECT generate_series(1, 5);
CREATE TEMP TABLE relfilebeforetestaddcol AS
    SELECT -1 segid, relname, relfilenode FROM pg_class WHERE relname LIKE 'testaddcol%'
    UNION SELECT gp_segment_id segid, relname, relfilenode FROM gp_dist_random('pg_class')
    WHERE relname LIKE 'testaddcol%' ORDER BY segid;
ALTER TABLE testaddcol ADD COLUMN j int DEFAULT 5;
CREATE TEMP TABLE relfileaftertestaddcol AS
    SELECT -1 segid, relname, relfilenode FROM pg_class WHERE relname LIKE 'testaddcol%'
    UNION SELECT gp_segment_id segid, relname, relfilenode FROM gp_dist_random('pg_class')
    WHERE relname LIKE 'testaddcol%' ORDER BY segid;
-- table shouldn't be rewritten
SELECT count(*) FROM (SELECT * FROM relfilebeforetestaddcol UNION SELECT * FROM relfileaftertestaddcol)a;
 count 
-------
     4
(1 row)

-- data is intact
SELECT * FROM testaddcol;
 i | j 
---+---
 2 | 5
 3 | 5
 4 | 5
 5 | 5
 1 | 5
(5 rows)