Re: SEARCH and CYCLE clauses

Hi

út 22. 9. 2020 v 20:01 odesílatel Peter Eisentraut <
peter(dot)eisentraut(at)2ndquadrant(dot)com> napsal:

> I have implemented the SEARCH and CYCLE clauses.
>
> This is standard SQL syntax attached to a recursive CTE to compute a
> depth- or breadth-first ordering and cycle detection, respectively.
> This is just convenience syntax for what you can already do manually.
> The original discussion about recursive CTEs briefly mentioned these as
> something to do later but then it was never mentioned again.
>
> SQL specifies these in terms of syntactic transformations, and so that's
> how I have implemented them also, mainly in the rewriter.
>
> I have successfully tested this against examples I found online that
> were aimed at DB2.
>
> The contained documentation and the code comment in rewriteHandler.c
> explain the details.
>

I am playing with this patch. It looks well, but I found some issues
(example is from attached data.sql)

WITH recursive destinations (departure, arrival, connections, cost) AS
(SELECT f.departure, f.arrival, 0, price
FROM flights f
WHERE f.departure = 'New York'
UNION ALL
SELECT r.departure, b.arrival, r.connections + 1,
r.cost + b.price
FROM destinations r, flights b
WHERE r.arrival = b.departure) cycle departure, arrival set
is_cycle to true default false using path

SELECT *
FROM destinations ;
;

The result is correct. When I tried to use UNION instead UNION ALL, the pg
crash

Program received signal SIGABRT, Aborted.
0x00007f761338ebc5 in raise () from /lib64/libc.so.6
(gdb) bt
#0 0x00007f761338ebc5 in raise () from /lib64/libc.so.6
#1 0x00007f76133778a4 in abort () from /lib64/libc.so.6
#2 0x000000000090e7eb in ExceptionalCondition (conditionName=<optimized
out>, errorType=<optimized out>, fileName=<optimized out>,
lineNumber=<optimized out>) at assert.c:67
#3 0x00000000007205e7 in generate_setop_grouplist
(targetlist=targetlist(at)entry=0x7f75fce5d018, op=<optimized out>,
op=<optimized out>)
at prepunion.c:1412
#4 0x00000000007219d0 in generate_recursion_path
(pTargetList=0x7fff073ee728, refnames_tlist=<optimized out>, root=0xf90bd8,
setOp=0xf90840)
at prepunion.c:502
#5 plan_set_operations (root=0xf90bd8) at prepunion.c:156
#6 0x000000000070f79b in grouping_planner (root=0xf90bd8,
inheritance_update=false, tuple_fraction=<optimized out>) at planner.c:1886
#7 0x0000000000712ea7 in subquery_planner (glob=<optimized out>,
parse=<optimized out>, parent_root=<optimized out>, hasRecursion=<optimized
out>,
tuple_fraction=0) at planner.c:1015
#8 0x000000000071a614 in SS_process_ctes (root=0xf7abd8) at subselect.c:952
#9 0x00000000007125d4 in subquery_planner (glob=glob(at)entry=0xf8a010,
parse=parse(at)entry=0xf6cf20, parent_root=parent_root(at)entry=0x0,
hasRecursion=hasRecursion(at)entry=false,
tuple_fraction=tuple_fraction(at)entry=0) at planner.c:645
#10 0x000000000071425b in standard_planner (parse=0xf6cf20,
query_string=<optimized out>, cursorOptions=256, boundParams=<optimized
out>)
at planner.c:405
#11 0x00000000007e5f68 in pg_plan_query (querytree=0xf6cf20,
query_string=query_string(at)entry=0xea6370 "WITH recursive destinations
(departure, arrival, connections, cost) AS \n (SELECT f.departure,
f.arrival, 0, price\n", ' ' <repeats 12 times>, "FROM flights f \n", ' '
<repeats 12 times>, "WHERE f.departure = 'New York' \n UNION "...,
cursorOptions=cursorOptions(at)entry=256, boundParams=boundParams(at)entry=0x0)
at postgres.c:875
#12 0x00000000007e6061 in pg_plan_queries (querytrees=0xf8b690,
query_string=query_string(at)entry=0xea6370 "WITH recursive destinations
(departure, arrival, connections, cost) AS \n (SELECT f.departure,
f.arrival, 0, price\n", ' ' <repeats 12 times>, "FROM flights f \n", ' '
<repeats 12 times>, "WHERE f.departure = 'New York' \n UNION "...,
cursorOptions=cursorOptions(at)entry=256, boundParams=boundParams(at)entry=0x0)
at postgres.c:966
#13 0x00000000007e63b8 in exec_simple_query (
query_string=0xea6370 "WITH recursive destinations (departure, arrival,
connections, cost) AS \n (SELECT f.departure, f.arrival, 0, price\n", '
' <repeats 12 times>, "FROM flights f \n", ' ' <repeats 12 times>, "WHERE
f.departure = 'New York' \n UNION "...) at postgres.c:1158
#14 0x00000000007e81e4 in PostgresMain (argc=<optimized out>,
argv=<optimized out>, dbname=<optimized out>, username=<optimized out>) at
postgres.c:4309
#15 0x00000000007592b9 in BackendRun (port=0xecaf20) at postmaster.c:4541
#16 BackendStartup (port=0xecaf20) at postmaster.c:4225
#17 ServerLoop () at postmaster.c:1742
#18 0x000000000075a0ed in PostmasterMain (argc=<optimized out>,
argv=0xea0c90) at postmaster.c:1415
#19 0x00000000004832ec in main (argc=3, argv=0xea0c90) at main.c:209

looks so clause USING in cycle detection is unsupported for DB2 and Oracle
- the examples from these databases doesn't work on PG without modifications

Regards

Pavel

>
> --
> Peter Eisentraut http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>