src/test/isolation2/expected/frozen_insert_crash.out

-- Test server crash in case of frozen insert. Make sure that after crash
-- recovery, the frozen insert and index are consistent:
--
-- 1. If crash happened before the row is frozen, the row will be invisible;
-- 2. If crash happened after the row is frozen, the row will be visible.
--
-- And the above behavior should remain consistent using seqscan or indexscan.
--
-- We test gp_fastsequence and bitmap here since they do frozen insert and
-- normal index insert, so that the inconsistency could exist.

-- Case 1. crash after the regular MVCC insert has made to disk, but not
-- the WAL record responsible for updating it to frozen.
-- After crash recovery, the insert will follow regular MVCC and not be seen.
1: create table tab_fi(a int) with (appendoptimized=true) distributed replicated;
CREATE

-- switch WAL on seg0 to reduce flakiness
1: select gp_segment_id, pg_switch_wal() is not null from gp_dist_random('gp_id') where gp_segment_id = 0;
 gp_segment_id | ?column? 
---------------+----------
 0             | t        
(1 row)

-- suspend right after the insert into gp_fastsequence during an AO table insert,
-- but before freezing the tuple
1: select gp_inject_fault('insert_fastsequence_before_freeze', 'suspend', ''/*DDL*/, ''/*DB*/, 'gp_fastsequence'/*table*/, 1/*start occur*/, 1/*end occur*/, 0/*extra_arg*/, dbid) from gp_segment_configuration where role = 'p' and content = 0;
 gp_inject_fault 
-----------------
 Success:        
(1 row)

2>: insert into tab_fi values(1);  <waiting ...>

1: select gp_wait_until_triggered_fault('insert_fastsequence_before_freeze', 1, dbid) from gp_segment_configuration where role = 'p' and content = 0;
 gp_wait_until_triggered_fault 
-------------------------------
 Success:                      
(1 row)

-- switch WAL on seg0, so the new row gets flushed (including its index)
1: select gp_segment_id, pg_switch_wal() is not null from gp_dist_random('gp_id') where gp_segment_id = 0;
 gp_segment_id | ?column? 
---------------+----------
 0             | t        
(1 row)

-- inject a panic, and resume the insert. The WAL for the freeze operation is not
-- going to be made to disk (we just flushed WALs), so we won't replay it during restart later.
-- skip FTS probe to prevent unexpected mirror promotion
1: select gp_inject_fault_infinite('fts_probe', 'skip', dbid) from gp_segment_configuration where role='p' and content=-1;
 gp_inject_fault_infinite 
--------------------------
 Success:                 
(1 row)
1: select gp_inject_fault('appendonly_insert', 'panic', ''/*DDL*/, ''/*DB*/, 'tab_fi'/*table*/, 1/*start occur*/, -1/*end occur*/, 0/*extra_arg*/, 2/*db_id*/);
 gp_inject_fault 
-----------------
 Success:        
(1 row)
1: select gp_inject_fault('insert_fastsequence_before_freeze', 'reset', dbid) from gp_segment_configuration where role = 'p' and content = 0;
 gp_inject_fault 
-----------------
 Success:        
(1 row)
1: select gp_inject_fault('fts_probe', 'reset', dbid) from gp_segment_configuration where role='p' and content=-1;
 gp_inject_fault 
-----------------
 Success:        
(1 row)

2<:  <... completed>
ERROR:  fault triggered, fault name:'appendonly_insert' fault type:'panic'  (seg0 127.0.1.1:7002 pid=14710)

1q: ... <quitting>

-- check the gp_fastsequence content w/ table vs index scan, neither should see the
-- new inserted row (objmod=1) following MVCC
1: set enable_indexscan = off;
SET
1: set enable_seqscan = on;
SET
1: select distinct f.gp_segment_id, f.objmod, f.last_sequence from gp_dist_random('gp_fastsequence') f left join gp_dist_random('pg_appendonly') a on segrelid = objid and a.gp_segment_id = f.gp_segment_id where a.gp_segment_id = 0 and relid = (select oid from pg_class where relname = 'tab_fi');
 gp_segment_id | objmod | last_sequence 
---------------+--------+---------------
 0             | 0      | 0             
(1 row)
1: set enable_indexscan = on;
SET
1: set enable_seqscan = off;
SET
1: select distinct f.gp_segment_id, f.objmod, f.last_sequence from gp_dist_random('gp_fastsequence') f left join gp_dist_random('pg_appendonly') a on segrelid = objid and a.gp_segment_id = f.gp_segment_id where a.gp_segment_id = 0 and relid = (select oid from pg_class where relname = 'tab_fi');
 gp_segment_id | objmod | last_sequence 
