Daniel,
You have a commit [1] that MIGHT fix this.
I have a script that recreates the problem, using random data in pg_temp.
And a nested cursor.
It took me a few days to reduce this from actual code that was experiencing this. If I turn off JIT, the problem goes away. (if I don't FETCH the first row, the memory loss does not happen. Maybe because opening a cursor is more decoration/prepare)
I don't have an easy way to test this script right now against the commit.
I am hopeful that your fix fixes this.
This was my first OOM issue in PG in 3yrs of working with it.
The problem goes away if the TABLE is analyzed, or JIT is disabled.
The current script, if run, will consume about 25% of my system memory (10GB).
Just call the function below until it dies if that's what you need. The only way to get the memory back down is to close the connection.
SELECT pg_temp.fx(497);
Surprisingly, to me, the report from pg_get_backend_memory_contexts() doesn't really show "missing memory", which I thought it would. (FWIW, we caught this with multiple rounds of testing our code, slowing down, then crashing... Is there ANY way to interrogate that we are above X% of system memory so we know to let this backend go?)
It takes about 18 minutes to run on my 4 CPU VM.
For now, we are going to add some ANALYZE statements to our code.
We will consider disabling JIT.
Thanks,
Kirk