回复: [External]Re: BUG #18308: SQL query information_schema metadata got error: server process was terminated by signal 11: Segment

Hi Laurenz,

Finally, we found out the this issue related to extension pg_show_plans.

When we removed it on parameter shared_preload_libraries, the issue was resolved.

#shared_preload_libraries = 'pg_stat_statements,pg_cron,pg_partman_bgw,pgaudit,repmgr,auto_explain,pg_show_plans'
shared_preload_libraries = 'pg_stat_statements,pg_cron,pg_partman_bgw,pgaudit,repmgr,auto_explain'

The pg_show_plans version is 1.1. Not sure it's a bug or not.

We also tested on pg_show_plans version 2.0， still have the same issue. Please refer to attachment.

Thanks
Jason
________________________________
发件人: Jason ChenTJ (CN) <Jason(dot)ChenTJ(at)homecredit(dot)cn>
发送时间: 2024年1月25日 12:55
收件人: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>; pgsql-bugs(at)lists(dot)postgresql(dot)org <pgsql-bugs(at)lists(dot)postgresql(dot)org>
抄送: YS Mao (CN) <YS(dot)Mao(at)homecreditcfc(dot)cn>
主题: 回复: [External]Re: BUG #18308: SQL query information_schema metadata got error: server process was terminated by signal 11: Segment

Hi Laurenz,

Added more info:

(gdb) bt
#0 0x00007fb094c875e9 in __memcpy_ssse3_back () from /lib64/libc.so.6
#1 0x00007fb08bdb4ff1 in pgsp_ExecutorStart () from /opt/pgsql-15/lib/pg_show_plans.so
#2 0x0000000000665706 in fmgr_sql ()
#3 0x000000000065f421 in ExecMakeFunctionResultSet ()
#4 0x000000000068506a in ExecProjectSRF ()
#5 0x0000000000685125 in ExecProjectSet ()
#6 0x00000000006761ae in ExecHashJoin ()
#7 0x000000000067baf2 in ExecMaterial ()
#8 0x00000000006849cf in ExecNestLoop ()
#9 0x00000000006761ae in ExecHashJoin ()
#10 0x0000000000684a8f in ExecNestLoop ()
#11 0x0000000000655ea2 in standard_ExecutorRun ()
#12 0x00007fb08cc2f4b5 in pgss_ExecutorRun () from /opt/pgsql-15/lib/pg_stat_statements.so
#13 0x00007fb08c3c5375 in pgaudit_ExecutorRun_hook () from /opt/pgsql-15/lib/pgaudit.so
#14 0x00007fb08bfb959e in explain_ExecutorRun () from /opt/pgsql-15/lib/auto_explain.so
#15 0x00007fb08bdb4c16 in pgsp_ExecutorRun () from /opt/pgsql-15/lib/pg_show_plans.so
#16 0x00000000007aedc0 in PortalRunSelect ()
#17 0x00000000007b0240 in PortalRun ()
#18 0x000000000049ce11 in exec_simple_query ()
#19 0x00000000007ae9f9 in PostgresMain ()
#20 0x000000000072dfd8 in ServerLoop ()
#21 0x000000000072ee7d in PostmasterMain ()
#22 0x000000000049d7ed in main ()

Thanks
Jason
________________________________
发件人: Jason ChenTJ (CN) <Jason(dot)ChenTJ(at)homecredit(dot)cn>
发送时间: 2024年1月25日 12:01
收件人: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>; pgsql-bugs(at)lists(dot)postgresql(dot)org <pgsql-bugs(at)lists(dot)postgresql(dot)org>
抄送: YS Mao (CN) <YS(dot)Mao(at)homecreditcfc(dot)cn>
主题: 回复: [External]Re: BUG #18308: SQL query information_schema metadata got error: server process was terminated by signal 11: Segment

Hi Laurenz，

Thanks for your support!

The reproduce step as below :

1.Connect to an empty database.(I created new database named tmp_db)

postgres(at)postgres:5007 >create database tmp_db template template0;
CREATE DATABASE
postgres(at)postgres:5007 >\c tmp_db postgres
You are now connected to database "tmp_db" as user "postgres".

2.Create 2 tables : A is parent table , B is child table

create table A (id int primary key, name varchar(20));
create table B (id int primary key, name varchar(20), pid int);
ALTER TABLE B ADD CONSTRAINT fk_a_id FOREIGN KEY (pid) REFERENCES A(id) ;

3.Run below SQL:

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 = 'a';

Got ERROR:
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.

4. Let's try again to collect the stack trace

