Subject: Interesting fail when migrating Pg from Ubuntu Bionic to Focal

From: hubert depesz lubaczewski <[email-masked]>
Date: Thu, 3 Mar 2022 16:04:28 +0100

Hi,
I know it's going to be most likely due to glibc and locales, but I found
interesting case that I can't figure out how to fix.

We have pg 12.6 on bionic. Works. Added focal replica (binary).

Replicates OK, but then fails when I try to pg_dump -s.

Error is:

pg_dump: error: query failed: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
pg_dump: error: query was: SELECT p.tableoid, p.oid, p.proname, p.prolang, p.pronargs, p.proargtypes, p.prorettype, (SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM pg_catalog.unnest(coalesce(p.proacl,pg_catalog.acldefault('f',p.proowner))) WITH ORDINALITY AS perm(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('f',p.proowner))) AS init(init_acl) WHERE acl = init_acl)) as foo) AS proacl, (SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('f',p.proowner))) WITH ORDINALITY AS initp(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(p.proacl,pg_catalog.acldefault('f',p.proowner))) AS permp(orig_acl) WHERE acl = orig_acl)) as foo) AS rproacl, NULL AS initproacl, NULL AS initrproacl, p.pronamespace, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = p.proowner) AS rolname FROM pg_proc p LEFT JOIN pg_init_privs pip ON (p.oid = pip.objoid AND pip.classoid = 'pg_proc'::regclass AND pip.objsubid = 0) WHERE p.prokind <> 'a'
  AND NOT EXISTS (SELECT 1 FROM pg_depend WHERE classid = 'pg_proc'::regclass AND objid = p.oid AND deptype = 'i')
  AND (
  pronamespace != (SELECT oid FROM pg_namespace WHERE nspname = 'pg_catalog')
  OR EXISTS (SELECT 1 FROM pg_cast
  WHERE pg_cast.oid > 16383 
  AND p.oid = pg_cast.castfunc)
  OR EXISTS (SELECT 1 FROM pg_transform
  WHERE pg_transform.oid > 16383 AND 
  (p.oid = pg_transform.trffromsql
  OR p.oid = pg_transform.trftosql))
  OR p.proacl IS DISTINCT FROM pip.initprivs)

Based on https://wiki.postgresql.org/wiki/Locale_data_changes
I wrote:
SELECT 'reindex index ' || indexrelid::regclass::text 
FROM (SELECT indexrelid, indrelid, indcollation[i] coll FROM pg_index, generate_subscripts(indcollation, 1) g(i)) s 
  JOIN pg_collation c ON coll=c.oid
WHERE collprovider IN ('d', 'c') AND collname NOT IN ('C', 'POSIX') \gexec

and let it run (80k+ indexes).

Afterwards - same problem.

So I did "reindex system".

And the query still fails.

I tried running simple "select * from table", for each of:

- pg_catalog.pg_roles
- pg_catalog.pg_proc
- pg_catalog.pg_init_privs
- pg_catalog.pg_depend
- pg_catalog.pg_namespace
- pg_catalog.pg_cast
- pg_catalog.pg_transform

and it worked, so I'm kinda at loss here.

I have test system, can test anything. Any idea on what could be the reason,
and if the system is fixable afterwards?

Ah, one more thing - straced backend when it was doing its thing. Last 50 lines:

