References: 
 <CA+wnhO1OfgXbmXgC4fv_uu=OxcDQuHvfoQ4k0DFeB0Qqd-X-rQ@mail.gmail.com>
 <2878480.1727017360@sss.pgh.pa.us>
 <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>
In-Reply-To: 
 <CA+hUKG+YZpOyP-XXT65o9hx=oGF+9-BJzjqzRXcdvWV=ZrJbTA@mail.gmail.com>
From: Craig Milhiser <craig@milhiser.com>
Date: Mon, 23 Sep 2024 21:43:50 -0400
Message-ID: 
 <CA+wnhO2NDZFWB1TrZga-uLzpfSDjKH5Axa2d2+1Q76JSsS7yMg@mail.gmail.com>
Subject: Re: Reference to - BUG #18349: ERROR: invalid DSA memory alloc
 request size 1811939328, CONTEXT: parallel worker
To: Thomas Munro <thomas.munro@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-bugs@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000db7e410622d3a314"
MIME-Version: 1.0
X-MS-Exchange-AntiSpam-MessageData-ChunkCount: 1
X-MS-Exchange-AntiSpam-MessageData-0: 
	=?utf-8?B?ZmJoYS82NUI1ekZjZzRpamZUNHVrdll3OGllb3cwbW1wdW44SUhOVTQva2g0?=
 =?utf-8?B?N3hEOU9NN3VOWU9NbllsanJKdzJ1dlp5eHBvc2tOckxiKzZIUFE1UFBJdW1a?=
 =?utf-8?B?RGJ4VEI0N3doa21KRWFEc2tMbjlTWnB1eG1RdkFleSs4ZHNMRWhYbHVUN3pN?=
 =?utf-8?B?SVpkRkF2ZUkxTC9VbURqVCtyNjZtWXlubkdNaFJYckF5UGdTZjV2UmkrZXNm?=
 =?utf-8?B?YmF2cTBUd2JxQ0gzcTQ1S0JqVmtxdkxueDk2QkZzdFJ3NTVNMklwVFhNU2NH?=
 =?utf-8?B?UHo1UnpueWlMZDBhSi9USFhZVjM0S1dNdlJPZG9SODhVbWlKTzFOcURvZGxq?=
 =?utf-8?B?ZFNqcXByUzR3RFdxSm1CV0pVRzFTd3MraStXMGJBTWRCZE1MSXhoZ3ZLZ1NB?=
 =?utf-8?B?QXEzSFNvL0o0MWh0c3p6NDR1V1YwZlNMOW9DRjJ1V0FLZUpnOThsTXdkTy9O?=
 =?utf-8?B?OXZkWWVjVW00SkorUGpUd3RJMk51U2FkWFVQbzlFUERkMFNRb2J1TC8zTmdU?=
 =?utf-8?B?RGdKbnBqTVZXek1NUzlHRTlrZ1Fmc1ZVZWdBMGtNYlR0SzFnc3hwc3VJTWVZ?=
 =?utf-8?B?bzhJb1lKVjBiRUpMSkpFVWl2b3BCZzY2OVpsTzZ2VzlsVndmQlIybzBPaHRU?=
 =?utf-8?B?dU9WZlJRb1VzblQ0U0ZTcW5HSXNDQWNXZ3J6WTNVQ3NlSk5mSklCdmQ0SENm?=
 =?utf-8?B?ZWlVMkM2alpIU2dZVmtsRXIxSFE1K0duTHl5Wk1TV3p6Z050TGJ4Vnd0OFZr?=
 =?utf-8?B?eFM0aTRTbzFXajJ5eFl1dGlTbG5EbC9JWWh6SXJ6ZThaQUhtRHZhd3FBOGY5?=
 =?utf-8?B?NU5qaVZjTnAwYmlhbXoyZFVKRzJZZ0NvOFRjR1ZhSmpKNUhQeEdvTWpyM09Y?=
 =?utf-8?B?emo5aUtoY0NYSnZyV1U2VG1sWXlQTUpFNWlDVUpoWlAyZFk5ejYxdWdtQVB0?=
 =?utf-8?B?MFZHTWtPZitaSWduN1ppWEtYc3pJaFJDVXE5d0dTeTRvNS85UWI1VnN6a0do?=
 =?utf-8?B?UlE4d1pQQTdhckxTSm9TQjRMbWhiRTlEYTcvbU1HWlFrNzVZUExIcHBkcFhl?=
 =?utf-8?B?VHBzL0F5ZEFwbzRBK29rWCs5YU9ZZ2hybGNsTythaDFnL1ZJY0xpako2SnZl?=
 =?utf-8?B?amozZUxIc3ltaXdtRWc3SmpzT2hYMndPWnJDQlQrdVowTk4rNE14M0NRcWVF?=
 =?utf-8?B?a0UxRTJXNDRraGJ5UnBOZFBxY0loRUZDRloyQ09sMkhzVkRDWWlsNnZpdy9Z?=
 =?utf-8?B?RDBnY0lVOUd6eUUxc0Ztc1BLUGNlVU0ybGhzWG56cDFpK0lMQVE2NmxJVUlG?=
 =?utf-8?B?eiszRXVCc0dxQytkVXBRbklqS3YyMzIvNmxBYkNhL04xcUYyTUpPUzlvTHJM?=
 =?utf-8?B?Y0VoZVA5NGZWNU90U3VDRlZGZjdERWZmYW1PR1R6MS9jemZSL1NiZWFnNXNF?=
 =?utf-8?B?aDdxcjBEOTBSaXFVR0R2a05YUlNRZDV1QmlRUXQvV2xHV0xVU0lxY29LQ2xB?=
 =?utf-8?B?Q3hIUGd5cUhtNURTYXpLVXh4ZXR6eVRjRUo0L3RaZ3Myc3NtYjFyQXZOWlpG?=
 =?utf-8?B?aGRkdVpkTUlhekxwQVpxZGxWTlB4RGFYc3ErR1EzVmdrWTJFREVXb3BiQVpN?=
 =?utf-8?B?RFhFUXczREc4QnRTa2lGTDlzTU5zOFdmdHlRNEd2TFFMSy9jRVBjNERnV3p2?=
 =?utf-8?B?T2NVVFF2RENpSnliMTRhZkx0b0l4OUoyY1ZlMHJOUXl4b0hyZEo0M3B6WWs4?=
 =?utf-8?B?dTBjN0tsRlVEenBwK29Qc0QxV1k4Y0xmK0FFMFNqUWNkcXpualBvUElHdVp0?=
 =?utf-8?B?WDgzMzJuKys5Z21EUlBFcWJpa0dEZCttQlUzT2x6VUtvei9oSzdTVmpFZEpZ?=
 =?utf-8?B?WG44WG1ML3pUWjJ5M0VNR3d3MitMWnVmajRCNGY0QTdVUHRDTDFaVlYzM0cx?=
 =?utf-8?B?dWpmeWRMMmlDNmJYNUpLbCsxcjgvRFduZXQ3UW9KTlJBQ1VKc1pZeExGY2hk?=
 =?utf-8?B?ZXpPeTZUWmg5TkkzdXJHR01lS2paeVZSSFpjeXJQeUZsR1ZqbU5ROHV5Qmdw?=
 =?utf-8?B?NzltdW5vOHc3WnpvRkNlZ0pJYWdBdlBHelRwYXZKV2Z6YkFQdStocjM2M214?=
 =?utf-8?Q?0UqGVX4hR6d5DOCqqVAWpBz/V?=
