Jesse Zhang <sbjesse@gmail.com> writes:
> For more kicks, I don't even think this is restricted to volatile
> functions only. To stir the pot, it's conceivable that planner might
> produce the following plan
> Seq Scan on pg_temp_3.foo
> Output: foo.a
> Filter: (SubPlan 1)
> SubPlan 1
> -> WindowAgg
> Output: sum(bar.d) OVER (?)
> -> Seq Scan on pg_temp_3.bar
> Output: bar.d
> For the following query
> SELECT a FROM foo WHERE b = ALL (
> SELECT sum(d) OVER (ROWS UNBOUNDED PRECEDING) FROM bar
> );
Interesting example. Normally you'd expect that repeated executions of
the inner seqscan would produce the same output in the same order ...
but if the table were big enough to allow the synchronize_seqscans logic
to kick in, that might not be true. You could argue about whether or
not synchronize_seqscans breaks any fundamental SQL guarantees, but
my feeling is that it doesn't: if the above query produces unstable
results, that's the user's fault for having written an underspecified
windowing query.
> Zhenghua and Tom, would you suggest the above plan is wrong (not
> suboptimal, but wrong) just because we don't materialize the WindowAgg
> under the subplan?
I would not, per above: the query is buggy, not the implementation.
(In standard-ese, the results of that query are undefined, not
implementation-defined, meaning that we don't have to produce
consistent results.)
regards, tom lane