LOAD 'pg_disorder';
SHOW pg_disorder.mode;
 pg_disorder.mode 
------------------
 off
(1 row)

SHOW pg_disorder.seed;
 pg_disorder.seed 
------------------
 0
(1 row)

CREATE TABLE t (id int, name text);
INSERT INTO t SELECT g, 'row' || g FROM generate_series(1, 20) g;
SET pg_disorder.mode = 'shuffle';
SET pg_disorder.seed = 12345;
SELECT string_agg(id::text, ',' ORDER BY id) AS ordered_ids FROM t;
                    ordered_ids                     
----------------------------------------------------
 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20
(1 row)

SELECT id FROM t ORDER BY id DESC LIMIT 3;
 id 
----
 20
 19
 18
(3 rows)

CREATE TEMP TABLE cap AS SELECT id FROM t;
SELECT array_agg(id ORDER BY id) AS multiset_preserved FROM cap;
                  multiset_preserved                  
------------------------------------------------------
 {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20}
(1 row)

SELECT count(*) AS n_rows FROM cap;
 n_rows 
--------
     20
(1 row)

SET max_parallel_workers_per_gather = 0;
CREATE TEMP TABLE shuffle_proof AS SELECT id FROM t;
SELECT (SELECT array_agg(id) FROM shuffle_proof)
       IS DISTINCT FROM
       (SELECT array_agg(id ORDER BY id) FROM t) AS shuffle_happened;
 shuffle_happened 
------------------
 t
(1 row)

CREATE TEMP TABLE cap5 AS SELECT id FROM t LIMIT 5;
SELECT count(*) AS limited FROM cap5;
 limited 
---------
       5
(1 row)

SELECT DISTINCT id FROM t WHERE id <= 3 ORDER BY id;
 id 
----
  1
  2
  3
(3 rows)

SELECT count(*) AS union_rows FROM (SELECT id FROM t UNION SELECT id FROM t) u;
 union_rows 
------------
         20
(1 row)

SELECT count(*) AS grouped_rows FROM (SELECT id FROM t GROUP BY id) g;
 grouped_rows 
--------------
           20
(1 row)

SELECT count(*) AS total FROM t;
 total 
-------
    20
(1 row)

SELECT id FROM t WHERE false;
 id 
----
(0 rows)

CREATE TEMP TABLE t_ins (id int);
INSERT INTO t_ins SELECT id FROM t;
SELECT count(*) AS inserted FROM t_ins;
 inserted 
----------
       20
(1 row)

CREATE FUNCTION pg_disorder_simple_expr() RETURNS int AS $$
DECLARE n int := 42;
BEGIN RETURN n; END;
$$ LANGUAGE plpgsql;
SELECT pg_disorder_simple_expr() AS simple_expr;
 simple_expr 
-------------
          42
(1 row)

CREATE FUNCTION pg_disorder_nested_scan() RETURNS bigint AS $$
DECLARE c bigint := 0; r record;
BEGIN
  FOR r IN SELECT id FROM t LOOP c := c + 1; END LOOP;
  RETURN c;
END;
$$ LANGUAGE plpgsql;
SELECT pg_disorder_nested_scan() AS nested_rows;
 nested_rows 
-------------
          20
(1 row)

-- reverse mode is deterministic, so unlike shuffle its exact row order can be
-- asserted.  On a freshly loaded table the physical scan order is the insertion
-- order, so a top-level unordered scan comes back reversed: 20..1.  (This must
-- be a bare top-level SELECT: wrapping it in an aggregate or a subquery would
-- make it ineligible and it would not be perturbed at all.)
SET pg_disorder.mode = 'reverse';
SELECT id FROM t;
 id 
----
 20
 19
 18
 17
 16
 15
 14
 13
 12
 11
 10
  9
  8
  7
  6
  5
  4
  3
  2
  1
(20 rows)

-- an explicit ORDER BY is still not perturbed
SELECT id FROM t ORDER BY id LIMIT 3;
 id 
----
  1
  2
  3
(3 rows)

-- the row count is preserved
SELECT count(*) AS reverse_total FROM t;
 reverse_total 
---------------
            20
(1 row)

-- an excluded shape still returns the full correct result under reverse
SELECT array_agg(id ORDER BY id) AS grouped_multiset
       FROM (SELECT id FROM t WHERE id <= 5 GROUP BY id) g;
 grouped_multiset 
------------------
 {1,2,3,4,5}
(1 row)

-- DO and CALL reach the executor through ProcessUtility rather than
-- ExecutorRun, so their bodies must still be recognised as nested and left
-- unperturbed.  The second SELECT is the important one: eligibility is decided
-- at plan time and cached, so a body perturbed here would leave a poisoned plan
-- behind and keep returning the wrong row from ordinary nested calls too.
CREATE FUNCTION pg_disorder_first_id() RETURNS int AS $$
DECLARE v int;
BEGIN SELECT id INTO v FROM t LIMIT 1; RETURN v; END;
$$ LANGUAGE plpgsql;
DO $$ BEGIN RAISE NOTICE 'do block sees %', pg_disorder_first_id(); END $$;
NOTICE:  do block sees 1
SELECT pg_disorder_first_id() AS after_do_block;
 after_do_block 
----------------
              1
(1 row)

CREATE PROCEDURE pg_disorder_scan_proc() AS $$
DECLARE c bigint := 0; r record;
BEGIN
  FOR r IN SELECT id FROM t LOOP c := c + 1; END LOOP;
  RAISE NOTICE 'call body scanned % rows, first id %', c, pg_disorder_first_id();
END;
$$ LANGUAGE plpgsql;
CALL pg_disorder_scan_proc();
NOTICE:  call body scanned 20 rows, first id 1
-- ... while a utility statement that merely carries a query is still perturbed.
-- The read-back is an aggregate, so it is ineligible and reports the temp
-- table's physical order, which is the perturbed order the CTAS wrote: 20..1.
CREATE TEMP TABLE reverse_ctas AS SELECT id FROM t;
SELECT (array_agg(id))[1:3] AS ctas_wrote_reversed FROM reverse_ctas;
 ctas_wrote_reversed 
---------------------
 {20,19,18}
(1 row)

SELECT id FROM t LIMIT 5;
 id 
----
 20
 19
 18
 17
 16
(5 rows)

SELECT id FROM t FOR SHARE LIMIT 5;
 id 
----
  1
  2
  3
  4
  5
(5 rows)

SELECT * FROM (SELECT id FROM t FOR UPDATE) s LIMIT 5;
 id 
----
  1
  2
  3
  4
  5
(5 rows)

WITH locked AS (SELECT id FROM t FOR UPDATE) SELECT * FROM locked LIMIT 5;
 id 
----
  1
  2
  3
  4
  5
(5 rows)

SELECT id FROM (SELECT id FROM t) plain LIMIT 5;
 id 
----
 20
 19
 18
 17
 16
(5 rows)

WITH RECURSIVE r(n) AS (VALUES (1) UNION ALL SELECT n + 1 FROM r)
SELECT * FROM r LIMIT 3;
 n 
---
 1
 2
 3
(3 rows)

WITH plain_cte AS (SELECT id FROM t) SELECT * FROM plain_cte LIMIT 3;
 id 
----
 20
 19
 18
(3 rows)

CREATE FUNCTION pg_disorder_sql_exec_arg() RETURNS int LANGUAGE sql AS $$
  SELECT id FROM t LIMIT 1;
$$;
PREPARE pg_disorder_param(int) AS SELECT $1 AS v;
EXECUTE pg_disorder_param(pg_disorder_sql_exec_arg());
 v 
---
 1
(1 row)

CREATE TEMP TABLE pg_disorder_ctas_exec AS
       EXECUTE pg_disorder_param(pg_disorder_sql_exec_arg());
SELECT v AS ctas_execute_param FROM pg_disorder_ctas_exec;
 ctas_execute_param 
--------------------
                  1
(1 row)

SELECT pg_disorder_sql_exec_arg() AS execute_param_left_no_poisoned_plan;
 execute_param_left_no_poisoned_plan 
-------------------------------------
                                   1
(1 row)

DEALLOCATE pg_disorder_param;
DROP FUNCTION pg_disorder_sql_exec_arg();
PREPARE pg_disorder_noparam AS SELECT id FROM t;
EXECUTE pg_disorder_noparam;
 id 
----
 20
 19
 18
 17
 16
 15
 14
 13
 12
 11
 10
  9
  8
  7
  6
  5
  4
  3
  2
  1
(20 rows)

DEALLOCATE pg_disorder_noparam;
BEGIN;
DECLARE pg_disorder_cur CURSOR FOR SELECT id FROM t;
FETCH 3 FROM pg_disorder_cur;
 id 
----
 20
 19
 18
(3 rows)

COMMIT;
COPY (SELECT id FROM t LIMIT 3) TO stdout;
20
19
18
CREATE TEMP TABLE reverse_insert_select (id int);
INSERT INTO reverse_insert_select SELECT id FROM t;
SELECT (array_agg(id))[1:3] AS insert_select_kept_order FROM reverse_insert_select;
 insert_select_kept_order 
--------------------------
 {1,2,3}
(1 row)

DO $$ BEGIN EXECUTE 'CREATE TEMP TABLE reverse_ctas_in_do AS SELECT id FROM t'; END $$;
SELECT (array_agg(id))[1:3] AS ctas_in_do_not_perturbed FROM reverse_ctas_in_do;
 ctas_in_do_not_perturbed 
--------------------------
 {1,2,3}
(1 row)

CREATE TABLE pg_disorder_trigger_log (v int);
CREATE FUNCTION pg_disorder_log_first_id() RETURNS trigger AS $$
DECLARE v int;
BEGIN
  SELECT id INTO v FROM t LIMIT 1;
  INSERT INTO pg_disorder_trigger_log VALUES (v);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TABLE pg_disorder_copy_target (x int);
