Re: Segmentation fault caused by Postgrest - reateplan.c:6178 - prepare_sort_from_pathkeys

On Mon, Jan 1, 2024 at 12:54 PM Michael B. Williams <
Michael(dot)Williams(at)glexia(dot)com> wrote:

>
> 2023-12-31 03:23:12.048 PST [17763] LOG: server process (PID 19014) was
> terminated by signal 11: Segmentation fault
> 2023-12-31 03:23:12.048 PST [17763] DETAIL: Failed process was running:
> with recursive
> pks_fks as (
> -- pk + fk referencing col
> select
> contype::text as contype,
> conname,
> array_length(conkey, 1) as ncol,
> conrelid as resorigtbl,
> col as resorigcol,
> ord
> from pg_constraint
> left join lateral unnest(conkey) with ordinality as _(col,
> ord) on true
> where contype IN ('p', 'f')
> union
> -- fk referenced col
> select
> concat(contype, '_ref') as contype,
> conname,
> array_length(confkey, 1) as ncol,
> confrelid,
> col,
> ord
> from pg_constraint
> left join lateral unnest(confkey) with ordinality as
> _(col, ord) on true
> where contype='f'
> ),
> views as (
> select
> c.oid as view_id,
> n.nspname as view_schema,
> c.relname as view_name,
> r.ev_action as view_definition
> from pg_class c
> join pg_namespace n on n.oid = c.relnames
> 2023-12-31 03:23:12.050 PST [17763] LOG: terminating any other active
> server processes
> Dec 31 03:23:12 sea-edge-2.glexia.com postgres[19021]: 2023-12-31
> 03:23:12.100 PST [19021] authenticator(at)chainweb-data FATAL: the database
> system is in recovery mode
> 2023-12-31 03:23:12.133 PST [17763] LOG: all server processes terminated;
> reinitializing
> 2023-12-31 03:23:13.592 PST [19024] LOG: database system was interrupted;
> last known up at 2023-12-31 03:22:27 PST
> Dec 31 03:23:13 sea-edge-2.glexia.com postgres[19027]: 2023-12-31
> 03:23:13.602 PST [19027] authenticator(at)chainweb-data FATAL: the database
> system is in recovery mode
> 2023-12-31 03:23:13.836 PST [19024] LOG: database system was not properly
> shut down; automatic recovery in progress
> 2023-12-31 03:23:13.844 PST [19024] LOG: redo starts at 289/C2940F78
>
> gdb `cat ExecutablePath` CoreDump
> GNU gdb (Ubuntu 12.1-0ubuntu1~22.04) 12.1
> Copyright (C) 2022 Free Software Foundation, Inc.
> License GPLv3+: GNU GPL version 3 or later <
> http://gnu.org/licenses/gpl.html
> <https://mailtrack.io/link/a72e949a32e0201583f367d4656de7e78baee227?url=http%3A%2F%2Fgnu.org%2Flicenses%2Fgpl.html&userId=10605575&signature=95c172d172e5788a>
> >
> 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-linux-gnu".
> Type "show configuration" for configuration details.
> For bug reporting instructions, please see:
> <https://www.gnu.org/software/gdb/bugs/
> <https://mailtrack.io/link/f71e0b93de0c10433defc2816294d38a49bf21f0?url=https%3A%2F%2Fwww.gnu.org%2Fsoftware%2Fgdb%2Fbugs%2F&userId=10605575&signature=58a65641ee893ef1>
> >.
> Find the GDB manual and other documentation resources online at:
> <http://www.gnu.org/software/gdb/documentation/
> <https://mailtrack.io/link/a2bb336eafdcead74ff37b1362276fe00f226b5b?url=http%3A%2F%2Fwww.gnu.org%2Fsoftware%2Fgdb%2Fdocumentation%2F&userId=10605575&signature=c8de8ecf99cc0ba1>
> >.
>
> For help, type "help".
> Type "apropos word" to search for commands related to "word"...
> Reading symbols from /opt/pgpro/std-16/bin/postgres...
> Reading symbols from /usr/lib/debug//opt/pgpro/std-16/bin/postgres...
>
> warning: Can't open file /anon_hugepage (deleted) during file-backed
> mapping note processing
>
> warning: Can't open file /dev/shm/PostgreSQL.1502966450 during file-backed
> mapping note processing
>
> warning: Can't open file /dev/shm/PostgreSQL.4020606926 during file-backed
> mapping note processing
>
> warning: Can't open file /SYSV009a00b1 (deleted) during file-backed
> mapping note processing
> [New LWP 19170]
> [Thread debugging using libthread_db enabled]
> Using host libthread_db library "/lib/x86_64-linux-gnu/libthread_db.so.1".
> Core was generated by `postgres: 16/main: authenticator chainweb-data
> 127.0.0.1(3399'.
> Program terminated with signal SIGSEGV, Segmentation fault.
> #0 prepare_sort_from_pathkeys (root=0x55af29f49b60, lefttree=<optimized
> out>, pathkeys=0x55af2a467c10, relids=0x0, reqColIdx=0x0,
> adjust_tlist_in_place=<optimized out>,
> p_numsortkeys=0x7fffe6432b54, p_sortColIdx=0x7fffe6432b70,
> p_sortOperators=0x7fffe6432b68, p_collations=0x7fffe6432b60,
> p_nullsFirst=0x7fffe6432b58)
> at
> optimizer/plan/./build/../src/backend/optimizer/plan/createplan.c:6178
> 6178 optimizer/plan/./build/../src/backend/optimizer/plan/createplan.c:
> No such file or directory.
> (gdb) bt
> #0 prepare_sort_from_pathkeys (root=0x55af29f49b60, lefttree=<optimized
> out>, pathkeys=0x55af2a467c10, relids=0x0, reqColIdx=0x0,
> adjust_tlist_in_place=<optimized out>,
> p_numsortkeys=0x7fffe6432b54, p_sortColIdx=0x7fffe6432b70,
> p_sortOperators=0x7fffe6432b68, p_collations=0x7fffe6432b60,
> p_nullsFirst=0x7fffe6432b58)
> at
> optimizer/plan/./build/../src/backend/optimizer/plan/createplan.c:6178
> #1 0x000055af2833b5d4 in make_sort_from_pathkeys (root=root(at)entry=0x55af29f49b60,
> lefttree=<optimized out>, pathkeys=<optimized out>, relids=<optimized out>)
> at
> optimizer/plan/./build/../src/backend/optimizer/plan/createplan.c:6331
> #2 0x000055af2833c1fc in create_sort_plan (flags=4,
> best_path=0x55af2a46b8b0, root=0x55af29f49b60) at
> optimizer/plan/./build/../src/backend/optimizer/plan/createplan.c:2193
> #3 create_plan_recurse (root=0x55af29f49b60, best_path=0x55af2a46b8b0,
> flags=4) at
> optimizer/plan/./build/../src/backend/optimizer/plan/createplan.c:491
> #4 0x000055af2833c565 in create_agg_plan (best_path=0x55af2a46ba80,
> root=0x55af29f49b60) at
> optimizer/plan/./build/../src/backend/optimizer/plan/createplan.c:2313
> #5 create_plan_recurse (root=0x55af29f49b60, best_path=0x55af2a46ba80,
> flags=1) at
> optimizer/plan/./build/../src/backend/optimizer/plan/createplan.c:511
> #6 0x000055af2833f13e in create_plan (root=0x55af29f49b60,
> best_path=<optimized out>) at
> optimizer/plan/./build/../src/backend/optimizer/plan/createplan.c:351
> #7 0x000055af2834b55f in standard_planner (parse=0x55af29a84030,
> query_string=<optimized out>, cursorOptions=2048, boundParams=<optimized
> out>)
> at optimizer/plan/./build/../src/backend/optimizer/plan/planner.c:433
> #8 0x00007f8255f83e2a in pgss_planner (parse=0x55af29a84030,
> query_string=0x55af2af82228 "\n with recursive\n pks_fks as
> (\n -- pk + fk referencing col\n select\n
> contype::text as contype,\n conname,\n
> array_length(conkey, 1) as ncol,\n c"..., cursorOptions=2048,
> boundParams=0x55af29b20718) at
> /home/builder/pgpro-stats-1.6.3/pgpro_stats.c:3280
> #9 0x00007f8255f64b13 in pathman_planner_hook (parse=0x55af29a84030,
> query_string=0x55af2af82228 "\n with recursive\n pks_fks as
> (\n -- pk + fk referencing col\n select\n
> contype::text as contype,\n conname,\n
> array_length(conkey, 1) as ncol,\n c"..., cursorOptions=2048,
> boundParams=0x55af29b20718) at ./build/../contrib/pg_pathman/src/hooks.c:763
> #10 0x000055af2834babe in planner (parse=0x55af29a84030,
> query_string=0x55af2af82228 "\n with recursive\n pks_fks as
> (\n -- pk + fk referencing col\n select\n
> contype::text as contype,\n conname,\n
> array_length(conkey, 1) as ncol,\n c"..., cursorOptions=2048,
> boundParams=0x55af29b20718) at
> optimizer/plan/./build/../src/backend/optimizer/plan/planner.c:285
> #11 0x000055af2842a428 in pg_plan_query (querytree=0x55af29a84030,
> query_string=query_string(at)entry=0x55af2af82228 "\n with
> recursive\n pks_fks as (\n -- pk + fk referencing col\n
> select\n contype::text as contype,\n conname,\n
> array_length(conkey, 1) as ncol,\n c"...,
> cursorOptions=cursorOptions(at)entry=2048, boundParams=boundParams(at)entry
> =0x55af29b20718)
> at tcop/./build/../src/backend/tcop/postgres.c:911
> #12 0x000055af2842a51a in pg_plan_queries (querytrees=0x55af29a83fe8,
> query_string=0x55af2af82228 "\n with recursive\n pks_fks as
> (\n -- pk + fk referencing col\n select\n
> contype::text as contype,\n conname,\n
> array_length(conkey, 1) as ncol,\n c"..., cursorOptions=2048,
> boundParams=0x55af29b20718) at
> tcop/./build/../src/backend/tcop/postgres.c:1003
> #13 0x000055af28591f43 in BuildCachedPlan (plansource=plansource(at)entry=0x55af2af3a440,
> qlist=0x55af29a83fe8, qlist(at)entry=0x0, boundParams=boundParams(at)entry=0x55af29b20718,
>
> queryEnv=queryEnv(at)entry=0x0) at
> utils/cache/./build/../src/backend/utils/cache/plancache.c:941
> #14 0x000055af2859226e in GetCachedPlan (plansource=0x55af2af3a440,
> boundParams=0x55af29b20718, owner=0x0, queryEnv=0x0) at
> utils/cache/./build/../src/backend/utils/cache/plancache.c:1225
> #15 0x000055af2842f903 in exec_bind_message (input_message=0x7fffe64337e0)
> at tcop/./build/../src/backend/tcop/postgres.c:2018
> #16 PostgresMain (dbname=<optimized out>, username=<optimized out>) at
> tcop/./build/../src/backend/tcop/postgres.c:4702
> #17 0x000055af2839973d in BackendRun (port=0x55af29b21f40,
> port=0x55af29b21f40) at
> postmaster/./build/../src/backend/postmaster/postmaster.c:4498
> #18 BackendStartup (port=0x55af29b21f40) at
> postmaster/./build/../src/backend/postmaster/postmaster.c:4226
> #19 ServerLoop () at
> postmaster/./build/../src/backend/postmaster/postmaster.c:1816
> #20 0x000055af283907a3 in PostmasterMain (argc=3, argv=<optimized out>) at
> postmaster/./build/../src/backend/postmaster/postmaster.c:1500
> #21 0x000055af2803ad5d in main (argc=3, argv=0x55af299ef390) at
> main/./build/../src/backend/main/main.c:205
> (gdb)
>
>
Your stack trace indicates you are not using Postgres, you are using pgpro?
This is not the same product. You are also using a number of non-standard
extensions if the stacktrace is correct.

Please see if you can reproduce the problem on PostgreSQL and if you can
report the stack trace for it here. If you can't, then you need to bring it
up with the authors of the product(s) you're using. You should also include
the actual query you're attempting to run in the report to help others
reproduce.

//Magnus