pg9.6 segfault using simple query (related to use fk for join estimates)

From: Stefan Huehner <stefan(at)huehner(dot)org>
To: pgsql-hackers(at)postgresql(dot)org
Cc: tomas(dot)vondra(at)2ndquadrant(dot)com
Subject: pg9.6 segfault using simple query (related to use fk for join estimates)
Date: 2016-04-29 10:25:31
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

@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

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)

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(at)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.


Attachment Content-Type Size
pg9.6-2016-04-29-crash-reproducer.sql application/x-sql 788 bytes
pg9.6-2016-04-29-crash-backtrace.txt text/plain 7.1 KB


Browse pgsql-hackers by date

  From Date Subject
Next Message Bruce Momjian 2016-04-29 11:08:46 Re: Detrimental performance impact of ringbuffers on performance
Previous Message Craig Ringer 2016-04-29 07:40:57 Re: Timeline following for logical slots