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

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