src/test/isolation2/expected/resource_queue_terminate.out - cloudberry - Git at Google

 -- Test various scenarios with respect to backend termination.

 0:CREATE RESOURCE QUEUE rq_terminate WITH (active_statements = 1);
 CREATE
 0:CREATE ROLE role_terminate RESOURCE QUEUE rq_terminate;
 CREATE

 --
 -- Scenario 1: Terminate a backend with a regular open cursor
 --
 1:SET ROLE role_terminate;
 SET
 1:BEGIN;
 BEGIN
 1:DECLARE cs1 CURSOR FOR SELECT 0;
 DECLARE

 0:SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE query='DECLARE cs1 CURSOR FOR SELECT 0;';
  pg_terminate_backend
 ----------------------
  t
 (1 row)

 1<:  <... completed>
 FAILED:  Execution failed
 -- Sanity check: Ensure that the resource queue is now empty.
 0:SELECT rsqcountlimit, rsqcountvalue FROM pg_resqueue_status WHERE rsqname = 'rq_terminate';
  rsqcountlimit | rsqcountvalue
 ---------------+---------------
  1             | 0
 (1 row)

 --
 -- Scenario 2: Terminate a backend with a holdable open cursor that has been
 -- persisted.
 --
 2:SET ROLE role_terminate;
 SET
 2:DECLARE cs2 CURSOR WITH HOLD FOR SELECT 0;
 DECLARE

 0:SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE query='DECLARE cs2 CURSOR WITH HOLD FOR SELECT 0;';
  pg_terminate_backend
 ----------------------
  t
 (1 row)

 2<:  <... completed>
 FAILED:  Execution failed
 -- Sanity check: Ensure that the resource queue is now empty.
 0:SELECT rsqcountlimit, rsqcountvalue FROM pg_resqueue_status WHERE rsqname = 'rq_terminate';
  rsqcountlimit | rsqcountvalue
 ---------------+---------------
  1             | 0
 (1 row)

 --
 -- Scenario 3: Terminate a backend with a waiting statement
 --
 3:SET ROLE role_terminate;
 SET
 3:BEGIN;
 BEGIN
 3:DECLARE cs3 CURSOR FOR SELECT 0;
 DECLARE
 4:SET ROLE role_terminate;
 SET
 4&:SELECT 331763;  <waiting ...>

 0:SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE query='SELECT 331763;';
  pg_terminate_backend
 ----------------------
  t
 (1 row)

 4<:  <... completed>
 FATAL:  terminating connection due to administrator command
 server closed the connection unexpectedly
 	This probably means the server terminated abnormally
 	before or while processing the request.
 3:END;
 END
 -- Sanity check: Ensure that the resource queue is now empty.
 0:SELECT rsqcountlimit, rsqcountvalue FROM pg_resqueue_status WHERE rsqname = 'rq_terminate';
  rsqcountlimit | rsqcountvalue
 ---------------+---------------
  1             | 0
 (1 row)

 --
 -- Scenario 4: Terminate a backend with a waiting holdable cursor
 --
 5:SET ROLE role_terminate;
 SET
 5:BEGIN;
 BEGIN
 5:DECLARE cs4 CURSOR FOR SELECT 0;
 DECLARE
 6:SET ROLE role_terminate;
 SET
 6&:DECLARE cs5 CURSOR WITH HOLD FOR SELECT 0;  <waiting ...>

 0:SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE query='DECLARE cs5 CURSOR WITH HOLD FOR SELECT 0;';
  pg_terminate_backend
 ----------------------
  t
 (1 row)

 6<:  <... completed>
 FATAL:  terminating connection due to administrator command
 server closed the connection unexpectedly
 	This probably means the server terminated abnormally
 	before or while processing the request.
 5:END;
 END
 -- Sanity check: Ensure that the resource queue is now empty.
 0:SELECT rsqcountlimit, rsqcountvalue FROM pg_resqueue_status WHERE rsqname = 'rq_terminate';
  rsqcountlimit | rsqcountvalue
 ---------------+---------------
  1             | 0
 (1 row)

 --
 -- Scenario 5: Race during termination of session having a waiting portal with
 -- another session waking up the same one. This can happen if the waiter during
 -- termination, hasn't yet removed itself from the wait queue in
 -- AbortOutOfAnyTransaction() -> .. -> ResLockWaitCancel(), and another session
 -- sees it on the wait queue, does an external grant and wakeup. This causes a
 -- leak, as the external grant is never cleaned up. In an asserts build we see:
 -- FailedAssertion(""!(SHMQueueEmpty(&(MyProc->myProcLocks[i])))"", File: ""proc.c", Line: 1031
 --
 7:SET ROLE role_terminate;
 SET
 7:BEGIN;
 BEGIN
 7:DECLARE cs6 CURSOR FOR SELECT 0;
 DECLARE
 8:SET ROLE role_terminate;
 SET
 8&:SELECT 331765;  <waiting ...>

 0:SELECT gp_inject_fault('res_lock_wait_cancel_before_partition_lock', 'suspend', dbid) FROM gp_segment_configuration WHERE content = -1 AND role = 'p';
  gp_inject_fault
 -----------------
  Success:
 (1 row)

 -- Fire the termination first and it will be stuck in the middle of aborting.
 0:SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE query='SELECT 331765;';
  pg_terminate_backend
 ----------------------
  t
 (1 row)
 0:SELECT gp_wait_until_triggered_fault('res_lock_wait_cancel_before_partition_lock', 1, dbid) FROM gp_segment_configuration WHERE content = -1 AND role = 'p';
  gp_wait_until_triggered_fault
 -------------------------------
  Success:
 (1 row)

 -- Now perform the external grant (as a part of relinquishing a spot in the queue)
 7:CLOSE cs6;
 CLOSE

 -- Sanity check: Ensure that the resource queue now has 1 active statement (from
 -- the external grant).
 0:SELECT rsqcountlimit, rsqcountvalue FROM pg_resqueue_status WHERE rsqname = 'rq_terminate';
  rsqcountlimit | rsqcountvalue
 ---------------+---------------
  1             | 1
 (1 row)

 0:SELECT gp_inject_fault('res_lock_wait_cancel_before_partition_lock', 'reset', dbid) FROM gp_segment_configuration WHERE content = -1 AND role = 'p';
  gp_inject_fault
 -----------------
  Success:
 (1 row)

 8<:  <... completed>
 FATAL:  terminating connection due to administrator command
 server closed the connection unexpectedly
 	This probably means the server terminated abnormally
 	before or while processing the request.
 7:END;
 END

 -- Sanity check: Ensure that the resource queue is now empty.
 0:SELECT rsqcountlimit, rsqcountvalue FROM pg_resqueue_status WHERE rsqname = 'rq_terminate';
  rsqcountlimit | rsqcountvalue
 ---------------+---------------
  1             | 0
 (1 row)

 --
 -- Scenario 6: Same as 5, except the statement being terminated is a holdable cursor.
 --
 9:SET ROLE role_terminate;
 SET
 9:BEGIN;
 BEGIN
 9:DECLARE cs7 CURSOR FOR SELECT 0;
 DECLARE
 10:SET ROLE role_terminate;
 SET
 10&:DECLARE cs8 CURSOR WITH HOLD FOR SELECT 0;  <waiting ...>

 0:SELECT gp_inject_fault('res_lock_wait_cancel_before_partition_lock', 'suspend', dbid) FROM gp_segment_configuration WHERE content = -1 AND role = 'p';
  gp_inject_fault
 -----------------
  Success:
 (1 row)

 -- Fire the termination first and it will be stuck in the middle of aborting.
 0:SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE query='DECLARE cs8 CURSOR WITH HOLD FOR SELECT 0;';
  pg_terminate_backend
 ----------------------
  t
 (1 row)
 0:SELECT gp_wait_until_triggered_fault('res_lock_wait_cancel_before_partition_lock', 1, dbid) FROM gp_segment_configuration WHERE content = -1 AND role = 'p';
  gp_wait_until_triggered_fault
 -------------------------------
  Success:
 (1 row)

 -- Now perform the external grant (as a part of relinquishing a spot in the queue)
 9:CLOSE cs6;
 ERROR:  cursor "cs6" does not exist

 -- Sanity check: Ensure that the resource queue now has 1 active statement (from
 -- the external grant).
 0:SELECT rsqcountlimit, rsqcountvalue FROM pg_resqueue_status WHERE rsqname = 'rq_terminate';
  rsqcountlimit | rsqcountvalue
 ---------------+---------------
  1             | 1
 (1 row)

 0:SELECT gp_inject_fault('res_lock_wait_cancel_before_partition_lock', 'reset', dbid) FROM gp_segment_configuration WHERE content = -1 AND role = 'p';
  gp_inject_fault
 -----------------
  Success:
 (1 row)

 10<:  <... completed>
 FATAL:  terminating connection due to administrator command
 server closed the connection unexpectedly
 	This probably means the server terminated abnormally
 	before or while processing the request.
 9:END;
 END

 -- Sanity check: Ensure that the resource queue is now empty.
 0:SELECT rsqcountlimit, rsqcountvalue FROM pg_resqueue_status WHERE rsqname = 'rq_terminate';
  rsqcountlimit | rsqcountvalue
 ---------------+---------------
  1             | 0
 (1 row)

 -- Cleanup
 0:DROP ROLE role_terminate;
 DROP
 0:DROP RESOURCE QUEUE rq_terminate;
 DROP
	-- Test various scenarios with respect to backend termination.

	0:CREATE RESOURCE QUEUE rq_terminate WITH (active_statements = 1);
	CREATE
	0:CREATE ROLE role_terminate RESOURCE QUEUE rq_terminate;
	CREATE

	--
	-- Scenario 1: Terminate a backend with a regular open cursor
	--
	1:SET ROLE role_terminate;
	SET
	1:BEGIN;
	BEGIN
	1:DECLARE cs1 CURSOR FOR SELECT 0;
	DECLARE

	0:SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE query='DECLARE cs1 CURSOR FOR SELECT 0;';
	pg_terminate_backend
	----------------------
	t
	(1 row)

	1<: <... completed>
	FAILED: Execution failed
	-- Sanity check: Ensure that the resource queue is now empty.
	0:SELECT rsqcountlimit, rsqcountvalue FROM pg_resqueue_status WHERE rsqname = 'rq_terminate';
	rsqcountlimit \| rsqcountvalue
	---------------+---------------
	1 \| 0
	(1 row)

	--
	-- Scenario 2: Terminate a backend with a holdable open cursor that has been
	-- persisted.
	--
	2:SET ROLE role_terminate;
	SET
	2:DECLARE cs2 CURSOR WITH HOLD FOR SELECT 0;
	DECLARE

	0:SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE query='DECLARE cs2 CURSOR WITH HOLD FOR SELECT 0;';
	pg_terminate_backend
	----------------------
	t
	(1 row)

	2<: <... completed>
	FAILED: Execution failed
	-- Sanity check: Ensure that the resource queue is now empty.
	0:SELECT rsqcountlimit, rsqcountvalue FROM pg_resqueue_status WHERE rsqname = 'rq_terminate';
	rsqcountlimit \| rsqcountvalue
	---------------+---------------
	1 \| 0
	(1 row)

	--
	-- Scenario 3: Terminate a backend with a waiting statement
	--
	3:SET ROLE role_terminate;
	SET
	3:BEGIN;
	BEGIN
	3:DECLARE cs3 CURSOR FOR SELECT 0;
	DECLARE
	4:SET ROLE role_terminate;
	SET
	4&:SELECT 331763; <waiting ...>

	0:SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE query='SELECT 331763;';
	pg_terminate_backend
	----------------------
	t
	(1 row)

	4<: <... completed>
	FATAL: terminating connection due to administrator command
	server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.
	3:END;
	END
	-- Sanity check: Ensure that the resource queue is now empty.
	0:SELECT rsqcountlimit, rsqcountvalue FROM pg_resqueue_status WHERE rsqname = 'rq_terminate';
	rsqcountlimit \| rsqcountvalue
	---------------+---------------
	1 \| 0
	(1 row)

	--
	-- Scenario 4: Terminate a backend with a waiting holdable cursor
	--
	5:SET ROLE role_terminate;
	SET
	5:BEGIN;
	BEGIN
	5:DECLARE cs4 CURSOR FOR SELECT 0;
	DECLARE
	6:SET ROLE role_terminate;
	SET
	6&:DECLARE cs5 CURSOR WITH HOLD FOR SELECT 0; <waiting ...>

	0:SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE query='DECLARE cs5 CURSOR WITH HOLD FOR SELECT 0;';
	pg_terminate_backend
	----------------------
	t
	(1 row)

	6<: <... completed>
	FATAL: terminating connection due to administrator command
	server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.
	5:END;
	END
	-- Sanity check: Ensure that the resource queue is now empty.
	0:SELECT rsqcountlimit, rsqcountvalue FROM pg_resqueue_status WHERE rsqname = 'rq_terminate';
	rsqcountlimit \| rsqcountvalue
	---------------+---------------
	1 \| 0
	(1 row)

	--
	-- Scenario 5: Race during termination of session having a waiting portal with
	-- another session waking up the same one. This can happen if the waiter during
	-- termination, hasn't yet removed itself from the wait queue in
	-- AbortOutOfAnyTransaction() -> .. -> ResLockWaitCancel(), and another session
	-- sees it on the wait queue, does an external grant and wakeup. This causes a
	-- leak, as the external grant is never cleaned up. In an asserts build we see:
	-- FailedAssertion(""!(SHMQueueEmpty(&(MyProc->myProcLocks[i])))"", File: ""proc.c", Line: 1031
	--
	7:SET ROLE role_terminate;
	SET
	7:BEGIN;
	BEGIN
	7:DECLARE cs6 CURSOR FOR SELECT 0;
	DECLARE
	8:SET ROLE role_terminate;
	SET
	8&:SELECT 331765; <waiting ...>

	0:SELECT gp_inject_fault('res_lock_wait_cancel_before_partition_lock', 'suspend', dbid) FROM gp_segment_configuration WHERE content = -1 AND role = 'p';
	gp_inject_fault
	-----------------
	Success:
	(1 row)

	-- Fire the termination first and it will be stuck in the middle of aborting.
	0:SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE query='SELECT 331765;';
	pg_terminate_backend
	----------------------
	t
	(1 row)
	0:SELECT gp_wait_until_triggered_fault('res_lock_wait_cancel_before_partition_lock', 1, dbid) FROM gp_segment_configuration WHERE content = -1 AND role = 'p';
	gp_wait_until_triggered_fault
	-------------------------------
	Success:
	(1 row)

	-- Now perform the external grant (as a part of relinquishing a spot in the queue)
	7:CLOSE cs6;
	CLOSE

	-- Sanity check: Ensure that the resource queue now has 1 active statement (from
	-- the external grant).
	0:SELECT rsqcountlimit, rsqcountvalue FROM pg_resqueue_status WHERE rsqname = 'rq_terminate';
	rsqcountlimit \| rsqcountvalue
	---------------+---------------
	1 \| 1
	(1 row)

	0:SELECT gp_inject_fault('res_lock_wait_cancel_before_partition_lock', 'reset', dbid) FROM gp_segment_configuration WHERE content = -1 AND role = 'p';
	gp_inject_fault
	-----------------
	Success:
	(1 row)

	8<: <... completed>
	FATAL: terminating connection due to administrator command
	server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.
	7:END;
	END

	-- Sanity check: Ensure that the resource queue is now empty.
	0:SELECT rsqcountlimit, rsqcountvalue FROM pg_resqueue_status WHERE rsqname = 'rq_terminate';
	rsqcountlimit \| rsqcountvalue
	---------------+---------------
	1 \| 0
	(1 row)

	--
	-- Scenario 6: Same as 5, except the statement being terminated is a holdable cursor.
	--
	9:SET ROLE role_terminate;
	SET
	9:BEGIN;
	BEGIN
	9:DECLARE cs7 CURSOR FOR SELECT 0;
	DECLARE
	10:SET ROLE role_terminate;
	SET
	10&:DECLARE cs8 CURSOR WITH HOLD FOR SELECT 0; <waiting ...>

	0:SELECT gp_inject_fault('res_lock_wait_cancel_before_partition_lock', 'suspend', dbid) FROM gp_segment_configuration WHERE content = -1 AND role = 'p';
	gp_inject_fault
	-----------------
	Success:
	(1 row)

	-- Fire the termination first and it will be stuck in the middle of aborting.
	0:SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE query='DECLARE cs8 CURSOR WITH HOLD FOR SELECT 0;';
	pg_terminate_backend
	----------------------
	t
	(1 row)
	0:SELECT gp_wait_until_triggered_fault('res_lock_wait_cancel_before_partition_lock', 1, dbid) FROM gp_segment_configuration WHERE content = -1 AND role = 'p';
	gp_wait_until_triggered_fault
	-------------------------------
	Success:
	(1 row)

	-- Now perform the external grant (as a part of relinquishing a spot in the queue)
	9:CLOSE cs6;
	ERROR: cursor "cs6" does not exist

	-- Sanity check: Ensure that the resource queue now has 1 active statement (from
	-- the external grant).
	0:SELECT rsqcountlimit, rsqcountvalue FROM pg_resqueue_status WHERE rsqname = 'rq_terminate';
	rsqcountlimit \| rsqcountvalue
	---------------+---------------
	1 \| 1
	(1 row)

	0:SELECT gp_inject_fault('res_lock_wait_cancel_before_partition_lock', 'reset', dbid) FROM gp_segment_configuration WHERE content = -1 AND role = 'p';
	gp_inject_fault
	-----------------
	Success:
	(1 row)

	10<: <... completed>
	FATAL: terminating connection due to administrator command
	server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.
	9:END;
	END

	-- Sanity check: Ensure that the resource queue is now empty.
	0:SELECT rsqcountlimit, rsqcountvalue FROM pg_resqueue_status WHERE rsqname = 'rq_terminate';
	rsqcountlimit \| rsqcountvalue
	---------------+---------------
	1 \| 0
	(1 row)

	-- Cleanup
	0:DROP ROLE role_terminate;
	DROP
	0:DROP RESOURCE QUEUE rq_terminate;
	DROP