---------------+--------+---------------
 0             | 0      | 0             
(1 row)
1: reset enable_indexscan;
RESET
1: reset enable_seqscan;
RESET

1: drop table tab_fi;
DROP

-- Case 2. crash after we have flushed the WAL that updates the row to be frozen.
-- After crash recovery, the insert should be seen.
1: create table tab_fi(a int) with (appendoptimized=true) distributed replicated;
CREATE

-- switch WAL on seg0 to reduce flakiness
1: select gp_segment_id, pg_switch_wal() is not null from gp_dist_random('gp_id') where gp_segment_id = 0;
 gp_segment_id | ?column? 
---------------+----------
 0             | t        
(1 row)

-- suspend right after freezing the tuple
1: select gp_inject_fault('insert_fastsequence_after_freeze', 'suspend', ''/*DDL*/, ''/*DB*/, 'gp_fastsequence'/*table*/, 1/*start occur*/, 1/*end occur*/, 0/*extra_arg*/, dbid) from gp_segment_configuration where role = 'p' and content = 0;
 gp_inject_fault 
-----------------
 Success:        
(1 row)

2>: insert into tab_fi values(1);  <waiting ...>

1: select gp_wait_until_triggered_fault('insert_fastsequence_after_freeze', 1, dbid) from gp_segment_configuration where role = 'p' and content = 0;
 gp_wait_until_triggered_fault 
-------------------------------
 Success:                      
(1 row)

-- switch WAL on seg0, so the freeze record gets flushed
1: select gp_segment_id, pg_switch_wal() is not null from gp_dist_random('gp_id') where gp_segment_id = 0;
 gp_segment_id | ?column? 
---------------+----------
 0             | t        
(1 row)

-- While we are on it, check the wal record for the freeze operation.
-- One of the purposes is to guard against unexpected addition to the xl_heap_freeze_tuple struct in future.
-- If this test failed due to WAL size, please check to see if the xl_heap_freeze_tuple struct
-- has changed, and if we should initialize any new field in heap_freeze_tuple_no_cutoff().
! seg0_datadir=$(psql -At -c "select datadir from gp_segment_configuration where content = 0 and role = 'p'" postgres) && seg0_last_wal_file=$(psql -At -c "SELECT pg_walfile_name(pg_current_wal_lsn()) from gp_dist_random('gp_id') where gp_segment_id = 0" postgres) && pg_waldump ${seg0_last_wal_file} -p ${seg0_datadir}/pg_wal | grep FREEZE_PAGE;
rmgr: Heap2       len (rec/tot):     67/    67, tx:       3751, lsn: 0/C00000A0, prev 0/C0000028, desc: FREEZE_PAGE snapshotConflictHorizon: 0, nplans: 1, plans: [{ xmax: 0, infomask: 2816, infomask2: 8, ntuples: 1, offsets: [1] }], blkref #0: rel 1663/197077/212994 blk 0
rmgr: Heap2       len (rec/tot):     67/    67, tx:       3751, lsn: 0/C0000210, prev 0/C00001C0, desc: FREEZE_PAGE snapshotConflictHorizon: 0, nplans: 1, plans: [{ xmax: 0, infomask: 2816, infomask2: 3, ntuples: 1, offsets: [28] }], blkref #0: rel 1663/197077/7023 blk 0


-- inject a panic and resume in same way as Case 1. But this time we will be able to replay the frozen insert.
-- skip FTS probe to prevent unexpected mirror promotion
1: select gp_inject_fault_infinite('fts_probe', 'skip', dbid) from gp_segment_configuration where role='p' and content=-1;
 gp_inject_fault_infinite 
--------------------------
 Success:                 
(1 row)
1: select gp_inject_fault('appendonly_insert', 'panic', ''/*DDL*/, ''/*DB*/, 'tab_fi'/*table*/, 1/*start occur*/, -1/*end occur*/, 0/*extra_arg*/, 2/*db_id*/);
 gp_inject_fault 
-----------------
 Success:        
(1 row)
1: select gp_inject_fault('insert_fastsequence_after_freeze', 'reset', dbid) from gp_segment_configuration where role = 'p' and content = 0;
 gp_inject_fault 
-----------------
 Success:        
(1 row)
1: select gp_inject_fault('fts_probe', 'reset', dbid) from gp_segment_configuration where role='p' and content=-1;
 gp_inject_fault 
