pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18308: SQL query information_schema metadata got error: server process was terminated by signal 11: Segment
3+ messages / 3 participants
[nested] [flat]
* BUG #18308: SQL query information_schema metadata got error: server process was terminated by signal 11: Segment
@ 2024-01-24 06:43 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: PG Bug reporting form @ 2024-01-24 06:43 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: jason.chentj@homecredit.cn
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #18308: SQL query information_schema metadata got error: server process was terminated by signal 11: Segment
@ 2024-01-24 15:32 Laurenz Albe <laurenz.albe@cybertec.at>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: Laurenz Albe @ 2024-01-24 15:32 UTC (permalink / raw)
To: jason.chentj@homecredit.cn; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [External]Re: BUG #18308: SQL query information_schema metadata got error: server process was terminated by signal 11: Segment
@ 2024-01-25 06:24 jian he <jian.universality@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 3+ messages in thread
From: jian he @ 2024-01-25 06:24 UTC (permalink / raw)
To: Jason ChenTJ (CN) <Jason.ChenTJ@homecredit.cn>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>; YS Mao (CN) <YS.Mao@homecreditcfc.cn>
On Thu, Jan 25, 2024 at 1:50 PM Jason ChenTJ (CN)
<Jason.ChenTJ@homecredit.cn> wrote:
>
> 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.
>
>
cannot reproduce.
The query can be executed on a public postgres15 instance.
https://dbfiddle.uk/l3vN-y7e
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-01-25 06:24 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-01-24 06:43 BUG #18308: SQL query information_schema metadata got error: server process was terminated by signal 11: Segment PG Bug reporting form <noreply@postgresql.org>
2024-01-24 15:32 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-01-25 06:24 ` jian he <jian.universality@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox