src/test/regress/expected/combocid_gp_optimizer.out

--
-- Additional tests for combocids, for sharing the the array between QE
-- processes, and for growing the array.
--
-- These tests are mostly copied from the upstream 'combocid' test, but
-- the SELECT queries are replaced with a more complicated query that runs
-- on two slices.
--
CREATE TEMP TABLE combocidtest (foobar int, distkey int) distributed by (distkey);
BEGIN;
-- a few dummy ops to push up the CommandId counter
INSERT INTO combocidtest SELECT 1 LIMIT 0;
INSERT INTO combocidtest SELECT 1 LIMIT 0;
INSERT INTO combocidtest SELECT 1 LIMIT 0;
INSERT INTO combocidtest SELECT 1 LIMIT 0;
INSERT INTO combocidtest SELECT 1 LIMIT 0;
INSERT INTO combocidtest SELECT 1 LIMIT 0;
INSERT INTO combocidtest SELECT 1 LIMIT 0;
INSERT INTO combocidtest SELECT 1 LIMIT 0;
INSERT INTO combocidtest SELECT 1 LIMIT 0;
INSERT INTO combocidtest SELECT 1 LIMIT 0;
INSERT INTO combocidtest VALUES (1);
INSERT INTO combocidtest VALUES (2);
SELECT ctid,cmin,* FROM combocidtest;
 ctid  | cmin | foobar | distkey 
-------+------+--------+---------
 (0,1) |   10 |      1 |        
 (0,2) |   11 |      2 |        
(2 rows)

SAVEPOINT s1;
UPDATE combocidtest SET foobar = foobar + 10;
-- here we should see only updated tuples
SELECT * FROM combocidtest a, combocidtest b WHERE a.foobar < b.foobar;
 foobar | distkey | foobar | distkey 
--------+---------+--------+---------
     11 |         |     12 |        
(1 row)

ROLLBACK TO s1;
-- now we should see old tuples, but with combo CIDs starting at 0
SELECT * FROM combocidtest a, combocidtest b WHERE a.foobar < b.foobar;
 foobar | distkey | foobar | distkey 
--------+---------+--------+---------
      1 |         |      2 |        
(1 row)

COMMIT;
-- combo data is not there anymore, but should still see tuples
SELECT * FROM combocidtest a, combocidtest b WHERE a.foobar < b.foobar;
 foobar | distkey | foobar | distkey 
--------+---------+--------+---------
      1 |         |      2 |        
(1 row)

-- Test combo cids with portals
BEGIN;
INSERT INTO combocidtest VALUES (333);
DECLARE c CURSOR FOR SELECT * FROM combocidtest a, combocidtest b WHERE a.foobar < b.foobar;
DELETE FROM combocidtest;
FETCH ALL FROM c;
 foobar | distkey | foobar | distkey 
--------+---------+--------+---------
      1 |         |      2 |        
      1 |         |    333 |        
      2 |         |    333 |        
(3 rows)

ROLLBACK;
SELECT * FROM combocidtest a, combocidtest b WHERE a.foobar < b.foobar;
 foobar | distkey | foobar | distkey 
--------+---------+--------+---------
      1 |         |      2 |        
(1 row)

--
-- Test growing the combocids array, including the shared combocids array.
--
CREATE TEMP TABLE manycombocids (i int, t text, distkey int) distributed by (distkey);
CREATE INDEX ON manycombocids (i);
BEGIN;
INSERT INTO manycombocids SELECT g, 'initially inserted',1 from generate_series(1, 10000) g;
ANALYZE manycombocids;
-- update some of the rows. The combocids generated by this are included in
-- the initial snapshot that the first FETCH acquires.
DO $$
declare
  j int;
begin
  set enable_seqscan=off;
  for j in 1..10 loop
    UPDATE manycombocids set t = 'updated1' where i = j;
  end loop;
end;
$$;
-- Launch a query that will scan the table, using a cursor.
--
-- Requirements for this test query:
--
-- - it should run on at least two slices, so that the combocids array is
--   shared between the QE processes
--
-- - it should return the rows in a deterministic order, because we use MOVE to
--   skip rows. This is just to keep the expected output reasonably short.
--
-- - it mustn't materialize the whole result on the first FETCH. Otherwise, the
--   reader processes won't see the combocids that are created only after the
--   first FETCH.
--
set enable_indexonlyscan=off;
set enable_indexscan=off;
set enable_bitmapscan=on;
explain (costs off)  SELECT a.i, b.i, a.t FROM manycombocids a, manycombocids b WHERE a.i = b.i AND a.distkey=1;
                            QUERY PLAN                            
------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)
   ->  Hash Join
         Hash Cond: (a.i = b.i)
         ->  Redistribute Motion 3:3  (slice2; segments: 3)
               Hash Key: a.i
               ->  Seq Scan on manycombocids a
                     Filter: (distkey = 1)
         ->  Hash
               ->  Redistribute Motion 3:3  (slice3; segments: 3)
                     Hash Key: b.i
                     ->  Seq Scan on manycombocids b
 Optimizer: GPORCA
