src/test/isolation2/output/distributed_snapshot.source - cloudberry - Git at Google

 -- Distributed snapshot tests

 create or replace function test_consume_xids(int4) returns void as '@abs_srcdir@/../regress/regress.so', 'test_consume_xids' language C;
 CREATE

 -- Scenario1: Test to validate GetSnapshotData()'s computation of globalXmin using
 -- distributed snapshot. It mainly uses a old read-only transaction to help
 -- create situation where globalXmin can be lower than distributed oldestXmin
 -- when calling DistributedLog_AdvanceOldestXmin().

 -- Setup
 CREATE TABLE distributed_snapshot_test1 (a int);
 CREATE

 1: BEGIN;
 BEGIN
 1: INSERT INTO distributed_snapshot_test1 values(1);
 INSERT 1
 -- Read transaction which helps to get lower globalXmin for session 3. As this
 -- will have MyProc->xmin set to transaction 1's xid.
 2: BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 BEGIN
 2: SELECT * from distributed_snapshot_test1;
  a
 ---
 (0 rows)
 -- Complete transaction 1, so that it no more appears in in-progress transaction
 -- list for following transactions.
 1: COMMIT;
 COMMIT

 -- Transaction to bump the latestCompletedXid
 1: INSERT INTO distributed_snapshot_test1 values(1);
 INSERT 1

 -- Hold after walking over ProcArray in GetSnpashotData(), right at start of
 -- DistributedLog_AdvanceOldestXmin()
 1: SELECT gp_inject_fault('distributedlog_advance_oldest_xmin', 'suspend', '', 'postgres', '', 1, -1, 5, dbid) from gp_segment_configuration where content = 0 and role = 'p';
  gp_inject_fault
 -----------------
  Success:
 (1 row)
 3&:@db_name postgres: SELECT count(*) > 0 from gp_dist_random('gp_id');  <waiting ...>
 1: SELECT gp_wait_until_triggered_fault('distributedlog_advance_oldest_xmin', 1, dbid) from gp_segment_configuration where content = 0 and role = 'p';
  gp_wait_until_triggered_fault
 -------------------------------
  Success:
 (1 row)
 2: COMMIT;
 COMMIT

 -- Transaction used to bump the distributed oldestXmin
 1: INSERT INTO distributed_snapshot_test1 values(1);
 INSERT 1
 -- let session 3 now move forward to compute distributed oldest xmin
 1: SELECT gp_inject_fault('distributedlog_advance_oldest_xmin', 'reset', dbid) from gp_segment_configuration where content = 0 and role = 'p';
  gp_inject_fault
 -----------------
  Success:
 (1 row)
 3<:  <... completed>
  ?column?
 ----------
  t
 (1 row)

 -- Scenario2: This scenario tests the boundary condition for Xmax in distributed snapshot

 -- Setup
 CREATE TABLE distributed_snapshot_test2 (a int);
 CREATE

 -- start transaction assigns distributed xid.
 1: BEGIN ISOLATION LEVEL REPEATABLE READ;
 BEGIN
 -- this sets latestCompletedXid
 2: INSERT INTO distributed_snapshot_test2 VALUES(1);
 INSERT 1
 -- here, take distributed snapshot
 1: SELECT 123 AS "establish snapshot";
  establish snapshot
 --------------------
  123
 (1 row)
 2: INSERT INTO distributed_snapshot_test2 VALUES(2);
 INSERT 1
 -- expected to see just VALUES(1)
 1: SELECT * FROM distributed_snapshot_test2;
  a
 ---
  1
 (1 row)
 1: COMMIT;
 COMMIT

 DROP TABLE distributed_snapshot_test2;
 DROP

 -- Scenario3: Test the one-phase commit transactions don't break repeatable read isolation.
 --
 -- Direct dispatch causes the select statements to be dispatched only to one of
 -- the three demo cluster segments. A segment acquires local snapshot only when it
 -- receives the dispatched statement. If one phase commit relied on local
 -- snapshots only, wrong results are possible depending on the order of local
 -- snapshot acquisition by the segments. This scenario validates that distributed
 -- snapshot is used by the segments to evaluate tuple visibility in case of
 -- one-phase commit and correct results are returned.
 --
 -- connection 40 inserts 100, 100 and 300 serially using one-phase commit
 -- protocol. Repeatable read transactions may read (100), (100,100) or
 -- (100,100,300), but not (100, 300).
 CREATE TABLE distributed_snapshot_test3 (a int);
 CREATE
 10: BEGIN ISOLATION LEVEL REPEATABLE READ;
 BEGIN
 20: BEGIN ISOLATION LEVEL REPEATABLE READ;
 BEGIN
 30: BEGIN ISOLATION LEVEL REPEATABLE READ;
 BEGIN
 40: INSERT INTO distributed_snapshot_test3 VALUES(100);
 INSERT 1
 10: SELECT gp_segment_id, * FROM distributed_snapshot_test3 where a = 100;
  gp_segment_id | a
 ---------------+-----
  2             | 100
 (1 row)
 40: INSERT INTO distributed_snapshot_test3 VALUES(100);
 INSERT 1
 30: SELECT 123 AS "establish snapshot";
  establish snapshot
 --------------------
  123
 (1 row)
 40: INSERT INTO distributed_snapshot_test3 VALUES(300);
 INSERT 1
 10: SELECT gp_segment_id, * FROM distributed_snapshot_test3;
  gp_segment_id | a
 ---------------+-----
  2             | 100
 (1 row)
 20: SELECT gp_segment_id, * FROM distributed_snapshot_test3;
  gp_segment_id | a
 ---------------+-----
  1             | 300
  2             | 100
  2             | 100
 (3 rows)
 30: SELECT gp_segment_id, * FROM distributed_snapshot_test3;
  gp_segment_id | a
 ---------------+-----
  2             | 100
  2             | 100
 (2 rows)
 10: COMMIT;
 COMMIT
 20: COMMIT;
 COMMIT
 30: COMMIT;
 COMMIT
 DROP TABLE distributed_snapshot_test3;
 DROP

 -- The following test cases are to test that QEs can get
 -- latest distribute snapshot to scan normal tables (not catalog).
 -- Cloudberry tests the visibility of heap tuples firstly using
 -- distributed snapshot. Distributed snapshot is generated on
 -- QD and then dispatched to QEs. Some utility statement needs
 -- to work under latest snapshot when executing, so that they
 -- invoke the function `GetLatestSnapshot` in QEs. But remember
 -- we cannot get the latest distributed snapshot.

 -- Subtle cases are: Alter Table or Alter Domain statements on QD
 -- get snapshot in Portal Run and then try to hold locks on the
 -- target table in ProcessUtilitySlow. Here is the key point:
 --   1. try to hold lock ==> it might be blocked by other transcations
 --   2. then it will be waked up to continue
 --   3. when it can continue, the world has changed because other transcations
 --      then blocks it have been over

 -- Previously, on QD we do not getsnapshot before we dispatch utility
 -- statement to QEs which leads to the distributed snapshot does not
 -- reflect the "world change". This will lead to some bugs. For example,
 -- if the first transaction is to rewrite the whole heap, and then
 -- the second Alter Table or Alter Domain statements continues with
 -- the distributed snapshot that txn1 does not commit yet, it will
 -- see no tuples in the new heap!
 -- See Github issue https://github.com/greenplum-db/gpdb/issues/10216

 -- Now this has been fixed, the following cases are tests to check this.

 -- Case 1: concurrently alter column type (will do rewrite heap)
 create table t_alter_snapshot_test(a int, b int, c int);
 CREATE
 insert into t_alter_snapshot_test values (1, 1, 1), (1, 1, 1);
 INSERT 2

 select * from t_alter_snapshot_test;
  a | b | c
 ---+---+---
  1 | 1 | 1
  1 | 1 | 1
 (2 rows)

 1: begin;
 BEGIN
 1: alter table t_alter_snapshot_test alter column b type text;
 ALTER

 -- the following statement will hang
 2&: alter table t_alter_snapshot_test alter column c type text;  <waiting ...>

 1: end;
 END
 -- after 1 commit, 2 can continue, it should use latest distributed
 -- snapshot so that the data will not be lost.
 2<:  <... completed>
 ALTER

 select * from t_alter_snapshot_test;
  a | b | c
 ---+---+---
  1 | 1 | 1
  1 | 1 | 1
 (2 rows)
 drop table t_alter_snapshot_test;
 DROP

 -- Case 2: concurrently add exclude constrain
 create table t_alter_snapshot_test(a int, b int);
 CREATE
 insert into t_alter_snapshot_test values (1, 1), (1, 1);
 INSERT 2

 select a from t_alter_snapshot_test;
  a
 ---
  1
  1
 (2 rows)

 1: begin;
 BEGIN
 1: alter table t_alter_snapshot_test alter column b type int using b::int;
 ALTER

 2&: alter table t_alter_snapshot_test add exclude using btree (a WITH =);  <waiting ...>

 1: end;
 END
 -- after 1 commit, 2 can go on and it should fail
 2<:  <... completed>
 ERROR:  could not create exclusion constraint "t_alter_snapshot_test_a_excl"  (seg1 127.0.1.1:7003 pid=39163)
 DETAIL:  Key (a)=(1) conflicts with key (a)=(1).

 drop table t_alter_snapshot_test;
 DROP

 -- Case 3: concurrently split partition
 create table t_alter_snapshot_test(id int, rank int, year int) distributed by (id) partition by range (year) ( start (0) end (20) every (4), default partition extra );
 CREATE

 insert into t_alter_snapshot_test select i,i,i from generate_series(1, 100)i;
 INSERT 100
 select count(*) from t_alter_snapshot_test;
  count
 -------
  100
 (1 row)

 1: begin;
 BEGIN
 1: alter table t_alter_snapshot_test alter column rank type text;
 ALTER

 2&: alter table t_alter_snapshot_test split partition for (5) at (5)  into (partition pa,  partition pb);  <waiting ...>

 1: end;
 END
 -- after 1 commit, 2 can go on and it should not lose data
 2<:  <... completed>
 ALTER

 select count(*) from t_alter_snapshot_test;
  count
 -------
  100
 (1 row)
 drop table t_alter_snapshot_test;
 DROP

 -- case 4: concurrently validate check
 create table t_alter_snapshot_test(a int, b int);
 CREATE
 insert into t_alter_snapshot_test values (1, 1), (2, 2);
 INSERT 2
 alter table t_alter_snapshot_test ADD CONSTRAINT mychk CHECK(a > 20) NOT VALID;
 ALTER

 1: begin;
 BEGIN
 1: alter table t_alter_snapshot_test alter column b type text;
 ALTER

 2&: alter table t_alter_snapshot_test validate CONSTRAINT mychk;  <waiting ...>

 1: end;
 END
 -- after 1 commit, 2 can go on and it should fail
 2<:  <... completed>
 ERROR:  check constraint "mychk" of relation "t_alter_snapshot_test" is violated by some row  (seg1 127.0.1.1:8003 pid=423039)

 drop table t_alter_snapshot_test;
 DROP

 -- case 5: concurrently domain check
 create domain domain_snapshot_test as int;
 CREATE
 create table t_alter_snapshot_test(i domain_snapshot_test, j int, k int);
 CREATE
 insert into t_alter_snapshot_test values(200,1,1);
 INSERT 1
 alter domain domain_snapshot_test ADD CONSTRAINT mychk CHECK(VALUE > 300)  NOT VALID;
 ALTER

 1: begin;
 BEGIN
 1: alter table t_alter_snapshot_test alter column k type text;
 ALTER

 2&: alter domain domain_snapshot_test validate CONSTRAINT mychk;  <waiting ...>

 1:end;
 END
 -- after 1 commit, 2 can go on and it should fail
 2<:  <... completed>
 ERROR:  column "i" of table "t_alter_snapshot_test" contains values that violate the new constraint  (seg2 127.0.1.1:7004 pid=39164)

 drop table t_alter_snapshot_test;
 DROP
 drop domain domain_snapshot_test;
 DROP

 -- case 6: alter table expand table
 create table t_alter_snapshot_test(a int, b int);
 CREATE
 set allow_system_table_mods = on;
 SET
 update gp_distribution_policy set numsegments = 2 where localoid = 't_alter_snapshot_test'::regclass::oid;
 UPDATE 1
 insert into t_alter_snapshot_test select i,i from generate_series(1, 10)i;
 INSERT 10
 select gp_segment_id, * from t_alter_snapshot_test;
  gp_segment_id | a  | b
 ---------------+----+----
  0             | 2  | 2
  0             | 3  | 3
  0             | 4  | 4
  0             | 6  | 6
  0             | 7  | 7
  0             | 8  | 8
  0             | 9  | 9
  0             | 10 | 10
  1             | 1  | 1
  1             | 5  | 5
 (10 rows)

 1: begin;
 BEGIN
 1: alter table t_alter_snapshot_test alter column b type text;
 ALTER

 2&: alter table t_alter_snapshot_test expand table;  <waiting ...>

 1: end;
 END
 -- after 1 commit, 2 can go on and data should not be lost
 2<:  <... completed>
 ALTER

 select gp_segment_id, * from t_alter_snapshot_test;
  gp_segment_id | a  | b
 ---------------+----+----
  0             | 2  | 2
  0             | 3  | 3
  0             | 4  | 4
  0             | 7  | 7
  0             | 8  | 8
  1             | 1  | 1
  2             | 6  | 6
  2             | 9  | 9
  2             | 10 | 10
  2             | 5  | 5
 (10 rows)
 drop table t_alter_snapshot_test;
 DROP

 -- case 7: alter table set distributed by
 create table t_alter_snapshot_test(a int, b int) distributed randomly;
 CREATE
 insert into t_alter_snapshot_test select i,i from generate_series(1, 10)i;
 INSERT 10
 select count(*) from t_alter_snapshot_test;
  count
 -------
  10
 (1 row)

 1: begin;
 BEGIN
 1: alter table t_alter_snapshot_test alter column b type text;
 ALTER

 2&: alter table t_alter_snapshot_test set distributed by (a);  <waiting ...>

 1: end;
 END
 -- after 1 commit, 2 can continue and data should not be lost
 2<:  <... completed>
 ALTER

 select count(*) from t_alter_snapshot_test;
  count
 -------
  10
 (1 row)
 drop table t_alter_snapshot_test;
 DROP

 -- case 8: DML concurrent with Alter Table
 create table t_alter_snapshot_test(a int, b int);
 CREATE

 ---- test for insert
 1: begin;
 BEGIN
 1: insert into t_alter_snapshot_test values (1, 1);
 INSERT 1
 2&: alter table t_alter_snapshot_test alter column b type text;  <waiting ...>
 1: end;
 END
 -- 2 can continue, and we should not lose data
 2<:  <... completed>
 ALTER
 select * from t_alter_snapshot_test;
  a | b
 ---+---
  1 | 1
 (1 row)

 ---- test for update
 truncate t_alter_snapshot_test;
 TRUNCATE
 insert into t_alter_snapshot_test values (1, 1);
 INSERT 1
 1: begin;
 BEGIN
 1: update t_alter_snapshot_test set b = '3';
 UPDATE 1
 2&: alter table t_alter_snapshot_test alter column b type int using b::int;  <waiting ...>
 1: end;
 END
 -- 2 can continue and we should see the data has been updated
 2<:  <... completed>
 ALTER
 select * from t_alter_snapshot_test;
  a | b
 ---+---
  1 | 3
 (1 row)

 ---- test for delete
 truncate t_alter_snapshot_test;
 TRUNCATE
 insert into t_alter_snapshot_test values (1, 1);
 INSERT 1
 1: begin;
 BEGIN
 1: delete from t_alter_snapshot_test;
 DELETE 1
 2&: alter table t_alter_snapshot_test alter column b type text;  <waiting ...>
 1: end;
 END
 -- 2 can continue and we should see the data has been deleted
 2<:  <... completed>
 ALTER
 select * from t_alter_snapshot_test;
  a | b
 ---+---
 (0 rows)
 drop table t_alter_snapshot_test;
 DROP

 -- Case 9: Repeatable Read Isolation Level Test
 create table t_alter_snapshot_test(a int, b int);
 CREATE
 insert into t_alter_snapshot_test values (1, 1);
 INSERT 1
 1: begin;
 BEGIN
 1: insert into t_alter_snapshot_test values (1, 1);
 INSERT 1

 2: begin isolation level repeatable read;
 BEGIN
 2: select * from  t_alter_snapshot_test;
  a | b
 ---+---
  1 | 1
 (1 row)
 2&: alter table t_alter_snapshot_test alter column b type text;  <waiting ...>

 1: end;
 END
 -- 2 can continue and after its alter rewrite the heap
 -- it can see all the data even under repeatable read
 2<:  <... completed>
 ALTER
 2: select * from t_alter_snapshot_test;
  a | b
 ---+---
  1 | 1
  1 | 1
 (2 rows)
 2: end;
 END

 select * from t_alter_snapshot_test;
  a | b
 ---+---
  1 | 1
  1 | 1
 (2 rows)
 drop table t_alter_snapshot_test;
 DROP

 ----------------------------------------
 -- Test for fixes
 ----------------------------------------
 -- Case 1. Test that when we advanced DLOG's oldestXmin to the
 -- latestCompletedXid + 1, and that it is the first xid of the
 -- next segment, we would truncate all DLOG segments (all txs
 -- have completed and no longer needed). And in that case, we
 -- should still be able to advance properly after restart.
 create table distributed_snapshot_fix1(a int);
 CREATE

 -- On a primary, burn xids until the next xid is the first one of a segment,
 -- which has 4096 (ENTRIES_PER_PAGE) * 32 (SLRU_PAGES_PER_SEGMENT) = 131072 xids.
 -- Details about how we consume it:
 -- 1. Using test_consume_xids to consume what's needed - 2;
 -- 2. The current transaction consumes 1 xid;
 -- 3. Use another transaction to consume 1 more. This is to mark the last
 --      one completed so that after restart we can start from that.
 1U: begin;
 BEGIN
 1U: select test_consume_xids((131070 - (cur % 131072))::int) from txid_current() cur;
  test_consume_xids
 -------------------

 (1 row)
 1U: end;
 END
 1U: insert into distributed_snapshot_fix1 values(1);
 INSERT 1
 1Uq: ... <quitting>
 1q: ... <quitting>

 -- Restart server, so that DistributedLogCtl->shared->latest_page_number is
 -- initialized to be the one that the next xid is on. When that happens, and
 -- when we do DistributedLog_AdvanceOldestXmin() again in the next query, we
 -- would successfully truncate the current working segment.
 select pg_ctl(datadir, 'restart') from gp_segment_configuration where role = 'p' and content = 1;
  pg_ctl
 --------
  OK
 (1 row)

 -- Do a SELECT. This assigns distributed snapshot but it won't assign new xid.
 -- Since we'll advance to the next future xid which is the first xid of the next segment,
 -- this will get all DLOG segments truncated.
 1: select * from distributed_snapshot_fix1;
  a
 ---
  1
 (1 row)

 -- Checking the DLOG segments we have right now, which is none.
 1U: select count(*) from gp_distributed_log;
  count
 -------
  0
 (1 row)

 1Uq: ... <quitting>
 1q: ... <quitting>

 -- Restart server again. Previously DistributedLogShared->oldestXmin is initialized to
 -- latestCompletedXid.
 select pg_ctl(datadir, 'restart') from gp_segment_configuration where role = 'p' and content = 1;
  pg_ctl
 --------
  OK
 (1 row)

 -- Do a SELECT. Previously this would complain about missing segment file because we've
 -- truncated the segment that latestCompletedXid is on. Now we don't, because we will
 -- be advancing from latestCompletedXid + 1.
 1: select * from distributed_snapshot_fix1;
  a
 ---
  1
 (1 row)


 -- test the distributed snapshot in the situation of direct dispatch
 0: create table direct_dispatch_snapshot_alpha(a int, b int);
 CREATE
 0: insert into direct_dispatch_snapshot_alpha select i, i from generate_series(1, 10) i;
 INSERT 10

 -- Direct dispach doesn't support transaction block. Use multiple '-c' to avoid psql creating single transaction.
 -- direct dispatch, one log line is expected
 ! psql -d isolation2test -c "set Debug_print_full_dtm = on;" -c "set client_min_messages = log;" -c "select * from direct_dispatch_snapshot_alpha where a = 6;" 2>&1 | grep "Got distributed snapshot from CreateDistributedSnapshot";
 LOG:  Got distributed snapshot from CreateDistributedSnapshot


 -- indirect dispatch, two log lines are expected
 ! psql -d isolation2test -c "set Debug_print_full_dtm = on;" -c "set client_min_messages = log;" -c "select * from direct_dispatch_snapshot_alpha where b = 6;" 2>&1 | grep "Got distributed snapshot from CreateDistributedSnapshot";
 LOG:  Got distributed snapshot from CreateDistributedSnapshot
 LOG:  Got distributed snapshot from CreateDistributedSnapshot
	-- Distributed snapshot tests

	create or replace function test_consume_xids(int4) returns void as '@abs_srcdir@/../regress/regress.so', 'test_consume_xids' language C;
	CREATE

	-- Scenario1: Test to validate GetSnapshotData()'s computation of globalXmin using
	-- distributed snapshot. It mainly uses a old read-only transaction to help
	-- create situation where globalXmin can be lower than distributed oldestXmin
	-- when calling DistributedLog_AdvanceOldestXmin().

	-- Setup
	CREATE TABLE distributed_snapshot_test1 (a int);
	CREATE

	1: BEGIN;
	BEGIN
	1: INSERT INTO distributed_snapshot_test1 values(1);
	INSERT 1
	-- Read transaction which helps to get lower globalXmin for session 3. As this
	-- will have MyProc->xmin set to transaction 1's xid.
	2: BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
	BEGIN
	2: SELECT * from distributed_snapshot_test1;
	a
	---
	(0 rows)
	-- Complete transaction 1, so that it no more appears in in-progress transaction
	-- list for following transactions.
	1: COMMIT;
	COMMIT

	-- Transaction to bump the latestCompletedXid
	1: INSERT INTO distributed_snapshot_test1 values(1);
	INSERT 1

	-- Hold after walking over ProcArray in GetSnpashotData(), right at start of
	-- DistributedLog_AdvanceOldestXmin()
	1: SELECT gp_inject_fault('distributedlog_advance_oldest_xmin', 'suspend', '', 'postgres', '', 1, -1, 5, dbid) from gp_segment_configuration where content = 0 and role = 'p';
	gp_inject_fault
	-----------------
	Success:
	(1 row)
	3&:@db_name postgres: SELECT count(*) > 0 from gp_dist_random('gp_id'); <waiting ...>
	1: SELECT gp_wait_until_triggered_fault('distributedlog_advance_oldest_xmin', 1, dbid) from gp_segment_configuration where content = 0 and role = 'p';
	gp_wait_until_triggered_fault
	-------------------------------
	Success:
	(1 row)
	2: COMMIT;
	COMMIT

	-- Transaction used to bump the distributed oldestXmin
	1: INSERT INTO distributed_snapshot_test1 values(1);
	INSERT 1
	-- let session 3 now move forward to compute distributed oldest xmin
	1: SELECT gp_inject_fault('distributedlog_advance_oldest_xmin', 'reset', dbid) from gp_segment_configuration where content = 0 and role = 'p';
	gp_inject_fault
	-----------------
	Success:
	(1 row)
	3<: <... completed>
	?column?
	----------
	t
	(1 row)

	-- Scenario2: This scenario tests the boundary condition for Xmax in distributed snapshot

	-- Setup
	CREATE TABLE distributed_snapshot_test2 (a int);
	CREATE

	-- start transaction assigns distributed xid.
	1: BEGIN ISOLATION LEVEL REPEATABLE READ;
	BEGIN
	-- this sets latestCompletedXid
	2: INSERT INTO distributed_snapshot_test2 VALUES(1);
	INSERT 1
	-- here, take distributed snapshot
	1: SELECT 123 AS "establish snapshot";
	establish snapshot
	--------------------
	123
	(1 row)
	2: INSERT INTO distributed_snapshot_test2 VALUES(2);
	INSERT 1
	-- expected to see just VALUES(1)
	1: SELECT * FROM distributed_snapshot_test2;
	a
	---
	1
	(1 row)
	1: COMMIT;
	COMMIT

	DROP TABLE distributed_snapshot_test2;
	DROP

	-- Scenario3: Test the one-phase commit transactions don't break repeatable read isolation.
	--
	-- Direct dispatch causes the select statements to be dispatched only to one of
	-- the three demo cluster segments. A segment acquires local snapshot only when it
	-- receives the dispatched statement. If one phase commit relied on local
	-- snapshots only, wrong results are possible depending on the order of local
	-- snapshot acquisition by the segments. This scenario validates that distributed
	-- snapshot is used by the segments to evaluate tuple visibility in case of
	-- one-phase commit and correct results are returned.
	--
	-- connection 40 inserts 100, 100 and 300 serially using one-phase commit
	-- protocol. Repeatable read transactions may read (100), (100,100) or
	-- (100,100,300), but not (100, 300).
	CREATE TABLE distributed_snapshot_test3 (a int);
	CREATE
	10: BEGIN ISOLATION LEVEL REPEATABLE READ;
	BEGIN
	20: BEGIN ISOLATION LEVEL REPEATABLE READ;
	BEGIN
	30: BEGIN ISOLATION LEVEL REPEATABLE READ;
	BEGIN
	40: INSERT INTO distributed_snapshot_test3 VALUES(100);
	INSERT 1
	10: SELECT gp_segment_id, * FROM distributed_snapshot_test3 where a = 100;
	gp_segment_id \| a
	---------------+-----
	2 \| 100
	(1 row)
	40: INSERT INTO distributed_snapshot_test3 VALUES(100);
	INSERT 1
	30: SELECT 123 AS "establish snapshot";
	establish snapshot
	--------------------
	123
	(1 row)
	40: INSERT INTO distributed_snapshot_test3 VALUES(300);
	INSERT 1
	10: SELECT gp_segment_id, * FROM distributed_snapshot_test3;
	gp_segment_id \| a
	---------------+-----
	2 \| 100
	(1 row)
	20: SELECT gp_segment_id, * FROM distributed_snapshot_test3;
	gp_segment_id \| a
	---------------+-----
	1 \| 300
	2 \| 100
	2 \| 100
	(3 rows)
	30: SELECT gp_segment_id, * FROM distributed_snapshot_test3;
	gp_segment_id \| a
	---------------+-----
	2 \| 100
	2 \| 100
	(2 rows)
	10: COMMIT;
	COMMIT
	20: COMMIT;
	COMMIT
	30: COMMIT;
	COMMIT
	DROP TABLE distributed_snapshot_test3;
	DROP

	-- The following test cases are to test that QEs can get
	-- latest distribute snapshot to scan normal tables (not catalog).
	-- Cloudberry tests the visibility of heap tuples firstly using
	-- distributed snapshot. Distributed snapshot is generated on
	-- QD and then dispatched to QEs. Some utility statement needs
	-- to work under latest snapshot when executing, so that they
	-- invoke the function `GetLatestSnapshot` in QEs. But remember
	-- we cannot get the latest distributed snapshot.

	-- Subtle cases are: Alter Table or Alter Domain statements on QD
	-- get snapshot in Portal Run and then try to hold locks on the
	-- target table in ProcessUtilitySlow. Here is the key point:
	-- 1. try to hold lock ==> it might be blocked by other transcations
	-- 2. then it will be waked up to continue
	-- 3. when it can continue, the world has changed because other transcations
	-- then blocks it have been over

	-- Previously, on QD we do not getsnapshot before we dispatch utility
	-- statement to QEs which leads to the distributed snapshot does not
	-- reflect the "world change". This will lead to some bugs. For example,
	-- if the first transaction is to rewrite the whole heap, and then
	-- the second Alter Table or Alter Domain statements continues with
	-- the distributed snapshot that txn1 does not commit yet, it will
	-- see no tuples in the new heap!
	-- See Github issue https://github.com/greenplum-db/gpdb/issues/10216

	-- Now this has been fixed, the following cases are tests to check this.

	-- Case 1: concurrently alter column type (will do rewrite heap)
	create table t_alter_snapshot_test(a int, b int, c int);
	CREATE
	insert into t_alter_snapshot_test values (1, 1, 1), (1, 1, 1);
	INSERT 2

	select * from t_alter_snapshot_test;
	a \| b \| c
	---+---+---
	1 \| 1 \| 1
	1 \| 1 \| 1
	(2 rows)

	1: begin;
	BEGIN
	1: alter table t_alter_snapshot_test alter column b type text;
	ALTER

	-- the following statement will hang
	2&: alter table t_alter_snapshot_test alter column c type text; <waiting ...>

	1: end;
	END
	-- after 1 commit, 2 can continue, it should use latest distributed
	-- snapshot so that the data will not be lost.
	2<: <... completed>
	ALTER

	select * from t_alter_snapshot_test;
	a \| b \| c
	---+---+---
	1 \| 1 \| 1
	1 \| 1 \| 1
	(2 rows)
	drop table t_alter_snapshot_test;
	DROP

	-- Case 2: concurrently add exclude constrain
	create table t_alter_snapshot_test(a int, b int);
	CREATE
	insert into t_alter_snapshot_test values (1, 1), (1, 1);
	INSERT 2

	select a from t_alter_snapshot_test;
	a
	---
	1
	1
	(2 rows)

	1: begin;
	BEGIN
	1: alter table t_alter_snapshot_test alter column b type int using b::int;
	ALTER

	2&: alter table t_alter_snapshot_test add exclude using btree (a WITH =); <waiting ...>

	1: end;
	END
	-- after 1 commit, 2 can go on and it should fail
	2<: <... completed>
	ERROR: could not create exclusion constraint "t_alter_snapshot_test_a_excl" (seg1 127.0.1.1:7003 pid=39163)
	DETAIL: Key (a)=(1) conflicts with key (a)=(1).

	drop table t_alter_snapshot_test;
	DROP

	-- Case 3: concurrently split partition
	create table t_alter_snapshot_test(id int, rank int, year int) distributed by (id) partition by range (year) ( start (0) end (20) every (4), default partition extra );
	CREATE

	insert into t_alter_snapshot_test select i,i,i from generate_series(1, 100)i;
	INSERT 100
	select count(*) from t_alter_snapshot_test;
	count
	-------
	100
	(1 row)

	1: begin;
	BEGIN
	1: alter table t_alter_snapshot_test alter column rank type text;
	ALTER

	2&: alter table t_alter_snapshot_test split partition for (5) at (5) into (partition pa, partition pb); <waiting ...>

	1: end;
	END
	-- after 1 commit, 2 can go on and it should not lose data
	2<: <... completed>
	ALTER

	select count(*) from t_alter_snapshot_test;
	count
	-------
	100
	(1 row)
	drop table t_alter_snapshot_test;
	DROP

	-- case 4: concurrently validate check
	create table t_alter_snapshot_test(a int, b int);
	CREATE
	insert into t_alter_snapshot_test values (1, 1), (2, 2);
	INSERT 2
	alter table t_alter_snapshot_test ADD CONSTRAINT mychk CHECK(a > 20) NOT VALID;
	ALTER

	1: begin;
	BEGIN
	1: alter table t_alter_snapshot_test alter column b type text;
	ALTER

	2&: alter table t_alter_snapshot_test validate CONSTRAINT mychk; <waiting ...>

	1: end;
	END
	-- after 1 commit, 2 can go on and it should fail
	2<: <... completed>
	ERROR: check constraint "mychk" of relation "t_alter_snapshot_test" is violated by some row (seg1 127.0.1.1:8003 pid=423039)

	drop table t_alter_snapshot_test;
	DROP

	-- case 5: concurrently domain check
	create domain domain_snapshot_test as int;
	CREATE
	create table t_alter_snapshot_test(i domain_snapshot_test, j int, k int);
	CREATE
	insert into t_alter_snapshot_test values(200,1,1);
	INSERT 1
	alter domain domain_snapshot_test ADD CONSTRAINT mychk CHECK(VALUE > 300) NOT VALID;
	ALTER

	1: begin;
	BEGIN
	1: alter table t_alter_snapshot_test alter column k type text;
	ALTER

	2&: alter domain domain_snapshot_test validate CONSTRAINT mychk; <waiting ...>

	1:end;
	END
	-- after 1 commit, 2 can go on and it should fail
	2<: <... completed>
	ERROR: column "i" of table "t_alter_snapshot_test" contains values that violate the new constraint (seg2 127.0.1.1:7004 pid=39164)

	drop table t_alter_snapshot_test;
	DROP
	drop domain domain_snapshot_test;
	DROP

	-- case 6: alter table expand table
	create table t_alter_snapshot_test(a int, b int);
	CREATE
	set allow_system_table_mods = on;
	SET
	update gp_distribution_policy set numsegments = 2 where localoid = 't_alter_snapshot_test'::regclass::oid;
	UPDATE 1
	insert into t_alter_snapshot_test select i,i from generate_series(1, 10)i;
	INSERT 10
	select gp_segment_id, * from t_alter_snapshot_test;
	gp_segment_id \| a \| b
	---------------+----+----
	0 \| 2 \| 2
	0 \| 3 \| 3
	0 \| 4 \| 4
	0 \| 6 \| 6
	0 \| 7 \| 7
	0 \| 8 \| 8
	0 \| 9 \| 9
	0 \| 10 \| 10
	1 \| 1 \| 1
	1 \| 5 \| 5
	(10 rows)

	1: begin;
	BEGIN
	1: alter table t_alter_snapshot_test alter column b type text;
	ALTER

	2&: alter table t_alter_snapshot_test expand table; <waiting ...>

	1: end;
	END
	-- after 1 commit, 2 can go on and data should not be lost
	2<: <... completed>
	ALTER

	select gp_segment_id, * from t_alter_snapshot_test;
	gp_segment_id \| a \| b
	---------------+----+----
	0 \| 2 \| 2
	0 \| 3 \| 3
	0 \| 4 \| 4
	0 \| 7 \| 7
	0 \| 8 \| 8
	1 \| 1 \| 1
	2 \| 6 \| 6
	2 \| 9 \| 9
	2 \| 10 \| 10
	2 \| 5 \| 5
	(10 rows)
	drop table t_alter_snapshot_test;
	DROP

	-- case 7: alter table set distributed by
	create table t_alter_snapshot_test(a int, b int) distributed randomly;
	CREATE
	insert into t_alter_snapshot_test select i,i from generate_series(1, 10)i;
	INSERT 10
	select count(*) from t_alter_snapshot_test;
	count
	-------
	10
	(1 row)

	1: begin;
	BEGIN
	1: alter table t_alter_snapshot_test alter column b type text;
	ALTER

	2&: alter table t_alter_snapshot_test set distributed by (a); <waiting ...>

	1: end;
	END
	-- after 1 commit, 2 can continue and data should not be lost
	2<: <... completed>
	ALTER

	select count(*) from t_alter_snapshot_test;
	count
	-------
	10
	(1 row)
	drop table t_alter_snapshot_test;
	DROP

	-- case 8: DML concurrent with Alter Table
	create table t_alter_snapshot_test(a int, b int);
	CREATE

	---- test for insert
	1: begin;
	BEGIN
	1: insert into t_alter_snapshot_test values (1, 1);
	INSERT 1
	2&: alter table t_alter_snapshot_test alter column b type text; <waiting ...>
	1: end;
	END
	-- 2 can continue, and we should not lose data
	2<: <... completed>
	ALTER
	select * from t_alter_snapshot_test;
	a \| b
	---+---
	1 \| 1
	(1 row)

	---- test for update
	truncate t_alter_snapshot_test;
	TRUNCATE
	insert into t_alter_snapshot_test values (1, 1);
	INSERT 1
	1: begin;
	BEGIN
	1: update t_alter_snapshot_test set b = '3';
	UPDATE 1
	2&: alter table t_alter_snapshot_test alter column b type int using b::int; <waiting ...>
	1: end;
	END
	-- 2 can continue and we should see the data has been updated
	2<: <... completed>
	ALTER
	select * from t_alter_snapshot_test;
	a \| b
	---+---
	1 \| 3
	(1 row)

	---- test for delete
	truncate t_alter_snapshot_test;
	TRUNCATE
	insert into t_alter_snapshot_test values (1, 1);
	INSERT 1
	1: begin;
	BEGIN
	1: delete from t_alter_snapshot_test;
	DELETE 1
	2&: alter table t_alter_snapshot_test alter column b type text; <waiting ...>
	1: end;
	END
	-- 2 can continue and we should see the data has been deleted
	2<: <... completed>
	ALTER
	select * from t_alter_snapshot_test;
	a \| b
	---+---
	(0 rows)
	drop table t_alter_snapshot_test;
	DROP

	-- Case 9: Repeatable Read Isolation Level Test
	create table t_alter_snapshot_test(a int, b int);
	CREATE
	insert into t_alter_snapshot_test values (1, 1);
	INSERT 1
	1: begin;
	BEGIN
	1: insert into t_alter_snapshot_test values (1, 1);
	INSERT 1

	2: begin isolation level repeatable read;
	BEGIN
	2: select * from t_alter_snapshot_test;
	a \| b
	---+---
	1 \| 1
	(1 row)
	2&: alter table t_alter_snapshot_test alter column b type text; <waiting ...>

	1: end;
	END
	-- 2 can continue and after its alter rewrite the heap
	-- it can see all the data even under repeatable read
	2<: <... completed>
	ALTER
	2: select * from t_alter_snapshot_test;
	a \| b
	---+---
	1 \| 1
	1 \| 1
	(2 rows)
	2: end;
	END

	select * from t_alter_snapshot_test;
	a \| b
	---+---
	1 \| 1
	1 \| 1
	(2 rows)
	drop table t_alter_snapshot_test;
	DROP

	----------------------------------------
	-- Test for fixes
	----------------------------------------
	-- Case 1. Test that when we advanced DLOG's oldestXmin to the
	-- latestCompletedXid + 1, and that it is the first xid of the
	-- next segment, we would truncate all DLOG segments (all txs
	-- have completed and no longer needed). And in that case, we
	-- should still be able to advance properly after restart.
	create table distributed_snapshot_fix1(a int);
	CREATE

	-- On a primary, burn xids until the next xid is the first one of a segment,
	-- which has 4096 (ENTRIES_PER_PAGE) * 32 (SLRU_PAGES_PER_SEGMENT) = 131072 xids.
	-- Details about how we consume it:
	-- 1. Using test_consume_xids to consume what's needed - 2;
	-- 2. The current transaction consumes 1 xid;
	-- 3. Use another transaction to consume 1 more. This is to mark the last
	-- one completed so that after restart we can start from that.
	1U: begin;
	BEGIN
	1U: select test_consume_xids((131070 - (cur % 131072))::int) from txid_current() cur;
	test_consume_xids
	-------------------

	(1 row)
	1U: end;
	END
	1U: insert into distributed_snapshot_fix1 values(1);
	INSERT 1
	1Uq: ... <quitting>
	1q: ... <quitting>

	-- Restart server, so that DistributedLogCtl->shared->latest_page_number is
	-- initialized to be the one that the next xid is on. When that happens, and
	-- when we do DistributedLog_AdvanceOldestXmin() again in the next query, we
	-- would successfully truncate the current working segment.
	select pg_ctl(datadir, 'restart') from gp_segment_configuration where role = 'p' and content = 1;
	pg_ctl
	--------
	OK
	(1 row)

	-- Do a SELECT. This assigns distributed snapshot but it won't assign new xid.
	-- Since we'll advance to the next future xid which is the first xid of the next segment,
	-- this will get all DLOG segments truncated.
	1: select * from distributed_snapshot_fix1;
	a
	---
	1
	(1 row)

	-- Checking the DLOG segments we have right now, which is none.
	1U: select count(*) from gp_distributed_log;
	count
	-------
	0
	(1 row)

	1Uq: ... <quitting>
	1q: ... <quitting>

	-- Restart server again. Previously DistributedLogShared->oldestXmin is initialized to
	-- latestCompletedXid.
	select pg_ctl(datadir, 'restart') from gp_segment_configuration where role = 'p' and content = 1;
	pg_ctl
	--------
	OK
	(1 row)

	-- Do a SELECT. Previously this would complain about missing segment file because we've
	-- truncated the segment that latestCompletedXid is on. Now we don't, because we will
	-- be advancing from latestCompletedXid + 1.
	1: select * from distributed_snapshot_fix1;
	a
	---
	1
	(1 row)


	-- test the distributed snapshot in the situation of direct dispatch
	0: create table direct_dispatch_snapshot_alpha(a int, b int);
	CREATE
	0: insert into direct_dispatch_snapshot_alpha select i, i from generate_series(1, 10) i;
	INSERT 10

	-- Direct dispach doesn't support transaction block. Use multiple '-c' to avoid psql creating single transaction.
	-- direct dispatch, one log line is expected
	! psql -d isolation2test -c "set Debug_print_full_dtm = on;" -c "set client_min_messages = log;" -c "select * from direct_dispatch_snapshot_alpha where a = 6;" 2>&1 \| grep "Got distributed snapshot from CreateDistributedSnapshot";
	LOG: Got distributed snapshot from CreateDistributedSnapshot


	-- indirect dispatch, two log lines are expected
	! psql -d isolation2test -c "set Debug_print_full_dtm = on;" -c "set client_min_messages = log;" -c "select * from direct_dispatch_snapshot_alpha where b = 6;" 2>&1 \| grep "Got distributed snapshot from CreateDistributedSnapshot";
	LOG: Got distributed snapshot from CreateDistributedSnapshot
	LOG: Got distributed snapshot from CreateDistributedSnapshot