src/test/regress/expected/appendoptimized_basic.out

create table ao_basic_t1 (a int, b varchar) using ao_row distributed by (a);
-- Validate that the appendoptimized table access method will be used
-- for this table.
select amhandler from pg_class c, pg_am a where c.relname = 'ao_basic_t1' and c.relam = a.oid;
       amhandler        
------------------------
 ao_row_tableam_handler
(1 row)

select count(*) = 1 from pg_appendonly where relid = 'ao_basic_t1'::regclass;
 ?column? 
----------
 t
(1 row)

insert into ao_basic_t1 values (1, 'abc'), (2, 'pqr'), (3, 'lmn');
insert into ao_basic_t1 select i, i from generate_series(1,12)i;
select * from ao_basic_t1;
 a  |  b  
----+-----
  5 | 5
  6 | 6
  9 | 9
 10 | 10
 11 | 11
  2 | pqr
  3 | lmn
  2 | 2
  3 | 3
  4 | 4
  7 | 7
  8 | 8
  1 | abc
  1 | 1
 12 | 12
(15 rows)

select gp_segment_id, * from ao_basic_t1;
 gp_segment_id | a  |  b  
---------------+----+-----
             0 |  2 | pqr
             0 |  3 | lmn
             0 |  2 | 2
             0 |  3 | 3
             0 |  4 | 4
             0 |  7 | 7
             0 |  8 | 8
             1 |  1 | abc
             1 |  1 | 1
             1 | 12 | 12
             2 |  5 | 5
             2 |  6 | 6
             2 |  9 | 9
             2 | 10 | 10
             2 | 11 | 11
(15 rows)

select gp_segment_id, count(*) from ao_basic_t1 group by gp_segment_id;
 gp_segment_id | count 
---------------+-------
             2 |     5
             0 |     7
             1 |     3
(3 rows)

insert into ao_basic_t1 values (1, repeat('abc', 100000));
-- create two segment files by inserting from concurrent sessions
begin;
insert into ao_basic_t1 select i, 'session1' from generate_series(1,20)i;
\! psql -d regression -c "insert into ao_basic_t1 select i, 'session2' from generate_series(1,20)i"
INSERT 0 20
end;
select a, length(b) from ao_basic_t1;
 a  | length 
----+--------
  2 |      3
  3 |      3
  2 |      1
  3 |      1
  4 |      1
  7 |      1
  8 |      1
  2 |      8
  3 |      8
  4 |      8
  7 |      8
  8 |      8
 16 |      8
 18 |      8
 19 |      8
  2 |      8
  3 |      8
  4 |      8
  7 |      8
  8 |      8
 16 |      8
 18 |      8
 19 |      8
  5 |      1
  6 |      1
  9 |      1
 10 |      2
 11 |      2
  5 |      8
  6 |      8
  9 |      8
 10 |      8
 11 |      8
 13 |      8
 14 |      8
 17 |      8
  5 |      8
  6 |      8
  9 |      8
 10 |      8
 11 |      8
 13 |      8
 14 |      8
 17 |      8
  1 |      3
  1 |      1
 12 |      2
  1 | 300000
  1 |      8
 12 |      8
 15 |      8
 20 |      8
  1 |      8
 12 |      8
 15 |      8
 20 |      8
(56 rows)

create index i_ao_basic_t1 on ao_basic_t1 using btree(a);
select blkdirrelid > 0 as blockdir_created from pg_appendonly where relid = 'ao_basic_t1'::regclass;
 blockdir_created 
------------------
 t
(1 row)

set enable_seqscan = off;
set enable_bitmapscan = on;
set enable_indexscan = on;
explain (costs off) select * from ao_basic_t1 where a < 4;
                     QUERY PLAN                      
-----------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)
   ->  Index Scan using i_ao_basic_t1 on ao_basic_t1
         Index Cond: (a < 4)
 Optimizer: Postgres query optimizer
(4 rows)

select a, length(b) from ao_basic_t1 where a < 4;
 a | length 
---+--------
 2 |      3
 2 |      1
 2 |      8
 2 |      8
 3 |      3
 3 |      1
 3 |      8
 3 |      8
 1 |      3
 1 |      1
 1 | 300000
 1 |      8
 1 |      8
(13 rows)

insert into ao_basic_t1 select i, 'index insert' from generate_series(1,12)i;
create table heap_t2(a int, b varchar) distributed by (a);
insert into heap_t2 select i, i from generate_series(1, 20)i;
select * from ao_basic_t1 t1 join heap_t2 t2 on t1.a=t2.a where t1.a != 1;
 a  |      b       | a  | b  
