Dear Maintainer,

I've stumbled over a reproduceable backend crash on arm64 running the following query on my smartmeter table,
please see the following post on pgsql-general for more details:
https://www.postgresql.org/message-id/CAFvQSYRatUx%2Bj3yeVf%3DAVRdDpXBoUNSHrxjcS02kwKQR2dODNg%40mail.gmail.com

the crash seems to be caused by a bug in llvm-14 (which debian's postgresql release is built against), this was suggested by upstream.
disabling the jit solved the crash, also the same query+schema works fine using ubuntu 23 which ships 15.4 + llvm-15.

the query:
SELECT floor(extract(epoch from ts)/60)*60 AS "time", AVG(spannungL1)
as l1, AVG(spannungL2) as l2, AVG(spannungL3) as l3 FROM smartmeter
WHERE ts BETWEEN '2023-12-01T13:01:30.514Z' AND
'2023-12-25T19:01:30.514Z' GROUP BY time order by time;SELECT floor(extract(epoch from ts)/60)*60 AS "time", AVG(spannungL1)
as l1, AVG(spannungL2) as l2, AVG(spannungL3) as l3 FROM smartmeter
WHERE ts BETWEEN '2023-12-01T13:01:30.514Z' AND
'2023-12-25T19:01:30.514Z' GROUP BY time order by time;

it would be therefore be great if postgresql-15 could be built against llvm >= 15, as those are shipped with debian anyway.

*** Reporter, please consider answering these questions, where appropriate ***

   * What led up to the situation?
   * What exactly did you do (or not do) that was effective (or
     ineffective)?
   * What was the outcome of this action?
   * What outcome did you expect instead?

*** End of the template - remove these template lines ***

Hello, anyone?

Version: 15.6-0+deb12u1

I don't think it is a bug in LLVM, those issues (whih have been
independently reported multiple times) are usually very bad memory leaks
due to the way PostgreSQL uses LLVM.

The new 15.6 point release fixes the memory leak for most cases (unless
functions are executed), can you try to upgrade and check whether your
problamatic case is fixed as well? If not, I will reopen this bug
report.


Thanks,

Michael

Hello Michael,

| can you try to upgrade and check whether your
| problamatic case is fixed as well? If not, I will reopen this bug
| report.
| are usually very bad memory leaks
| due to the way PostgreSQL uses LLVM.

I've just tested the query with 15.6 - and the crash still occurs,
despite the closed bug report ;)
As can be seen in my email to the postgresql mailing list this is a
segfault crash, not some failed allocation or the linux oom killer
kicking in.

Also the issue was analyzed by the postgresql devs and they came to
the conclusion it is a bug within llvm-14.
As mentioned, I've tested the same query on the same dataset with the
same version of postgresql (15.5) on the same hardware on ubuntu, with
the sole difference that ubuntu links to llvm-15 and there the query
succeeds and postgresql doesn't crash.

Just to be curious, what was the rational behind linking against the
oldest available llvm version available in the debian repos (I saw 14,
15 and 16 are provided).

Best regards, Clemens

Hi,

Ok, I have reopened it.

If you want to stay on Debian, you can try the Postgres community
repositories from apt.postgresql.org - the postgresql-15 packages for
Debian 12 are linked against llvm16 it seems:

https://wiki.postgresql.org/wiki/Apt
https://apt.postgresql.org/pub/repos/apt/pool/main/p/postgresql-15/postgresql-15_15.6-1.pgdg120%2B2_arm64.deb

I don't know, but Debian will not switch LLVM versions in a stable
release I believe, sorry.


Michael

Hi,


we probably have the same situation in our environment (ARM, official
postgres 14.x from docker hub, segfault, only happens with jit).
I would like to test this on other versions of postgres with other
versions of the llvm version.

how can I find, what version of the llvm library a given version of
postgres is linked to?
Clemens, you have that version list of the postgresql-15 in the original
bug report, listing libllvm14.
how can I get that version list on a different v15 or v16?


Regards
   Stefan

Re: Stefan Heine

It should be visible in the linker flags:

select * from pg_config();

LDFLAGS           │ -Wl,-z,relro -Wl,-z,now -L/usr/lib/llvm-16/lib -Wl,--as-needed

Christoph