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. Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development
Description: OpenPGP digital signature