Message-ID: <96a328e9-4fbf-433b-a8d7-071da18e12c4@gmail.com>
Date: Mon, 14 Oct 2024 12:45:09 +0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Reference to - BUG #18349: ERROR: invalid DSA memory alloc
 request size 1811939328, CONTEXT: parallel worker
To: Thomas Munro <thomas.munro@gmail.com>, Craig Milhiser <craig@milhiser.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-bugs@lists.postgresql.org
References: 
 <CA+wnhO1OfgXbmXgC4fv_uu=OxcDQuHvfoQ4k0DFeB0Qqd-X-rQ@mail.gmail.com>
 <CA+wnhO1zP13p5sn6d-top=-gmCnE+ymYEfkPbN2d=QKWMbp6Sg@mail.gmail.com>
 <CA+hUKGJo9H75w3QoZrZCVm3eGg7ifJ0UvZZMw6bz=HCpwg0gKw@mail.gmail.com>
 <CA+hUKG+YZpOyP-XXT65o9hx=oGF+9-BJzjqzRXcdvWV=ZrJbTA@mail.gmail.com>
 <CA+wnhO2NDZFWB1TrZga-uLzpfSDjKH5Axa2d2+1Q76JSsS7yMg@mail.gmail.com>
 <CA+wnhO0S2kxJ2_M57uJXRocMpcDtz6N4RkP-3Xy=pFs1wp7hmg@mail.gmail.com>
 <CA+hUKGLPKWamfKjtEkPLvRDSeOqoZZ0wDfU65d-hF-1upL8iGw@mail.gmail.com>
 <CA+wnhO3kT7mxbadbteppSZVcs3FRPvGL5xTY2DbrWSGohmhyVw@mail.gmail.com>
 <6785e9e8-4c64-41b0-9e72-c13d1822b599@gmail.com>
 <CA+wnhO0VdTP9OVdZP9bmkavma2WkE-pYpP1yW8yDwRrJ97j-2g@mail.gmail.com>
 <0c3b0e1b-55d1-42b6-8c3c-4392479187db@gmail.com>
 <CA+wnhO2sHcUOfi6_gvaoPMk=2DsKUOWkDjdUtGxJOguQOXDvOQ@mail.gmail.com>
 <cc488d1c-6477-4e20-8bd0-cf152a2e59bf@gmail.com>
 <CA+wnhO3t7xqCiDmGduxgg1Mv5M5R-GeQd0kzVcJKkUFkj91Wbw@mail.gmail.com>
 <CA+hUKGLYx2GGd8BggND5QnyyJKbf1r+Q0cEYWrN+miXzaxwGNA@mail.gmail.com>
Content-Language: en-AU
From: Andrei Lepikhov <lepihov@gmail.com>
In-Reply-To: 
 <CA+hUKGLYx2GGd8BggND5QnyyJKbf1r+Q0cEYWrN+miXzaxwGNA@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/96a328e9-4fbf-433b-a8d7-071da18e12c4%40gmail.com>
Precedence: bulk

On 10/14/24 04:08, Thomas Munro wrote:
> On Mon, Oct 14, 2024 at 12:23 AM Craig Milhiser <craig@milhiser.com> wrote:
>> I have reproduced the problem with synthetic data. The script is below. Thank you for your patience with me.
> 
> Thanks, repro'd here.  At first glance, it looks like it's trying to
> load this distribution into a hash table and failing to handle the
> skew as well as non-parallel hash:
> Getting coffee and looking more closely...
Hmm, with reproduction, it is too easy to solve ;)
My explanation (correct if I'm wrong):
OUTER JOINs allow NULLs to be in a hash table. At the same time, a hash 
value for NULL is 0, and it goes to the batch==0.
If batch number 0 gets overfilled, the 
ExecParallelHashIncreaseNumBatches routine attempts to increase the 
number of batches - but nothing happens. The initial batch is still too 
big, and the number of batches doubles up to the limit.
At the limit, parallel HashJoin stops this grow and (I didn't trace this 
part, just guess) allocates memory for 2097152 batches that causes OOM.
To support this chain of thought, you can see the simple example below, 
which triggers the issue:

DROP TABLE IF EXISTS test;
CREATE TABLE test (n int);
INSERT INTO test (n) SELECT NULL FROM generate_series(1,1E6);
INSERT INTO test (n) VALUES (1, 'a');
ANALYZE test;

SET enable_nestloop = 'off';
SET enable_mergejoin = 'off';

SET max_parallel_workers_per_gather = 2;
SET min_parallel_table_scan_size = 0;
SET min_parallel_index_scan_size = 0;
SET parallel_setup_cost = 0.001;
SET parallel_tuple_cost = 0.0001;

EXPLAIN (ANALYZE, VERBOSE, COSTS OFF)
SELECT t1.n FROM test t1 LEFT JOIN test t2 USING (n);

I think, now it is much easier to find a proper solution.

-- 
regards, Andrei Lepikhov