Re: wCTE: why not finish sub-updates at the end, not the beginning?

Marko Tiikkaja <marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi> writes:
> On 2011-02-25 4:58 PM, Tom Lane wrote:
>> Specifically, I'm imagining getting rid of the patch's additions to
>> InitPlan and ExecutePlan that find all the modifying sub-queries and
>> force them to be cycled to completion before the main plan runs.
>> Just run the main plan and let it pull tuples from the CTEs as needed.
>> Then, in ExecutorEnd, cycle any unfinished ModifyTable nodes to
>> completion before shutting down the plan.

> This idea has actually been discussed before when we talked about
> optimizing wCTEs, but IIRC you said that doing this in ExecutorEnd is a
> bit ugly.

Further experimentation has reminded me of why I didn't want to put such
processing in ExecutorEnd :-(. There are some nasty interactions with
EXPLAIN:

1. EXPLAIN ANALYZE fails to include the execution cycles associated with
running the ModifyTable nodes to completion. In the worst case, such as
"WITH t AS (INSERT ...) SELECT 1", it will claim the INSERT subplan is
never executed, even though rows certainly got inserted. This is
because EXPLAIN extracts all the counts from the execution state tree
before shutting it down with ExecutorEnd.

2. But it gets worse. Try the same query *without* ANALYZE. You'll
find the INSERT executes anyway! That's because EXPLAIN still calls
ExecutorEnd to clean up the execution state tree, and ExecutorEnd
doesn't realize it's not supposed to run any of the plan.

So we really need some refactoring here. I dislike adding another
fundamental step to the ExecutorStart/ExecutorRun/ExecutorEnd sequence,
but there may not be a better way. The only way I see to fix this
without changing that API is to have ExecutorRun do the cleanup
processing just after the top plan node returns a null tuple, and that
seems a bit ugly as well.

Thoughts?

regards, tom lane