15:01:31.229198 futex(0xfffe49b8cb3c, FUTEX_WAKE_PRIVATE, 2147483647) = 0 <0.000012>
15:01:31.229233 futex(0xfffe49b8c41c, FUTEX_WAKE_PRIVATE, 2147483647) = 0 <0.000012>
15:01:31.229268 futex(0xfffe49b8cdfc, FUTEX_WAKE_PRIVATE, 2147483647) = 0 <0.000012>
15:01:31.229303 futex(0xfffe49b8ce00, FUTEX_WAKE_PRIVATE, 2147483647) = 0 <0.000012>
15:01:31.229338 futex(0xfffe49b8ce08, FUTEX_WAKE_PRIVATE, 2147483647) = 0 <0.000012>
15:01:31.229379 futex(0xfffe49b8dde4, FUTEX_WAKE_PRIVATE, 2147483647) = 0 <0.000012>
15:01:31.229415 futex(0xfffe49b91524, FUTEX_WAKE_PRIVATE, 2147483647) = 0 <0.000012>
15:01:31.229450 futex(0xfffe49b8998c, FUTEX_WAKE_PRIVATE, 2147483647) = 0 <0.000012>
15:01:31.229485 futex(0xfffe49b8cf9c, FUTEX_WAKE_PRIVATE, 2147483647) = 0 <0.000012>
15:01:31.229521 futex(0xfffe49b882e4, FUTEX_WAKE_PRIVATE, 2147483647) = 0 <0.000012>
15:01:31.229556 futex(0xfffe49b8e548, FUTEX_WAKE_PRIVATE, 2147483647) = 0 <0.000012>
15:01:31.229596 futex(0xfffe49b8d75c, FUTEX_WAKE_PRIVATE, 2147483647) = 0 <0.000012>
15:01:31.229634 futex(0xfffe49b8cfe8, FUTEX_WAKE_PRIVATE, 2147483647) = 0 <0.000012>
15:01:31.229670 futex(0xfffe49b8dfe8, FUTEX_WAKE_PRIVATE, 2147483647) = 0 <0.000012>
15:01:31.229705 futex(0xfffe49b8dff0, FUTEX_WAKE_PRIVATE, 2147483647) = 0 <0.000012>
15:01:31.229740 futex(0xfffe49b8dff8, FUTEX_WAKE_PRIVATE, 2147483647) = 0 <0.000012>
15:01:31.229775 futex(0xfffe49b8ddf0, FUTEX_WAKE_PRIVATE, 2147483647) = 0 <0.000012>
15:01:31.229810 futex(0xfffe49b8e0a8, FUTEX_WAKE_PRIVATE, 2147483647) = 0 <0.000012>
15:01:31.229845 futex(0xfffe49b8e200, FUTEX_WAKE_PRIVATE, 2147483647) = 0 <0.000012>
15:01:31.229881 futex(0xfffe49b8e534, FUTEX_WAKE_PRIVATE, 2147483647) = 0 <0.000012>
15:01:31.229925 futex(0xfffe49b8e734, FUTEX_WAKE_PRIVATE, 2147483647) = 0 <0.000012>
15:01:31.229961 futex(0xfffe49b8e7dc, FUTEX_WAKE_PRIVATE, 2147483647) = 0 <0.000012>
15:01:31.229998 futex(0xfffe49b8e924, FUTEX_WAKE_PRIVATE, 2147483647) = 0 <0.000012>
15:01:31.230034 futex(0xfffe49b8ea6c, FUTEX_WAKE_PRIVATE, 2147483647) = 0 <0.000012>
15:01:31.230070 futex(0xfffe49b8ea74, FUTEX_WAKE_PRIVATE, 2147483647) = 0 <0.000012>
15:01:31.230105 futex(0xfffe49b91514, FUTEX_WAKE_PRIVATE, 2147483647) = 0 <0.000012>
15:01:31.230141 futex(0xfffe49b8ad84, FUTEX_WAKE_PRIVATE, 2147483647) = 0 <0.000012>
15:01:31.230177 futex(0xfffe49b91518, FUTEX_WAKE_PRIVATE, 2147483647) = 0 <0.000012>
15:01:31.230212 futex(0xfffe49b9152c, FUTEX_WAKE_PRIVATE, 2147483647) = 0 <0.000012>
15:01:31.230247 futex(0xfffe49b91530, FUTEX_WAKE_PRIVATE, 2147483647) = 0 <0.000012>
15:01:31.230283 futex(0xfffe49b91718, FUTEX_WAKE_PRIVATE, 2147483647) = 0 <0.000012>
15:01:31.230323 futex(0xfffe49b91720, FUTEX_WAKE_PRIVATE, 2147483647) = 0 <0.000012>
15:01:31.230384 futex(0xfffe49b8e488, FUTEX_WAKE_PRIVATE, 2147483647) = 0 <0.000013>
15:01:31.230423 futex(0xfffe49b946cc, FUTEX_WAKE_PRIVATE, 2147483647) = 0 <0.000012>
15:01:31.230464 futex(0xfffe49b884d4, FUTEX_WAKE_PRIVATE, 2147483647) = 0 <0.000012>
15:01:31.230515 futex(0xfffe49b955c0, FUTEX_WAKE_PRIVATE, 2147483647) = 0 <0.000012>
15:01:31.230624 futex(0xfffe49ba8738, FUTEX_WAKE_PRIVATE, 2147483647) = 0 <0.000013>
15:01:31.230678 futex(0xfffe49b91288, FUTEX_WAKE_PRIVATE, 2147483647) = 0 <0.000012>
15:01:31.231031 brk(0xaaaae513e000)     = 0xaaaae513e000 <0.000016>
15:01:31.232480 brk(0xaaaae515f000)     = 0xaaaae515f000 <0.000018>
15:01:31.239133 brk(0xaaaae5180000)     = 0xaaaae5180000 <0.000019>
15:01:31.245463 brk(0xaaaae51a1000)     = 0xaaaae51a1000 <0.000020>
15:01:31.245847 brk(0xaaaae51c2000)     = 0xaaaae51c2000 <0.000016>
15:01:31.259644 mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0xffff80da8000 <0.000025>
15:01:31.259705 mmap(NULL, 36864, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0xfffe4a36a000 <0.000014>
15:01:31.260012 futex(0xaaaae4f7fcf8, FUTEX_WAKE_PRIVATE, 2147483647) = 0 <0.000014>
15:01:31.260074 mprotect(0xfffe4a36a000, 36864, PROT_READ|PROT_EXEC) = 0 <0.000017>
15:01:31.260120 mprotect(0xffff80da8000, 4096, PROT_READ|PROT_EXEC) = 0 <0.000013>
15:01:31.260165 --- SIGSEGV {si_signo=SIGSEGV, si_code=SEGV_MAPERR, si_addr=0xfffd80da80a8} ---
15:01:31.451341 +++ killed by SIGSEGV (core dumped) +++

depesz

---

From: hubert depesz lubaczewski <[email-masked]>
Date: Thu, 3 Mar 2022 16:11:56 +0100

> and it worked, so I'm kinda at loss here.

based on some talk on IRC, I was able to get stack trace from fail:

(gdb) bt
#0  0x0000fffe4a36e4d8 in ?? ()
#1  0x0000aaaabe03ffb8 in ExecProcNode (node=0xaaaae4f87cf8) at ./build/../src/include/executor/executor.h:242
#2  ExecMergeJoin (pstate=0xaaaae4f87da8) at ./build/../src/backend/executor/nodeMergejoin.c:656
#3  0x0000aaaabe03ffb8 in ExecProcNode (node=0xaaaae4f87ae8) at ./build/../src/include/executor/executor.h:242
#4  ExecMergeJoin (pstate=0xaaaae4f876c8) at ./build/../src/backend/executor/nodeMergejoin.c:656
#5  0x0000aaaabe039b1c in ExecProcNode (node=0xaaaae4f876c8) at ./build/../src/include/executor/executor.h:242
#6  ExecHashJoinImpl (parallel=false, pstate=0xaaaae4f87408) at ./build/../src/backend/executor/nodeHashjoin.c:262
#7  ExecHashJoin (pstate=0xaaaae4f87408) at ./build/../src/backend/executor/nodeHashjoin.c:591
#8  0x0000aaaabe01ed5c in ExecProcNode (node=0xaaaae4f87408) at ./build/../src/include/executor/executor.h:242
#9  ExecutePlan (execute_once=<optimized out>, dest=0xfffe49be0828, direction=<optimized out>, numberTuples=0, sendTuples=<optimized out>, operation=CMD_SELECT, use_parallel_mode=<optimized out>, planstate=0xaaaae4f87408, estate=0xaaaae4ce4180)
    at ./build/../src/backend/executor/execMain.c:1632
#10 standard_ExecutorRun (queryDesc=0xaaaae4e9da70, direction=<optimized out>, count=0, execute_once=<optimized out>) at ./build/../src/backend/executor/execMain.c:350
#11 0x0000ffff7d1981fc in pgss_ExecutorRun (queryDesc=0xaaaae4e9da70, direction=ForwardScanDirection, count=0, execute_once=true) at ./build/../contrib/pg_stat_statements/pg_stat_statements.c:893
#12 0x0000ffff7d182688 in explain_ExecutorRun (queryDesc=0xaaaae4e9da70, direction=ForwardScanDirection, count=0, execute_once=true) at ./build/../contrib/auto_explain/auto_explain.c:320
#13 0x0000aaaabe1754c8 in PortalRunSelect (portal=portal@entry=0xaaaae4d476e0, forward=forward@entry=true, count=0, count@entry=9223372036854775807, dest=dest@entry=0xfffe49be0828) at ./build/../src/backend/tcop/pquery.c:938
#14 0x0000aaaabe176a64 in PortalRun (portal=portal@entry=0xaaaae4d476e0, count=count@entry=9223372036854775807, isTopLevel=isTopLevel@entry=true, run_once=run_once@entry=true, dest=dest@entry=0xfffe49be0828, altdest=altdest@entry=0xfffe49be0828, 
    completionTag=completionTag@entry=0xffffc89001f8 "") at ./build/../src/backend/tcop/pquery.c:779
#15 0x0000aaaabe172a18 in exec_simple_query (
    query_string=query_string@entry=0xaaaae4c6c7b0 "SELECT\n    p.tableoid,\n    p.oid,\n    p.proname,\n    p.prolang,\n    p.pronargs,\n    p.proargtypes,\n    p.prorettype,\n    (\n        SELECT\n", ' ' <repeats 12 times>, "pg_catalog.array_agg(acl ORDER BY row_n)\n        F"...) at ./build/../src/backend/tcop/postgres.c:1215
#16 0x0000aaaabe1736ac in PostgresMain (argc=<optimized out>, argv=argv@entry=0xaaaae4cc9018, dbname=<optimized out>, username=<optimized out>) at ./build/../src/backend/tcop/postgres.c:4271
#17 0x0000aaaabe0fa768 in BackendRun (port=0xaaaae4cb88e0, port=0xaaaae4cb88e0) at ./build/../src/backend/postmaster/postmaster.c:4510
#18 BackendStartup (port=0xaaaae4cb88e0) at ./build/../src/backend/postmaster/postmaster.c:4193
#19 ServerLoop () at ./build/../src/backend/postmaster/postmaster.c:1725
#20 0x0000aaaabe0fb74c in PostmasterMain (argc=<optimized out>, argv=<optimized out>) at ./build/../src/backend/postmaster/postmaster.c:1398
#21 0x0000aaaabde8c8a8 in main (argc=5, argv=0xaaaae4c65ea0) at ./build/../src/backend/main/main.c:228

