答复: a segfault failure of query

The error will occur during execution.
The test results are as follows.

$ psql -p 5432 -d xxxx
Timing is on.
psql (11.3)
Type "help" for help.

#
# show max_parallel_workers_per_gather;
max_parallel_workers_per_gather
---------------------------------
2
(1 row)

Time: 18.140 ms

# explain SELECT
CASE
WHEN field1 = 'TestAl' THEN
'?'
WHEN field1 IN ('T1', 'T2') THEN
'??'
WHEN field1 IN ('S1', 'S2', 'S3', 'S4') THEN
'?'
WHEN field1 IN (
'01',
'02',
'03',
'04',
'05',
'06',
'07',
'08',
'09',
'10',
'11',
'12'
) THEN
'?'
WHEN field1 LIKE 'W__' THEN
'?'
WHEN field1 LIKE '____' THEN
'?'
ELSE
''
END AS "FREQ",
field1 AS "field1"
FROM
TABLE_D
WHERE
1 = 1
AND field2 IN ('field28300001')
AND field3 IN ('field38300000')
AND field4 IN ('2000')
AND field5 IN ('field58300007')
AND field6 IN ('field68300000')
AND field7 IN ('Flxjf')
AND field8 IN ('Ikjfjk')
AND
field9
IN ('field95300000')
AND (
field1 IN (
'01',
'02',
'03',
'04',
'05',
'06',
'07',
'08',
'09',
'10',
'11',
'12'
)
)
ORDER BY 1=1
limit 16 offset 77;

QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------
Limit (cost=38327.74..46084.16 rows=16 width=37)
-> Gather (cost=1000.00..52386.25 rows=106 width=37)
Workers Planned: 2
-> Parallel Append (cost=0.00..51373.82 rows=45 width=37)
-> Parallel Bitmap Heap Scan on TABLE_D_2000 (cost=2555.46..51371.79 rows=44 width=3)
Recheck Cond: (((account)::text = 'field25300001'::text) AND ((entity)::text = 'field35300000'::text))
Filter: (((year)::text = '2000'::text) AND ((bl)::text = 'field58300007'::text) AND ((expandch)::text = 'field65300000'::text) AND ((scenario)::text = 'Flxjf'::text) AND ((vie
w)::text = 'Ikjfjk'::text) AND ((value)::text = 'field95300000'::text) AND ((period)::text = ANY ('{01,02,03,04,05,06,07,08,09,10,11,12}'::text[])))
-> Bitmap Index Scan on TABLE_D_2000_index (cost=0.00..2555.44 rows=64751 width=0)
Index Cond: (((account)::text = 'field25300001'::text) AND ((entity)::text = 'field35300000'::text))
-> Parallel Seq Scan on TABLE_D (cost=0.00..0.00 rows=1 width=118)
Filter: (((account)::text = 'field25300001'::text) AND ((entity)::text = 'field35300000'::text) AND ((year)::text = '2000'::text) AND ((bl)::text = 'field58300007'::text) AND ((exp
andch)::text = 'field65300000'::text) AND ((scenario)::text = 'Flxjf'::text) AND ((view)::text = 'Ikjfjk'::text) AND ((value)::text = 'field95300000'::text) AND ((period)::text = ANY ('{01,02,03,
04,05,06,07,08,09,10,11,12}'::text[])))
(11 rows)

Time: 125.984 ms

#
# SELECT
CASE
WHEN field1 = 'TestAl' THEN
'?'
WHEN field1 IN ('T1', 'T2') THEN
'??'
WHEN field1 IN ('S1', 'S2', 'S3', 'S4') THEN
'?'
WHEN field1 IN (
'01',
'02',
'03',
'04',
'05',
'06',
'07',
'08',
'09',
'10',
'11',
'12'
) THEN
'?'
WHEN field1 LIKE 'W__' THEN
'?'
WHEN field1 LIKE '____' THEN
'?'
ELSE
''
END AS "FREQ",
field1 AS "field1"
FROM
TABLE_D
WHERE
1 = 1
AND field2 IN ('field25300001')
AND field3 IN ('field35300000')
AND field4 IN ('2000')
AND field5 IN ('field58300007')
AND field6 IN ('field65300000')
AND field7 IN ('Flxjf')
AND field8 IN ('Ikjfjk')
AND
field9
IN ('field95300000')
AND (
field1 IN (
'01',
'02',
'03',
'04',
'05',
'06',
'07',
'08',
'09',
'10',
'11',
'12'
)
)
ORDER BY 1=1
limit 16 offset 77;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
Time: 22179.898 ms (00:22.180)
[:@] [08-24.17:52:42]!>
[:@] [08-24.17:52:44]!> \q

After modifying the value of max_parallel_workers_per_gather, SQL execution will not report an error. The test results are as follows

$ psql -p 5432 -d xxxx
Timing is on.
psql (11.3)
Type "help" for help.

[postgres:5432(at)psrstj] [08-24.17:52:49]=#
[postgres:5432(at)psrstj] [08-24.17:52:51]=# set max_parallel_workers_per_gather=0;
SET
Time: 0.431 ms
[postgres:5432(at)psrstj] [08-24.17:52:53]=# explain SELECT
CASE
WHEN field1 = 'TestAl' THEN
'?'
WHEN field1 IN ('T1', 'T2') THEN
'??'
WHEN field1 IN ('S1', 'S2', 'S3', 'S4') THEN
'?'
WHEN field1 IN (
'01',
'02',
'03',
'04',
'05',
'06',
'07',
'08',
'09',
'10',
'11',
'12'
) THEN
'?'
WHEN field1 LIKE 'W__' THEN
'?'
WHEN field1 LIKE '____' THEN
'?'
ELSE
''
END AS "FREQ",
field1 AS "field1"
FROM
TABLE_D
WHERE
1 = 1
AND field2 IN ('field25300001')
AND field3 IN ('field35300000')
AND field4 IN ('2000')
AND field5 IN ('field58300007')
AND field6 IN ('field65300000')
AND field7 IN ('Flxjf')
AND field8 IN ('Ikjfjk')
AND
field9
IN ('field95300000')
AND (
field1 IN (
'01',
'02',
'03',
'04',
'05',
'06',
'07',
'08',
'09',
'10',
'11',
'12'
)
)
ORDER BY 1=1
limit 16 offset 77;

QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------
Limit (cost=38555.41..46566.92 rows=16 width=37)
-> Result (cost=0.00..53076.27 rows=106 width=37)
-> Append (cost=0.00..53072.03 rows=106 width=4)
-> Seq Scan on TABLE_D (cost=0.00..0.00 rows=1 width=118)
Filter: (((account)::text = 'field25300001'::text) AND ((entity)::text = 'field35300000'::text) AND ((year)::text = '2000'::text) AND ((bl)::text = 'field58300007'::text) AND ((exp
andch)::text = 'field65300000'::text) AND ((scenario)::text = 'Flxjf'::text) AND ((view)::text = 'Ikjfjk'::text) AND ((value)::text = 'field95300000'::text) AND ((period)::text = ANY ('{01,02,03,
04,05,06,07,08,09,10,11,12}'::text[])))
-> Bitmap Heap Scan on TABLE_D_2000 (cost=2555.46..53071.50 rows=105 width=3)
Recheck Cond: (((account)::text = 'field25300001'::text) AND ((entity)::text = 'field35300000'::text))
Filter: (((year)::text = '2000'::text) AND ((bl)::text = 'field58300007'::text) AND ((expandch)::text = 'field65300000'::text) AND ((scenario)::text = 'Flxjf'::text) AND ((vie
w)::text = 'Ikjfjk'::text) AND ((value)::text = 'field95300000'::text) AND ((period)::text = ANY ('{01,02,03,04,05,06,07,08,09,10,11,12}'::text[])))
-> Bitmap Index Scan on TABLE_D_2000_index (cost=0.00..2555.44 rows=64751 width=0)
Index Cond: (((account)::text = 'field25300001'::text) AND ((entity)::text = 'field35300000'::text))
(10 rows)

Time: 6.668 ms
#
# SELECT
CASE
WHEN field1 = 'TestAl' THEN
'?'
WHEN field1 IN ('T1', 'T2') THEN
'??'
WHEN field1 IN ('S1', 'S2', 'S3', 'S4') THEN
'?'
WHEN field1 IN (
'01',
'02',
'03',
'04',
'05',
'06',
'07',
'08',
'09',
'10',
'11',
'12'
) THEN
'?'
WHEN field1 LIKE 'W__' THEN
'?'
WHEN field1 LIKE '____' THEN
'?'
ELSE
''
END AS "FREQ",
field1 AS "field1"
FROM
TABLE_D
WHERE
1 = 1
AND field2 IN ('field25300001')
AND field3 IN ('field35300000')
AND field4 IN ('2000')
AND field5 IN ('field58300007')
AND field6 IN ('field65300000')
AND field7 IN ('Flxjf')
AND field8 IN ('Ikjfjk')
AND
field9
IN ('field95300000')
AND (
field1 IN (
'01',
'02',
'03',
'04',
'05',
'06',
'07',
'08',
'09',
'10',
'11',
'12'
)
)
ORDER BY 1=1
limit 16 offset 77;
FREQ | field1
------+--------
? | 02
(1 row)

Time: 146.805 ms

康桥平 Qiaoping Kang
Mobile: 86-13570809194   Phone: 0755-88-670124

-----邮件原件-----
发件人: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
发送时间: 2020年8月10日 21:45
收件人: 康桥平 <KANGQIAOPING754(at)pingan(dot)com(dot)cn>
抄送: pgsql-bugs(at)postgresql(dot)org; 栾长苗 <LUANCHANGMIAO531(at)pingan(dot)com(dot)cn>
主题: Re: a segfault failure of query

=?gb2312?B?v7XHxca9?= <KANGQIAOPING754(at)pingan(dot)com(dot)cn> writes:
> I encountered segmentation fault when executing the sql statement of the query.

Hm...

> #0 pg_detoast_datum_packed (datum=0x0) at fmgr.c:1951
> #1 0x0000000000806fa2 in text_to_cstring (t=0x0) at varlena.c:185
> #2 0x0000000000831845 in FunctionCall1Coll (flinfo=<optimized out>, collation=collation(at)entry=0, arg1=<optimized out>) at fmgr.c:1123
> #3 0x000000000083293a in OutputFunctionCall (flinfo=<optimized
> out>, val=<optimized out>) at fmgr.c:1755

This isn't terribly helpful. It seems that the query has returned a text datum that's actually a null (zero) pointer, but where that came from is impossible to tell at this very late stage of query execution.

> This problem can be temporarily solved after executing the command set max_parallel_workers_per_gather=0.

That's pretty interesting, but again, not very useful for localizing the source of the issue. Parallel query invokes a *lot* of code that is not in the non-parallel path.

The first thing I'd note is that 11.3 is five minor releases ago (and it'll be six minor releases out of date by the end of the week).
So really the *first* thing you ought to do is update to 11.8 to see if this is already fixed.

If it turns out it's not fixed, is there any chance of showing us a self-contained test case?

regards, tom lane

********************************************************************************************************************************
The information in this email is confidential and may be legally privileged. If you have received this email in error or are not the intended recipient, please immediately notify the sender and delete this message from your computer. Any use, distribution, or copying of this email other than by the intended recipient is strictly prohibited. All messages sent to and from us may be monitored to ensure compliance with internal policies and to protect our business.
Emails are not secure and cannot be guaranteed to be error free as they can be intercepted, amended, lost or destroyed, or contain viruses. Anyone who communicates with us by email is taken to accept these risks.

收发邮件者请注意：
本邮件含涉密信息，请保守秘密，若误收本邮件，请务必通知发送人并直接删去，不得使用、传播或复制本邮件。
进出邮件均受到本公司合规监控。邮件可能发生被截留、被修改、丢失、被破坏或包含计算机病毒等不安全情况。
********************************************************************************************************************************