Re: Restoring a pg_dump fails with

Reiner Dassing <dassing(at)wettzell(dot)ifag(dot)de> writes:
> Sorry for coming up again with the same problem:
> COPY FROM fails with "Memory exhausted in AllocSetAlloc(40)"

> CREATE TABLE "wetter" (
> "sensor_id" int4 NOT NULL REFERENCES sensoren_an_orten,
> "epoche" datetime NOT NULL,
> "wert" float4 NOT NULL,
> PRIMARY KEY (sensor_id,epoche));

Ah-hah, so there *is* an AFTER trigger on "wetter", namely the one that
enforces the REFERENCES check.

Probably you are running out of memory for the list of pending trigger
activations; there will be one entry in it per row inserted during the
current transaction, so I can easily believe that you might have a
problem after a couple million rows have been loaded by COPY.

We have a TO-DO item to allow the trigger list to be shoved out to disk
when it gets too long, but that's not done yet ...

I suggest you either break your data load into chunks of no more than
a million rows, or (if you are sure the incoming data is good) disable
the table's triggers during bulk load. For an example of doing that,
look at the script that pg_dump emits for data-only backup.

regards, tom lane