And one more thing - if it matters - it's on aarch64 architecture
(graviton ec2 in AWS)

Best regards,

depesz

---

From: hubert depesz lubaczewski <[email-masked]>
Date: Thu, 3 Mar 2022 17:39:21 +0100

> based on some talk on IRC, I was able to get stack trace from fail:

Based on the stack trace I was able to get it to break using simple
query:
select p.proname, (SELECT rolname from pg_catalog.pg_roles where oid = p.proowner) from pg_proc p;

I took a simple look at ranges of oid/prowner, and they look fine:

=# select min(proowner), max(proowner), count(*) from pg_proc;
 min │ max │ count 
─────┼─────┼───────
  10 │  10 │  2970
(1 row)

16:38:34 db: postgres@postgres, pid:1991057
=# select min(oid), max(oid), count(*) from pg_roles;
 min │    max    │ count 
─────┼───────────┼───────
  10 │ 310235824 │   244
(1 row)

Also, as I didn't mention it before: it's Pg 12.9.

Best regards,

depesz

---

From: hubert depesz lubaczewski <[email-masked]>
Date: Thu, 3 Mar 2022 17:44:01 +0100

> =# select min(proowner), max(proowner), count(*) from pg_proc;

OK. Traced it back to JIT. With JIT enabled:

=# show jit;
 jit 
