EXPLAIN(VERBOSE) to CTE with SEARCH BREADTH FIRST fails

torikoshia <torikoshia@oss.nttdata.com>

From: torikoshia <torikoshia@oss.nttdata.com>
To: Pgsql Hackers <pgsql-hackers@postgresql.org>
Date: 2021-09-07T03:41:02Z
Lists: pgsql-hackers
Hi,

While working on [1], we found that EXPLAIN(VERBOSE) to CTE with SEARCH 
BREADTH FIRST ends up ERROR.

This can be reproduced at the current HEAD(4c3478859b7359912d7):

   =# create table graph0( f int, t int, label text);
   CREATE TABLE

   =# insert into graph0 values (1, 2, 'arc 1 -> 2'),(1, 3, 'arc 1 -> 
3'),(2, 3, 'arc 2 -> 3'),(1, 4, 'arc 1 -> 4'),(4, 5, 'arc 4 -> 5');
   INSERT 0 5

   =# explain(verbose) with recursive search_graph(f, t, label) as (
       select * from graph0 g
       union all
       select g.*
       from graph0 g, search_graph sg
       where g.f = sg.t
   ) search breadth first by f, t set seq
   select * from search_graph order by seq;
   ERROR:  failed to find plan for CTE sg

Is this a bug?


[1] 
https://www.postgresql.org/message-id/flat/cf8501bcd95ba4d727cbba886ba9eea8@oss.nttdata.com

Regards,

--
Atsushi Torikoshi
NTT DATA CORPORATION



Commits

  1. Fix EXPLAIN of SEARCH BREADTH FIRST queries some more.

  2. Fix EXPLAIN to handle SEARCH BREADTH FIRST queries.

  3. Message style improvements