Hello, @Tomas put you in CC as it looks like related to work on fk -> join estimates
i did a tiny bit of testing of our software against the nightly postgresql-9.6
debs from apt.postgresql.org
Specifically against:
ii postgresql-9.6
9.6~~devel~20160428.1605-1~664.git23b09e1.pgdg+1 amd64 object-relational
SQL database, version 9.6 server
ii postgresql-9.6-dbg
9.6~~devel~20160428.1605-1~664.git23b09e1.pgdg+1 amd64 debug symbols for
postgresql-9.6
so autobuilt from last night.
I get postgres consistently to segfault using the following query (trimmed down
to shortest example still triggering the crash)
SELECT 1
FROM ad_model_object mo
LEFT JOIN ad_menu m ON mo.ad_process_id = m.ad_process_id
AND mo.action IN ('P', 'R');
With the trigger being a FK definition from ad_menu.ad_process_id to
ad_process.ad_process_id.
Dropping that fk makes the crash go away.
See attached files for trimmed down table definition to directly reproduce.
Backtrace ends in:
#0 get_leftop (clause=clause@entry=0x5652932e2d98)
at
/build/postgresql-9.6-8aVkeq/postgresql-9.6-9.6~~devel~20160428.1605/build/../src/backend/optimizer/util/clauses.c:212
#1 0x0000565291ec6ba0 in quals_match_foreign_key (root=0x7fca9b3bcba0,
fkrel=0x5652932ab980, foreignrel=0x5652932e77b8,
joinquals=0x7fca9b3bcba0, fkinfo=0x5652932e6ce8)
at
/build/postgresql-9.6-8aVkeq/postgresql-9.6-9.6~~devel~20160428.1605/build/../src/backend/optimizer/path/costsize.c:3961
so probably related to the 'Use Foreign keys to improve joins estimates'
project from Tomas
If you need any more info or testing done just let me know.
Regards,
Stefan
pg9.6-2016-04-29-crash-reproducer.sql
Description: application/sql
Program received signal SIGSEGV, Segmentation fault.
get_leftop (clause=clause@entry=0x5652932e2d98)
at
/build/postgresql-9.6-8aVkeq/postgresql-9.6-9.6~~devel~20160428.1605/build/../src/backend/optimizer/util/clauses.c:212
212
/build/postgresql-9.6-8aVkeq/postgresql-9.6-9.6~~devel~20160428.1605/build/../src/backend/optimizer/util/clauses.c:
No such file or directory.
(gdb) bt
#0 get_leftop (clause=clause@entry=0x5652932e2d98)
at
/build/postgresql-9.6-8aVkeq/postgresql-9.6-9.6~~devel~20160428.1605/build/../src/backend/optimizer/util/clauses.c:212
#1 0x0000565291ec6ba0 in quals_match_foreign_key (root=0x7fca9b3bcba0,
fkrel=0x5652932ab980, foreignrel=0x5652932e77b8,
joinquals=0x7fca9b3bcba0, fkinfo=0x5652932e6ce8)
at
/build/postgresql-9.6-8aVkeq/postgresql-9.6-9.6~~devel~20160428.1605/build/../src/backend/optimizer/path/costsize.c:3961
#2 find_best_foreign_key_quals (fkrel=fkrel@entry=0x5652932ab980,
foreignrel=foreignrel@entry=0x5652932e77b8,
joinquals=joinquals@entry=0x7fca9b3bcba0,
joinqualsbitmap=joinqualsbitmap@entry=0x7ffdd3f0d848, root=0x5652932b0e78)
at
/build/postgresql-9.6-8aVkeq/postgresql-9.6-9.6~~devel~20160428.1605/build/../src/backend/optimizer/path/costsize.c:4110
#3 0x0000565291ec6e6d in clauselist_join_selectivity
(root=root@entry=0x5652932b0e78, joinquals=joinquals@entry=0x7fca9b3bcba0,
jointype=jointype@entry=JOIN_LEFT, sjinfo=sjinfo@entry=0x7fca9b3b0108)
at
/build/postgresql-9.6-8aVkeq/postgresql-9.6-9.6~~devel~20160428.1605/build/../src/backend/optimizer/path/costsize.c:4170
#4 0x0000565291ec743e in calc_joinrel_size_estimate (root=0x5652932b0e78,
outer_rows=1, inner_rows=127, sjinfo=0x7fca9b3b0108,
restrictlist=<optimized out>)
at
/build/postgresql-9.6-8aVkeq/postgresql-9.6-9.6~~devel~20160428.1605/build/../src/backend/optimizer/path/costsize.c:4292
#5 0x0000565291ecb621 in set_joinrel_size_estimates
(root=root@entry=0x5652932b0e78, rel=rel@entry=0x7fca9b3b3a20,
outer_rel=outer_rel@entry=0x5652932ab980,
inner_rel=inner_rel@entry=0x5652932e77b8, sjinfo=sjinfo@entry=0x7fca9b3b0108,
restrictlist=restrictlist@entry=0x7fca9b3bca80)
at
/build/postgresql-9.6-8aVkeq/postgresql-9.6-9.6~~devel~20160428.1605/build/../src/backend/optimizer/path/costsize.c:3840
#6 0x0000565291f00d8a in build_join_rel (root=root@entry=0x5652932b0e78,
joinrelids=joinrelids@entry=0x7fca9b3bc8d0,
outer_rel=outer_rel@entry=0x5652932ab980,
inner_rel=inner_rel@entry=0x5652932e77b8, sjinfo=0x7fca9b3b0108,
restrictlist_ptr=restrictlist_ptr@entry=0x7ffdd3f0d998)
at
/build/postgresql-9.6-8aVkeq/postgresql-9.6-9.6~~devel~20160428.1605/build/../src/backend/optimizer/util/relnode.c:502
#7 0x0000565291ed508d in make_join_rel (root=root@entry=0x5652932b0e78,
rel1=rel1@entry=0x5652932ab980, rel2=rel2@entry=0x5652932e77b8)
at
/build/postgresql-9.6-8aVkeq/postgresql-9.6-9.6~~devel~20160428.1605/build/../src/backend/optimizer/path/joinrels.c:714
#8 0x0000565291ed5abb in make_rels_by_clause_joins (other_rels=<optimized
out>, old_rel=<optimized out>, root=<optimized out>)
at
/build/postgresql-9.6-8aVkeq/postgresql-9.6-9.6~~devel~20160428.1605/build/../src/backend/optimizer/path/joinrels.c:274
#9 join_search_one_level (root=root@entry=0x5652932b0e78, level=level@entry=2)
at
/build/postgresql-9.6-8aVkeq/postgresql-9.6-9.6~~devel~20160428.1605/build/../src/backend/optimizer/path/joinrels.c:96
#10 0x0000565291ec544b in standard_join_search (root=0x5652932b0e78,
levels_needed=6, initial_rels=<optimized out>)
at
/build/postgresql-9.6-8aVkeq/postgresql-9.6-9.6~~devel~20160428.1605/build/../src/backend/optimizer/path/allpaths.c:2148
#11 0x0000565291ec583c in make_one_rel (root=root@entry=0x5652932b0e78,
joinlist=joinlist@entry=0x5652932e5e38)
at
/build/postgresql-9.6-8aVkeq/postgresql-9.6-9.6~~devel~20160428.1605/build/../src/backend/optimizer/path/allpaths.c:175
#12 0x0000565291ee0ff0 in query_planner (root=root@entry=0x5652932b0e78,
tlist=tlist@entry=0x5652932e0938,
qp_callback=qp_callback@entry=0x565291ee1610 <standard_qp_callback>,
qp_extra=qp_extra@entry=0x7ffdd3f0dc60)
at
/build/postgresql-9.6-8aVkeq/postgresql-9.6-9.6~~devel~20160428.1605/build/../src/backend/optimizer/plan/planmain.c:246
#13 0x0000565291ee2d4f in grouping_planner (root=root@entry=0x5652932b0e78,
inheritance_update=inheritance_update@entry=0 '\000',
tuple_fraction=<optimized out>, tuple_fraction@entry=0)
at
/build/postgresql-9.6-8aVkeq/postgresql-9.6-9.6~~devel~20160428.1605/build/../src/backend/optimizer/plan/planner.c:1673
#14 0x0000565291ee5655 in subquery_planner (glob=glob@entry=0x5652932a9000,
parse=parse@entry=0x5652932a8b48,
parent_root=parent_root@entry=0x0, hasRecursion=hasRecursion@entry=0
'\000', tuple_fraction=tuple_fraction@entry=0)
at
/build/postgresql-9.6-8aVkeq/postgresql-9.6-9.6~~devel~20160428.1605/build/../src/backend/optimizer/plan/planner.c:758
#15 0x0000565291ee64bf in standard_planner (parse=0x5652932a8b48,
cursorOptions=256, boundParams=0x0)
at
/build/postgresql-9.6-8aVkeq/postgresql-9.6-9.6~~devel~20160428.1605/build/../src/backend/optimizer/plan/planner.c:307
#16 0x0000565291f75944 in pg_plan_query (querytree=<optimized out>,
cursorOptions=cursorOptions@entry=256, boundParams=boundParams@entry=0x0)
at
/build/postgresql-9.6-8aVkeq/postgresql-9.6-9.6~~devel~20160428.1605/build/../src/backend/tcop/postgres.c:798
#17 0x0000565291f75a24 in pg_plan_queries (querytrees=<optimized out>,
cursorOptions=cursorOptions@entry=256,
boundParams=boundParams@entry=0x0)
at
/build/postgresql-9.6-8aVkeq/postgresql-9.6-9.6~~devel~20160428.1605/build/../src/backend/tcop/postgres.c:857
#18 0x0000565291f76cfa in exec_simple_query (
query_string=0x565293278020 "SELECT mo.action AS TYPE,\n (CASE
mo.action\n", ' ' <repeats 12 times>, "WHEN 'X' THEN mo.ad_form_id\n", ' '
<repeats 12 times>, "WHEN 'P' THEN mo.ad_process_id\n", ' ' <repeats 12 times>,
"WHEN 'R' THEN mo.ad_process_id\n", ' ' <repeats 12 times>, "WHEN 'S' THEN"...)
at
/build/postgresql-9.6-8aVkeq/postgresql-9.6-9.6~~devel~20160428.1605/build/../src/backend/tcop/postgres.c:1022
#19 PostgresMain (argc=<optimized out>, argv=argv@entry=0x565293219bd0,
dbname=0x565293219a88 "obpi96", username=<optimized out>)
at
/build/postgresql-9.6-8aVkeq/postgresql-9.6-9.6~~devel~20160428.1605/build/../src/backend/tcop/postgres.c:4059
#20 0x0000565291cf4b84 in BackendRun (port=0x565293213660)
at
/build/postgresql-9.6-8aVkeq/postgresql-9.6-9.6~~devel~20160428.1605/build/../src/backend/postmaster/postmaster.c:4258
#21 BackendStartup (port=0x565293213660)
at
/build/postgresql-9.6-8aVkeq/postgresql-9.6-9.6~~devel~20160428.1605/build/../src/backend/postmaster/postmaster.c:3932
#22 ServerLoop () at
/build/postgresql-9.6-8aVkeq/postgresql-9.6-9.6~~devel~20160428.1605/build/../src/backend/postmaster/postmaster.c:1690
#23 0x0000565291f14978 in PostmasterMain (argc=5, argv=<optimized out>)
at
/build/postgresql-9.6-8aVkeq/postgresql-9.6-9.6~~devel~20160428.1605/build/../src/backend/postmaster/postmaster.c:1298
#24 0x0000565291cf5d61 in main (argc=5, argv=0x5652931d6350)
at
/build/postgresql-9.6-8aVkeq/postgresql-9.6-9.6~~devel~20160428.1605/build/../src/backend/main/main.c:228
(gdb) q
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
