Thread: signal 11: Segmentation fault ; query constraint list; pg16.3

signal 11: Segmentation fault ; query constraint list; pg16.3

From
milist ujang
Date:
postgres 16.1; rocky 9.3

when connect to database postgres this query is OK, but run on user database, got segmentation fault.


2024-05-20 17:16:54.942 WIB,,,134291,,6648420a.20c93,165,,2024-05-18 12:52:10 WIB,,0,LOG,00000,"server process (PID 487952) was terminated by signal 11: Segmentation fault","Failed process was running: select
    ct.conname as constraint_name,
    a.attname as column_name,
    fc.relname as foreign_table_name,
    fns.nspname as foreign_table_schema,
    fa.attname as foreign_column_name
from
    (SELECT ct.conname, ct.conrelid, ct.confrelid, ct.conkey, ct.contype, ct.confkey, generate_subscripts(ct.conkey, 1) AS s
       FROM pg_constraint ct
    ) AS ct
    inner join pg_class c on c.oid=ct.conrelid
    inner join pg_namespace ns on c.relnamespace=ns.oid
    inner join pg_attribute a on a.attrelid=ct.conrelid and a.attnum = ct.conkey[ct.s]
    left join pg_class fc on fc.oid=ct.confrelid
    left join pg_namespace fns on fc.relnamespace=fns.oid
    left join pg_attribute fa on fa.attrelid=ct.confrelid and fa.attnum = ct.confkey[ct.s]
where
    ct.contype='f'
    and c.relname='table1'
    and ns.nspname='schemauser'
order by
    fns.nspname, fc.relname, a.attnum
;",,,,,,,,"","postmaster",,0

2024-05-20 17:16:54.942 WIB,,,134291,,6648420a.20c93,166,,2024-05-18 12:52:10 WIB,,0,LOG,00000,"terminating any other active server processes",,,,,,,,,"","postmaster",,0

--
regards

ujang jaenudin | Self-Employed, DBA Consultant
http://id.linkedin.com/pub/ujang-jaenudin/12/64/bab
On Mon, 20 May 2024 at 22:32, milist ujang <ujang.milist@gmail.com> wrote:
>
> postgres 16.1; rocky 9.3
>
> when connect to database postgres this query is OK, but run on user database, got segmentation fault.

I tried your query on 16.1 and I'm unable to reproduce the crash.

Are you able to recreate this on a freshly installed instance after
creating the table in question? If not, does it crash after you do a
pg_dump --schema-only from the problem database and restoring that to
the fresh instance and running ANALYZE? The crash may depend on the
query plan chosen and that will depend on the schema in that database.

We're probably going to need a recreator script for this. You might be
able to come up with one from doing the --schema-only dump and
restoring that somewhere and dropping unrelated objects to find the
minimum set you need for the crash.

Alternatively, a stack trace would be useful. See:

https://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD

David



as of subject, it should be 16.3.

scenario was:
- a few months ago install + deployed user data pg 16.2.
- 2 days ago, updated to 16.3

now I can reproduce the issue, the segment fault comes from 
postgres[495645]: segfault at 0 ip 00007f318b17e1f4 sp 00007ffc7f1b15d8 error 4 in citus.so[7f318b0a4000+ee000] likely on CPU 93 (core 1, socket 1)

after dropping the citus extension, now it's OK.

Thanks for the reply.


On Mon, May 20, 2024 at 6:01 PM David Rowley <dgrowleyml@gmail.com> wrote:
On Mon, 20 May 2024 at 22:32, milist ujang <ujang.milist@gmail.com> wrote:
>
> postgres 16.1; rocky 9.3
>
> when connect to database postgres this query is OK, but run on user database, got segmentation fault.

I tried your query on 16.1 and I'm unable to reproduce the crash.

Are you able to recreate this on a freshly installed instance after
creating the table in question? If not, does it crash after you do a
pg_dump --schema-only from the problem database and restoring that to
the fresh instance and running ANALYZE? The crash may depend on the
query plan chosen and that will depend on the schema in that database.

We're probably going to need a recreator script for this. You might be
able to come up with one from doing the --schema-only dump and
restoring that somewhere and dropping unrelated objects to find the
minimum set you need for the crash.

Alternatively, a stack trace would be useful. See:

https://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD

David


--
regards

ujang jaenudin | Self-Employed, DBA Consultant
http://id.linkedin.com/pub/ujang-jaenudin/12/64/bab