Hi, one of our local users reported he's getting OOM errors on 9.2, although on 9.1 the code worked fine. Attached is a simple test-case that should give you an OOM error almost immediately.
What it does:
1) creates a simple table called "test" with one text column.
2) creates a plpgsql function with one parameter, and all that function
does is passing the parameter to EXECUTE
3) calls the function with a string containing many INSERTs into the
test table
The way the EXECUTE is used is a bit awkward, but the failures seem a
bit strange to me. The whole script is ~500kB and most of that is about
11k of very simple INSERT statements:
insert into test(value) values (''aaaaaaaaaa'');
all of them are exactly the same. Yet when it fails with OOM, the log
contains memory context stats like these:
TopMemoryContext: 5303376 total in 649 blocks; 2648 free ...
PL/pgSQL function context: 8192 total in 1 blocks; 3160 free ...
TopTransactionContext: 8192 total in 1 blocks; 6304 free ...
ExecutorState: 8192 total in 1 blocks; 7616 free ...
ExprContext: 8192 total in 1 blocks; 8160 free ...
SPI Exec: 33554432 total in 14 blocks; 6005416 free ...
CachedPlanSource: 3072 total in 2 blocks; 1856 free ...
CachedPlanSource: 538688 total in 3 blocks; 1744 free ...
CachedPlanQuery: 3072 total in 2 blocks; 1648 free ...
CachedPlanSource: 538688 total in 3 blocks; 1744 free ...
CachedPlanQuery: 3072 total in 2 blocks; 1648 free ...
CachedPlanSource: 538688 total in 3 blocks; 1744 free ...
CachedPlanQuery: 3072 total in 2 blocks; 1648 free ...
CachedPlanSource: 538688 total in 3 blocks; 1744 free ...
CachedPlanQuery: 3072 total in 2 blocks; 1648 free ...
CachedPlanSource: 538688 total in 3 blocks; 1744 free ...
...
There is ~9500 of these CachedPlanSource + CachedPlanQuery row pairs
(see the attached log). That seems a bit strange to me, because all the
queries are exactly the same in this test case.
The number of queries needed to get OOM is inversely proportional to the
query length - by using a longer text (instead of 'aaaaaaaaaaa') you may
use much less queries.
I am no expert in this area, but it seems to me that the code does not
expect that many INSERTs in EXECUTE and does not release the memory for
some reason (e.g. because the plans are allocated in SPI Exec memory
context, etc.).
regards
Tomas
pg-oom.log.gz
Description: application/gzip
test2.sql.gz
Description: application/gzip
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
