Re: CTE inlining

On 05/05/17 06:39, Tomas Vondra wrote:
>
>
> On 5/4/17 8:03 PM, Joe Conway wrote:
>> On 05/04/2017 10:56 AM, Andrew Dunstan wrote:
>>>
>>>
>>> On 05/04/2017 01:52 PM, Joe Conway wrote:
>>>> On 05/04/2017 10:33 AM, Alvaro Herrera wrote:
>>>>> I'm not sure what your point is. We know that for some cases the
>>>>> optimization barrier semantics are useful, which is why the
>>>>> proposal is
>>>>> to add a keyword to install one explicitely:
>>>>>
>>>>> with materialized r as
>>>>> (
>>>>> select json_populate_record(null::mytype, myjson) as x
>>>>> from mytable
>>>>> )
>>>>> select (x).*
>>>>> from r;
>>>>>
>>>>> this would preserve the current semantics.
>>>> I haven't been able to follow this incredibly long thread, so please
>>>> excuse me if way off base, but are we talking about that a CTE
>>>> would be
>>>> silently be rewritten as an inline expression potentially unless it is
>>>> decorated with some new syntax?
>>>>
>>>> I would find that very disconcerting myself. For example, would
>>>> this CTE
>>>> potentially get rewritten with multiple evaluation as follows?
>>>>
>>>> DROP SEQUENCE IF EXISTS foo_seq;
>>>> CREATE SEQUENCE foo_seq;
>>>>
>>>> WITH a(f1) AS (SELECT nextval('foo_seq'))
>>>> SELECT a.f1, a.f1 FROM a;
>>>> f1 | ?column?
>>>> ----+----------
>>>> 1 | 1
>>>> (1 row)
>>>>
>>>> ALTER SEQUENCE foo_seq RESTART;
>>>> SELECT nextval('foo_seq'), nextval('foo_seq');
>>>> nextval | ?column?
>>>> ---------+----------
>>>> 1 | 2
>>>> (1 row)
>>>>
>>>
>>> I think that would be a change in semantics, which we should definitely
>>> not be getting. Avoiding a change in semantics might be an interesting
>>> exercise, but we have lots of clever coders ...
>>
>> Well I think my point is that I always have understood CTEs to be
>> executed precisely once producing a temporary result set that is then
>> referenced elsewhere. I don't think that property of CTEs should change.
>> Somewhere else in the thread someone mentioned predicate push down --
>> that makes sense and maybe some clever coder can come up with a patch
>> that does that, but I would not be in favor of CTEs being inlined and
>> therefore evaluated multiple times.
>>
>
> I agree with this, but there's a difference between "executed exactly
> once" and "producing the same result as if executed exactly once".
>
> I may be misunderstanding what other people proposed in this thread,
> but I think the plan was to only inline CTEs where we know it won't
> change the results, etc. So e.g. CTEs with volatile functions would
> not get inlined, which includes nextval() for example.
>
> regards
>
It was the behaviour of "producing the same result as if executed
exactly once" that I was thinking of - I think this is still valid for
triggers & volatile functions, but such behaviour should be clearly
documented. This what I implicitly thought about CTE's when I first
came across them - to me it is the intuitively obvious behaviour.
However, limiting the rows based on the body of the SELECT would often
be a very useful optimisation

Cheers,
Gavin