Re: Postgresql out of memory during big transaction

Marc Van Olmen <mvo(at)sky4studios(dot)be> writes:
> [ out of memory while ]
> * trying to import about 20GBytes of data (40K rows + rest large image blob's)

> What I notice is that the python app stays around 200Mbytes of memory usage, but the postgres process on my MacOSX 10.9.5 is growing and growing. Until it runs out of memory (running 32-bit version).

Hm ... why are you running a 32-bit build on recent OS X? I'm pretty sure
that OS X release doesn't support any non-64-bit hardware. Not that the
leak isn't a problem, but you'd have a bunch more headroom with a 64-bit
executable.

> * The database has several Triggers that are fired written in PL/Python
> * some of them are simple "NOTIFY ..;" others are 500 lines of python code
> ...

> Error I see:
> TopMemoryContext: 64727172 total in 7901 blocks; 132784 free (7871 chunks); 64594388 used
> CFuncHash: 8192 total in 1 blocks; 4936 free (0 chunks); 3256 used
> PL/Python procedures: 8192 total in 1 blocks; 4872 free (0 chunks); 3320 used
> SPI exceptions: 8192 total in 1 blocks; 2328 free (0 chunks); 5864 used
> Rendezvous variable hash: 8192 total in 1 blocks; 3848 free (0 chunks); 4344 used
> TopTransactionContext: 3859664 total in 6 blocks; 98504 free (31 chunks); 3761160 used
> CurTransactionContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used

> .... (thousands of these)

Thousands of CurTransactionContext lines? That would suggest that you're
creating a subtransaction (savepoint) in each trigger firing. You should
try to avoid that if you can. I'm too tired to look up which plpython
constructs might cause that exactly.

It may be that there is also some internal leak in plpython; the fact that
we only seem to have accounted for ~75MB of memory in the memory context
dump suggests that something outside the core pgsql code is eating memory,
and python might be the something. It's unlikely we can debug that unless
you can provide a self-contained test case, though.

regards, tom lane