MIME-Version: 1.0
References: 
 <CACJufxFAQhbOD9EVCTAy-VwDbG4446N10GsxCcgdpFnjHO1Efw@mail.gmail.com>
 <CACJufxG6rKfd0WtsVZgzcoh8vOEwo_8UDWkuOd888ATavNv_uw@mail.gmail.com>
 <CAAAe_zDfrGSTqhgy8vOC1K6mzDsqVJGEmMy5N84G6=5EJT6--g@mail.gmail.com>
 <20260618.185007.1430819167281911424.ishii@postgresql.org>
 <CACJufxH_Z5aaYEir3=GHoZu-0pKzQdScu85LgCm1C8n=oQo=4Q@mail.gmail.com>
 <CAAAe_zAe8CRN-ggNhcP7b-ALiRrHxKTXBWbNVMhdW_QoO=1c4Q@mail.gmail.com>
In-Reply-To: 
 <CAAAe_zAe8CRN-ggNhcP7b-ALiRrHxKTXBWbNVMhdW_QoO=1c4Q@mail.gmail.com>
Reply-To: assam258@gmail.com
From: Henson Choi <assam258@gmail.com>
Date: Fri, 19 Jun 2026 15:10:54 +0900
Message-ID: 
 <CAAAe_zB_X+b26A_OW8HJYeRiT1KhOSWgouVrYN=9f4oCAy_3ew@mail.gmail.com>
Subject: Re: Row pattern recognition
To: Tatsuo Ishii <ishii@postgresql.org>, jian he <jian.universality@gmail.com>
Cc: 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
Content-Type: multipart/alternative; boundary="00000000000077835f06549528fd"
Archived-At: 
 <https://www.postgresql.org/message-id/CAAAe_zB_X%2Bb26A_OW8HJYeRiT1KhOSWgouVrYN%3D9f4oCAy_3ew%40mail.gmail.com>
Precedence: bulk

--00000000000077835f06549528fd
Content-Type: text/plain; charset="UTF-8"

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

--00000000000077835f06549528fd
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi hackers,<br><br>When a WINDOW clause is not referenced =
by any window function, the pattern<br>is never matched and the DEFINE expr=
essions are never evaluated.<br><br>One consequence is that a DEFINE which =
would raise a run-time error raises<br>nothing when the window is unused.=
=C2=A0 The same window definition behaves<br>differently depending only on =
whether a window function consumes it:<br><br>=C2=A0 CREATE TABLE t (id int=
, v int);<br>=C2=A0 INSERT INTO t VALUES (1, 10), (2, 20), (3, 15);<br><br>=
-- (1) the window IS used (count(*) OVER w): the DEFINE is evaluated<br><br=
>=C2=A0 SELECT count(*) OVER w AS cnt<br>=C2=A0 FROM t<br>=C2=A0 WINDOW w A=
S (<br>=C2=A0 =C2=A0 =C2=A0 ORDER BY id<br>=C2=A0 =C2=A0 =C2=A0 ROWS BETWEE=
N CURRENT ROW AND UNBOUNDED FOLLOWING<br>=C2=A0 =C2=A0 =C2=A0 INITIAL<br>=
=C2=A0 =C2=A0 =C2=A0 PATTERN (A+)<br>=C2=A0 =C2=A0 =C2=A0 DEFINE A AS (1 / =
(v - v)) &gt; 0<br>=C2=A0 );<br>=C2=A0 -- ERROR: =C2=A0division by zero<br>=
<br>-- (2) the window is NOT used (no window function): same definition<br>=
<br>=C2=A0 SELECT v<br>=C2=A0 FROM t<br>=C2=A0 WINDOW w AS (<br>=C2=A0 =C2=
=A0 =C2=A0 ORDER BY id<br>=C2=A0 =C2=A0 =C2=A0 ROWS BETWEEN CURRENT ROW AND=
 UNBOUNDED FOLLOWING<br>=C2=A0 =C2=A0 =C2=A0 INITIAL<br>=C2=A0 =C2=A0 =C2=
=A0 PATTERN (A+)<br>=C2=A0 =C2=A0 =C2=A0 DEFINE A AS (1 / (v - v)) &gt; 0<b=
r>=C2=A0 );<br>=C2=A0 -- =C2=A0v<br>=C2=A0 -- ----<br>=C2=A0 -- =C2=A010<br=
>=C2=A0 -- =C2=A020<br>=C2=A0 -- =C2=A015<br>=C2=A0 -- (no error)<br><br>EX=
PLAIN (VERBOSE, COSTS OFF) of (2) confirms the window, and with it the<br>D=
EFINE expression, are gone entirely:<br><br>=C2=A0 =C2=A0 =C2=A0 Seq Scan o=
n public.t<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 Output: v, id<br><br>(A column-de=
pendent division by zero is used so that the expression does not<br>constan=
t-fold and can only error at run time.)<br><br>This follows from existing, =
general planner behavior.=C2=A0 A window definition<br>that no window funct=
ion references is removed from the plan by<br>select_active_windows(); the =
planner does this for every window, because a<br>window with no consumer pr=
oduces no output, so sorting or partitioning it<br>would be wasted work.=C2=
=A0 Before RPR that reasoning was complete: an ordinary<br>window has nothi=
ng but its output, so dropping an unused one changes nothing<br>observable.=
<br><br>RPR is what turns this into a question.=C2=A0 A DEFINE clause is a =
per-row<br>predicate that can have a run-time effect of its own -- here, ra=
ising an<br>error -- independent of any output the window produces.=C2=A0 S=
o the assumption<br>behind the optimization, that an unused window has no o=
bservable behavior, no<br>longer holds automatically once a window carries =
a DEFINE.=C2=A0 Whether the<br>existing &quot;drop it&quot; optimization sh=
ould still extend to an RPR window is a<br>decision RPR&#39;s addition forc=
es, not something the prior behavior settles.<br><br>The question is whethe=
r the current behavior is what we want:<br><br>=C2=A0 (a) Keep it.=C2=A0 Sk=
ipping the pattern matching for a window that produces<br>=C2=A0 =C2=A0 =C2=
=A0 nothing is the natural optimization, and an expression that is never<br=
>=C2=A0 =C2=A0 =C2=A0 evaluated raising no error is normal behavior.<br><br=
>=C2=A0 (b) Run the matching anyway, even though there is no output to prod=
uce, so<br>=C2=A0 =C2=A0 =C2=A0 that DEFINE errors are raised regardless of=
 whether a window function<br>=C2=A0 =C2=A0 =C2=A0 consumes the window.<br>=
<br>Option (a) is cheaper and consistent with how unevaluated expressions<b=
r>behave, but I lean towards (b): a faulty DEFINE should fail consistently<=
br>rather than pass silently just because no window function happens to con=
sume<br>the window.=C2=A0 I would like to hear what you think.<br><br>Regar=
ds,<br>Henson<br></div>

--00000000000077835f06549528fd--