-----------------
 Success:        
(1 row)

2<:  <... completed>
ERROR:  fault triggered, fault name:'appendonly_insert' fault type:'panic'  (seg0 127.0.1.1:7002 pid=14775)

1q: ... <quitting>

-- check the gp_fastsequence content w/ table vs index scan, both should see the new inserted row (objmod=1)
1: set enable_indexscan = off;
SET
1: set enable_seqscan = on;
SET
1: select distinct f.gp_segment_id, f.objmod, f.last_sequence from gp_dist_random('gp_fastsequence') f left join gp_dist_random('pg_appendonly') a on segrelid = objid and a.gp_segment_id = f.gp_segment_id where a.gp_segment_id = 0 and relid = (select oid from pg_class where relname = 'tab_fi');
 gp_segment_id | objmod | last_sequence 
---------------+--------+---------------
 0             | 0      | 0             
 0             | 1      | 100           
(2 rows)
1: set enable_indexscan = on;
SET
1: set enable_seqscan = off;
SET
1: select distinct f.gp_segment_id, f.objmod, f.last_sequence from gp_dist_random('gp_fastsequence') f left join gp_dist_random('pg_appendonly') a on segrelid = objid and a.gp_segment_id = f.gp_segment_id where a.gp_segment_id = 0 and relid = (select oid from pg_class where relname = 'tab_fi');
 gp_segment_id | objmod | last_sequence 
---------------+--------+---------------
 0             | 0      | 0             
 0             | 1      | 100           
(2 rows)
1: reset enable_indexscan;
RESET
1: reset enable_seqscan;
RESET

1: drop table tab_fi;
DROP


-- Test for aoseg: suspend the insert into aoseg table before we mark the row frozen.
-- Another session should still be able to choose a different segno.
1: create table tab_aoseg(a int) using ao_row;
CREATE
1: select gp_inject_fault('insert_aoseg_before_freeze', 'suspend', dbid) from gp_segment_configuration where role = 'p' and content = 0;
 gp_inject_fault 
-----------------
 Success:        
(1 row)
1: begin;
BEGIN
1>: insert into tab_aoseg select * from generate_series(1,10);  <waiting ...>
-- wait until the aoseg record is inserted but not yet frozen
2: select gp_wait_until_triggered_fault('insert_aoseg_before_freeze', 1, dbid) from gp_segment_configuration where role = 'p' and content = 0;
 gp_wait_until_triggered_fault 
-------------------------------
 Success:                      
(1 row)
2: begin;
BEGIN
2>: insert into tab_aoseg select * from generate_series(1,10);  <waiting ...>
3: select gp_inject_fault('insert_aoseg_before_freeze', 'reset', dbid) from gp_segment_configuration where role = 'p' and content = 0;
 gp_inject_fault 
-----------------
 Success:        
(1 row)
1<:  <... completed>
INSERT 10
2<:  <... completed>
INSERT 10
1: end;
END
2: end;
END
3: select segment_id, segno, eof from gp_toolkit.__gp_aoseg('tab_aoseg') where segment_id = 0;
 segment_id | segno | eof 
------------+-------+-----
 0          | 1     | 88  
 0          | 2     | 88  
(2 rows)

-- Same set of tests for bitmap LOV insert.

-- Function to check the bitmap lov content regarding the column 'b'
-- which is the table column that we will have bitmap created on.
-- Basically, we want to see if "SELECT b FROM pg_bitmapindex.pg_bm_xxx"
-- returns the same result in seqscan and indexscan.
CREATE OR REPLACE FUNCTION insert_bm_lov_res() RETURNS void AS $$ DECLARE lov_table text; /* in func */ sql text; /* in func */ BEGIN /* in func */ drop table if exists bm_lov_res; /* in func */ create temp table bm_lov_res(b int); /* in func */ SELECT c.relname INTO lov_table /* in func */ FROM bm_metap('tab_fi_idx') b /* in func */ JOIN pg_class c ON b.auxrelid = c.oid; /* in func */ sql := format('INSERT INTO bm_lov_res SELECT b FROM pg_bitmapindex.%I', lov_table); /* in func */ EXECUTE sql; /* in func */ END; /* in func */ $$ LANGUAGE plpgsql;
CREATE

