src/test/isolation2/output/parallel_retrieve_cursor/replicated_table.source

-- @Description Tests the PARALLEL RETRIEVE CURSOR for select replcated table
--
DROP TABLE IF EXISTS rt1;
DROP
CREATE TABLE rt1 (a INT) DISTRIBUTED REPLICATED;
CREATE
insert into rt1 select generate_series(1,100);
INSERT 100

--------- Test1: Basic test for PARALLEL RETRIEVE CURSOR on replicated table

-- Replicated table will execute on seg id: session_id % segment_number
-- Declare a cursor and check gp_get_endpoints(), we can find out the real
-- segment id by joining gp_segment_configuration. This should equal to
-- session_id % 3 (size of demo cluster).
1: BEGIN;
BEGIN
1: DECLARE c1 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM rt1;
DECLARE
1: SELECT sc.content = current_setting('gp_session_id')::int % 3 AS diff FROM gp_get_endpoints() ep, gp_segment_configuration sc WHERE ep.gp_segment_id = sc.content;
 diff 
------
 t    
 t    
(2 rows)
1: ROLLBACK;
ROLLBACK
1q: ... <quitting>

--------- Test2: Basic test for PARALLEL RETRIEVE CURSOR on replicated table
1: BEGIN;
BEGIN
1: DECLARE c1 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM rt1 ORDER BY a;
DECLARE
2: BEGIN;
BEGIN
2: DECLARE c2 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM rt1 ORDER BY a;
DECLARE
3: BEGIN;
BEGIN
3: DECLARE c3 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM rt1 ORDER BY a;
DECLARE
4: BEGIN;
BEGIN
4: DECLARE c4 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM rt1 ORDER BY a;
DECLARE
5: BEGIN;
BEGIN
5: DECLARE c5 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM rt1 ORDER BY a;
DECLARE
6: BEGIN;
BEGIN
6: DECLARE c6 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM rt1 ORDER BY a;
DECLARE

-- Here because replicated table will execute on seg id: session_id % segment_number
-- Just declare & CHECK PARALLEL RETRIEVE CURSORs in all segment_number (i.e. 3) sessions,
-- so that there should have specific session: MOD(sessionid,3)=1;
-- Get token only in specific session id and retrieve this token.
7: @post_run 'parse_endpoint_info 2 1 2 3 4': SELECT endpointname,auth_token,hostname,port,state FROM gp_get_endpoints() WHERE MOD(sessionid,3)=1 LIMIT 1;
 endpoint_id2 | token_id | host_id | port_id | READY
(1 row)
1&: SELECT * FROM gp_wait_parallel_retrieve_cursor('c1', -1);  <waiting ...>
2&: SELECT * FROM gp_wait_parallel_retrieve_cursor('c2', -1);  <waiting ...>
3&: SELECT * FROM gp_wait_parallel_retrieve_cursor('c3', -1);  <waiting ...>
4&: SELECT * FROM gp_wait_parallel_retrieve_cursor('c4', -1);  <waiting ...>
5&: SELECT * FROM gp_wait_parallel_retrieve_cursor('c5', -1);  <waiting ...>
6&: SELECT * FROM gp_wait_parallel_retrieve_cursor('c6', -1);  <waiting ...>
*U: @pre_run 'set_endpoint_variable @ENDPOINT2': SELECT state FROM gp_get_segment_endpoints() WHERE endpointname='@ENDPOINT2';
 state 
-------
(0 rows)

 state 
-------
(0 rows)

 state 
-------
 READY 
(1 row)

 state 
-------
(0 rows)
*R: @pre_run 'set_endpoint_variable @ENDPOINT2': RETRIEVE 10 FROM ENDPOINT "@ENDPOINT2";
#-1retrieve> connection to server at "host_id", port port_id failed: FATAL:  retrieve auth token is invalid


#0retrieve> connection to server at "host_id", port port_id failed: FATAL:  retrieve auth token is invalid


 a  
----
 1  
 2  
 3  
 4  
 5  
 6  
 7  
 8  
 9  
 10 
(10 rows)

#2retrieve> connection to server at "host_id", port port_id failed: FATAL:  retrieve auth token is invalid

-- cancel all 6 sessions
7: select pg_cancel_backend(pid) from pg_stat_activity, gp_get_endpoints() where sess_id = sessionid AND (cursorname ='c1' or cursorname='c2' or cursorname='c3' or cursorname ='c4' or cursorname='c5' or cursorname='c6');
 pg_cancel_backend 
-------------------
 t                 
 t                 
 t                 
 t                 
 t                 
 t                 
(6 rows)
1<:  <... completed>
ERROR:  canceling statement due to user request
2<:  <... completed>
ERROR:  canceling statement due to user request
3<:  <... completed>
ERROR:  canceling statement due to user request
4<:  <... completed>
ERROR:  canceling statement due to user request
5<:  <... completed>
ERROR:  canceling statement due to user request
6<:  <... completed>
ERROR:  canceling statement due to user request

