SELECT min_val, max_val FROM pg_settings WHERE name = 'gp_resqueue_priority_cpucores_per_segment';
 min_val | max_val 
---------+---------
 0.1     | 512
(1 row)

-- Test cursor gang should not be reused if SET command happens.
CREATE OR REPLACE FUNCTION test_set_cursor_func() RETURNS text as $$
DECLARE
  result text;
BEGIN
  EXECUTE 'select setting from pg_settings where name=''temp_buffers''' INTO result;
  RETURN result;
END;
$$ LANGUAGE plpgsql;
SET temp_buffers = 2000;
BEGIN;
  DECLARE set_cusor CURSOR FOR SELECT relname FROM gp_dist_random('pg_class');
  -- The GUC setting should not be dispatched to the cursor gang.
  SET temp_buffers = 3000;
END;
-- Verify the cursor gang is not reused. If the gang is reused, the
-- temp_buffers value on that gang should be old one, i.e. 2000 instead of
-- the new committed 3000.
SELECT * from (SELECT test_set_cursor_func() FROM gp_dist_random('pg_class') limit 1) t1
  JOIN (SELECT test_set_cursor_func() FROM gp_dist_random('pg_class') limit 1) t2 ON TRUE;
 test_set_cursor_func | test_set_cursor_func 
----------------------+----------------------
 3000                 | 3000
(1 row)

RESET temp_buffers;
--
-- Test GUC if cursor is opened
--
-- start_ignore
drop table if exists test_cursor_set_table;
NOTICE:  table "test_cursor_set_table" does not exist, skipping
drop function if exists test_set_in_loop();
NOTICE:  function test_set_in_loop() does not exist, skipping
drop function if exists test_call_set_command();
NOTICE:  function test_call_set_command() does not exist, skipping
-- end_ignore
create table test_cursor_set_table as select * from generate_series(1, 100);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'generate_series' as the Greenplum Database 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 FUNCTION test_set_in_loop () RETURNS numeric
    AS $$
DECLARE
    rec record;
    result numeric;
    tmp numeric;
BEGIN
	result = 0;
FOR rec IN select * from test_cursor_set_table
LOOP
        select test_call_set_command() into tmp;
        result = result + 1;
END LOOP;
return result;
END;
$$
    LANGUAGE plpgsql NO SQL;
CREATE FUNCTION test_call_set_command() returns numeric
AS $$
BEGIN
       execute 'SET gp_workfile_limit_per_query=524;';
       return 0;
END;
$$
    LANGUAGE plpgsql NO SQL;
SELECT * from test_set_in_loop();
 test_set_in_loop 
------------------
              100
(1 row)

CREATE FUNCTION test_set_within_initplan () RETURNS numeric
AS $$
DECLARE
	result numeric;
	tmp RECORD;
BEGIN
	result = 1;
	execute 'SET gp_workfile_limit_per_query=524;';
	select into tmp * from test_cursor_set_table limit 100;
	return result;
END;
$$
	LANGUAGE plpgsql;
CREATE TABLE test_initplan_set_table as select * from test_set_within_initplan();
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'test_set_within_initplan' as the Greenplum Database 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.
DROP TABLE if exists test_initplan_set_table;
DROP TABLE if exists test_cursor_set_table;
DROP FUNCTION if exists test_set_in_loop();
DROP FUNCTION if exists test_call_set_command();
-- Set work_mem. It emits a WARNING, but it should only emit it once.
--
-- We used to erroneously set the GUC twice in the QD node, whenever you issue
-- a SET command. If this stops emitting a WARNING in the future, we'll need
-- another way to detect that the GUC's assign-hook is called only once.
set work_mem='1MB';
WARNING:  "work_mem": setting is deprecated, and may be removed in a future release.
reset work_mem;
WARNING:  "work_mem": setting is deprecated, and may be removed in a future release.
--
-- Test if RESET timezone is dispatched to all slices
--
CREATE TABLE timezone_table AS SELECT * FROM (VALUES (123,1513123564),(123,1512140765),(123,1512173164),(123,1512396441)) foo(a, b) DISTRIBUTED RANDOMLY;
SELECT to_timestamp(b)::timestamp WITH TIME ZONE AS b_ts FROM timezone_table ORDER BY b_ts;
             b_ts             
------------------------------
 Fri Dec 01 07:06:05 2017 PST
 Fri Dec 01 16:06:04 2017 PST
 Mon Dec 04 06:07:21 2017 PST
 Tue Dec 12 16:06:04 2017 PST
(4 rows)

SET timezone= 'America/New_York';
-- Check if it is set correctly on QD.
SELECT to_timestamp(1613123565)::timestamp WITH TIME ZONE;
         to_timestamp         
------------------------------
 Fri Feb 12 04:52:45 2021 EST
(1 row)

-- Check if it is set correctly on the QEs.
SELECT to_timestamp(b)::timestamp WITH TIME ZONE AS b_ts FROM timezone_table ORDER BY b_ts;
             b_ts             