1: create table tab_fi(a int, b int) with (appendoptimized=true) distributed replicated;
CREATE
1: create index tab_fi_idx on tab_fi using bitmap(b);
CREATE
1: insert into tab_fi values(1, 1);
INSERT 1
-- switch WAL on seg0 to reduce flakiness
1: select gp_segment_id, pg_switch_wal() is not null from gp_dist_random('gp_id') where gp_segment_id = 0;
 gp_segment_id | ?column? 
---------------+----------
 0             | t        
(1 row)

-- case 1: suspend and flush WAL before freezing the tuple

-- suspend right after the insert into the bitmap lov table and its index
-- during a table insert, but before freezing the tuple
1: select gp_inject_fault('insert_bmlov_before_freeze', 'suspend', dbid) from gp_segment_configuration where role = 'p' and content = 0;
 gp_inject_fault 
-----------------
 Success:        
(1 row)
2>: insert into tab_fi values(2, 2);  <waiting ...>
1: select gp_wait_until_triggered_fault('insert_bmlov_before_freeze', 1, dbid) from gp_segment_configuration where role = 'p' and content = 0;
 gp_wait_until_triggered_fault 
-------------------------------
 Success:                      
(1 row)
-- switch WAL on seg0, so the new row gets flushed (including its index)
1: select gp_segment_id, pg_switch_wal() is not null from gp_dist_random('gp_id') where gp_segment_id = 0;
 gp_segment_id | ?column? 
---------------+----------
 0             | t        
(1 row)
-- inject a panic, and resume the insert. The WAL for the freeze operation is not
-- going to be made to disk (we just flushed WALs), so we won't replay it during restart later.
-- skip FTS probe to prevent unexpected mirror promotion
1: select gp_inject_fault_infinite('fts_probe', 'skip', dbid) from gp_segment_configuration where role='p' and content=-1;
 gp_inject_fault_infinite 
--------------------------
 Success:                 
(1 row)
1: select gp_inject_fault('qe_exec_finished', 'panic', dbid) from gp_segment_configuration where role = 'p' and content = 0;
 gp_inject_fault 
-----------------
 Success:        
(1 row)
1: select gp_inject_fault('insert_bmlov_before_freeze', 'reset', dbid) from gp_segment_configuration where role = 'p' and content = 0;
 gp_inject_fault 
-----------------
 Success:        
(1 row)
1: select gp_inject_fault('fts_probe', 'reset', dbid) from gp_segment_configuration where role='p' and content=-1;
 gp_inject_fault 
-----------------
 Success:        
(1 row)
2<:  <... completed>
ERROR:  fault triggered, fault name:'qe_exec_finished' fault type:'panic'
1q: ... <quitting>
-- wait for segment to complete recovering
!\retcode bash -c 'for i in $(seq 1 120); do pg_isready -q -p 7002 && exit 0; sleep 0.5; done; exit 1';
(exited with code 0)
-- check the lov table content w/ table vs index scan, neither should see the
-- new inserted row (b=2)
0U: set enable_indexscan = on;
SET
0U: set enable_seqscan = off;
SET
0U: select insert_bm_lov_res();
 insert_bm_lov_res 
-------------------
                   
(1 row)
0U: select * from bm_lov_res;
 b 
---
 1 
(1 row)
0U: set enable_indexscan = off;
SET
0U: set enable_seqscan = on;
SET
0U: select insert_bm_lov_res();
 insert_bm_lov_res 
-------------------
                   
(1 row)
0U: select * from bm_lov_res;
 b 
---
 1 
(1 row)
0Uq: ... <quitting>
1: drop table tab_fi;
DROP

-- case 2: suspend and flush WAL after freezing the tuple

1: create table tab_fi(a int, b int) with (appendoptimized=true) distributed replicated;
CREATE
1: create index tab_fi_idx on tab_fi using bitmap(b);
CREATE
1: insert into tab_fi values(1, 1);
INSERT 1
-- switch WAL on seg0 to reduce flakiness
1: select gp_segment_id, pg_switch_wal() is not null from gp_dist_random('gp_id') where gp_segment_id = 0;
 gp_segment_id | ?column? 
---------------+----------
 0             | t        
(1 row)
-- suspend right after freezing the tuple
1: select gp_inject_fault('insert_bmlov_after_freeze', 'suspend', dbid) from gp_segment_configuration where role = 'p' and content = 0;
 gp_inject_fault 
-----------------
 Success:        
(1 row)
2>: insert into tab_fi values(2, 2);  <waiting ...>
1: select gp_wait_until_triggered_fault('insert_bmlov_after_freeze', 1, dbid) from gp_segment_configuration where role = 'p' and content = 0;
 gp_wait_until_triggered_fault 