----+--------------+----+----
  5 | 5            |  5 | 5
  5 | session1     |  5 | 5
  5 | session2     |  5 | 5
  5 | index insert |  5 | 5
  6 | 6            |  6 | 6
  6 | session1     |  6 | 6
  6 | session2     |  6 | 6
  6 | index insert |  6 | 6
  9 | 9            |  9 | 9
  9 | session1     |  9 | 9
  9 | session2     |  9 | 9
  9 | index insert |  9 | 9
 10 | 10           | 10 | 10
 10 | session1     | 10 | 10
 10 | session2     | 10 | 10
 10 | index insert | 10 | 10
 11 | 11           | 11 | 11
 11 | session1     | 11 | 11
 11 | session2     | 11 | 11
 11 | index insert | 11 | 11
 13 | session1     | 13 | 13
 13 | session2     | 13 | 13
 14 | session1     | 14 | 14
 14 | session2     | 14 | 14
 17 | session1     | 17 | 17
 17 | session2     | 17 | 17
  2 | pqr          |  2 | 2
  2 | 2            |  2 | 2
  2 | session1     |  2 | 2
  2 | session2     |  2 | 2
  2 | index insert |  2 | 2
  3 | lmn          |  3 | 3
  3 | 3            |  3 | 3
  3 | session1     |  3 | 3
  3 | session2     |  3 | 3
  3 | index insert |  3 | 3
  4 | 4            |  4 | 4
  4 | session1     |  4 | 4
  4 | session2     |  4 | 4
  4 | index insert |  4 | 4
  7 | 7            |  7 | 7
  7 | session1     |  7 | 7
  7 | session2     |  7 | 7
  7 | index insert |  7 | 7
  8 | 8            |  8 | 8
  8 | session1     |  8 | 8
  8 | session2     |  8 | 8
  8 | index insert |  8 | 8
 16 | session1     | 16 | 16
 16 | session2     | 16 | 16
 18 | session1     | 18 | 18
 18 | session2     | 18 | 18
 19 | session1     | 19 | 19
 19 | session2     | 19 | 19
 12 | 12           | 12 | 12
 12 | session1     | 12 | 12
 12 | session2     | 12 | 12
 12 | index insert | 12 | 12
 15 | session1     | 15 | 15
 15 | session2     | 15 | 15
 20 | session1     | 20 | 20
 20 | session2     | 20 | 20
(62 rows)

create table ao_basic_t2 (a int) using ao_row distributed by (a);
insert into ao_basic_t2 select i from generate_series(1,20)i;
alter table ao_basic_t2 add column b varchar default 'abc';
select * from ao_basic_t2;
 a  |  b  
----+-----
  2 | abc
  3 | abc
  4 | abc
  7 | abc
  8 | abc
 16 | abc
 18 | abc
 19 | abc
  5 | abc
  6 | abc
  9 | abc
 10 | abc
 11 | abc
 13 | abc
 14 | abc
 17 | abc
  1 | abc
 12 | abc
 15 | abc
 20 | abc
(20 rows)

insert into ao_basic_t2 select  i, 'new column' from generate_series(1,12)i;
select * from ao_basic_t2 where b != 'abc';
 a  |     b      
----+------------
  2 | new column
  3 | new column
  4 | new column
  7 | new column
  8 | new column
  1 | new column
 12 | new column
  5 | new column
  6 | new column
  9 | new column
 10 | new column
 11 | new column
(12 rows)

create table ao_ctas using ao_row as select * from heap_t2;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) 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.
select amhandler from pg_class c, pg_am a where c.relname = 'ao_ctas' and c.relam = a.oid;
       amhandler        
------------------------
 ao_row_tableam_handler
(1 row)

insert into ao_ctas values (0, 'inserted');
table ao_ctas;
 a  |    b     
----+----------
  1 | 1
 12 | 12
 15 | 15
 20 | 20
  0 | inserted
  5 | 5
  6 | 6
  9 | 9
 10 | 10
 11 | 11
 13 | 13
 14 | 14
 17 | 17
  2 | 2
  3 | 3
  4 | 4
  7 | 7
  8 | 8
 16 | 16
 18 | 18
 19 | 19
(21 rows)

insert into ao_ctas select * from heap_t2;
table ao_ctas;
 a  |    b     
----+----------
  1 | 1
 12 | 12
 15 | 15
 20 | 20
  0 | inserted
  1 | 1
 12 | 12
 15 | 15
 20 | 20
  5 | 5
  6 | 6
  9 | 9
 10 | 10
 11 | 11
 13 | 13
 14 | 14
 17 | 17
  5 | 5
  6 | 6
  9 | 9
 10 | 10
 11 | 11
 13 | 13
 14 | 14
 17 | 17
  2 | 2
  3 | 3
  4 | 4
  7 | 7
  8 | 8
 16 | 16
 18 | 18
 19 | 19
  2 | 2
  3 | 3
  4 | 4
  7 | 7
  8 | 8
 16 | 16
 18 | 18
 19 | 19
(41 rows)

--delete
delete from ao_ctas where a < 5;
select * from ao_ctas;
 a  | b  
----+----
  7 | 7
  8 | 8
 16 | 16
 18 | 18
 19 | 19
  7 | 7
  8 | 8
 16 | 16
 18 | 18
 19 | 19
 12 | 12
 15 | 15
 20 | 20
 12 | 12
 15 | 15
 20 | 20
  5 | 5
  6 | 6
  9 | 9
 10 | 10
 11 | 11
 13 | 13
 14 | 14
 17 | 17
  5 | 5
  6 | 6
  9 | 9
 10 | 10
 11 | 11
 13 | 13
 14 | 14
 17 | 17
(32 rows)

delete from ao_basic_t1 where a in (1, 10, 4);
select * from ao_basic_t1 where a = 1;
 a | b 
---+---
(0 rows)