pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedSegmentation fault with parallelism PG 10.4
7+ messages / 2 participants
[nested] [flat]
* Segmentation fault with parallelism PG 10.4
@ 2018-06-12 17:36 Heinemann, Manfred (IMS) <HeinemannM@imsweb.com>
0 siblings, 1 reply; 7+ messages in thread
From: Heinemann, Manfred (IMS) @ 2018-06-12 17:36 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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.
Attachments:
segmentation_query_parallel.txt (text/plain, 3.5K) download
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)
segmentation_query_no_parallel.txt (text/plain, 3.7K) download
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)
* Re: Segmentation fault with parallelism PG 10.4
@ 2018-06-12 17:45 Tom Lane <tgl@sss.pgh.pa.us>
parent: Heinemann, Manfred (IMS) <HeinemannM@imsweb.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2018-06-12 17:45 UTC (permalink / raw)
To: Heinemann, Manfred (IMS) <HeinemannM@imsweb.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
"Heinemann, Manfred (IMS)" <HeinemannM@imsweb.com> writes:
> 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.
The info you've provided is not of much help, but if you could show
a stack trace from the point of the segfault, that might narrow
things down.
https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* RE: Segmentation fault with parallelism PG 10.4
@ 2018-06-12 20:15 Heinemann, Manfred (IMS) <HeinemannM@imsweb.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Heinemann, Manfred (IMS) @ 2018-06-12 20:15 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
This is what I see in a stack trace
Program received signal SIGSEGV, Segmentation fault.
0x000000000084dcf3 in pglz_decompress ()
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, June 12, 2018 1:45 PM
To: Heinemann, Manfred (IMS) <HeinemannM@imsweb.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Segmentation fault with parallelism PG 10.4
"Heinemann, Manfred (IMS)" <HeinemannM@imsweb.com> writes:
> 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.
The info you've provided is not of much help, but if you could show a stack trace from the point of the segfault, that might narrow things down.
https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend
regards, tom lane
________________________________
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* RE: Segmentation fault with parallelism PG 10.4
@ 2018-06-19 14:49 Heinemann, Manfred (IMS) <HeinemannM@imsweb.com>
parent: Heinemann, Manfred (IMS) <HeinemannM@imsweb.com>
0 siblings, 1 reply; 7+ messages in thread
From: Heinemann, Manfred (IMS) @ 2018-06-19 14:49 UTC (permalink / raw)
To: Heinemann, Manfred (IMS) <HeinemannM@imsweb.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Is this stack trace helpful at all?
Is there anything else I could do to investigate?
Thanks,
Manfred
>This is what I see in a stack trace
>
>Program received signal SIGSEGV, Segmentation fault.
>0x000000000084dcf3 in pglz_decompress ()
>
>>-----Original Message-----
>>From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
>>Sent: Tuesday, June 12, 2018 1:45 PM
>>To: Heinemann, Manfred (IMS) <HeinemannM@imsweb.com>
>>Cc: pgsql-admin@lists.postgresql.org
>>Subject: Re: Segmentation fault with parallelism PG 10.4
>>
>>"Heinemann, Manfred (IMS)" <HeinemannM@imsweb.com> writes:
>>> 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.
>>
>>The info you've provided is not of much help, but if you could show a stack trace from the point of the segfault, that might narrow things down.
>>
>>https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend
>>
>>regards, tom lane
________________________________
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.
________________________________
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Segmentation fault with parallelism PG 10.4
@ 2018-06-19 15:14 Tom Lane <tgl@sss.pgh.pa.us>
parent: Heinemann, Manfred (IMS) <HeinemannM@imsweb.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2018-06-19 15:14 UTC (permalink / raw)
To: Heinemann, Manfred (IMS) <HeinemannM@imsweb.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
"Heinemann, Manfred (IMS)" <HeinemannM@imsweb.com> writes:
> Is this stack trace helpful at all?
No, because it isn't actually a stack trace, just the top frame. See
https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* RE: Segmentation fault with parallelism PG 10.4
@ 2018-07-02 18:27 Heinemann, Manfred (IMS) <HeinemannM@imsweb.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Heinemann, Manfred (IMS) @ 2018-07-02 18:27 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Here is what I get for the stack trace, is it more helpful?
Program received signal SIGSEGV, Segmentation fault.
0x000000000084dcb0 in pglz_decompress ()
#0 0x000000000084dcb0 in pglz_decompress ()
#1 0x00000000004b8d2f in toast_decompress_datum ()
#2 0x0000000000793ce0 in numeric_eq ()
#3 0x00000000005e68eb in ExecInterpExpr ()
#4 0x00000000005ef589 in ExecScan ()
#5 0x0000000000608784 in ExecNestLoop ()
#6 0x00000000005fc6b5 in ExecGather ()
#7 0x00000000005feb35 in ExecHashJoin ()
#8 0x00000000006086d4 in ExecNestLoop ()
#9 0x00000000005e9b02 in standard_ExecutorRun ()
#10 0x000000000071305b in PortalRunSelect ()
#11 0x00000000007142d1 in PortalRun ()
#12 0x000000000071038b in exec_simple_query ()
#13 0x0000000000711589 in PostgresMain ()
#14 0x0000000000476399 in ServerLoop ()
#15 0x00000000006a9acc in PostmasterMain ()
#16 0x000000000062b58c in main ()
Thanks,
Manfred
>"Heinemann, Manfred (IMS)" <HeinemannM@imsweb.com> writes:
>> Is this stack trace helpful at all?
>
>No, because it isn't actually a stack trace, just the top frame. See
>
>https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend
>
>regards, tom lane
________________________________
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Segmentation fault with parallelism PG 10.4
@ 2018-07-04 20:55 Tom Lane <tgl@sss.pgh.pa.us>
parent: Heinemann, Manfred (IMS) <HeinemannM@imsweb.com>
0 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2018-07-04 20:55 UTC (permalink / raw)
To: Heinemann, Manfred (IMS) <HeinemannM@imsweb.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
"Heinemann, Manfred (IMS)" <HeinemannM@imsweb.com> writes:
> Here is what I get for the stack trace, is it more helpful?
> Program received signal SIGSEGV, Segmentation fault.
> 0x000000000084dcb0 in pglz_decompress ()
> #0 0x000000000084dcb0 in pglz_decompress ()
> #1 0x00000000004b8d2f in toast_decompress_datum ()
> #2 0x0000000000793ce0 in numeric_eq ()
> #3 0x00000000005e68eb in ExecInterpExpr ()
> #4 0x00000000005ef589 in ExecScan ()
> #5 0x0000000000608784 in ExecNestLoop ()
> #6 0x00000000005fc6b5 in ExecGather ()
> #7 0x00000000005feb35 in ExecHashJoin ()
> #8 0x00000000006086d4 in ExecNestLoop ()
> #9 0x00000000005e9b02 in standard_ExecutorRun ()
> #10 0x000000000071305b in PortalRunSelect ()
> #11 0x00000000007142d1 in PortalRun ()
> #12 0x000000000071038b in exec_simple_query ()
> #13 0x0000000000711589 in PostgresMain ()
> #14 0x0000000000476399 in ServerLoop ()
> #15 0x00000000006a9acc in PostmasterMain ()
> #16 0x000000000062b58c in main ()
Hmm. So (1) apparently we have some corrupt data in a numeric column,
and (2) it looks like the crash is happening in a parallel leader
process, not a worker process, which makes it pretty mystifying why
it's connected to parallelism at all.
But we're not going to be able to get far with that amount of info.
Is there any chance of extracting a self-contained test case?
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2018-07-04 20:55 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-06-12 17:36 Segmentation fault with parallelism PG 10.4 Heinemann, Manfred (IMS) <HeinemannM@imsweb.com>
2018-06-12 17:45 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-06-12 20:15 ` Heinemann, Manfred (IMS) <HeinemannM@imsweb.com>
2018-06-19 14:49 ` Heinemann, Manfred (IMS) <HeinemannM@imsweb.com>
2018-06-19 15:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-07-02 18:27 ` Heinemann, Manfred (IMS) <HeinemannM@imsweb.com>
2018-07-04 20:55 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox