src/test/regress/expected/freeze_aux_tables.out

-- These tests verify that vacuum freeze correctly updates age of auxiliary
-- tables along with the age of a parent appendonly table
-- First, create some helper functions.
CREATE TEMPORARY TABLE dummytable (id int4) distributed randomly;
CREATE FUNCTION advance_xid_counter(n integer) RETURNS void as $$
declare
  i integer;
begin
  -- do multiple updates to advance the XID counter
  -- The purpose of the exception block is to begin a new subtransaction for
  -- each command, to consume an XID.
  for i in 1..n loop
    begin
      insert into dummytable values (i);
    exception when others then
      raise notice 'something went wrong: %', sqlerrm;
    end;
  end loop;
end;
$$ language 'plpgsql';
CREATE FUNCTION classify_age(age integer) RETURNS text AS $$
  SELECT CASE WHEN $1 < 0 THEN 'negative'
              WHEN $1 = 0 THEN 'zero'
	      WHEN $1 < 50 THEN 'very young' -- less than vacuum_freeze_min_age=50 that we use below
	      WHEN $1 < 100 THEN 'young' -- recently processed by vacuum freeze
	      ELSE 'old' END; -- not frozen
$$ LANGUAGE SQL IMMUTABLE STRICT;
CREATE TYPE rel_ages AS (segid integer, relname text, age integer);
-- Get age(relfrozenxid) of a table, and all its auxiliary tables. On master,
-- and on all segments. For AO and CO table relfrozenxid = 0 and should stay
-- 0. So, essentially base AO / CO tables should not show-up in the results.
CREATE OR REPLACE FUNCTION aux_rel_ages(testrelid regclass) RETURNS SETOF rel_ages as
$$
declare
  rec rel_ages;
begin
  for rec in select gp_segment_id, replace(relname, testrelid::oid::text, '<oid>'), age(relfrozenxid)
    from pg_class
    where relkind in ('r','t','o','b','M') and relam in (2, 3434, 3435)
    and (relname like '%\_' || testrelid::oid or oid = testrelid::oid )
    and not relfrozenxid = 0
  loop
    return next rec;
  end loop;

  for rec in select gp_segment_id, replace(relname, testrelid::oid::text, '<oid>'), age(relfrozenxid)
    from gp_dist_random('pg_class')
    where relkind in ('r','t','o','b','M') and relam in (2, 3434, 3435)
    and (relname like '%\_' || testrelid::oid or oid = testrelid::oid )
    and not relfrozenxid = 0
  loop
    return next rec;
  end loop;
end;
$$ language plpgsql;
-- Start tests
--
-- The basic test structure is:
-- 1. CREATE TABLE, leave it empty
-- 2. Run some commands, to advance XID counter
-- 3. VACUUM
-- 4. Verify that the relfrozenxid was advanced by the vacuum
--
set vacuum_freeze_min_age = 50;
create table test_table_heap (id int, col1 int) with (appendonly=false);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' 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.
create table test_table_heap_with_toast (id int, col1 int, col2 text) with (appendonly=false);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' 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.
create table test_table_ao (id int, col1 int) with (appendonly=true, orientation=row);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' 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.
create table test_table_ao_with_toast (id int, col1 int, col2 text) with (appendonly=true, orientation=row);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' 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.
create table test_table_co (id int, col1 int) with (appendonly=true, orientation=column);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' 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.
create index test_heap_idx on test_table_heap using btree(id);
create index test_heap_wt_idx on test_table_heap_with_toast using btree(id);
create index test_heap_ao_idx on test_table_ao using btree(id);
create index test_heap_ao_wt_idx on test_table_ao_with_toast using btree(id);
create index test_heap_co_idx on test_table_co using btree(id);
-- Advance XID counter, vacuum, and check that relfrozenxid was advanced for
-- all the tables, including auxiliary tables, even though there were no
-- dead tuples.
select advance_xid_counter(500);
 advance_xid_counter 
---------------------
 
(1 row)

-- check table ages before vacuum.
select segid = -1 as is_master, relname, classify_age(age) from aux_rel_ages('test_table_heap')
group by segid = -1, relname, classify_age(age);
 is_master |     relname     | classify_age 
-----------+-----------------+--------------
 f         | test_table_heap | old
 t         | test_table_heap | very young
(2 rows)

select segid = -1 as is_master, relname, classify_age(age) from aux_rel_ages('test_table_heap_with_toast')
group by segid = -1, relname, classify_age(age);
 is_master |          relname           | classify_age 