postgres(at)postgres:5007 >\c tmp_db
You are now connected to database "tmp_db" as user "postgres".
postgres(at)tmp_db:5007 >select pg_backend_pid();
pg_backend_pid
----------------
2611
(1 row)

PROD [postgres(at)whdcpsql006 ~]# sudo gdb -p 2611
GNU gdb (GDB) Red Hat Enterprise Linux 7.6.1-120.0.3.el7
Copyright (C) 2013 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law. Type "show copying"
and "show warranty" for details.
This GDB was configured as "x86_64-redhat-linux-gnu".
For bug reporting instructions, please see:
<http://www.gnu.org/software/gdb/bugs/>.
Attaching to process 2611
Reading symbols from /opt/pgsql-15/bin/postgres...(no debugging symbols found)...done.
Reading symbols from /lib64/libzstd.so.1...Reading symbols from /lib64/libzstd.so.1...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libzstd.so.1
Reading symbols from /lib64/liblz4.so.1...Reading symbols from /lib64/liblz4.so.1...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/liblz4.so.1
Reading symbols from /lib64/libxml2.so.2...Reading symbols from /lib64/libxml2.so.2...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libxml2.so.2
Reading symbols from /lib64/libssl.so.10...Reading symbols from /lib64/libssl.so.10...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libssl.so.10
Reading symbols from /lib64/libcrypto.so.10...Reading symbols from /lib64/libcrypto.so.10...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libcrypto.so.10
Reading symbols from /lib64/libz.so.1...Reading symbols from /lib64/libz.so.1...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libz.so.1
Reading symbols from /lib64/libpthread.so.0...(no debugging symbols found)...done.
[Thread debugging using libthread_db enabled]
Using host libthread_db library "/lib64/libthread_db.so.1".
Loaded symbols for /lib64/libpthread.so.0
Reading symbols from /lib64/librt.so.1...(no debugging symbols found)...done.
Loaded symbols for /lib64/librt.so.1
Reading symbols from /lib64/libdl.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib64/libdl.so.2
Reading symbols from /lib64/libm.so.6...(no debugging symbols found)...done.
Loaded symbols for /lib64/libm.so.6
Reading symbols from /lib64/libc.so.6...(no debugging symbols found)...done.
Loaded symbols for /lib64/libc.so.6
Reading symbols from /lib64/liblzma.so.5...Reading symbols from /lib64/liblzma.so.5...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/liblzma.so.5
Reading symbols from /lib64/libgssapi_krb5.so.2...Reading symbols from /lib64/libgssapi_krb5.so.2...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libgssapi_krb5.so.2
Reading symbols from /lib64/libkrb5.so.3...Reading symbols from /lib64/libkrb5.so.3...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libkrb5.so.3
Reading symbols from /lib64/libcom_err.so.2...Reading symbols from /lib64/libcom_err.so.2...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libcom_err.so.2
Reading symbols from /lib64/libk5crypto.so.3...Reading symbols from /lib64/libk5crypto.so.3...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libk5crypto.so.3
Reading symbols from /lib64/ld-linux-x86-64.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib64/ld-linux-x86-64.so.2
Reading symbols from /lib64/libkrb5support.so.0...Reading symbols from /lib64/libkrb5support.so.0...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libkrb5support.so.0
Reading symbols from /lib64/libkeyutils.so.1...Reading symbols from /lib64/libkeyutils.so.1...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libkeyutils.so.1
Reading symbols from /lib64/libresolv.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib64/libresolv.so.2
Reading symbols from /lib64/libselinux.so.1...Reading symbols from /lib64/libselinux.so.1...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libselinux.so.1
Reading symbols from /lib64/libpcre.so.1...Reading symbols from /lib64/libpcre.so.1...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libpcre.so.1
Reading symbols from /opt/pgsql-15/lib/pg_stat_statements.so...(no debugging symbols found)...done.
Loaded symbols for /opt/pgsql-15/lib/pg_stat_statements.so
Reading symbols from /opt/pgsql-15/lib/pg_cron.so...(no debugging symbols found)...done.
Loaded symbols for /opt/pgsql-15/lib/pg_cron.so
Reading symbols from /opt/pgsql-15/lib/libpq.so.5...(no debugging symbols found)...done.
Loaded symbols for /opt/pgsql-15/lib/libpq.so.5
Reading symbols from /opt/pgsql-15/lib/pg_partman_bgw.so...(no debugging symbols found)...done.
Loaded symbols for /opt/pgsql-15/lib/pg_partman_bgw.so
Reading symbols from /opt/pgsql-15/lib/pgaudit.so...(no debugging symbols found)...done.
Loaded symbols for /opt/pgsql-15/lib/pgaudit.so
Reading symbols from /opt/pgsql-15/lib/repmgr.so...done.
Loaded symbols for /opt/pgsql-15/lib/repmgr.so
Reading symbols from /opt/pgsql-15/lib/auto_explain.so...(no debugging symbols found)...done.
Loaded symbols for /opt/pgsql-15/lib/auto_explain.so
Reading symbols from /opt/pgsql-15/lib/pg_show_plans.so...(no debugging symbols found)...done.
Loaded symbols for /opt/pgsql-15/lib/pg_show_plans.so
0x00007fb094c300e3 in __epoll_wait_nocancel () from /lib64/libc.so.6
Missing separate debuginfos, use: debuginfo-install glibc-2.17-326.0.9.el7_9.x86_64 keyutils-libs-1.5.8-3.el7.x86_64 krb5-libs-1.15.1-55.0.1.el7_9.x86_64 libcom_err-1.45.4-3.0.5.el7.x86_64 libselinux-2.5-15.el7.x86_64 libxml2-2.9.1-6.0.3.el7_9.6.x86_64 libzstd-1.5.5-1.el7.x86_64 lz4-1.8.3-1.el7.x86_64 openssl-libs-1.0.2k-26.el7_9.x86_64 pcre-8.32-17.el7.x86_64 xz-libs-5.2.2-2.el7_9.x86_64 zlib-1.2.7-21.el7_9.x86_64
(gdb) cont
Continuing.