─────
 on
(1 row)

=# explain select p.proname, (SELECT rolname from pg_catalog.pg_roles where oid = p.proowner) from pg_proc p;
                                          QUERY PLAN                                          
──────────────────────────────────────────────────────────────────────────────────────────────
 Seq Scan on pg_proc p  (cost=0.00..156507.84 rows=63264 width=128)
   SubPlan 1
     ->  Index Scan using pg_authid_oid_index on pg_authid  (cost=0.14..2.36 rows=1 width=64)
           Index Cond: (oid = p.proowner)
 JIT:
   Functions: 8
   Options: Inlining false, Optimization false, Expressions true, Deforming true
(7 rows)

=# select p.proname, (SELECT rolname from pg_catalog.pg_roles where oid = p.proowner) from pg_proc p;
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.
16:42:44 canvas test, cluster 999, standalone db: @, pid:2014255
?!> 

But when I disable jit - query works fine.

versions of things that I think are relevant:

=$ dpkg -l | grep -E 'llvm|clang|gcc|glibc'
ii  gcc                                4:9.3.0-1ubuntu2                      arm64        GNU C compiler
ii  gcc-10-base:arm64                  10.3.0-1ubuntu1~20.04                 arm64        GCC, the GNU Compiler Collection (base package)
ii  gcc-9                              9.3.0-17ubuntu1~20.04                 arm64        GNU C compiler
ii  gcc-9-base:arm64                   9.3.0-17ubuntu1~20.04                 arm64        GCC, the GNU Compiler Collection (base package)
ii  libgcc-9-dev:arm64                 9.3.0-17ubuntu1~20.04                 arm64        GCC support library (development files)
ii  libgcc-s1:arm64                    10.3.0-1ubuntu1~20.04                 arm64        GCC support library
ii  libllvm9:arm64                     1:9.0.1-12                            arm64        Modular compiler and toolchain technologies, runtime library

Best regards,

depesz

---

From: Tom Lane <[email-masked]>
Date: Fri, 04 Mar 2022 14:09:52 -0500

> OK. Traced it back to JIT. With JIT enabled:

Hah, that's useful info.  Seems like it must be incorrect code
generated by JIT.

> ii  libllvm9:arm64                     1:9.0.1-12                            arm64        Modular compiler and toolchain technologies, runtime library

arm64, eh?  I wonder if that's buggier than the Intel code paths.

I tried and failed to reproduce this on Fedora 35 on aarch64,
but that has what I think is a newer LLVM version:

clang-13.0.0-3.fc35.aarch64
clang-libs-13.0.0-3.fc35.aarch64
clang-resource-filesystem-13.0.0-3.fc35.aarch64
gcc-11.2.1-9.fc35.aarch64
gcc-c++-11.2.1-9.fc35.aarch64
llvm-13.0.0-4.fc35.aarch64
llvm-devel-13.0.0-4.fc35.aarch64
llvm-libs-13.0.0-4.fc35.aarch64
llvm-static-13.0.0-4.fc35.aarch64
llvm-test-13.0.0-4.fc35.aarch64

Don't think I can readily install anything as old as LLVM 9 ...

			regards, tom lane

---

From: hubert depesz lubaczewski <[email-masked]>
Date: Fri, 4 Mar 2022 21:45:10 +0100

> but that has what I think is a newer LLVM version:

I have suspicion that it also kinda depends on number of rows in there.

When I deleted some schemas, which included some functions, the problem
disappeared.

I wasn't able to pinpoint specific thing, though, and when I called the
pg_proc + pg_roles query for each separate row - it worked flawlessly.

Best regards,

depesz

---

From: Tom Lane <[email-masked]>
Date: Fri, 04 Mar 2022 17:03:14 -0500

> pg_proc + pg_roles query for each separate row - it worked flawlessly.

Mmm ... it might have just been that the planner chose not to use
JIT when it thought there were fewer rows involved.  Did you check
with EXPLAIN that these cut-down cases still used JIT?

			regards, tom lane

---

From: Mladen Gogala <[email-masked]>
Date: Fri, 4 Mar 2022 21:42:53 -0500

