Message-ID: <82ffe6ad-a219-4255-9fea-daff619a1ca0@vondra.me>
Date: Fri, 4 Oct 2024 21:11:15 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: PostgreSQL 17 Segmentation Fault
To: Cameron Vogt <cvogt@automaticcontrols.net>,
 Michael Paquier <michael@paquier.xyz>, Sean Massey <sean.f.massey@gmail.com>
Cc: "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
References: 
 <6aca4816-be12-4f54-9eba-8c9b55cdc801.094840f8-2c1e-4d5a-9984-8ac00be38080.9eae5fd3-5d56-4ffb-9d4c-3e272ee57cba@emailsignatures365.codetwo.com>
 <DM6PR16MB314588EDCBF487F0C1EF7BCEAB702@DM6PR16MB3145.namprd16.prod.outlook.com>
 <CAJfn5C0Kqn52wxVqRBaFSyTf4O6Avo6xoLoS76SWysRQWjx4Rw@mail.gmail.com>
 <Zv3vkGRGKaCyMyva@paquier.xyz>
 <BY5PR16MB31442E65985FE0817C1D2C85AB712@BY5PR16MB3144.namprd16.prod.outlook.com>
Content-Language: en-US
From: Tomas Vondra <tomas@vondra.me>
In-Reply-To: 
 <BY5PR16MB31442E65985FE0817C1D2C85AB712@BY5PR16MB3144.namprd16.prod.outlook.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/82ffe6ad-a219-4255-9fea-daff619a1ca0%40vondra.me>
Precedence: bulk

Hi,

Thanks for the provided information. Per the backtrace, the failure
happens in the LLVM JIT code in  nestloop/seqscan, so it has to be in
this part of the plan:

  ->  Nested Loop  (cost=0.42..6074.84 rows=117 width=641)
        ->  Parallel Seq Scan on tasks__projects  (cost=0.00..2201.62
rows=745 width=16)
              Filter: (gid = '1138791545416725'::text)
        ->  Index Scan using tasks_pkey on tasks tasks_1
(cost=0.42..5.20 rows=1 width=102)
              Index Cond: (gid = tasks__projects._sdc_source_key_gid)
              Filter: ((NOT completed) AND (name <> ''::text))

But it's not clear why this should consume a lot of memory, though. It's
possible the memory is consumed elsewhere, and this is simply the straw
that breaks the camel's back ...

Presumably it takes a while for the query to consume a lot of memory and
crash - can you attach a debugger to it after after it allocates a lot
of memory (but before the crash), and do this:

  call MemoryContextStats(TopMemoryContext)

That should write memory context stats to the server log. Perhaps that
will tell us which part of the query allocates memory.

Next, try running the query with jit=off. If that resolves the problem,
maybe it's another JIT issue. But if it completes with lower shared
buffers, that doesn't seem likely.

The plan has a bunch of hash joins. I wonder if that might be causing
issues, because the hash tables may be kept until the end of the query,
and each may be up to 64MB (you have work_mem=32, but there's also 2x
multiplier since PG13). The row estimates are pretty low, but could it
be that the real row counts are much higher? Did you run analyze after
the upgrade? Maybe try with lower work_mem?

One last thing you should check is memory overcommit. Chances are it's
set just low enough for the query to hit it with SB=4GB, but not with
SB=3GB. In that case you may need to tune this a bit. See /proc/meminfo
and /proc/sys/vm/overcommit_*).


regards

-- 
Tomas Vondra