[Tomas Vondra <tomas.vondra@enterprisedb.com>]

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

Attachments:

  test.sql (application/sql, 710B) download

backtrace.txt (text/plain, 3.0K) download
Missing separate debuginfos, use: dnf debuginfo-install libgcc-11.2.1-9.fc34.x86_64
(gdb) bt
#0  0x00007159d9be82a2 in raise () from /lib64/libc.so.6
#1  0x00007159d9bd18a4 in abort () from /lib64/libc.so.6
#2  0x000000000096d3fa in ExceptionalCondition (conditionName=conditionName@entry=0xae4ed3 "context.refcount == 0", errorType=errorType@entry=0x9c3017 "FailedAssertion", fileName=fileName@entry=0xae4da6 "subselect.c", lineNumber=lineNumber@entry=1166) at assert.c:69
#3  0x000000000078acda in inline_cte (cte=0x134e240, root=0x1242298) at subselect.c:1166
#4  SS_process_ctes (root=root@entry=0x1242298) at subselect.c:963
#5  0x0000000000782fb1 in subquery_planner (glob=0x134dbc0, parse=parse@entry=0x134daa8, parent_root=parent_root@entry=0x13491e8, hasRecursion=hasRecursion@entry=false, tuple_fraction=0) at planner.c:650
#6  0x0000000000752afe in set_subquery_pathlist (rte=<optimized out>, rti=<optimized out>, rel=<optimized out>, root=<optimized out>) at allpaths.c:2572
#7  set_rel_size (root=<optimized out>, rel=<optimized out>, rti=<optimized out>, rte=<optimized out>) at allpaths.c:424
#8  0x0000000000755080 in set_base_rel_sizes (root=<optimized out>) at allpaths.c:325
#9  make_one_rel (root=root@entry=0x13491e8, joinlist=joinlist@entry=0x134e078) at allpaths.c:187
#10 0x000000000077b4c2 in query_planner (root=root@entry=0x13491e8, qp_callback=qp_callback@entry=0x77c450 <standard_qp_callback>, qp_extra=qp_extra@entry=0x7fff5a500ba0) at planmain.c:276
#11 0x0000000000781122 in grouping_planner (root=<optimized out>, tuple_fraction=<optimized out>) at planner.c:1467
#12 0x00000000007838a1 in subquery_planner (glob=glob@entry=0x134dbc0, parse=parse@entry=0x1241818, parent_root=parent_root@entry=0x0, hasRecursion=hasRecursion@entry=false, tuple_fraction=tuple_fraction@entry=0) at planner.c:1044
#13 0x0000000000783ee3 in standard_planner (parse=0x1241818, query_string=<optimized out>, cursorOptions=2048, boundParams=<optimized out>) at planner.c:406
#14 0x000000000084e7a8 in pg_plan_query (querytree=0x1241818, query_string=query_string@entry=0x12407d0 "SELECT run FROM results_agg ORDER BY 1;", cursorOptions=cursorOptions@entry=2048, boundParams=boundParams@entry=0x0) at postgres.c:883
#15 0x000000000084e8a1 in pg_plan_queries (querytrees=0x134bf00, query_string=query_string@entry=0x12407d0 "SELECT run FROM results_agg ORDER BY 1;", cursorOptions=cursorOptions@entry=2048, boundParams=boundParams@entry=0x0) at postgres.c:975
#16 0x000000000084ebab in exec_simple_query (query_string=0x12407d0 "SELECT run FROM results_agg ORDER BY 1;") at postgres.c:1169
#17 0x00000000008506df in PostgresMain (dbname=<optimized out>, username=<optimized out>) at postgres.c:4542
#18 0x00000000007c1d12 in BackendRun (port=0x1265e90, port=0x1265e90) at postmaster.c:4491
#19 BackendStartup (port=0x1265e90) at postmaster.c:4219
#20 ServerLoop () at postmaster.c:1793
#21 0x00000000007c2c20 in PostmasterMain (argc=argc@entry=3, argv=argv@entry=0x123a3b0) at postmaster.c:1465
#22 0x0000000000510725 in main (argc=3, argv=0x123a3b0) at main.c:202