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

Поиск
Список
Период
Сортировка
От Jason ChenTJ (CN)
Тема 回复: [External]Re: BUG #18308: SQL query information_schema metadata got error: server process was terminated by signal 11: Segment
Дата
Msg-id ZQ0PR01MB1080CAFA0EE479FE1BC771E0927AA@ZQ0PR01MB1080.CHNPR01.prod.partner.outlook.cn
обсуждение исходный текст
Ответ на Re: BUG #18308: SQL query information_schema metadata got error: server process was terminated by signal 11: Segment  (Laurenz Albe <laurenz.albe@cybertec.at>)
Ответы 回复: [External]Re: BUG #18308: SQL query information_schema metadata got error: server process was terminated by signal 11: Segment  ("Jason ChenTJ (CN)" <Jason.ChenTJ@homecredit.cn>)
Список pgsql-bugs
Дерево обсуждения
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@postgres:5007 >create database tmp_db template template0;
CREATE DATABASE
postgres@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@postgres:5007 >\c tmp_db
You are now connected to database "tmp_db" as user "postgres".
postgres@tmp_db:5007 >select pg_backend_pid();
 pg_backend_pid
----------------
           2611
(1 row)

PROD [postgres@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@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.albe@cybertec.at>
发送时间: 2024年1月24日 23:32
收件人: Jason ChenTJ (CN) <Jason.ChenTJ@homecredit.cn>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.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

В списке pgsql-bugs по дате отправления:

Предыдущее
От: PG Bug reporting form
Дата:
Сообщение: BUG #18310: Some SQL commands fail to process duplicate objects with error: tuple already updated by self
Следующее
От: Michael Paquier
Дата:
Сообщение: Re: BUG #18274: Error 'invalid XML content'