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-relationalSQL database, version 9.6 server
ii postgresql-9.6-dbg 9.6~~devel~20160428.1605-1~664.git23b09e1.pgdg+1 amd64 debug symbols
forpostgresql-9.6
so autobuilt from last night.
I get postgres consistently to segfault using the following query (trimmed down to shortest example still triggering
thecrash)
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