Postgres Pro
Downloads
Home   >   mailing lists

Thread: pg_dump: ERROR: array size exceeds the maximum allowed (268435455)

pg_dump: ERROR: array size exceeds the maximum allowed (268435455)

From
Laurent FAILLIE
Date:
Hello,

I'm trying to save a database from a dying disk on a Gentoo/Linux box.

Unfortunately, when I'm issuing a

$ pg_dumpall --clean

I got

pg_dump: [programme d'archivage (db)] échec de la requête : ERROR:  array size exceeds the maximum allowed (268435455)
pg_dump: [programme d'archivage (db)] la requête était : SELECT p.tableoid, p.oid, p.proname AS aggname, p.pronamespace AS aggnamespace, p.pronargs, p.proargtypes, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = p.proowner) AS rolname, (SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM pg_catalog.unnest(coalesce(p.proacl,pg_catalog.acldefault('f',p.proowner))) WITH ORDINALITY AS perm(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('f',p.proowner))) AS init(init_acl) WHERE acl = init_acl)) as foo) AS aggacl, (SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('f',p.proowner))) WITH ORDINALITY AS initp(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(p.proacl,pg_catalog.acldefault('f',p.proowner))) AS permp(orig_acl) WHERE acl = orig_acl)) as foo) AS raggacl, NULL AS initaggacl, NULL AS initraggacl FROM pg_proc p LEFT JOIN pg_init_privs pip ON (p.oid = pip.objoid AND pip.classoid = 'pg_proc'::regclass AND pip.objsubid = 0) WHERE p.proisagg AND (p.pronamespace != (SELECT oid FROM pg_namespace WHERE nspname = 'pg_catalog') OR p.proacl IS DISTINCT FROM pip.initprivs)
pg_dumpall : échec de pg_dump sur la base de données « postgres », quitte

All references I found about this error is about user made request.
Any clue to how I can correct this issue ? (as said, my disk is dying due to its controller failure so, obviously time is running :) ).
This database contains my smart home figures so nothing critical and I have a "weeks old" backup.

This box is running
    pg_dump (PostgreSQL) 10.3
but I was on way to upgrade to 10.6 when I discovered this issue.

Best regards,

Laurent

Re: pg_dump: ERROR: array size exceeds the maximum allowed(268435455)

From
Ron
Date:
On 12/10/2018 03:15 PM, Laurent FAILLIE wrote:
Hello,

I'm trying to save a database from a dying disk on a Gentoo/Linux box.

[snip]
This box is running
    pg_dump (PostgreSQL) 10.3
but I was on way to upgrade to 10.6 when I discovered this issue.

Since 10.3 and 10.6 are the same primary version, a binary backup would work just as well.


--
Angular momentum makes the world go 'round.

Re: pg_dump: ERROR: array size exceeds the maximum allowed (268435455)

From
Tom Lane
Date:
Laurent FAILLIE <l_faillie@yahoo.com> writes:
> I'm trying to save a database from a dying disk on a Gentoo/Linux box.
> Unfortunately, when I'm issuing a
> $ pg_dumpall --clean
> I got

> pg_dump: [programme d'archivage (db)] échec de la requête : ERROR:  array size exceeds the maximum allowed
(268435455)
> pg_dump: [programme d'archivage (db)] la requête était : SELECT p.tableoid, p.oid, p.proname AS aggname,
p.pronamespaceAS aggnamespace, p.pronargs, p.proargtypes, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid =
p.proowner)AS rolname, (SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM
pg_catalog.unnest(coalesce(p.proacl,pg_catalog.acldefault('f',p.proowner)))WITH ORDINALITY AS perm(acl,row_n) WHERE NOT
EXISTS( SELECT 1 FROM pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('f',p.proowner))) AS
init(init_acl)WHERE acl = init_acl)) as foo) AS aggacl, (SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT
acl,row_n FROM pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('f',p.proowner))) WITH ORDINALITY AS
initp(acl,row_n)WHERE NOT EXISTS ( SELECT 1 FROM
pg_catalog.unnest(coalesce(p.proacl,pg_catalog.acldefault('f',p.proowner)))AS permp(orig_acl) WHERE acl = orig_acl)) as
foo)AS raggacl, NULL AS initaggacl, NULL AS initraggacl FROM pg_proc p LEFT JOIN pg_init_privs pip ON (p.oid =
pip.objoidAND pip.classoid = 'pg_proc'::regclass AND pip.objsubid = 0) WHERE p.proisagg AND (p.pronamespace != (SELECT
oidFROM pg_namespace WHERE nspname = 'pg_catalog') OR p.proacl IS DISTINCT FROM pip.initprivs) 
> pg_dumpall : échec de pg_dump sur la base de données « postgres », quitte

This looks like a manifestation of data corruption in the system
catalogs, specifically a broken proacl field in a pg_proc row for
an aggregate function.  Now typically, all those fields would just
be null, so you might be able to recover by looking for such rows
and setting their proacl fields back to null.  At worst, if you'd
actually granted or revoked any privileges on aggregates, you'd
lose that ... but in a data recovery situation, that's probably
not your worst problem.  Try

select proname, proacl from pg_proc where proisagg and proacl is not null;

and see what you get.

It's likely that the affected row(s) have more problems than this,
in which case deleting them altogether might be your best bet.

            regards, tom lane