------------------------------
 Fri Dec 01 10:06:05 2017 EST
 Fri Dec 01 19:06:04 2017 EST
 Mon Dec 04 09:07:21 2017 EST
 Tue Dec 12 19:06:04 2017 EST
(4 rows)

RESET timezone;
-- Check if it is reset correctly on QD.
SELECT to_timestamp(1613123565)::timestamp WITH TIME ZONE;
         to_timestamp         
------------------------------
 Fri Feb 12 01:52:45 2021 PST
(1 row)

-- Check if it is reset correctly on the QEs.
SELECT to_timestamp(b)::timestamp WITH TIME ZONE AS b_ts FROM timezone_table ORDER BY b_ts;
             b_ts             
------------------------------
 Fri Dec 01 07:06:05 2017 PST
 Fri Dec 01 16:06:04 2017 PST
 Mon Dec 04 06:07:21 2017 PST
 Tue Dec 12 16:06:04 2017 PST
(4 rows)

--
-- Test if SET TIME ZONE INTERVAL is dispatched correctly to all segments
--
SET TIME ZONE INTERVAL '04:30:06' HOUR TO MINUTE;
-- Check if it is set correctly on QD.
SELECT to_timestamp(1613123565)::timestamp WITH TIME ZONE;
          to_timestamp           
---------------------------------
 Fri Feb 12 14:22:45 2021 +04:30
(1 row)

-- Check if it is set correctly on the QEs.
SELECT to_timestamp(b)::timestamp WITH TIME ZONE AS b_ts FROM timezone_table ORDER BY b_ts;
              b_ts               
---------------------------------
 Fri Dec 01 19:36:05 2017 +04:30
 Sat Dec 02 04:36:04 2017 +04:30
 Mon Dec 04 18:37:21 2017 +04:30
 Wed Dec 13 04:36:04 2017 +04:30
(4 rows)

-- Test default_transaction_isolation and transaction_isolation fallback from serializable to repeatable read
CREATE TABLE test_serializable(a int);
insert into test_serializable values(1);
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL serializable;
show default_transaction_isolation;
 default_transaction_isolation 
-------------------------------
 repeatable read
(1 row)

SELECT * FROM test_serializable;
 a 
---
 1
(1 row)

SET default_transaction_isolation = 'read committed';
SET default_transaction_isolation = 'serializable';
show default_transaction_isolation;
 default_transaction_isolation 
-------------------------------
 repeatable read
(1 row)

SELECT * FROM test_serializable;
 a 
---
 1
(1 row)

SET default_transaction_isolation = 'read committed';
BEGIN TRANSACTION ISOLATION LEVEL serializable;
	show transaction_isolation;
 transaction_isolation 
-----------------------
 repeatable read
(1 row)

	SELECT * FROM test_serializable;
 a 
---
 1
(1 row)

COMMIT;
DROP TABLE test_serializable;
-- Test single query guc rollback
set allow_segment_DML to on;
set datestyle='german';
select gp_inject_fault('set_variable_fault', 'error', dbid)
from gp_segment_configuration where content=0 and role='p';
 gp_inject_fault 
-----------------
 Success:
(1 row)

set datestyle='sql, mdy';
ERROR:  fault triggered, fault name:'set_variable_fault' fault type:'error'  (seg0 10.34.58.103:25432 pid=31389)
-- after guc set failed, before next query handle, qd will sync guc
-- to qe. using `select 1` trigger guc reset.
select 1;
 ?column? 
----------
        1
(1 row)

select current_setting('datestyle') from gp_dist_random('gp_id');
 current_setting 
-----------------
 German, DMY
 German, DMY
 German, DMY
(3 rows)

select gp_inject_fault('all', 'reset', dbid) from gp_segment_configuration;
 gp_inject_fault 
-----------------
 Success:
 Success:
 Success:
 Success:
 Success:
 Success:
 Success:
 Success:
(8 rows)

set allow_segment_DML to off;
--
-- Test DISCARD TEMP.
--
-- There's a test like this in upstream 'guc' test, but this expanded version
-- verifies that temp tables are dropped on segments, too.
--
CREATE TEMP TABLE reset_test ( data text ) ON COMMIT DELETE ROWS;
DISCARD TEMP;
-- Try to create a new temp table with same. Should work.
CREATE TEMP TABLE reset_test ( data text ) ON COMMIT PRESERVE ROWS;
-- Now test that the effects of DISCARD TEMP can be rolled back
BEGIN;
DISCARD TEMP;
ROLLBACK;
-- the table should still exist.
INSERT INTO reset_test VALUES (1);
-- Unlike DISCARD TEMP, DISCARD ALL cannot be run in a transaction.
BEGIN;
DISCARD ALL;
ERROR:  DISCARD ALL cannot run inside a transaction block
COMMIT;
-- the table should still exist.
INSERT INTO reset_test VALUES (2);
SELECT * FROM reset_test;
 data 
