RE: ERROR: invalid memory alloc request size when committing transaction

Thanks Simon

Setting wal_level to minimal has indeed allowed it to work. Thanks for the suggestion!

We aren't easily able to change the number of partitions - it consists of a large number of partitioned tables each having a reasonable number of partitions, but they multiply up to a huge number. Probably it needs to be broken into multiple smaller databases, but that's easier said than done.

Thanks again

Cheers
Mike

-----Original Message-----
From: Simon Riggs <simon(dot)riggs(at)enterprisedb(dot)com>
Sent: Thursday, August 12, 2021 7:19 PM
To: Michael Harris <michael(dot)harris(at)ericsson(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>; pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Re: ERROR: invalid memory alloc request size when committing transaction

On Thu, 12 Aug 2021 at 06:42, Michael Harris <michael(dot)harris(at)ericsson(dot)com> wrote:
>
> Thanks Tom,
>
> > How many is "a large number"?
>
> 377k approx.

I'm going to guess that it is the invalidation messages for all the DDL that is causing the memory allocation error. If you set wal_level = minimal then this might work.

The total number indicates you are using too many partitions and should probably lower that by a factor of about 100.
If you do that, you probably won't need to set wal_level.

--
Simon Riggs http://www.EnterpriseDB.com/