(12 rows)

DECLARE c CURSOR FOR SELECT a.i, b.i, a.t FROM manycombocids a, manycombocids b WHERE a.i = b.i AND a.distkey=1;
-- Start the cursor.
FETCH 1 FROM c;
 i  | i  |         t          
----+----+--------------------
 11 | 11 | initially inserted
(1 row)

-- Perform more updates.
DO $$
declare
  j int;
begin
  set enable_seqscan=off;
  for j in 1..1000 loop
    UPDATE manycombocids set t = 'updated2' where i = j * 10;
  end loop;
end;
$$;
-- Run the cursor to completion. This will encounter the combocids generated by the
-- previous updates, and should correctly see that the updates are not visible to
-- the cursor. (MOVE to keep the expected output at a reasonable size.)
MOVE 9900 FROM c;
FETCH ALL FROM c;
   i   |   i   |         t          
-------+-------+--------------------
  9912 |  9912 | initially inserted
  9913 |  9913 | initially inserted
  9914 |  9914 | initially inserted
  9915 |  9915 | initially inserted
  9916 |  9916 | initially inserted
  9917 |  9917 | initially inserted
  9918 |  9918 | initially inserted
  9919 |  9919 | initially inserted
  9920 |  9920 | initially inserted
  9921 |  9921 | initially inserted
  9922 |  9922 | initially inserted
  9923 |  9923 | initially inserted
  9924 |  9924 | initially inserted
  9925 |  9925 | initially inserted
  9926 |  9926 | initially inserted
  9927 |  9927 | initially inserted
  9928 |  9928 | initially inserted
  9929 |  9929 | initially inserted
  9930 |  9930 | initially inserted
  9931 |  9931 | initially inserted
  9932 |  9932 | initially inserted
  9933 |  9933 | initially inserted
  9934 |  9934 | initially inserted
  9935 |  9935 | initially inserted
  9936 |  9936 | initially inserted
  9937 |  9937 | initially inserted
  9938 |  9938 | initially inserted
  9939 |  9939 | initially inserted
  9940 |  9940 | initially inserted
  9941 |  9941 | initially inserted
  9942 |  9942 | initially inserted
  9943 |  9943 | initially inserted
  9944 |  9944 | initially inserted
  9945 |  9945 | initially inserted
  9946 |  9946 | initially inserted
  9947 |  9947 | initially inserted
  9948 |  9948 | initially inserted
  9949 |  9949 | initially inserted
  9950 |  9950 | initially inserted
  9951 |  9951 | initially inserted
  9952 |  9952 | initially inserted
  9953 |  9953 | initially inserted
  9954 |  9954 | initially inserted
  9955 |  9955 | initially inserted
  9956 |  9956 | initially inserted
  9957 |  9957 | initially inserted
  9958 |  9958 | initially inserted
  9959 |  9959 | initially inserted
  9960 |  9960 | initially inserted
  9961 |  9961 | initially inserted
  9962 |  9962 | initially inserted
  9963 |  9963 | initially inserted
  9964 |  9964 | initially inserted
  9965 |  9965 | initially inserted
  9966 |  9966 | initially inserted
  9967 |  9967 | initially inserted
  9968 |  9968 | initially inserted
  9969 |  9969 | initially inserted
  9970 |  9970 | initially inserted
  9971 |  9971 | initially inserted
  9972 |  9972 | initially inserted
  9973 |  9973 | initially inserted
  9974 |  9974 | initially inserted
  9975 |  9975 | initially inserted
  9976 |  9976 | initially inserted
  9977 |  9977 | initially inserted
  9978 |  9978 | initially inserted
  9979 |  9979 | initially inserted
  9980 |  9980 | initially inserted
  9981 |  9981 | initially inserted
  9982 |  9982 | initially inserted
  9983 |  9983 | initially inserted
  9984 |  9984 | initially inserted
  9985 |  9985 | initially inserted
  9986 |  9986 | initially inserted
  9987 |  9987 | initially inserted
  9988 |  9988 | initially inserted
  9989 |  9989 | initially inserted
  9990 |  9990 | initially inserted
  9991 |  9991 | initially inserted
  9992 |  9992 | initially inserted
  9993 |  9993 | initially inserted
  9994 |  9994 | initially inserted
  9995 |  9995 | initially inserted
  9996 |  9996 | initially inserted
  9997 |  9997 | initially inserted
  9998 |  9998 | initially inserted
  9999 |  9999 | initially inserted
 10000 | 10000 | initially inserted
     1 |     1 | updated1
     2 |     2 | updated1
     3 |     3 | updated1
     4 |     4 | updated1
     5 |     5 | updated1
     6 |     6 | updated1
     7 |     7 | updated1
     8 |     8 | updated1
     9 |     9 | updated1
    10 |    10 | updated1
(99 rows)

rollback;