I am trying to patch postgres to accept this query. A recursive query
referencing the recursive table twice. By removing some checks it accepts
the query and generates a plan.
### Query
create table base(k1,k2,k3) as (values
(1,null,null),(2,null,null),(3,1,2));
with recursive x(k1,k2,k3) as ( select k1,null::record,null ::record from
base union all select b.k1,x1,x2 from base as b join x as x1 on
x1.k1=b.k2 join x as x2 on x2.k1=b.k3 where b.k2 is not null or b.k3 is
not null) select * from x;
drop table base;
### Plan
CTE Scan on x (cost=7745569.00..11990441.80 rows=212243640 width=68)
CTE x
-> Recursive Union (cost=0.00..7745569.00 rows=212243640 width=60)
-> Seq Scan on base (cost=0.00..30.40 rows=2040 width=4)
-> Merge Join (cost=31081.98..350066.58 rows=21224160 width=6
0)
Merge Cond: (x2.k1 = b.k3)
-> Sort (cost=1868.26..1919.26 rows=20400 width=32)
Sort Key: x2.k1
-> WorkTable Scan on x x2 (cost=0.00..408.00 rows
=20400 width=32)
-> Materialize (cost=29213.72..30254.12 rows=208080 wid
th=36)
-> Sort (cost=29213.72..29733.92 rows=208080 widt
h=36)
Sort Key: b.k3
-> Merge Join (cost=2010.80..5142.20 rows=2
08080 width=36)
Merge Cond: (b.k2 = x1.k1)
-> Sort (cost=142.54..147.64 rows=204
0 width=12)
Sort Key: b.k2
-> Seq Scan on base b (cost=0.0
0..30.40 rows=2040 width=12)
Filter: ((k2 IS NOT NULL) O
R (k3 IS NOT NULL))
-> Sort (cost=1868.26..1919.26 rows=2
0400 width=32)
Sort Key: x1.k1
-> WorkTable Scan on x x1 (cost
=0.00..408.00 rows=20400 width=32)
But when the query is executed looks like the working table can't be read
twice. Then the second join returns empty. Reading the executor of
workingtablescan, i see some notes about a private read pointer and
copying.
It's my first time in the postgres codebase. Can someone point out if the
working_table table can't really be read twice. And if there is similar
code in other executor that allow it.
--
Wesley S. Massuda
