Обсуждение: BUG #18108: server process was terminated by signal 11: Segmentation fault

The following bug has been logged on the website:

Bug reference:      18108
Logged by:          xianglinsao1
Email address:      liuqi7850@126.com
PostgreSQL version: 15.2
Operating system:   redhat 7.9
Description:

Recently I executed a merge SQL with over 1.5W rows inside PG15.2, which
caused the database to report a crash error and then terminated all database
sessions，but changing the merge to insert syntax worked fine, I'm trying to
understand where this is and if I need to change the settings.
Below are the DB and OS logs(ID or address data was changed to unreal):

OS LOG：
       Sep 14 09:35:29 localhost kernel: postgres[155588]: segfault at
1857ffc ip 00008afe187b4716 sp 0000fdh87ab9818 error 4 in
libc-2.17.so[7efe1dsf65e000+1c4000]
       Sep 14 09:35:29 localhost abrt-hook-ccpp: Process 155588 (postgres)
of user 1000 killed by SIGSEGV - dumping core
       Sep 14 09:35:37 localhost abrt-server: Package 'postgresql15-server'
isn't signed with proper key
       Sep 14 09:35:37 localhost abrt-server: 'post-create' on
'/xxxxxx/ccpp-2023-09-14-09:35:29-145588' exited with 1
       Sep 14 09:35:37 localhost abrt-server: Deleting problem directory
'/var/spool/abrt/ccpp-2023-09-14-09:35:29-144588'

DB LOG：
       2023-09-14 09:35:37.490 CST [155588] LOG:  server process (PID
155588) was terminated by signal 11: Segmentation fault
       2023-09-14 09:35:37.490 CST [155588] DETAIL:  Failed process was
running: merge into XXXX d using( ******* ) s
              WHEN NOT MATCHED THEN INSERT （********） VALUES (*******)
              WHEN MATCHED AND (******) THEN UPDATE SET *******
       2023-09-14 09:35:37.490 CST [155588] LOG:  terminating any other
active server processes
       2023-09-14 09:35:37.540 CST [155588] LOG:  connection received:
host=xxxxxxxx port=xxxx
       2023-09-14 09:35:37.541 CST [155588] FATAL:  the database system is
in recovery mode

On Thu, 14 Sept 2023 at 21:04, PG Bug reporting form
<noreply@postgresql.org> wrote:
> Recently I executed a merge SQL with over 1.5W rows inside PG15.2, which
> caused the database to report a crash error and then terminated all database
> sessions，but changing the merge to insert syntax worked fine, I'm trying to
> understand where this is and if I need to change the settings.
> Below are the DB and OS logs(ID or address data was changed to unreal):

It would be very difficult for us to do anything based on the
information you've supplied here.

Are you able to get a stack trace (See [1]) or come up with a
self-contained script to reproduce it?

I'm also not sure how many rows 1.5W is.

David

[1] https://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD

I reproduced the operation and got this stack trace:

       Program received signal SIGSEGV, Segmentation fault.

0x00007efe187b4598 in __memcpy_ssse3_back () from /lib64/libc.so.6

Continuing.

Program terminated with signal SIGSEGV, Segmentation fault.

The program no longer exists.

Error detected on fd 0

error detected on stdin

Sorry I wasn't clear, the rows 1.5w refers to the number of formatted lines of sql code (roughly 2.5w characters), not the number of data rows

On Thu, 14 Sept 2023 at 21:04, PG Bug reporting form
<noreply@postgresql.org> wrote:

Recently I executed a merge SQL with over 1.5W rows inside PG15.2, which

caused the database to report a crash error and then terminated all database

sessions，but changing the merge to insert syntax worked fine, I'm trying to

understand where this is and if I need to change the settings.

Below are the DB and OS logs(ID or address data was changed to unreal):

It would be very difficult for us to do anything based on the
information you've supplied here.

Are you able to get a stack trace (See [1]) or come up with a
self-contained script to reproduce it?

I'm also not sure how many rows 1.5W is.

David

[1] https://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD

On Fri, 15 Sept 2023 at 22:11, Jay <liuqi7850@126.com> wrote:
>
> I reproduced the operation and got this stack trace:
>
>        Program received signal SIGSEGV, Segmentation fault.
>
> 0x00007efe187b4598 in __memcpy_ssse3_back () from /lib64/libc.so.6
>
> Continuing.

Please send the output of: bt full

David

On Fri, Sep 15, 2023 at 10:19:41PM +1200, David Rowley wrote:
> On Fri, 15 Sept 2023 at 22:11, Jay <liuqi7850@126.com> wrote:
>>
>> I reproduced the operation and got this stack trace:
>>
>>        Program received signal SIGSEGV, Segmentation fault.
>>
>> 0x00007efe187b4598 in __memcpy_ssse3_back () from /lib64/libc.so.6
>>
>> Continuing.
>
> Please send the output of: bt full

A few more things.

The bug report refers to 15.2.  Please note that there have been a few
fixes in the area of MERGE in 15.3, while the latest minor version of
15.X released is 15.4.  So could you update to 15.4 and check if your
problem happens again when running this version?

Another thing that would save a lot of time for all of us would be to
provide a self-contained test case, with a minimal schema for the test
case involved, as there could be things related to the table's schema
itself that could influence the issue, say indexes, foreign keys, etc.
If you send such a test case, you'd better make sure that any
sensitive data is obfuscated.
--
Michael