Re: Row pattern recognition

Hi hackers,

When a WINDOW clause is not referenced by any window function, the pattern
is never matched and the DEFINE expressions are never evaluated.

One consequence is that a DEFINE which would raise a run-time error raises
nothing when the window is unused. The same window definition behaves
differently depending only on whether a window function consumes it:

CREATE TABLE t (id int, v int);
INSERT INTO t VALUES (1, 10), (2, 20), (3, 15);

-- (1) the window IS used (count(*) OVER w): the DEFINE is evaluated

SELECT count(*) OVER w AS cnt
FROM t
WINDOW w AS (
ORDER BY id
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
INITIAL
PATTERN (A+)
DEFINE A AS (1 / (v - v)) > 0
);
-- ERROR: division by zero

-- (2) the window is NOT used (no window function): same definition

SELECT v
FROM t
WINDOW w AS (
ORDER BY id
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
INITIAL
PATTERN (A+)
DEFINE A AS (1 / (v - v)) > 0
);
-- v
-- ----
-- 10
-- 20
-- 15
-- (no error)

EXPLAIN (VERBOSE, COSTS OFF) of (2) confirms the window, and with it the
DEFINE expression, are gone entirely:

Seq Scan on public.t
Output: v, id

(A column-dependent division by zero is used so that the expression does not
constant-fold and can only error at run time.)

This follows from existing, general planner behavior. A window definition
that no window function references is removed from the plan by
select_active_windows(); the planner does this for every window, because a
window with no consumer produces no output, so sorting or partitioning it
would be wasted work. Before RPR that reasoning was complete: an ordinary
window has nothing but its output, so dropping an unused one changes nothing
observable.

RPR is what turns this into a question. A DEFINE clause is a per-row
predicate that can have a run-time effect of its own -- here, raising an
error -- independent of any output the window produces. So the assumption
behind the optimization, that an unused window has no observable behavior,
no
longer holds automatically once a window carries a DEFINE. Whether the
existing "drop it" optimization should still extend to an RPR window is a
decision RPR's addition forces, not something the prior behavior settles.

The question is whether the current behavior is what we want:

(a) Keep it. Skipping the pattern matching for a window that produces
nothing is the natural optimization, and an expression that is never
evaluated raising no error is normal behavior.

(b) Run the matching anyway, even though there is no output to produce, so
that DEFINE errors are raised regardless of whether a window function
consumes the window.

Option (a) is cheaper and consistent with how unevaluated expressions
behave, but I lean towards (b): a faulty DEFINE should fail consistently
rather than pass silently just because no window function happens to consume
the window. I would like to hear what you think.

Regards,
Henson