BUG #18308: SQL query information_schema metadata got error: server process was terminated by signal 11: Segment - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      18308
Logged by:          SQL query information_schema metadata make the PG background process crash
Email address:      jason.chentj@homecredit.cn
PostgreSQL version: 15.4
Operating system:   Red Hat Enterprise Linux Server release 7.9
Description:

Hi PG team,

Today when I ran the SQL query from  information_schema to get table
relationship as below: 

select tc.table_schema, tc.table_name, 
       tc.constraint_type,tc.constraint_name, tc.is_deferrable,
       tc2.table_name as child_table,
       rc.unique_constraint_name as refer_key,
       case when rc.unique_constraint_name is not null then
            (select tc1.table_name 
               from information_schema.table_constraints tc1
              where tc1.constraint_name = rc.unique_constraint_name)
       end as ref_table,
       cc.check_clause,
       kc.column_name,
       kc.ordinal_position
  from information_schema.table_constraints tc
  left join information_schema.referential_constraints rc
    on tc.constraint_name = rc.constraint_name
  left join information_schema.check_constraints cc
    on tc.constraint_name = cc.constraint_name
  left join information_schema.key_column_usage kc
    on tc.constraint_name = kc.constraint_name
  left join LATERAL (
       select tc2.table_name, rc1.unique_constraint_name 
              from information_schema.referential_constraints rc1
              join information_schema.table_constraints tc2
                on rc1.constraint_name = tc2.constraint_name 
             where tc.constraint_name = rc1.unique_constraint_name
             ) tc2 on true
 where tc.table_name = 't1';

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.
The connection to the server was lost. Attempting reset: Failed.

===============================================================
Got blow error from PG log: seems all background process were terminated and
reinited.  

2024-01-24 10:13:45.236 CST [93775] LOG:  server process (PID 140220) was
terminated by signal 11: Segmentation fault
2024-01-24 10:13:45.236 CST [93775] LOG:  terminating any other active
server processes
2024-01-24 10:13:45.338 CST [149275]
[unknown]@[unknown]-[local]/[unknown]LOG:  connection received:
host=[local]
2024-01-24 10:13:45.338 CST [149275] postgres@db_pay-[local]/[unknown]FATAL:
 the database system is in recovery mode
2024-01-24 10:42:14.578 CST [60665] LOG:  all server processes terminated;
reinitializing

Also got the error on Linux OS kernel logs:

Jan 24 10:31:49 whdcpsql002 kernel: postgres[156820]: segfault at 1e5dffb ip
00007f907824e76c sp 00007ffea7f71548 error 4 in
libc-2.17.so[7f90780f8000+1c4000]
Jan 24 10:31:49 whdcpsql002 kernel: Code: c1 e1 03 4c 39 ca 76 06 48 01 ca
48 31 c9 48 81 ea 80 00 00 00 48 81 ea 80 00 00 00 0f 18 8e 00 fe ff ff 0f
18 8e 00 fd ff ff <f3> 0f 6f 4e f0 f3 0f 6f 56 e0 f3 0f 6f 5e d0 f3 0f 6f 66
c0 f3 0f
Jan 24 10:31:49 whdcpsql002 abrt-hook-ccpp: Process 156820 (postgres) of
user 1004 killed by SIGSEGV - dumping core
Jan 24 10:31:50 whdcpsql002 abrt-server: Executable
'/opt/pgsql-15/bin/postgres' doesn't belong to any package and
ProcessUnpackaged is set to 'no'
Jan 24 10:31:50 whdcpsql002 abrt-server: 'post-create' on
'/var/spool/abrt/ccpp-2024-01-24-10:31:49-156820' exited with 1
Jan 24 10:31:50 whdcpsql002 abrt-server: Deleting problem directory
'/var/spool/abrt/ccpp-2024-01-24-10:31:49-156820'
Jan 24 10:33:36 whdcpsql002 kernel: postgres[157627]: segfault at 221d000 ip
00007f907824e5e4 sp 00007ffea7f71408 error 4 in
libc-2.17.so[7f90780f8000+1c4000]
Jan 24 10:33:36 whdcpsql002 kernel: Code: 48 81 ea 80 00 00 00 48 81 ea 80
00 00 00 0f 18 8e 00 02 00 00 0f 18 8e 00 03 00 00 f3 0f 6f 06 f3 0f 6f 4e
10 f3 0f 6f 56 20 <f3> 0f 6f 5e 30 f3 0f 6f 66 40 f3 0f 6f 6e 50 f3 0f 6f 76
60 f3 0f
Jan 24 10:33:36 whdcpsql002 abrt-hook-ccpp: Process 157627 (postgres) of
user 1004 killed by SIGSEGV - dumping core
Jan 24 10:33:37 whdcpsql002 abrt-server: Executable
'/opt/pgsql-15/bin/postgres' doesn't belong to any package and
ProcessUnpackaged is set to 'no'
Jan 24 10:33:37 whdcpsql002 abrt-server: 'post-create' on
'/var/spool/abrt/ccpp-2024-01-24-10:33:36-157627' exited with 1
Jan 24 10:33:37 whdcpsql002 abrt-server: Deleting problem directory
'/var/spool/abrt/ccpp-2024-01-24-10:33:36-157627'

Also I can reproduce this issue on pg version 16.1.  

Please help investigate if it's a bug or not. 




Thanks
Jason