CREATE TRIGGER pg_disorder_copy_trg BEFORE INSERT ON pg_disorder_copy_target
       FOR EACH ROW EXECUTE FUNCTION pg_disorder_log_first_id();
COPY pg_disorder_copy_target FROM stdin;
SELECT v AS trigger_under_copy FROM pg_disorder_trigger_log;
 trigger_under_copy 
--------------------
                  1
(1 row)

TRUNCATE pg_disorder_trigger_log;
INSERT INTO pg_disorder_copy_target VALUES (2);
SELECT v AS trigger_under_insert FROM pg_disorder_trigger_log;
 trigger_under_insert 
----------------------
                    1
(1 row)

CREATE TABLE pg_disorder_deferred (x int);
CREATE CONSTRAINT TRIGGER pg_disorder_deferred_trg AFTER INSERT ON pg_disorder_deferred
       DEFERRABLE INITIALLY DEFERRED
       FOR EACH ROW EXECUTE FUNCTION pg_disorder_log_first_id();
TRUNCATE pg_disorder_trigger_log;
BEGIN;
INSERT INTO pg_disorder_deferred VALUES (1);
COMMIT;
SELECT v AS trigger_deferred_to_commit FROM pg_disorder_trigger_log;
 trigger_deferred_to_commit 
----------------------------
                          1
(1 row)

CREATE PROCEDURE pg_disorder_commit_proc() AS $$
BEGIN
  INSERT INTO pg_disorder_deferred VALUES (2);
  COMMIT;
END;
$$ LANGUAGE plpgsql;
CALL pg_disorder_commit_proc();
SELECT count(*) AS committed_inside_procedure FROM pg_disorder_deferred;
 committed_inside_procedure 
----------------------------
                          2
(1 row)

CREATE FUNCTION pg_disorder_secdef_first_id() RETURNS int
       SECURITY DEFINER SET work_mem = '4MB' AS $$
DECLARE v int;
BEGIN SELECT id INTO v FROM t LIMIT 1; RETURN v; END;
$$ LANGUAGE plpgsql;
SELECT pg_disorder_secdef_first_id() AS security_definer_body;
 security_definer_body 
-----------------------
                     1
(1 row)

CREATE FUNCTION pg_disorder_sql_immutable() RETURNS int LANGUAGE sql IMMUTABLE AS $$
  SELECT id FROM t LIMIT 1;
$$;
CREATE FUNCTION pg_disorder_sql_values() RETURNS int LANGUAGE sql IMMUTABLE AS $$
  SELECT x FROM (VALUES (1), (2), (3)) v(x) LIMIT 1;
$$;
SELECT pg_disorder_sql_immutable() AS sql_body_const_folded,
       pg_disorder_sql_values() AS sql_values_body_const_folded;
 sql_body_const_folded | sql_values_body_const_folded 
-----------------------+------------------------------
                     1 |                            1
(1 row)

SELECT pg_disorder_sql_immutable() AS sql_body_const_folded_again,
       pg_disorder_sql_values() AS sql_values_body_const_folded_again;
 sql_body_const_folded_again | sql_values_body_const_folded_again 
-----------------------------+------------------------------------
                           1 |                                  1
(1 row)

DROP FUNCTION pg_disorder_sql_immutable();
DROP FUNCTION pg_disorder_sql_values();
CREATE FUNCTION pg_disorder_sql_first_id() RETURNS int LANGUAGE sql AS $$
  SELECT 1;
  SELECT id FROM t LIMIT 1;
$$;
CREATE TABLE pg_disorder_rewrite (a int);
INSERT INTO pg_disorder_rewrite VALUES (1);
ALTER TABLE pg_disorder_rewrite ADD COLUMN b int DEFAULT pg_disorder_sql_first_id();
SELECT b AS sql_function_under_table_rewrite FROM pg_disorder_rewrite;
 sql_function_under_table_rewrite 
----------------------------------
                                1
(1 row)

CREATE TABLE pg_disorder_sql_default (a int, b int DEFAULT pg_disorder_sql_first_id());
COPY pg_disorder_sql_default (a) FROM stdin;
SELECT b AS sql_function_under_copy_default FROM pg_disorder_sql_default;
 sql_function_under_copy_default 
---------------------------------
                               1
(1 row)

DROP TABLE pg_disorder_rewrite;
DROP TABLE pg_disorder_sql_default;
DROP FUNCTION pg_disorder_sql_first_id();
DROP TABLE pg_disorder_copy_target;
DROP TABLE pg_disorder_deferred;
DROP TABLE pg_disorder_trigger_log;
DROP PROCEDURE pg_disorder_commit_proc();
DROP PROCEDURE pg_disorder_scan_proc();
DROP FUNCTION pg_disorder_log_first_id();
DROP FUNCTION pg_disorder_secdef_first_id();
DROP FUNCTION pg_disorder_first_id();
DROP FUNCTION pg_disorder_nested_scan();
DROP FUNCTION pg_disorder_simple_expr();
DROP TABLE t;