Re: pgsql: Do assorted mop-up in the planner.

Hi Tom,

On Tue, 31 Jan 2023 at 05:43, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Do assorted mop-up in the planner.

This commit is causing occasional Asserts in my testing.
SQL / Backtrace / triaging below.

Program terminated with signal SIGABRT, Aborted.
#0 __GI_raise (sig=sig(at)entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
50 ../sysdeps/unix/sysv/linux/raise.c: No such file or directory.
(gdb) bt
#0 __GI_raise (sig=sig(at)entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
#1 0x00007f6d091d0859 in __GI_abort () at abort.c:79
#2 0x000055bf99f9d962 in ExceptionalCondition
(conditionName=0x55bf9a153280 "bms_is_subset(rinfo->required_relids,
both_input_relids)", fileName=0x55bf9a152faf "relnode.c",
lineNumber=1314) at assert.c:66
#3 0x000055bf99cc3689 in subbuild_joinrel_restrictlist
(root=0x55bf9b35ec08, joinrel=0x55bf9b36bc38,
input_rel=0x55bf9b367420, both_input_relids=0x55bf9b36c7f8,
new_restrictlist=0x0)
at relnode.c:1314
#4 0x000055bf99cc33f9 in build_joinrel_restrictlist
(root=0x55bf9b35ec08, joinrel=0x55bf9b36bc38,
outer_rel=0x55bf9b367420, inner_rel=0x55bf9b3660e0) at relnode.c:1232
#5 0x000055bf99cc2730 in build_join_rel (root=0x55bf9b35ec08,
joinrelids=0x55bf9b36b2a8, outer_rel=0x55bf9b367420,
inner_rel=0x55bf9b3660e0, sjinfo=0x55bf9b368068,
restrictlist_ptr=0x7ffd89ee7560) at relnode.c:771
#6 0x000055bf99c5eedf in make_join_rel (root=0x55bf9b35ec08,
rel1=0x55bf9b367420, rel2=0x55bf9b3660e0) at joinrels.c:756
#7 0x000055bf99c5e3bd in make_rels_by_clause_joins
(root=0x55bf9b35ec08, old_rel=0x55bf9b367420,
other_rels_list=0x55bf9b36b408, other_rels=0x55bf9b36b420) at
joinrels.c:312
#8 0x000055bf99c5dedd in join_search_one_level (root=0x55bf9b35ec08,
level=3) at joinrels.c:123
#9 0x000055bf99c3fbc7 in standard_join_search (root=0x55bf9b35ec08,
levels_needed=3, initial_rels=0x55bf9b36b408) at allpaths.c:3387
#10 0x000055bf99c3fb34 in make_rel_from_joinlist (root=0x55bf9b35ec08,
joinlist=0x55bf9b367f18) at allpaths.c:3318
#11 0x000055bf99c3aabd in make_one_rel (root=0x55bf9b35ec08,
joinlist=0x55bf9b367f18) at allpaths.c:211
#12 0x000055bf99c7aa5e in query_planner (root=0x55bf9b35ec08,
qp_callback=0x55bf99c8112d <standard_qp_callback>,
qp_extra=0x7ffd89ee79b0) at planmain.c:278
#13 0x000055bf99c7d374 in grouping_planner (root=0x55bf9b35ec08,
tuple_fraction=0) at planner.c:1488
#14 0x000055bf99c7ca2f in subquery_planner (glob=0x55bf9b340e18,
parse=0x55bf9b26f5b8, parent_root=0x0, hasRecursion=false,
tuple_fraction=0) at planner.c:1057
#15 0x000055bf99c7b137 in standard_planner (parse=0x55bf9b26f5b8,
query_string=0x55bf9b26de28 "SELECT pg_catalog.avg(NULL::NUMERIC)
OVER (ORDER BY ref_3.schemaname) AS c2\nFROM (SELECT) AS subq_0\n
LEFT JOIN pg_catalog.pg_statio_all_sequences AS ref_3 ON NULL;",
cursorOptions=2048, boundParams=0x0) at planner.c:411

SQL:
SELECT pg_catalog.avg(NULL::NUMERIC) OVER (ORDER BY ref_3.schemaname) AS c2
FROM (SELECT) AS subq_0
LEFT JOIN pg_catalog.pg_statio_all_sequences AS ref_3 ON NULL;

Checking (fb1a59de0c~10) - 8c1cd726c5d997d5d170505ec15a2dc1dfe81d6a - Crash
Checking (fb1a59de0c~11) - 54e72b66ed1a55c2fa558bc60d534bdc61dbb62f - Crash
Checking (fb1a59de0c~12) - 3bef56e11650a33f70adeb6dd442bc2b48bb9b72 - Crash
Checking (fb1a59de0c~13) - b448f1c8d83f8b65e2f0080c556ee21a7076da25 - Crash
Checking (fb1a59de0c~14) - 2489d76c4906f4461a364ca8ad7e0751ead8aa0d - Success
Checking (fb1a59de0c~15) - ec7e053a98f39a9e3c7e6d35f0d2e83933882399 - Success

Thanks to SQLSmith / SQLReduce.

-
Robins Tharakan
Amazon Web Services