Program received signal SIGSEGV, Segmentation fault.
0x00007fb094c8776c in __memcpy_ssse3_back () from /lib64/libc.so.6
(gdb) cont
Continuing.

Program terminated with signal SIGSEGV, Segmentation fault.
The program no longer exists.

We suspect if this issue related to compile config setting:

PROD [postgres(at)whdcpsql006 ~]# pg_config
BINDIR = /opt/pgsql-15/bin
DOCDIR = /opt/pgsql-15/share/doc
HTMLDIR = /opt/pgsql-15/share/doc
INCLUDEDIR = /opt/pgsql-15/include
PKGINCLUDEDIR = /opt/pgsql-15/include
INCLUDEDIR-SERVER = /opt/pgsql-15/include/server
LIBDIR = /opt/pgsql-15/lib
PKGLIBDIR = /opt/pgsql-15/lib
LOCALEDIR = /opt/pgsql-15/share/locale
MANDIR = /opt/pgsql-15/share/man
SHAREDIR = /opt/pgsql-15/share
SYSCONFDIR = /opt/pgsql-15/etc
PGXS = /opt/pgsql-15/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--prefix=/opt/pgsql-15' '--with-perl' '--with-python' '--with-lz4' '--with-zstd' '-with-ssl=openssl' '--with-llvm' '--with-libxml' '--with-libxslt' '--with-pgport=5005'
CC = gcc
CPPFLAGS = -D_GNU_SOURCE -I/usr/include/libxml2
CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -O2
CFLAGS_SL = -fPIC
LDFLAGS = -L/opt/rh/llvm-toolset-7/root/usr/lib64 -Wl,--as-needed -Wl,-rpath,'/opt/pgsql-15/lib',--enable-new-dtags
LDFLAGS_EX =
LDFLAGS_SL =
LIBS = -lpgcommon -lpgport -lzstd -llz4 -lxslt -lxml2 -lssl -lcrypto -lz -lreadline -lpthread -lrt -ldl -lm
VERSION = PostgreSQL 15.4

Thanks
Jason
________________________________
发件人: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
发送时间: 2024年1月24日 23:32
收件人: Jason ChenTJ (CN) <Jason(dot)ChenTJ(at)homecredit(dot)cn>; pgsql-bugs(at)lists(dot)postgresql(dot)org <pgsql-bugs(at)lists(dot)postgresql(dot)org>
主题: [External]Re: BUG #18308: SQL query information_schema metadata got error: server process was terminated by signal 11: Segment

This message is from an EXTERNAL SENDER - be CAUTIOUS, particularly with links and attachments.
________________________________

On Wed, 2024-01-24 at 06:43 +0000, PG Bug reporting form wrote:
> Today when I ran the SQL query from information_schema to get table
> relationship as below:

I cannot reproduce that.

Can you provide a complete test case that I can tun on an empty
database to reproduce the problem?

A stack trace from the crash could also be helpful.

Yours,
Laurenz Albe