------
 1
 2
(2 rows)

-- Also DISCARD ALL does not have cluster wide effects. CREATE will fail as the
-- table will not be dropped in the segments.
DISCARD ALL;
NOTICE:  command without clusterwide effect
HINT:  Concider alternatives as DEALLOCATE ALL, or DISCARD TEMP if a clusterwide effect is desired.
CREATE TEMP TABLE reset_test ( data text ) ON COMMIT PRESERVE ROWS;
ERROR:  relation "reset_test" already exists  (seg0 127.0.1.1:7002 pid=26153)
-- test for guc dev_opt_unsafe_truncate_in_subtransaction
-- start_ignore
CREATE LANGUAGE plpythonu;
-- end_ignore
CREATE OR REPLACE FUNCTION run_all_in_one() RETURNS VOID AS
$$
     plpy.execute('CREATE TABLE unsafe_truncate(a int, b int) DISTRIBUTED BY (a)')
     plpy.execute('INSERT INTO unsafe_truncate SELECT * FROM generate_series(1, 10)')
     for i in range(1,4):
         plpy.execute('UPDATE unsafe_truncate SET b = b + 1')
         plpy.execute('CREATE TABLE foobar AS SELECT * FROM unsafe_truncate DISTRIBUTED BY (a)')

         before_truncate = plpy.execute('SELECT relfilenode FROM gp_dist_random(\'pg_class\') WHERE relname=\'unsafe_truncate\' ORDER BY gp_segment_id')
         plpy.execute('truncate unsafe_truncate')
         after_truncate = plpy.execute('SELECT relfilenode FROM gp_dist_random(\'pg_class\') WHERE relname=\'unsafe_truncate\' ORDER BY gp_segment_id')

         plpy.execute('DROP TABLE unsafe_truncate')
         plpy.execute('ALTER TABLE foobar RENAME TO unsafe_truncate')

         if before_truncate[0]['relfilenode'] == after_truncate[0]['relfilenode']:
	     plpy.info('iteration:%d unsafe truncate performed' % (i))
         else:
	     plpy.info('iteration:%d safe truncate performed' % (i))

	 plpy.execute('SET dev_opt_unsafe_truncate_in_subtransaction TO ON')
     plpy.execute('DROP TABLE unsafe_truncate')
     plpy.execute('RESET dev_opt_unsafe_truncate_in_subtransaction')
$$ language plpythonu;
select run_all_in_one();
INFO:  iteration:1 safe truncate performed
INFO:  iteration:2 unsafe truncate performed
INFO:  iteration:3 unsafe truncate performed
 run_all_in_one 
----------------
 
(1 row)

CREATE TABLE guc_gp_t1(i int);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database 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.
INSERT INTO guc_gp_t1 VALUES(1),(2);
-- generate an idle redaer gang by the following query
SELECT count(*) FROM guc_gp_t1, guc_gp_t1 t;
 count 
-------
     4
(1 row)

-- test create role and set role in the same transaction
BEGIN;
DROP ROLE IF EXISTS guc_gp_test_role1;
NOTICE:  role "guc_gp_test_role1" does not exist, skipping
CREATE ROLE guc_gp_test_role1;
NOTICE:  resource queue required -- using default resource queue "pg_default"
SET ROLE guc_gp_test_role1;
RESET ROLE;
END;
-- generate an idle redaer gang by the following query
SELECT count(*) FROM guc_gp_t1, guc_gp_t1 t;
 count 
-------
     4
(1 row)

BEGIN ISOLATION LEVEL REPEATABLE READ;
DROP ROLE IF EXISTS guc_gp_test_role2;
NOTICE:  role "guc_gp_test_role2" does not exist, skipping
CREATE ROLE guc_gp_test_role2;
NOTICE:  resource queue required -- using default resource queue "pg_default"
SET ROLE guc_gp_test_role2;
RESET ROLE;
END;
-- test cursor case
-- cursors are also reader gangs, but they are not idle, thus will not be
-- destroyed by utility statement.
BEGIN;
DECLARE c1 CURSOR FOR SELECT * FROM guc_gp_t1 a, guc_gp_t1 b order by a.i, b.i;
DECLARE c2 CURSOR FOR SELECT * FROM guc_gp_t1 a, guc_gp_t1 b order by a.i, b.i;
FETCH c1;
 i | i 
---+---
 1 | 1
(1 row)

DROP ROLE IF EXISTS guc_gp_test_role1;
CREATE ROLE guc_gp_test_role1;
NOTICE:  resource queue required -- using default resource queue "pg_default"
SET ROLE guc_gp_test_role1;
RESET ROLE;
FETCH c2;
 i | i 
---+---
 1 | 1
(1 row)

FETCH c1;
 i | i 
---+---
 1 | 2
(1 row)

FETCH c2;
 i | i 
---+---
 1 | 2
(1 row)

END;
DROP TABLE guc_gp_t1;