Hi All,
We recently upgraded to Postgresql 10.4 from 9.6.7.
We allow some user querying against the database and a user wrote sql that would repeatedly cause a segmentation fault. We can’t share the data but I will show what I can.
Here is an example from the postgres logs with the sql simplified but still throwing the error. It is not very well written but we can’t have a bad query bring down the instance.
<2018-06-12 08:59:28 PDT [9109]: [38-1] db=, user=, host=> LOG: server process (PID 11471) was terminated by signal 11: Segmentation fault
<2018-06-12 08:59:28 PDT [9109]: [39-1] db=, user=, host=> DETAIL: Failed process was running: SELECT p.vital_status, c.addr_at_dx_street_name
FROM ctc c
INNER JOIN patient p ON c.pat_id = p.pat_id
LEFT JOIN ctc_registry cr ON c.ctc_id = cr.ctc_id
LEFT JOIN facility_admission fa ON fa.ctc_id = cr.ctc_id AND fa.fac_id = cr.follow_up_hospital
LEFT JOIN patient_reference_id pr ON pr.pat_id = p.pat_id AND pr.fac_id = cr.follow_up_hospital
WHERE
c.primary_site between 'C500' and 'C509'
AND c.date_of_diagnosis_mm between case when c.date_of_diagnosis_yyyy = 2006 then 7 else 1 end and case when c.date_of_diagnosis_yyyy = 2015 then 6 else 12 end
AND (c.sequence_number = '00'
OR (c.sequence_number = '02'
AND p.pat_id in (select pat_id
from ctc
where behavior_icdo3 = 2
and sequence_number = '01'
and deleted = 0
and pat_id in (select pat_id
from ctc
where deleted = 0)))
)
;
We changed max_parallel_workers_per_gather to 0 from the default of 2 and the sql runs fine. I attached the explain plans with parallel turned on and off to see if that helps.
We would obviously like to be able to run the system with parallel turned on.
Thanks,
Manfred