> with EXPLAIN that these cut-down cases still used JIT?

This is interesting and informative answer. How do I check whether JIT 
is used in the explain plan? Can you give me an example?

TIA

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

---

From: Tom Lane <[email-masked]>
Date: Fri, 04 Mar 2022 21:48:33 -0500

> is used in the explain plan? Can you give me an example?

If EXPLAIN prints some stuff about JIT, then JIT is going to be
used, otherwise not.  Here's an example from my (entirely
unsuccessful) attempts to duplicate depesz's problem:

=# explain select p.proname, (SELECT rolname from pg_catalog.pg_roles where oid = p.proowner) from pg_proc p;
                                          QUERY PLAN                                          
----------------------------------------------------------------------------------------------
 Seq Scan on pg_proc p  (cost=0.00..393587.22 rows=47243 width=128)
   SubPlan 1
     ->  Index Scan using pg_authid_oid_index on pg_authid  (cost=0.28..8.30 rows=1 width=64)
           Index Cond: (oid = p.proowner)
 JIT:
   Functions: 8
   Options: Inlining false, Optimization false, Expressions true, Deforming true
(7 rows)

			regards, tom lane

---

From: hubert depesz lubaczewski <[email-masked]>
Date: Sun, 6 Mar 2022 15:04:02 +0100

> with EXPLAIN that these cut-down cases still used JIT?

Hi,
I tore these boxes down, so can't check immediately, but I think
I remember that you're right - single-row queries didn't use JIT.

Best regards,

depesz

---

From: Tom Lane <[email-masked]>
Date: Sun, 06 Mar 2022 11:10:00 -0500

> I remember that you're right - single-row queries didn't use JIT.

FWIW, I went to the trouble of installing Ubuntu Focal on my
raspberry pi to see if I could duplicate this, and I couldn't.
However, what you get from a fresh install now is

$ dpkg -l | grep libllvm                  
ii  libllvm10:arm64                1:10.0.0-4ubuntu1                     arm64        Modular compiler and toolchain technologies, runtime library

not 9.0.1.  I also found that Fedora 31/aarch64 is still downloadable, and
that does contain LLVM 9 ... and I could not reproduce it there either.

So I've run out of things to try.  It still seems like a bug in a specific
point release of LLVM is a possible explanation, especially given that
Debian went so far as to replace that release in an LTS distro.  (The
gcc-related packages are all exactly the same versions as you show.)
I'd believe that data corruption could be a contributing factor too,
except that you were able to read all the rows without JIT.

			regards, tom lane

---

From: hubert depesz lubaczewski <[email-masked]>
Date: Mon, 7 Mar 2022 16:11:28 +0100

> > I remember that you're right - single-row queries didn't use JIT.

Got focal box up. Loaded schema for Pg.

Initially select didn't break anything, but when I tuned down
jit_above_cost so that it will kick in - got fails immediately.

> that does contain LLVM 9 ... and I could not reproduce it there either.

Soo... plot thickens.

Looks that pg 12 supplied by pgdg required libllvm9:

=$ apt-cache show postgresql-12 | grep -E '^(Package|Version|Depends):'
Package: postgresql-12
Version: 12.9-2.pgdg20.04+1
Depends: ..., libllvm9 (>= 1:9~svn298832-1~), ...

Package: postgresql-12
Version: 12.9-0ubuntu0.20.04.1
Depends: ..., libllvm10 (>= 1:9~svn298832-1~), ...

Package: postgresql-12
Version: 12.2-4
Depends: ..., libllvm10 (>= 1:9~svn298832-1~), ...

Newer pg12 (12.10) from pgdg also depends on llvm9. Perhaps changing the deb to
use/depend-on llvm10 would solve the problem, for now I'm not sure how to do
it. Reached to Christoph about it.

Best regards,

depesz

---

From: Tom Lane <[email-masked]>
Date: Mon, 07 Mar 2022 12:22:26 -0500

> jit_above_cost so that it will kick in - got fails immediately.

Hmph.  I tried two more builds:

* downgraded llvm to llvm-9, compiled PG 12 from source

* installed postgresql-12.10-1.pgdg20.04+1 from pgdg repo

