Re: Row pattern recognition

From: Henson Choi <assam258(at)gmail(dot)com>
To: Tatsuo Ishii <ishii(at)postgresql(dot)org>
Cc: jian(dot)universality(at)gmail(dot)com, zsolt(dot)parragi(at)percona(dot)com, sjjang112233(at)gmail(dot)com, vik(at)postgresfriends(dot)org, er(at)xs4all(dot)nl, jacob(dot)champion(at)enterprisedb(dot)com, david(dot)g(dot)johnston(at)gmail(dot)com, peter(at)eisentraut(dot)org, li(dot)evan(dot)chao(at)gmail(dot)com, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Row pattern recognition
Date: 2026-11-21 11:09:10
Message-ID: CAAAe_zBhKZRuyzET5XFPgdOfw6TdSbpRVYm72j=Mr4qp7b_nRQ@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi Tatsuo, Jian,

> > Meanwhile, I am studying the planner side of the patch and auditing
> > v54 for defects. I will group what I find by severity and send it
> > in follow-up mails.

Before I send the fixes, I would like to agree on one design point:
whether, and how, planning keeps the argument of a navigation such as
PREV() from raising an error at plan time. Section 2 asks you to
choose between two options. The fix changes how this is implemented,
so I want to explain the change, and the other ways I tried, before
you see the patch.

The problem:

WITH t(id, v) AS (VALUES (1, 10))
SELECT count(*) OVER w AS cnt
FROM t
WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
PATTERN (A) DEFINE A AS PREV(v / 0) > 0);

A navigation evaluates its argument only at the row it moves to, and
not at all when there is no such row. Here the only row has no
previous row, so the query should return 0. But pull-up replaces v
with 10, and constant folding then raises division by zero at plan
time.

1. Two ways a column becomes a constant

A column in a navigation argument can turn into a constant during
planning in two ways, and they happen in different places.

Substitution. A step that runs before constant folding puts
something else in the column's place. These steps are spread over
the planner, and core keeps adding them: virtual generated column
expansion came in v18 (1e4351af329), SupportRequestInlineInFrom in
v19 (b140c8d7a3f).

a. Subquery, VALUES and constant function pull-up:

SELECT count(*) OVER w
FROM (SELECT 1 AS id, 10 AS v) t
WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
PATTERN (A) DEFINE A AS PREV(v / 0) > 0);

and the same with the VALUES of the opening example, or with
FROM abs(10) AS t(v).

b. Join alias expansion, where a merged column carries the constant
of one side:

CREATE TABLE rj (k bigint);
INSERT INTO rj VALUES (10);
SELECT count(*) OVER w
FROM (SELECT 10 AS k) a LEFT JOIN rj USING (k)
WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
PATTERN (A) DEFINE A AS PREV(k / 0) > 0);

c. Virtual generated columns with a constant expression:

CREATE TABLE gv (id int, g int GENERATED ALWAYS AS (10) VIRTUAL);
INSERT INTO gv (id) VALUES (1);
SELECT id, count(*) OVER w FROM gv
WINDOW w AS (ORDER BY id
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
PATTERN (A) DEFINE A AS PREV(g / 0) > 0);

Simplification inside constant folding. eval_const_expressions()
itself drops the column while it simplifies, and what is left is
then evaluated as a constant. The output below is from v55.

d. Inlining of a SQL function that does not use its argument:

CREATE FUNCTION f_drop(x int) RETURNS int
LANGUAGE sql IMMUTABLE AS 'SELECT 1 / 0';
CREATE TABLE nt (id int, v int);
INSERT INTO nt VALUES (1, 10);
SELECT id, count(*) OVER w FROM nt
WINDOW w AS (ORDER BY id
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
PATTERN (A) DEFINE A AS PREV(f_drop(v)) > 0);
ERROR: division by zero
CONTEXT: SQL function "f_drop" during inlining

e. IS NULL on a NOT NULL column, reduced by a not-null proof, which
came in v19 (e2debb64380):

CREATE TABLE nn (id int, v int NOT NULL);
INSERT INTO nn VALUES (1, 10);
SELECT id, count(*) OVER w FROM nn
WINDOW w AS (ORDER BY id
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
PATTERN (A)
DEFINE A AS PREV(1 / (v IS NULL)::int) > 0);
ERROR: division by zero

The same happens with COALESCE(1, v), CASE WHEN true THEN 1 ELSE v
END, v > 0 AND false, a strict function given a null, a field taken
from a row, and a null test on a row.

How it has been handled so far:

- Up to v53, the argument folded like any other expression, so
every query above raised.
- v54 and v55 guard the substitutions: pull-up, join alias
expansion and virtual generated column expansion wrap the
replacement in a PlaceHolderVar, so a, b and c return 0. The
second kind is left open, and d and e still raise.