1: ROLLBACK;
ROLLBACK
2: ROLLBACK;
ROLLBACK
3: ROLLBACK;
ROLLBACK
4: ROLLBACK;
ROLLBACK
5: ROLLBACK;
ROLLBACK
6: ROLLBACK;
ROLLBACK

1q: ... <quitting>
2q: ... <quitting>
3q: ... <quitting>
4q: ... <quitting>
5q: ... <quitting>
6q: ... <quitting>
-1Rq:Sessions not started cannot be quit
1Rq: ... <quitting>
2Rq:Sessions not started cannot be quit
3Rq:Sessions not started cannot be quit
4Rq:Sessions not started cannot be quit
5Rq:Sessions not started cannot be quit
6Rq:Sessions not started cannot be quit

--------- Test3: Basic test for PARALLEL RETRIEVE CURSOR on replicated table
1: BEGIN;
BEGIN
1: DECLARE c1 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM rt1 WHERE MOD(a,3)=1;
DECLARE
2: BEGIN;
BEGIN
2: DECLARE c2 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM rt1 WHERE MOD(a,3)=1;
DECLARE
3: BEGIN;
BEGIN
3: DECLARE c3 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM rt1 WHERE MOD(a,3)=1;
DECLARE
4: BEGIN;
BEGIN
4: DECLARE c4 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM rt1 WHERE MOD(a,3)=1;
DECLARE
5: BEGIN;
BEGIN
5: DECLARE c5 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM rt1 WHERE MOD(a,3)=1;
DECLARE
6: BEGIN;
BEGIN
6: DECLARE c6 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM rt1 WHERE MOD(a,3)=1;
DECLARE

-- Here because replicated table will execute on seg id: session_id % segment_number
-- Just declare & CHECK PARALLEL RETRIEVE CURSORs in all segment_number (i.e. 3) sessions,
-- so that there should have specific session: MOD(sessionid,3)=1;
-- Get token only in specific session id and retrieve this token.
7: @post_run 'parse_endpoint_info 3 1 2 3 4': SELECT endpointname,auth_token,hostname,port,state FROM gp_get_endpoints() WHERE MOD(sessionid,3)=1 LIMIT 1;
 endpoint_id3 | token_id | host_id | port_id | READY
(1 row)
1&: SELECT * FROM gp_wait_parallel_retrieve_cursor('c1', -1);  <waiting ...>
2&: SELECT * FROM gp_wait_parallel_retrieve_cursor('c2', -1);  <waiting ...>
3&: SELECT * FROM gp_wait_parallel_retrieve_cursor('c3', -1);  <waiting ...>
4&: SELECT * FROM gp_wait_parallel_retrieve_cursor('c4', -1);  <waiting ...>
5&: SELECT * FROM gp_wait_parallel_retrieve_cursor('c5', -1);  <waiting ...>
6&: SELECT * FROM gp_wait_parallel_retrieve_cursor('c6', -1);  <waiting ...>
*U: @pre_run 'set_endpoint_variable @ENDPOINT3': SELECT state FROM gp_get_segment_endpoints() WHERE endpointname='@ENDPOINT3';
 state 
-------
(0 rows)

 state 
-------
(0 rows)

 state 
-------
 READY 
(1 row)

 state 
-------
(0 rows)
*R: @pre_run 'set_endpoint_variable @ENDPOINT3': RETRIEVE 10 FROM ENDPOINT "@ENDPOINT3";
#-1retrieve> connection to server at "host_id", port port_id failed: FATAL:  retrieve auth token is invalid


#0retrieve> connection to server at "host_id", port port_id failed: FATAL:  retrieve auth token is invalid


 a  
----
 1  
 4  
 7  
 10 
 13 
 16 
 19 
 22 
 25 
 28 
(10 rows)

#2retrieve> connection to server at "host_id", port port_id failed: FATAL:  retrieve auth token is invalid

-- cancel all 6 sessions
7: select pg_cancel_backend(pid) from pg_stat_activity, gp_get_endpoints() where sess_id = sessionid AND (cursorname ='c1' or cursorname='c2' or cursorname='c3' or cursorname ='c4' or cursorname='c5' or cursorname='c6');
 pg_cancel_backend 
-------------------
 t                 
 t                 
 t                 
 t                 
 t                 
 t                 
(6 rows)
1<:  <... completed>
ERROR:  canceling statement due to user request
2<:  <... completed>
ERROR:  canceling statement due to user request
3<:  <... completed>
ERROR:  canceling statement due to user request
4<:  <... completed>
ERROR:  canceling statement due to user request
5<:  <... completed>
ERROR:  canceling statement due to user request
6<:  <... completed>
ERROR:  canceling statement due to user request

1: ROLLBACK;
ROLLBACK
2: ROLLBACK;
ROLLBACK
3: ROLLBACK;
ROLLBACK
4: ROLLBACK;
ROLLBACK
5: ROLLBACK;
ROLLBACK
6: ROLLBACK;
ROLLBACK

