Thread: "ERROR: out of memory" during pg_restore

I've got a 7.4 database that gives postgres an "out of memory" error
when restoring into a 32-bit build 8.1, yet restores into a 64-bit
build of 8.1.

I dumped a 7.4.9 database, running on Debian/testing, 32-bit, using
this command:

    pg_dump --format=c --blobs production --verbose >production.dbarchive

This results in a 37G file.  The great bulk of this database is in
large objects.

I can restore it into 8.1.0, running on Debian testing, 64-bit, using
this command:

    pg_restore -d production production.dbarchive

However, I cannot restore it into 8.1.3, running on Debian testing,
32-bit, using the same command.  I get this in the postgres log:

TopMemoryContext: 45592 total in 4 blocks; 4032 free (31 chunks); 41560 used
Filesystem: -1367351296 total in 361 blocks; 34704 free (305 chunks); -1367386000 used
TopTransactionContext: 8192 total in 1 blocks; 7856 free (0 chunks); 336 used
PLpgSQL function cache: 8192 total in 1 blocks; 5968 free (0 chunks); 2224 used
CFuncHash: 8192 total in 1 blocks; 4936 free (0 chunks); 3256 used
Type information cache: 8192 total in 1 blocks; 1864 free (0 chunks); 6328 used
Operator class cache: 8192 total in 1 blocks; 4936 free (0 chunks); 3256 used
MessageContext: 8192 total in 1 blocks; 1952 free (2 chunks); 6240 used
smgr relation table: 24576 total in 2 blocks; 14080 free (3 chunks); 10496 used
Portal hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used
PortalMemory: 8192 total in 1 blocks; 8176 free (1 chunks); 16 used
Relcache by OID: 8192 total in 1 blocks; 2336 free (0 chunks); 5856 used
CacheMemoryContext: 1040384 total in 7 blocks; 226336 free (1 chunks); 814048 used
pg_largeobject_loid_pn_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
pg_toast_2618_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
pg_rewrite_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_constraint_contypid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_constraint_conrelid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_constraint_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_constraint_conname_nsp_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
pg_shdepend_depender_index: 1024 total in 1 blocks; 256 free (0 chunks); 768 used
pg_attrdef_adrelid_adnum_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
pg_attrdef_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_pltemplate_name_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_shdepend_reference_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
pg_depend_depender_index: 1024 total in 1 blocks; 256 free (0 chunks); 768 used
pg_depend_reference_index: 1024 total in 1 blocks; 256 free (0 chunks); 768 used
pg_index_indrelid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_type_typname_nsp_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
pg_type_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
pg_statistic_relid_att_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
pg_auth_members_member_role_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
pg_auth_members_role_member_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
pg_proc_proname_args_nsp_index: 1024 total in 1 blocks; 256 free (0 chunks); 768 used
pg_proc_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_operator_oprname_l_r_n_index: 1024 total in 1 blocks; 192 free (0 chunks); 832 used
pg_operator_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_opclass_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_opclass_am_name_nsp_index: 1024 total in 1 blocks; 256 free (0 chunks); 768 used
pg_namespace_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_namespace_nspname_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_language_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_language_name_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_inherits_relid_seqno_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
pg_index_indexrelid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_authid_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_authid_rolname_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_database_datname_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_conversion_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_conversion_name_nsp_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
pg_conversion_default_index: 1024 total in 1 blocks; 192 free (0 chunks); 832 used
pg_class_relname_nsp_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
pg_class_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_cast_source_target_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
pg_amproc_opc_proc_index: 1024 total in 1 blocks; 256 free (0 chunks); 768 used
pg_amop_opr_opc_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
pg_amop_opc_strat_index: 1024 total in 1 blocks; 256 free (0 chunks); 768 used
pg_aggregate_fnoid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
MdSmgr: 8192 total in 1 blocks; 2608 free (0 chunks); 5584 used
LockTable (locallock hash): 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used
Timezones: 47592 total in 2 blocks; 5968 free (0 chunks); 41624 used
ErrorContext: 8192 total in 1 blocks; 8176 free (3 chunks); 16 used
ERROR:  out of memory
DETAIL:  Failed on request of size 80.

What other information can I supply?  What experiments should I do?

Thanks in advance for any help you can offer.

        Wayne Conrad

Wayne Conrad <wconrad@yagni.com> writes:
> I've got a 7.4 database that gives postgres an "out of memory" error
> when restoring into a 32-bit build 8.1, yet restores into a 64-bit
> build of 8.1.

> Filesystem: -1367351296 total in 361 blocks; 34704 free (305 chunks); -1367386000 used

Now that I look at it, it's pretty obvious that the backend's
large-object functions all risk leaking memory that won't be recovered
till end of transaction.  I'm not sure why this wasn't noticed before
... maybe the potential leaks were only potential, or something else
changed about the usage pattern.  Anyway, it clearly needs to be fixed
to avoid problems when a single transaction executes a whole lot of
large-object operations.  I've applied the attached patch to 8.1.*,
but it could use more testing --- do you want to patch locally and
confirm it's OK for you?

            regards, tom lane

Tom, You bet.  I'll give it a go and report back.

On Tue, Apr 25, 2006 at 08:39:46PM -0400, Tom Lane wrote:
> I've applied the attached patch to 8.1.*,
> but it could use more testing --- do you want to patch locally and
> confirm it's OK for you?