-----------+----------------------------+--------------
 f         | pg_toast_<oid>             | old
 t         | test_table_heap_with_toast | very young
 f         | test_table_heap_with_toast | old
 t         | pg_toast_<oid>             | very young
(4 rows)

select segid = -1 as is_master, relname, classify_age(age) from aux_rel_ages('test_table_ao')
group by segid = -1, relname, classify_age(age);
 is_master |      relname       | classify_age 
-----------+--------------------+--------------
 t         | pg_aovisimap_<oid> | very young
 f         | pg_aovisimap_<oid> | old
 f         | pg_aoblkdir_<oid>  | old
 f         | pg_aoseg_<oid>     | old
 t         | pg_aoseg_<oid>     | very young
 t         | pg_aoblkdir_<oid>  | very young
(6 rows)

select segid = -1 as is_master, relname, classify_age(age) from aux_rel_ages('test_table_ao_with_toast')
group by segid = -1, relname, classify_age(age);
 is_master |      relname       | classify_age 
-----------+--------------------+--------------
 f         | pg_toast_<oid>     | old
 t         | pg_toast_<oid>     | very young
 t         | pg_aovisimap_<oid> | very young
 f         | pg_aovisimap_<oid> | old
 f         | pg_aoblkdir_<oid>  | old
 f         | pg_aoseg_<oid>     | old
 t         | pg_aoseg_<oid>     | very young
 t         | pg_aoblkdir_<oid>  | very young
(8 rows)

select segid = -1 as is_master, relname, classify_age(age) from aux_rel_ages('test_table_co')
group by segid = -1, relname, classify_age(age);
 is_master |      relname       | classify_age 
-----------+--------------------+--------------
 t         | pg_aocsseg_<oid>   | very young
 t         | pg_aovisimap_<oid> | very young
 f         | pg_aocsseg_<oid>   | old
 f         | pg_aovisimap_<oid> | old
 f         | pg_aoblkdir_<oid>  | old
 t         | pg_aoblkdir_<oid>  | very young
(6 rows)

-- Vacuum. This should advance relfrozenxid on all the tables.
vacuum test_table_heap;
vacuum test_table_heap_with_toast;
vacuum test_table_ao;
vacuum test_table_ao_with_toast;
vacuum test_table_co;
-- Check table ages.
select segid = -1 as is_master, relname, classify_age(age) from aux_rel_ages('test_table_heap')
group by segid = -1, relname, classify_age(age);
 is_master |     relname     | classify_age 
-----------+-----------------+--------------
 f         | test_table_heap | young
 t         | test_table_heap | very young
(2 rows)

select segid = -1 as is_master, relname, classify_age(age) from aux_rel_ages('test_table_heap_with_toast')
group by segid = -1, relname, classify_age(age);
 is_master |          relname           | classify_age 
-----------+----------------------------+--------------
 t         | test_table_heap_with_toast | very young
 f         | test_table_heap_with_toast | young
 f         | pg_toast_<oid>             | young
 t         | pg_toast_<oid>             | very young
(4 rows)

select segid = -1 as is_master, relname, classify_age(age) from aux_rel_ages('test_table_ao')
group by segid = -1, relname, classify_age(age);
 is_master |      relname       | classify_age 
-----------+--------------------+--------------
 f         | pg_aoblkdir_<oid>  | young
 f         | pg_aovisimap_<oid> | young
 t         | pg_aovisimap_<oid> | very young
 t         | pg_aoseg_<oid>     | very young
 f         | pg_aoseg_<oid>     | young
 t         | pg_aoblkdir_<oid>  | very young
(6 rows)

select segid = -1 as is_master, relname, classify_age(age) from aux_rel_ages('test_table_ao_with_toast')
group by segid = -1, relname, classify_age(age);
 is_master |      relname       | classify_age 
-----------+--------------------+--------------
 f         | pg_aoblkdir_<oid>  | young
 f         | pg_toast_<oid>     | young
 t         | pg_toast_<oid>     | very young
 f         | pg_aovisimap_<oid> | young
 t         | pg_aovisimap_<oid> | very young
 t         | pg_aoseg_<oid>     | very young
 f         | pg_aoseg_<oid>     | young
 t         | pg_aoblkdir_<oid>  | very young
(8 rows)

select segid = -1 as is_master, relname, classify_age(age) from aux_rel_ages('test_table_co')
group by segid = -1, relname, classify_age(age);
 is_master |      relname       | classify_age 
