On Sun, Apr 14, 2019 at 11:04 PM Gunther <raj@xxxxxxxx> wrote:
Could you rerun the query with \set VERBOSITY verbose to show the file/line that's failing ?Here goes:
integrator=# \set VERBOSITY verbose integrator=# SET ENABLE_NESTLOOP TO OFF; SET integrator=# INSERT INTO reports.BusinessOperation SELECT * FROM reports.v_BusinessOperation; ERROR: 53200: out of memory DETAIL: Failed on request of size 32800 in memory context "HashBatchContext". LOCATION: MemoryContextAlloc, mcxt.c:798you notice that I set ENABLE_NESTLOOP to off, that is because the planner goes off thinking the NL plan is marginally more efficient, but in fact it will take 5 hours to get to the same out of memory crash, while the no NL plan gets there in half an hour. That verbose setting didn't help much I guess.
I think the backtrace of the enable_nestloop=on plan would be more useful. Here someone has filled up memory, and then we see HashBatchContext trip over it that. But it isn't the one the one that caused the problem, so the backtrace doesn't help. With the previous plan, it was an allocation into ExecutorState which tripped over the problem, and it is likely that it is coming from the same series of allocations that caused the problem.
To get it to happen faster, maybe you could run the server with a small setting of "ulimit -v"? Or, you could try to capture it live in gdb. Unfortunately I don't know how to set a breakpoint for allocations into a specific context, and setting a breakpoint for any memory allocation is probably going to fire too often to be useful.
Yes, the verbose option didn't help (but the gdb backtrace made up for it--kind of--we really need the backtrace of the allocations into ExecutorState). It isn't helpful to know that a memory allocation failed in the mcxt.c code. To bad it doesn't report the location of the caller of that code. I know in Perl you can use Carp::croak to do that, but I don't know to do it in C.
But really the first thing I want to know now is what if you just do the select, without the insert?
explain analyze SELECT * FROM reports.v_BusinessOperation
If that works, what about "create temporary table foo as SELECT * FROM reports.v_BusinessOperation" ?
And if that works, what about "INSERT INTO reports.BusinessOperation SELECT * FROM foo"?
If the ERROR happens in the first or last of these, it might be much easier to analyze in that simplified context. If it happens in the middle one, then we probably haven't achieved much. (And if there is no ERROR at all, then you have workaround, but we still haven't found the fundamental bug).
Are you not showing the view definition for proprietary reasons, or just because you don't think it will be useful? If the latter, please send it as an attachment, I can't guarantee it will be useful, but there is only one way find out.
Cheers,
Jeff