The wrapping for join aliases also crashes or fails with internal
errors when a join alias is expanded below its join level, as when a
merged JOIN USING column is passed to an inlined SQL function:

CREATE TABLE s1 (id int, v int);
INSERT INTO s1 SELECT g, g FROM generate_series(1, 3) g;
CREATE TABLE s2 (id int);
INSERT INTO s2 SELECT generate_series(1, 3);
CREATE TABLE rb (k bigint);
INSERT INTO rb VALUES (10);
CREATE FUNCTION fj(p bigint) RETURNS TABLE(cnt bigint)
LANGUAGE sql STABLE AS $$
SELECT count(*) OVER w FROM s1 JOIN s2 USING (id)
WINDOW w AS (ORDER BY id
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
PATTERN (A B+) DEFINE B AS PREV(v + p) > 0)
$$;
SELECT k, max(cnt)
FROM (SELECT 10 AS k) a LEFT JOIN rb USING (k), LATERAL fj(k) g
GROUP BY k;
server closed the connection unexpectedly

On an assert-enabled build an assertion in alias_relid_set() fails:
the PlaceHolderVar is made at level zero with relids of the outer
query, so the function body reads them against its own range table.
Item 3 of my Oct 9 mail [1] is an internal error from the same
wrapping, and other shapes fail with other internal errors.

2. Should planning avoid the error at all?

One question comes before the proposal.

Folding does not change a result: a folded value is the value
execution would compute. The one exception is a navigation that
falls outside the partition, or outside the match for FIRST and
LAST, and returns NULL without evaluating its argument. If that
argument folds at plan time and raises, the error stands where
execution would have given that NULL. This is the only case where a
plan-time error matters, and the opening example is one.

The v inside PREV() is the v of the previous row, and the only row
has none, so that v is not even NULL or UNKNOWN: it does not exist.
PREV() must then return NULL without evaluating its argument, so the
answer is 0. Putting 10 in place of v and folding at plan time
evaluates the argument it must not evaluate, and raises. With
option 1 below it raises; with option 2 it returns 0.

So there are two ways to go:

1. Accept the plan-time error. Remove the PlaceHolderVar wrapping,
which also fixes the crash above, and document that the argument
may fold at plan time. The planner needs nothing else.
2. Keep that NULL. This is the proposal in section 3. It costs a
pre-pass in eval_const_expressions(), and care when that code
changes.

Strictly, only option 2 gives the correct result. Whether so small
a difference is worth the added code and its maintenance is a
matter of judgment, and I would like your call on it. The rest of
this mail describes option 2, so that you can weigh it.

3. Proposal for option 2

The fix folds a navigation argument in DEFINE in two passes, one on
each side of the substitutions:

- Before any substitution, a pre-pass folds each navigation
argument as usual, except for the simplifications that can make
a column disappear: inlining a SQL function that does not use
its argument; COALESCE, CASE, AND and OR dropping an argument
that reads a column when another argument is a deciding
constant; a strict function given a null, a field taken from a
row and a null test on a row; and not-null proofs.
- After the substitutions, the main pass does below a navigation
argument only the simplifications the pre-pass left out, and
evaluates nothing.

Outside navigation arguments, folding is the same as before.

The comment in preprocess_expression() says that every expression
must go through eval_const_expressions(), because that is where named
arguments become positional and default arguments are filled in. A
navigation argument still goes through all of that; only the
evaluation step is left out.

A substitution puts in either an expression over the same row or a
value that is the same on every row, so deferring the evaluation to
execution gives the same result. Nothing is wrapped in a
PlaceHolderVar, so the join alias crash of section 1 cannot happen.

A change to the substitutions cannot introduce a defect: a new step
that replaces columns needs nothing, as long as it runs between the
two passes. Only a change to the constant folding code in clauses.c
can. A new place that evaluates must, as in the main pass, evaluate
nothing below a navigation argument, and a new simplification that
drops a column must be left out of the pre-pass.

4. Step by step

A separate commit makes EXPLAIN print the DEFINE conditions on a
Define line after the Pattern line; the Define lines below are its
output.

The opening example with 2 * 3 added, PREV(v / 0 + 2 * 3) over the
WITH VALUES, is a substitution by pull-up:

1. The pre-pass folds 2 * 3, giving PREV(v / 0 + 6). A 1 / 0
written in the argument, as in PREV(v + 1 / 0), raises here.
2. Pull-up of the VALUES puts 10 in place of v, giving
PREV(10 / 0 + 6).
3. The main pass expands join aliases, inlines SQL functions in
expressions and simplifies, but evaluates nothing below a
navigation argument:

Define: a AS (PREV(((10 / 0) + 6)) > 0)

On the only row PREV() points outside the partition, so the
query returns 0.

PREV(g / 0 + 2 * 3) over table gv of case c in section 1, where g is
the virtual column generated as 10, is a substitution:

1. The pre-pass folds 2 * 3, giving PREV(g / 0 + 6).
2. Virtual generated column expansion puts 10 in place of g, giving
PREV(10 / 0 + 6).
3. The main pass again evaluates nothing below the navigation
argument:

Define: a AS (PREV(((10 / 0) + 6)) > 0)

At execution, on a single row PREV() points outside the
partition, so its argument is never evaluated, while
LAST(g / 0 + 2 * 3) on the same row lands on that row and raises
then.

PREV(f_drop(v)) over table nt of case d, where v is an ordinary
column, is a simplification:

1. f_drop() does not use its argument, so the pre-pass does not
inline it.
2. Nothing replaces v.
3. The main pass inlines f_drop(v) and leaves the result
unevaluated:

Define: a AS (PREV((1 / 0)) > 0)

PREV(f_inc(v)) over the same table nt, where f_inc(x) returns
x + 2 * 3, inlines a SQL function that uses its argument:

1. The pre-pass inlines f_inc(v) and folds 2 * 3, giving
PREV(v + 6).
2. Nothing replaces v.
3. The main pass evaluates nothing below the navigation argument:

Define: a AS (PREV((v + 6)) > 0)

Over the VALUES of the opening example, pull-up puts 10 in place
of v at step 2, and PREV((10 + 6)) is left for execution.

PREV(1 / (v IS NULL)::int) over table nn of case e, where v is NOT
NULL:

1. The pre-pass uses no not-null proof, and every other part reads
v, so nothing folds and the argument stays as written.
2. Nothing replaces v.
3. The main pass reduces v IS NULL to false by the not-null proof,
but leaves the cast and the division unevaluated:

Define: a AS (PREV((1 / (false)::integer)) > 0)

PREV(COALESCE(1 + 1, v) + 1) IS NULL over table nt shows both passes:

1. The pre-pass computes the written 1 + 1 to 2 but keeps v after
it, giving PREV(COALESCE(2, v) + 1).
2. Nothing replaces v.
3. The main pass reduces COALESCE(2, v) to 2, but leaves 2 + 1
unevaluated:

Define: a AS (PREV((2 + 1)) IS NULL)

Folding 2 + 1 to 3 there would be sound, but the main pass has
no way to tell it from 2 / 0: both are calls on constants, and
nothing records which functions cannot raise, so it evaluates
neither.

The navigation argument code in var.c, rewriteManip.c, rewriteManip.h
and prepjointree.c goes away; what replaces it is in clauses.c and
planner.c, with a declaration in optimizer.h. README.rpr gets a
section on the two passes.

5. What I tried on top of the v55 code, and did not send

- Refusing to pull up an RTE that a navigation argument reads. It
covers only part of the first kind: it keeps the subquery and
changes the plan of the whole query, it does not help virtual
generated columns or inlining in FROM, and it does nothing for
the second kind. DEFINE is evaluated only in the window node and
plays no part in choosing scans or joins, so keeping the subquery
costs more than it protects.
- On top of that refusal, a guard for each case as it was found:
no inlining and no not-null proofs inside a navigation argument
at all, and handling of its own for constant virtual generated
columns. It treats the second kind like the first: each guard
gives a simplification up for good, one guard per case found,
and the audit found d and e only one at a time.
- Without a pre-pass, not folding a navigation argument in DEFINE
at all, or simplifying it without evaluating anything. Either
covers both kinds, but written constants stop folding too:
PREV(v + 2 * 3) stays as written, and the multiply is evaluated
again at every row.

None of these was satisfactory. Each either stopped more folding
than it should, or left a path open.

6. What still folds

The pre-pass runs before any substitution, so a value given in the
argument itself -- written, bound, or passed into a SQL function's
body -- is folded and may raise at plan time, inside a navigation as
outside:

- PREV(v + 1 / 0)
- PREV(v + $1 / 0) under a custom plan, as with PREPARE
- PREV(v + x / 0) in the body of a SQL function f(x int), called
as f(10)
- PREV(f_err(v)), where f_err(x) returns x + 1 / 0: the function
uses its argument, so the pre-pass inlines it and folds 1 / 0

In the same mail [1] I asked whether such a function parameter
should count as an outer reference. With this fix it does not: it
is a value passed into the body, and it folds like $1.

With option 1 the fix will come in my next mail as two commits,
removing the wrapping, with tests for the shapes that crashed or
failed, and showing DEFINE in EXPLAIN; with option 2 as three,
adding the folding described here, with regression tests for a to e.

[1]
https://postgr.es/m/CAAAe_zBno3aWzkJ6iu-Qsj-wniF+j+i3crZRs9DX9UCZZjG_Xg@mail.gmail.com

Best regards,
Henson

In response to

Browse pgsql-hackers by date

  From Date Subject
Previous Message Michael Paquier 2026-11-21 10:06:12 Re: Backend crash in expanded_record_info functions