-----------+--------------------+--------------
 t         | pg_aocsseg_<oid>   | very young
 f         | pg_aocsseg_<oid>   | young
 f         | pg_aoblkdir_<oid>  | young
 f         | pg_aovisimap_<oid> | young
 t         | pg_aovisimap_<oid> | very young
 t         | pg_aoblkdir_<oid>  | very young
(6 rows)

-- Repeat the tests on a table that has been inserted to, but all the rows
-- have been deleted.
INSERT INTO test_table_heap select i, i*2 from generate_series(1, 20)i;
INSERT INTO test_table_heap_with_toast select i, i*2, i*5 from generate_series(1, 20)i;
INSERT INTO test_table_ao select i, i*2 from generate_series(1, 20)i;
INSERT INTO test_table_ao_with_toast select i, i*2, i*5 from generate_series(1, 20)i;
INSERT INTO test_table_co select i, i*2 from generate_series(1, 20)i;
delete from test_table_heap;
delete from test_table_heap_with_toast;
delete from test_table_ao;
delete from test_table_ao_with_toast;
delete from test_table_co;
select count(*) from test_table_heap;
 count 
-------
     0
(1 row)

select count(*) from test_table_heap_with_toast;
 count 
-------
     0
(1 row)

select count(*) from test_table_ao;
 count 
-------
     0
(1 row)

select count(*) from test_table_ao_with_toast;
 count 
-------
     0
(1 row)

select count(*) from test_table_co;
 count 
-------
     0
(1 row)

select advance_xid_counter(500);
 advance_xid_counter 
---------------------
 
(1 row)

vacuum freeze test_table_heap;
vacuum freeze test_table_heap_with_toast;
vacuum freeze test_table_ao;
vacuum freeze test_table_ao_with_toast;
vacuum freeze test_table_co;
-- Check table ages again. Because we used VACUUM FREEZE, they should be
-- very young now.
select segid = -1 as is_master, relname, classify_age(age) from aux_rel_ages('test_table_heap')
group by segid = -1, relname, classify_age(age);
 is_master |     relname     | classify_age 
-----------+-----------------+--------------
 f         | test_table_heap | very young
 t         | test_table_heap | very young
(2 rows)

select segid = -1 as is_master, relname, classify_age(age) from aux_rel_ages('test_table_heap_with_toast')
group by segid = -1, relname, classify_age(age);
 is_master |          relname           | classify_age 
-----------+----------------------------+--------------
 f         | pg_toast_<oid>             | very young
 t         | test_table_heap_with_toast | very young
 f         | test_table_heap_with_toast | very young
 t         | pg_toast_<oid>             | very young
(4 rows)

select segid = -1 as is_master, relname, classify_age(age) from aux_rel_ages('test_table_ao')
group by segid = -1, relname, classify_age(age);
 is_master |      relname       | classify_age 
-----------+--------------------+--------------
 f         | pg_aoblkdir_<oid>  | very young
 f         | pg_aoseg_<oid>     | very young
 t         | pg_aovisimap_<oid> | very young
 t         | pg_aoseg_<oid>     | very young
 t         | pg_aoblkdir_<oid>  | very young
 f         | pg_aovisimap_<oid> | very young
(6 rows)

select segid = -1 as is_master, relname, classify_age(age) from aux_rel_ages('test_table_ao_with_toast')
group by segid = -1, relname, classify_age(age);
 is_master |      relname       | classify_age 
-----------+--------------------+--------------
 f         | pg_toast_<oid>     | very young
 f         | pg_aoblkdir_<oid>  | very young
 f         | pg_aoseg_<oid>     | very young
 t         | pg_toast_<oid>     | very young
 t         | pg_aovisimap_<oid> | very young
 t         | pg_aoseg_<oid>     | very young
 t         | pg_aoblkdir_<oid>  | very young
 f         | pg_aovisimap_<oid> | very young
(8 rows)

select segid = -1 as is_master, relname, classify_age(age) from aux_rel_ages('test_table_co')
group by segid = -1, relname, classify_age(age);
 is_master |      relname       | classify_age 
-----------+--------------------+--------------
 t         | pg_aocsseg_<oid>   | very young
 f         | pg_aoblkdir_<oid>  | very young
 t         | pg_aovisimap_<oid> | very young
 f         | pg_aocsseg_<oid>   | very young
 t         | pg_aoblkdir_<oid>  | very young
 f         | pg_aovisimap_<oid> | very young
(6 rows)