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

Thanks Tom,

> How many is "a large number"?

377k approx.

I thought we had logged all the SQL statements last time we ran this, but we forgot to set log_statement. We'll do that next time so we can get a figure on exactly how many of different types of statement are in the transaction.

> Can you adjust your migration script to break it into multiple transactions that process fewer tables per transaction?

We are considering this. It is not ideal, because if conversion fails for any reason we would like the database to roll back to the pre-migration state, but maybe that's not possible with a database of this size.

I am surprised by the error message though - I thought that if we'd hit some limit on a transaction we would get a more specific error.

Cheers
Mike

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

Michael Harris <michael(dot)harris(at)ericsson(dot)com> writes:
> - Next we run a migration script on each database in the cluster. The migration script
> converts a large number of tables from inheritance based partitioning to declarative
> partitioning. Unfortunately I am not at liberty to share the migration script.

How many is "a large number"?

> The migration is performed inside a transaction, and during the
> process of committing the transaction the following error is thrown:
> [2021-08-11 11:27:50 CEST] aue_75(at)218006 218015(at)[local] db_vrqv1
> ERROR: invalid memory alloc request size 1073741824
> [2021-08-11 11:27:50 CEST] aue_75(at)218006 218015(at)[local] db_vrqv1
> STATEMENT: commit
> [2021-08-11 11:27:50 CEST] aue_75(at)218006 218015(at)[local] db_vrqv1
> WARNING: AbortTransaction while in COMMIT state

It's impossible to do more than speculate with this amount of information.
However, a plausible speculation is that you're processing so many objects in one transaction as to overrun the limit on how large a WAL commit record, or something related to that, can be. Can you adjust your migration script to break it into multiple transactions that process fewer tables per transaction?

regards, tom lane