Neither of those configurations fail for me, so either
it's been fixed since 12.9, or (more likely) there is
something to your test case beyond what you've mentioned.

(I guess a long-shot possibility is that my raspberry pi 3B+
is sufficiently different hardware from your box as to not
see the problem.  Doubt that though.)

Can you create a self-contained test case?

			regards, tom lane

---

From: Andres Freund <[email-masked]>
Date: Mon, 7 Mar 2022 09:54:22 -0800

Hi,

> jit_above_cost so that it will kick in - got fails immediately.

Could you set jit_debugging_support=on and show a backtrace with that?

> Looks that pg 12 supplied by pgdg required libllvm9:

What are the libllvm package versions on which you can reproduce the crash?

> it. Reached to Christoph about it.

It'd require rebuilding.

It's possible that the problem is one of the patches applied by ubuntu to llvm
and that's why others can't reproduce so far. There's quite a few...

Greetings,

Andres Freund

---

From: hubert depesz lubaczewski <[email-masked]>
Date: Mon, 7 Mar 2022 20:35:40 +0100

> something to your test case beyond what you've mentioned.

Upgraded to 12.10 from pgdg, same problem.

> see the problem.  Doubt that though.)

My "hardware" is AWS EC2 graviton instance (c6g.2xlarge).

> Can you create a self-contained test case?

I tried, but it seems that number of functions/users is a factor, and
I wasn't able to make simple test that would replicate the issue.

I guess I will just disable JIT on arm for now, and be done with it.

Best regards,

depesz

---

From: hubert depesz lubaczewski <[email-masked]>
Date: Tue, 8 Mar 2022 21:15:59 +0100

> Could you set jit_debugging_support=on and show a backtrace with that?

Here you go:
Program received signal SIGSEGV, Segmentation fault.
0x0000fffe7587b6c8 in deform_0_8 ()
(gdb) bt
#0  0x0000fffe7587b6c8 in deform_0_8 ()
#1  0x0000fffe7587b5f4 in evalexpr_0_7 ()
#2  0x0000aaaab67e6f7c in ExecEvalExprSwitchContext (isNull=0xfffff7956217, econtext=0xaaaaf6014608, state=0xaaaaf6014ab0) at ./build/../src/include/executor/executor.h:316
#3  ExecProject (projInfo=0xaaaaf6014aa8) at ./build/../src/include/executor/executor.h:350
#4  ExecScan (node=<optimized out>, accessMtd=0xaaaab68051b8 <SeqNext>, recheckMtd=0xaaaab6805248 <SeqRecheck>) at ./build/../src/backend/executor/execScan.c:239
#5  0x0000aaaab67ded5c in ExecProcNode (node=0xaaaaf60143b8) at ./build/../src/include/executor/executor.h:242
#6  ExecutePlan (execute_once=<optimized out>, dest=0xaaaaf5fc15e8, direction=<optimized out>, numberTuples=0, sendTuples=<optimized out>, operation=CMD_SELECT, use_parallel_mode=<optimized out>, planstate=0xaaaaf60143b8, estate=0xaaaaf5e01360)
    at ./build/../src/backend/executor/execMain.c:1632
#7  standard_ExecutorRun (queryDesc=0xaaaaf5ef4130, direction=<optimized out>, count=0, execute_once=<optimized out>) at ./build/../src/backend/executor/execMain.c:350
#8  0x0000ffffa87751fc in pgss_ExecutorRun (queryDesc=0xaaaaf5ef4130, direction=ForwardScanDirection, count=0, execute_once=true) at ./build/../contrib/pg_stat_statements/pg_stat_statements.c:893
#9  0x0000ffffa875f688 in explain_ExecutorRun (queryDesc=0xaaaaf5ef4130, direction=ForwardScanDirection, count=0, execute_once=true) at ./build/../contrib/auto_explain/auto_explain.c:320
#10 0x0000aaaab69354c8 in PortalRunSelect (portal=portal@entry=0xaaaaf5e83ef0, forward=forward@entry=true, count=0, count@entry=9223372036854775807, dest=dest@entry=0xaaaaf5fc15e8) at ./build/../src/backend/tcop/pquery.c:938
#11 0x0000aaaab6936a64 in PortalRun (portal=portal@entry=0xaaaaf5e83ef0, count=count@entry=9223372036854775807, isTopLevel=isTopLevel@entry=true, run_once=run_once@entry=true, dest=dest@entry=0xaaaaf5fc15e8, altdest=altdest@entry=0xaaaaf5fc15e8, 
    completionTag=completionTag@entry=0xfffff7956848 "") at ./build/../src/backend/tcop/pquery.c:779
