Date: Fri, 19 Jun 2026 17:57:28 +0900 (JST)
Message-Id: <20260619.175728.667662038194755204.ishii@postgresql.org>
To: assam258@gmail.com
Cc: jian.universality@gmail.com, zsolt.parragi@percona.com,
 sjjang112233@gmail.com, vik@postgresfriends.org, er@xs4all.nl,
 jacob.champion@enterprisedb.com, david.g.johnston@gmail.com,
 peter@eisentraut.org, li.evan.chao@gmail.com, pgsql-hackers@postgresql.org
Subject: Re: Row pattern recognition
From: Tatsuo Ishii <ishii@postgresql.org>
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>
Mime-Version: 1.0
Content-Type: Text/Plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/20260619.175728.667662038194755204.ishii%40postgresql.org>
Precedence: bulk

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