src/test/regress/expected/catcache_optimizer.out

-- Test abort transaction should invalidate reader gang's cat cache
-- Discussion: https://groups.google.com/a/greenplum.org/g/gpdb-dev/c/u3-D7isdvmM
set optimizer_force_multistage_agg = 1;
SET optimizer_trace_fallback = on;
create table dml_14027_union_s (a int not null, b numeric default 10.00) distributed by (a) partition by range(b);
create table dml_14027_union_s_1_prt_2 partition of dml_14027_union_s for values from (1) to (1001);
NOTICE:  table has parent, setting distribution columns to match parent table
create table dml_14027_union_s_1_prt_def partition of dml_14027_union_s default;
NOTICE:  table has parent, setting distribution columns to match parent table
insert into dml_14027_union_s select generate_series(1,1), generate_series(1,1);
analyze dml_14027_union_s;
begin;
drop table dml_14027_union_s_1_prt_def;
explain select count(distinct(b)) from dml_14027_union_s;
                                                  QUERY PLAN                                                   
---------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=0.00..431.00 rows=1 width=8)
   ->  Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..431.00 rows=1 width=5)
         ->  GroupAggregate  (cost=0.00..431.00 rows=1 width=5)
               Group Key: b
               ->  Sort  (cost=0.00..431.00 rows=1 width=5)
                     Sort Key: b
                     ->  Redistribute Motion 3:3  (slice2; segments: 3)  (cost=0.00..431.00 rows=1 width=5)
                           Hash Key: b
                           ->  Streaming HashAggregate  (cost=0.00..431.00 rows=1 width=5)
                                 Group Key: b
                                 ->  Dynamic Seq Scan on dml_14027_union_s  (cost=0.00..431.00 rows=1 width=5)
                                       Number of partitions to scan: 1 (out of 1)
 Optimizer: Pivotal Optimizer (GPORCA)
(13 rows)

select count(distinct(b)) from dml_14027_union_s;
 count 
-------
     1
(1 row)

rollback;
explain update dml_14027_union_s set a = (select null union select null)::numeric;
INFO:  GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL:  Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(update) on partitioned tables
                                                       QUERY PLAN                                                       
------------------------------------------------------------------------------------------------------------------------
 Update on dml_14027_union_s  (cost=0.07..2.19 rows=0 width=0)
   Update on dml_14027_union_s_1_prt_2 dml_14027_union_s_1
   Update on dml_14027_union_s_1_prt_def dml_14027_union_s_2
   InitPlan 1 (returns $0)  (slice2)
     ->  Unique  (cost=0.06..0.07 rows=2 width=32)
           Group Key: (NULL::text)
           ->  Sort  (cost=0.06..0.07 rows=2 width=32)
                 Sort Key: (NULL::text)
                 ->  Append  (cost=0.00..0.05 rows=2 width=32)
                       ->  Result  (cost=0.00..0.01 rows=1 width=32)
                       ->  Result  (cost=0.00..0.01 rows=1 width=32)
   ->  Explicit Redistribute Motion 3:3  (slice1; segments: 3)  (cost=0.00..2.12 rows=4 width=36)
         ->  Split  (cost=0.00..2.04 rows=4 width=36)
               ->  Append  (cost=0.00..2.04 rows=2 width=36)
                     ->  Seq Scan on dml_14027_union_s_1_prt_2 dml_14027_union_s_1  (cost=0.00..1.02 rows=1 width=36)
                     ->  Seq Scan on dml_14027_union_s_1_prt_def dml_14027_union_s_2  (cost=0.00..1.02 rows=1 width=36)
 Optimizer: Postgres query optimizer
(17 rows)

-- Should not raise error due to stale catcache in reader gang.
-- eg: ERROR: expected partdefid 134733, but got 0
update dml_14027_union_s set a = (select null union select null)::numeric;
INFO:  GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL:  Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(update) on partitioned tables
ERROR:  null value in column "a" of relation "dml_14027_union_s_1_prt_2" violates not-null constraint  (seg0 127.0.0.1:7002 pid=1872432)
DETAIL:  Failing row contains (null, 1).
drop table dml_14027_union_s;
reset optimizer_force_multistage_agg;
reset optimizer_trace_fallback;