#12 0x0000aaaab6932a18 in exec_simple_query (query_string=query_string@entry=0xaaaaf5d9af90 "select p.proname, (SELECT rolname from pg_catalog.pg_roles where oid = p.proowner) from pg_proc p;") at ./build/../src/backend/tcop/postgres.c:1215
#13 0x0000aaaab69336ac in PostgresMain (argc=<optimized out>, argv=argv@entry=0xaaaaf5df6f18, dbname=<optimized out>, username=<optimized out>) at ./build/../src/backend/tcop/postgres.c:4271
#14 0x0000aaaab68ba768 in BackendRun (port=0xaaaaf5df0910, port=0xaaaaf5df0910) at ./build/../src/backend/postmaster/postmaster.c:4510
#15 BackendStartup (port=0xaaaaf5df0910) at ./build/../src/backend/postmaster/postmaster.c:4193
#16 ServerLoop () at ./build/../src/backend/postmaster/postmaster.c:1725
#17 0x0000aaaab68bb74c in PostmasterMain (argc=<optimized out>, argv=<optimized out>) at ./build/../src/backend/postmaster/postmaster.c:1398
#18 0x0000aaaab664c8a8 in main (argc=5, argv=0xaaaaf5d94ea0) at ./build/../src/backend/main/main.c:228

> What are the libllvm package versions on which you can reproduce the crash?

Hmm .. not sure I fully understand. Here are all packages that I think are relevant:

root@c999-pg1302:~# dpkg -l | grep -iE 'postgresql-12|llvm|clang'
ii  libllvm9:arm64                     1:9.0.1-12                            arm64        Modular compiler and toolchain technologies, runtime library
ii  postgresql-12                      12.9-2.pgdg20.04+1                    arm64        The World's Most Advanced Open Source Relational Database
ii  postgresql-12-dbgsym               12.9-2.pgdg20.04+1                    arm64        debug symbols for postgresql-12
ii  postgresql-12-pg-collkey           0.5.1-1insops1                        arm64        ICU collation function wrapper for PostgreSQL 12
ii  postgresql-12-postgis-2.5          2.5.5+dfsg-1.pgdg20.04+2              arm64        Geographic objects support for PostgreSQL 12
ii  postgresql-12-postgis-2.5-scripts  2.5.5+dfsg-1.pgdg20.04+2              all          Geographic objects support for PostgreSQL 12 -- SQL scripts
ii  postgresql-12-repack               1.4.7-2.pgdg20.04+1                   arm64        reorganize tables in PostgreSQL databases with minimal locks
ii  postgresql-12-system-stats         1.0.0-1                               arm64        A packaged version of EnterpriseDB's system_stats postgres extension

Hope it helps.

Best regards,

depesz

---

From: Christoph Berg <[email-masked]>
Date: Thu, 31 Mar 2022 16:01:27 +0200

Re: hubert depesz lubaczewski
> it. Reached to Christoph about it.

Thanks for spotting that. The problem turned out me being not smart
enough to determine the newest llvm version installed; the
not-quite-correct Makefile code failed on "9" < "10".

I have now fixed that problem. Updated postgresql-{11,12,13,14}
packages using libllvm10 instead of libllvm9 are available in
focal-pgdg-testing.

Since this is potentially a breaking change, I'll not push these live
immediately but will give people a chance to test these until next
week.

Christoph

---

From: Christoph Berg <[email-masked]>
Date: Mon, 4 Apr 2022 16:56:36 +0200

Re: To hubert depesz lubaczewski
> immediately but will give people a chance to test these until next

The packages have now been promoted.

Christoph