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

Laurent FAILLIE <l_faillie(at)yahoo(dot)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.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

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