1q: ... <quitting>
2q: ... <quitting>
3q: ... <quitting>
4q: ... <quitting>
5q: ... <quitting>
6q: ... <quitting>
-1Rq:Sessions not started cannot be quit
1Rq: ... <quitting>
2Rq:Sessions not started cannot be quit
3Rq:Sessions not started cannot be quit
4Rq:Sessions not started cannot be quit
5Rq:Sessions not started cannot be quit
6Rq:Sessions not started cannot be quit

--------- Test4: Basic test for PARALLEL RETRIEVE CURSOR on replicated table
1: BEGIN;
BEGIN
1: DECLARE c1 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM rt1 WHERE MOD(a,3)=1 OR MOD(a,3)=2;
DECLARE
2: BEGIN;
BEGIN
2: DECLARE c2 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM rt1 WHERE MOD(a,3)=1 OR MOD(a,3)=2;
DECLARE
3: BEGIN;
BEGIN
3: DECLARE c3 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM rt1 WHERE MOD(a,3)=1 OR MOD(a,3)=2;
DECLARE
4: BEGIN;
BEGIN
4: DECLARE c4 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM rt1 WHERE MOD(a,3)=1 OR MOD(a,3)=2;
DECLARE
5: BEGIN;
BEGIN
5: DECLARE c5 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM rt1 WHERE MOD(a,3)=1 OR MOD(a,3)=2;
DECLARE
6: BEGIN;
BEGIN
6: DECLARE c6 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM rt1 WHERE MOD(a,3)=1 OR MOD(a,3)=2;
DECLARE

-- Here because replicated table will execute on seg id: session_id % segment_number
-- Just declare & CHECK PARALLEL RETRIEVE CURSORs in all segment_number (i.e. 3) sessions,
-- so that there should have specific session: MOD(sessionid,3)=1;
-- Get token only in specific session id and retrieve this token.
7: @post_run 'parse_endpoint_info 4 1 2 3 4': SELECT endpointname,auth_token,hostname,port,state FROM gp_get_endpoints() WHERE MOD(sessionid,3)=1 LIMIT 1;
 endpoint_id4 | token_id | host_id | port_id | READY
(1 row)
1&: SELECT * FROM gp_wait_parallel_retrieve_cursor('c1', -1);  <waiting ...>
2&: SELECT * FROM gp_wait_parallel_retrieve_cursor('c2', -1);  <waiting ...>
3&: SELECT * FROM gp_wait_parallel_retrieve_cursor('c3', -1);  <waiting ...>
4&: SELECT * FROM gp_wait_parallel_retrieve_cursor('c4', -1);  <waiting ...>
5&: SELECT * FROM gp_wait_parallel_retrieve_cursor('c5', -1);  <waiting ...>
6&: SELECT * FROM gp_wait_parallel_retrieve_cursor('c6', -1);  <waiting ...>
*U: @pre_run 'set_endpoint_variable @ENDPOINT4': SELECT state FROM gp_get_segment_endpoints() WHERE endpointname='@ENDPOINT4';
 state 
-------
(0 rows)

 state 
-------
(0 rows)

 state 
-------
 READY 
(1 row)

 state 
-------
(0 rows)
*R: @pre_run 'set_endpoint_variable @ENDPOINT4': RETRIEVE 10 FROM ENDPOINT "@ENDPOINT4";
#-1retrieve> connection to server at "host_id", port port_id failed: FATAL:  retrieve auth token is invalid


#0retrieve> connection to server at "host_id", port port_id failed: FATAL:  retrieve auth token is invalid


 a  
----
 1  
 2  
 4  
 5  
 7  
 8  
 10 
 11 
 13 
 14 
(10 rows)

#2retrieve> connection to server at "host_id", port port_id failed: FATAL:  retrieve auth token is invalid

-- cancel all 6 sessions
7: select pg_cancel_backend(pid) from pg_stat_activity, gp_get_endpoints() where sess_id = sessionid AND (cursorname ='c1' or cursorname='c2' or cursorname='c3' or cursorname ='c4' or cursorname='c5' or cursorname='c6');
 pg_cancel_backend 
-------------------
 t                 
 t                 
 t                 
 t                 
 t                 
 t                 
(6 rows)
1<:  <... completed>
ERROR:  canceling statement due to user request
2<:  <... completed>
ERROR:  canceling statement due to user request
3<:  <... completed>
ERROR:  canceling statement due to user request
4<:  <... completed>
ERROR:  canceling statement due to user request
5<:  <... completed>
ERROR:  canceling statement due to user request
6<:  <... completed>
ERROR:  canceling statement due to user request

1: ROLLBACK;
ROLLBACK
2: ROLLBACK;
ROLLBACK
3: ROLLBACK;
ROLLBACK
4: ROLLBACK;
ROLLBACK
5: ROLLBACK;
ROLLBACK
6: ROLLBACK;
ROLLBACK