Re: Writeable CTEs and empty relations

On Wed, Feb 10, 2010 at 6:25 PM, Marko Tiikkaja
<marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi> wrote:
> On 2010-02-11 00:50 +0200, Marko Tiikkaja wrote:
>> On 2010-02-10 23:57 +0200, Tom Lane wrote:
>>> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>>>> If the executor has buried in it the assumption that the snapshot
>>>> can't change after startup, then does that mean that we need to start
>>>> up and shut down the executor for each subquery?
>>>
>>> Yes, I think so.  That's the way it's always worked in the past;
>>> see for example PortalRunMulti() and ProcessQuery().  I think trying
>>> to change that is a high-risk, low-reward activity.
>>>
>>> This probably means that the planner output for queries involving
>>> writeable CTEs has to be a separate PlannedStmt per such CTE.
>>
>> I'm looking at this, but I can't think of any good way of associating
>> the tuplestores from PortalRunMulti() with the correct CTEs.  Any ideas?
>
> Ok, what about the following:
>  - after planning the original query, standard_planner() goes through
>    the list of top-level CTEs and assigns a running number for each of
>    the DML WITHs, in the order they will be executed and returns a
>    list of PlannedStmts.  all necessary statements wi have a flag
>    signaling that the result should be stored in a tuplestore.
>  - the portal keeps the list of tuplestores around and passes that
>    list to every query through PlannedStmt.  it keeps on executing
>    the statements until it finds a PlannedStmt that doesn't want its
>    results stored anywhere and then frees the list of tuplestores

Wouldn't you'd want to stop when you ran out of PlannedStmts, not when
you hit one that didn't need its results stored? What happens if
there's an unreferenced CTE in the middle of the list?

> Does this sound reasonable?  And more importantly, am I going to be
> wasting my time implementing this?  The 15th isn't that far away..

I have to admit I've been starting to have a feeling over the last
couple of days that this patch isn't going to make it for 9.0... but
obviously I'm in no position to guarantee anything one way or the
other. Please do keep us up to date on your plans, though.

Thanks,

...Robert