Re: [HACKERS] CTE inlining - Mailing list pgsql-hackers

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