On 05/05/17 06:39, Tomas Vondra wrote:
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
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
to add a keyword to install one explicitely:
with materialized r as
select json_populate_record(null::mytype, myjson) as x
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
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
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
ALTER SEQUENCE foo_seq RESTART;
SELECT nextval('foo_seq'), nextval('foo_seq');
nextval | ?column?
1 | 2
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.
Sent via pgsql-hackers mailing list (firstname.lastname@example.org)
To make changes to your subscription: