|
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 Information in this e-mail may be confidential. It is intended only for the addressee(s) identified above. If you are not the addressee(s), or an employee or agent of the addressee(s), please note that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender of the error. |
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=215599.42..373713.81 rows=12203 width=16)
-> Hash Join (cost=215598.99..367613.65 rows=12203 width=27)
Hash Cond: (c.pat_id = p.pat_id)
Join Filter: (((c.sequence_number)::text = '00'::text) OR (((c.sequence_number)::text = '02'::text) AND (hashed SubPlan 1)))
-> Gather (cost=4410.46..151621.59 rows=12598 width=27)
Workers Planned: 2
-> Nested Loop Left Join (cost=3410.46..149361.79 rows=5249 width=27)
-> Nested Loop Left Join (cost=3410.03..145921.17 rows=5249 width=33)
-> Parallel Bitmap Heap Scan on ctc c (cost=3409.61..125274.28 rows=5249 width=28)
Recheck Cond: (((primary_site)::text >= 'C500'::text) AND ((primary_site)::text <= 'C509'::text))
Filter: ((((sequence_number)::text = '00'::text) OR ((sequence_number)::text = '02'::text)) AND (date_of_diagnosis_mm >= (CASE WHEN (date_of_diagnosis_yyyy = '2006'::numeric) THEN 7 ELSE 1 END)::numeric) AND (date_of_diagnosis_mm <= (CASE WHEN (date_of_diagnosis_yyyy = '2015'::numeric) THEN 6 ELSE 12 END)::numeric))
-> Bitmap Index Scan on x_ctc_p_site (cost=0.00..3406.46 rows=162203 width=0)
Index Cond: (((primary_site)::text >= 'C500'::text) AND ((primary_site)::text <= 'C509'::text))
-> Index Scan using ctc_registry_pkey on ctc_registry cr (cost=0.43..3.93 rows=1 width=11)
Index Cond: (c.ctc_id = ctc_id)
-> Index Scan using xie2facility_admission on facility_admission fa (cost=0.43..0.65 rows=1 width=11)
Index Cond: (ctc_id = cr.ctc_id)
Filter: (fac_id = cr.follow_up_hospital)
-> Hash (cost=41948.71..41948.71 rows=896171 width=9)
-> Seq Scan on patient p (cost=0.00..41948.71 rows=896171 width=9)
SubPlan 1
-> Gather (cost=1000.43..153630.62 rows=12424 width=6)
Workers Planned: 2
-> Nested Loop Semi Join (cost=0.43..151388.22 rows=5177 width=6)
-> Parallel Seq Scan on ctc (cost=0.00..120343.49 rows=5177 width=6)
Filter: ((NOT deleted) AND (behavior_icdo3 = '2'::numeric) AND ((sequence_number)::text = '01'::text))
-> Index Scan using xie2ctc on ctc ctc_1 (cost=0.43..8.60 rows=1 width=6)
Index Cond: (pat_id = ctc.pat_id)
Filter: (NOT deleted)
-> Index Only Scan using idx_pri_pifi on patient_reference_id pr (cost=0.43..0.49 rows=1 width=11)
Index Cond: ((pat_id = p.pat_id) AND (fac_id = cr.follow_up_hospital))
(31 rows)
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=387141.77..427213.64 rows=12203 width=16)
-> Nested Loop Left Join (cost=387141.34..421113.48 rows=12203 width=27)
-> Hash Right Join (cost=387140.91..413114.65 rows=12203 width=33)
Hash Cond: (cr.ctc_id = c.ctc_id)
-> Seq Scan on ctc_registry cr (cost=0.00..21852.25 rows=1066525 width=11)
-> Hash (cost=386988.37..386988.37 rows=12203 width=28)
-> Hash Join (cost=257008.52..386988.37 rows=12203 width=28)
Hash Cond: (c.pat_id = p.pat_id)
Join Filter: (((c.sequence_number)::text = '00'::text) OR (((c.sequence_number)::text = '02'::text) AND (hashed SubPlan 1)))
-> Bitmap Heap Scan on ctc c (cost=3409.61..128585.92 rows=12598 width=28)
Recheck Cond: (((primary_site)::text >= 'C500'::text) AND ((primary_site)::text <= 'C509'::text))
Filter: ((((sequence_number)::text = '00'::text) OR ((sequence_number)::text = '02'::text)) AND (date_of_diagnosis_mm >= (CASE WHEN (date_of_diagnosis_yyyy = '2006'::numeric) THEN 7 ELSE 1 END)::numeric) AND (date_of_diagnosis_mm <= (CASE WHEN (date_of_diagnosis_yyyy = '2015'::numeric) THEN 6 ELSE 12 END)::numeric))
-> Bitmap Index Scan on x_ctc_p_site (cost=0.00..3406.46 rows=162203 width=0)
Index Cond: (((primary_site)::text >= 'C500'::text) AND ((primary_site)::text <= 'C509'::text))
-> Hash (cost=41948.71..41948.71 rows=896171 width=9)
-> Seq Scan on patient p (cost=0.00..41948.71 rows=896171 width=9)
SubPlan 1
-> Nested Loop Semi Join (cost=2130.07..196041.01 rows=12424 width=6)
-> Bitmap Heap Scan on ctc (cost=2129.64..121538.44 rows=12424 width=6)
Recheck Cond: (behavior_icdo3 = '2'::numeric)
Filter: ((NOT deleted) AND ((sequence_number)::text = '01'::text))
-> Bitmap Index Scan on xie4ctc (cost=0.00..2126.54 rows=114948 width=0)
Index Cond: (behavior_icdo3 = '2'::numeric)
-> Index Scan using xie2ctc on ctc ctc_1 (cost=0.43..8.60 rows=1 width=6)
Index Cond: (pat_id = ctc.pat_id)
Filter: (NOT deleted)
-> Index Scan using xie2facility_admission on facility_admission fa (cost=0.43..0.65 rows=1 width=11)
Index Cond: (ctc_id = cr.ctc_id)
Filter: (fac_id = cr.follow_up_hospital)
-> Index Only Scan using idx_pri_pifi on patient_reference_id pr (cost=0.43..0.49 rows=1 width=11)
Index Cond: ((pat_id = p.pat_id) AND (fac_id = cr.follow_up_hospital))
(31 rows)
