Hi,
it seems there's something wrong with CTE inlining when there's a view
containing a correlated subquery referencing the CTE. Consider a simple
example like this:
create table results (
id serial primary key,
run text,
tps float4
);
create view results_agg as
with base_tps as (
select run, tps from results
)
select
run,
count(*) as runs,
(select tps from base_tps b where b.run = r.run) AS base_tps
from results r
group by
run
order by
run;
explain SELECT run FROM results_agg ORDER BY 1;
This crashes on this assert in inline_cte():
Assert(context.refcount == 0);
because the refcount value remains 1. There's a backtrace attached.
I don't know why exactly this happens, my knowledge of CTE inlining is
somewhat limited. The counter is clearly out of sync
but a couple more observations:
1) it fails all the way back to PG12, where CTE inlining was added
2) it does not happen if the CTE is defined as MATERIALIZED
QUERY PLAN
-----------------------------------------
Subquery Scan on results_agg
-> Sort
Sort Key: r.run
CTE base_tps
-> Seq Scan on results
-> HashAggregate
Group Key: r.run
-> Seq Scan on results r
(8 rows)
3) without asserts, it seems to work and the query generates this plan
QUERY PLAN
-----------------------------------------
Subquery Scan on results_agg
-> Sort
Sort Key: r.run
-> HashAggregate
Group Key: r.run
-> Seq Scan on results r
(6 rows)
4) it does not seem to happen without the view, i.e. this works
explain
with base_tps as (
select run, tps from results
)
select run from (
select
run,
count(*) as runs,
(select tps from base_tps b where b.run = r.run) AS base_tps
from results r
group by
run
order by
run
) results_agg order by 1;
The difference between plans in (2) and (3) is interesting, because it
seems the CTE got inlined, so why was the refcount not decremented?
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
