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
nexttime 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
backto 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
specificerror.
Cheers
Mike
-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Thursday, August 12, 2021 1:41 PM
To: Michael Harris <michael.harris@ericsson.com>
Cc: 'pgsql-general@lists.postgresql.org' <pgsql-general@lists.postgresql.org>
Subject: Re: ERROR: invalid memory alloc request size when committing transaction
Michael Harris <michael.harris@ericsson.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@218006 218015@[local] db_vrqv1
> ERROR: invalid memory alloc request size 1073741824
> [2021-08-11 11:27:50 CEST] aue_75@218006 218015@[local] db_vrqv1
> STATEMENT: commit
> [2021-08-11 11:27:50 CEST] aue_75@218006 218015@[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
onhow large a WAL commit record, or something related to that, can be. Can you adjust your migration script to break
itinto multiple transactions that process fewer tables per transaction?
regards, tom lane