-------------------------------
 Success:                      
(1 row)
-- switch WAL on seg0, so the freeze record gets flushed
1: select gp_segment_id, pg_switch_wal() is not null from gp_dist_random('gp_id') where gp_segment_id = 0;
 gp_segment_id | ?column? 
---------------+----------
 0             | t        
(1 row)
-- While we are on it, check the wal record for the freeze operation.
! seg0_datadir=$(psql -At -c "select datadir from gp_segment_configuration where content = 0 and role = 'p'" postgres) && seg0_last_wal_file=$(psql -At -c "SELECT pg_walfile_name(pg_current_wal_lsn()) from gp_dist_random('gp_id') where gp_segment_id = 0" postgres) && pg_waldump ${seg0_last_wal_file} -p ${seg0_datadir}/pg_wal | grep FREEZE_PAGE;
rmgr: Heap2       len (rec/tot):     67/    67, tx:        950, lsn: 0/280001E0, prev 0/28000198, desc: FREEZE_PAGE snapshotConflictHorizon: 0, nplans: 1, plans: [{ xmax: 0, infomask: 2816, infomask2: 3, ntuples: 1, offsets: [2] }], blkref #0: rel 1663/17018/98313 blk 0

-- inject a panic and resume in same way as Case 1. But this time we will be able to replay the frozen insert.
-- skip FTS probe to prevent unexpected mirror promotion
1: select gp_inject_fault_infinite('fts_probe', 'skip', dbid) from gp_segment_configuration where role='p' and content=-1;
 gp_inject_fault_infinite 
--------------------------
 Success:                 
(1 row)
1: select gp_inject_fault('qe_exec_finished', 'panic', dbid) from gp_segment_configuration where role = 'p' and content = 0;
 gp_inject_fault 
-----------------
 Success:        
(1 row)
1: select gp_inject_fault('insert_bmlov_after_freeze', 'reset', dbid) from gp_segment_configuration where role = 'p' and content = 0;
 gp_inject_fault 
-----------------
 Success:        
(1 row)
1: select gp_inject_fault('fts_probe', 'reset', dbid) from gp_segment_configuration where role='p' and content=-1;
 gp_inject_fault 
-----------------
 Success:        
(1 row)
2<:  <... completed>
ERROR:  fault triggered, fault name:'qe_exec_finished' fault type:'panic'
1q: ... <quitting>
-- wait for segment to complete recovering
!\retcode bash -c 'for i in $(seq 1 120); do pg_isready -q -p 7002 && exit 0; sleep 0.5; done; exit 1';
(exited with code 0)
-- check the lov table content w/ table vs index scan, both should see the
-- new inserted row (b=2)
0U: set enable_indexscan = on;
SET
0U: set enable_seqscan = off;
SET
0U: select insert_bm_lov_res();
 insert_bm_lov_res 
-------------------
                   
(1 row)
0U: select * from bm_lov_res;
 b 
---
 1 
 2 
(2 rows)
0U: set enable_indexscan = off;
SET
0U: set enable_seqscan = on;
SET
0U: select insert_bm_lov_res();
 insert_bm_lov_res 
-------------------
                   
(1 row)
0U: select * from bm_lov_res;
 b 
---
 1 
 2 
(2 rows)

-- validate that we've actually tested desired scan method
-- for some reason this disrupts the output of subsequent queries so
-- validating at the end here
! psql postgres -At -c "set enable_indexscan = off; set enable_seqscan = on; explain (costs off) select distinct f.gp_segment_id, f.objmod, f.last_sequence from gp_dist_random('gp_fastsequence') f left join gp_dist_random('pg_appendonly') a on segrelid = objid and a.gp_segment_id = f.gp_segment_id where a.gp_segment_id = 0 and relid = (select oid from pg_class where relname = 'tab_fi');" | grep "Seq Scan on gp_fastsequence";
                                ->  Seq Scan on gp_fastsequence f

! psql postgres -At -c "set enable_indexscan = on; set enable_seqscan = off; explain (costs off) select distinct f.gp_segment_id, f.objmod, f.last_sequence from gp_dist_random('gp_fastsequence') f left join gp_dist_random('pg_appendonly') a on segrelid = objid and a.gp_segment_id = f.gp_segment_id where a.gp_segment_id = 0 and relid = (select oid from pg_class where relname = 'tab_fi');" | grep "Index Scan using gp_fastsequence";
                                ->  Index Scan using gp_fastsequence_objid_objmod_index on gp_fastsequence f