public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tatsuo Ishii <[email protected]>
To: [email protected]
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Subject: Re: Row pattern recognition
Date: Fri, 19 Jun 2026 17:57:28 +0900 (JST)
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAAAe_zB_X+b26A_OW8HJYeRiT1KhOSWgouVrYN=9f4oCAy_3ew@mail.gmail.com>
References: <CACJufxH_Z5aaYEir3=GHoZu-0pKzQdScu85LgCm1C8n=oQo=4Q@mail.gmail.com>
	<CAAAe_zAe8CRN-ggNhcP7b-ALiRrHxKTXBWbNVMhdW_QoO=1c4Q@mail.gmail.com>
	<CAAAe_zB_X+b26A_OW8HJYeRiT1KhOSWgouVrYN=9f4oCAy_3ew@mail.gmail.com>

Hi Henson,

> 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.

I think we should follow (a).

view thread (136+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Row pattern recognition
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox