From: Tom Lane <tgl@sss.pgh.pa.us>
To: Roland Che <rolandngwa31@gmail.com>
cc: pgsql-novice <pgsql-novice@postgresql.org>
Subject: Re: invalid memory alloc request size Error
In-reply-to: 
 <CAKnNPjwYYijsh9v17_OtDzBHtgMRbffvZjZFk1MnT+J2gbTezg@mail.gmail.com>
References: 
 <CAKnNPjwYYijsh9v17_OtDzBHtgMRbffvZjZFk1MnT+J2gbTezg@mail.gmail.com>
Comments: In-reply-to Roland Che <rolandngwa31@gmail.com>
	message dated "Wed, 05 Apr 2023 05:31:37 -0400"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2174203.1680731164.1@sss.pgh.pa.us>
Date: Wed, 05 Apr 2023 17:46:04 -0400
Message-ID: <2174204.1680731164@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2174204.1680731164%40sss.pgh.pa.us>
Precedence: bulk

Roland Che <rolandngwa31@gmail.com> writes:
> I am having below error when our ANALYZE script runs. Our maintenance
> work_mem is set to 128M but when this analyze script is ran, the
> maintenance work_mem is set to session level at 2G but we are still
> getting the error on some table columns.

> ERROR: invalid memory alloc request size 8589934668.

I am guessing that this happens when one of the pg_statistic entries
for the column exceeds the datum size limit of 1GB.  Do you have
exceedingly wide values, and/or a very high default_statistics_target
setting?

> I saw a past dba had to disabled statistics by setting the statistics
> value to 0 meaning stats will not be updated on such table columns.

Dialing that setting back to zero is probably excessive.  But if you
don't want to back off default_statistics_target overall, you'll
need to set a smaller value for your very wide column(s).

I'm kind of surprised that this is an issue really, because ANALYZE
tends to discriminate against storing very wide values at all.
Can you provide more information about the column that's problematic?
Do you have extended statistics of any sort on it?

			regards, tom lane