X-OriginatorOrg: milhiser.com
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 52151dfa-7f43-4468-2163-08dcdc3a605f
X-MS-Exchange-CrossTenant-AuthSource: BL1PR11MB5544.namprd11.prod.outlook.com
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 24 Sep 2024 01:44:09.2650
 (UTC)
X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted
X-MS-Exchange-CrossTenant-Id: 538ddb49-3475-41a4-a685-313f6b534707
X-MS-Exchange-CrossTenant-MailboxType: HOSTED
X-MS-Exchange-CrossTenant-UserPrincipalName: 
 TT03cNPU4yxcuOuhyQhE/IcxVgRFtS/YSO3kHagyWVP5Qxu7cRUUT3ntRwyN0z1x7YL8WkWFAqarueN4u2y0iw==
X-MS-Exchange-Transport-CrossTenantHeadersStamped: PH0PR11MB4999
List-Id: <pgsql-bugs.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-bugs@lists.postgresql.org>
List-Owner: <mailto:pgsql-bugs-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-bugs>
Archived-At: 
 <https://www.postgresql.org/message-id/CA%2BwnhO2NDZFWB1TrZga-uLzpfSDjKH5Axa2d2%2B1Q76JSsS7yMg%40mail.gmail.com>
Precedence: bulk

--000000000000db7e410622d3a314
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Sun, Sep 22, 2024 at 10:23=E2=80=AFPM Thomas Munro <thomas.munro@gmail.c=
om>
wrote:

> On Mon, Sep 23, 2024 at 1:46=E2=80=AFPM Thomas Munro <thomas.munro@gmail.=
com>
> wrote:
> > 432 bytes
>
> Oh, as Tomas pointed out in the referenced thread,


Thanks for working on it and the detailed explanation. I tested set
max_parallel_workers_per_gather =3D 0 from the original thread and it was
working. We are putting that into the application, for our largest
customers. Set to 0 before the query then back to 2 after.

Your explanation also shows why rewriting of the query works. I reduced the
number of rows being processed much earlier in the query. The query was
written with 1 set of many joins which worked on millions of rows then
reduced to a handful. I broke this into a materialized CTE that forced
Postgres to reduce the rows early then do the joins.  Rewriting the query
is better regardless of this issue.

I am working on getting a stock Postgres in our production protected
enclave with our production database.  Probably a full day of work that I
need to splice in.  We have a similar mechanism in our development
environment. Once working I can help test and debug any changes. I can also
work on a reproducible example.

>
>

--000000000000db7e410622d3a314
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Dutf-8"><d=
iv><br></div><div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"=
gmail_attr">On Sun, Sep 22, 2024 at 10:23=E2=80=AFPM Thomas Munro &lt;<a hr=
ef=3D"mailto:thomas.munro@gmail.com">thomas.munro@gmail.com</a>&gt; wrote:<=
br></div><blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;borde=
r-left:1px #ccc solid;padding-left:1ex" dir=3D"auto">On Mon, Sep 23, 2024 a=
t 1:46=E2=80=AFPM Thomas Munro &lt;<a href=3D"mailto:thomas.munro@gmail.com=
" target=3D"_blank">thomas.munro@gmail.com</a>&gt; wrote:<br>
&gt; 432 bytes<br>
<br>
Oh, as Tomas pointed out in the referenced thread,</blockquote><blockquote =
class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid=
;padding-left:1ex" dir=3D"auto"></blockquote><div dir=3D"auto"><br></div><d=
iv dir=3D"auto">Thanks for working on it and the detailed explanation. I te=
sted set max_parallel_workers_per_gather =3D 0 from the original thread and=
 it was working. We are putting that into the application, for our largest =
customers. Set to 0 before the query then back to 2 after.&nbsp;</div><div =
dir=3D"auto"><br></div><div dir=3D"auto">Your explanation also shows why re=
writing of the query works. I reduced the number of rows being processed mu=
ch earlier in the query. The query was written with 1 set of many joins whi=
ch worked on millions of rows then reduced to a handful. I broke this into =
a materialized CTE that forced Postgres to reduce the rows early then do th=
e joins.&nbsp; Rewriting the query is better regardless of this issue.&nbsp=
;</div><div dir=3D"auto"><br></div><div dir=3D"auto">I am working on gettin=
g a stock Postgres in our production protected enclave with our production =
database.&nbsp; Probably a full day of work that I need to splice in.&nbsp;=
 We have a similar mechanism in our development environment. Once working I=
 can help test and debug any changes. I can also work on a reproducible exa=
mple.&nbsp;</div><blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8=
ex;border-left:1px #ccc solid;padding-left:1ex" dir=3D"auto"><br>
</blockquote></div></div>

--000000000000db7e410622d3a314--