回复: [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