Date: Thu, 05 Feb 2026 10:25:19 +0900 (JST)
Message-Id: <20260205.102519.1298921431937419793.ishii@postgresql.org>
To: assam258@gmail.com
Cc: jacob.champion@enterprisedb.com, david.g.johnston@gmail.com,
 vik@postgresfriends.org, er@xs4all.nl, peter@eisentraut.org,
 pgsql-hackers@postgresql.org
Subject: Re: Row pattern recognition
From: Tatsuo Ishii <ishii@postgresql.org>
In-Reply-To: <CAAAe_zA=BDm=e04aNFYe3Az0CzPipK+HHzu1KGHiLOg+r_z8XQ@mail.gmail.com>
References: <CAAAe_zC95wT9EdBu_nBn6mWVB7-xNNuJ4N8wPr0LrWJBdwmxiQ@mail.gmail.com>
	<20260204.192222.892684621512920290.ishii@postgresql.org>
	<CAAAe_zA=BDm=e04aNFYe3Az0CzPipK+HHzu1KGHiLOg+r_z8XQ@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/20260205.102519.1298921431937419793.ishii%40postgresql.org>
Precedence: bulk

Hi Henson,

>> ## Proposal for consistency
>> >
>> >   /* Initialize NFA free lists for row pattern matching */
>> >   winstate->nfaContext = NULL;
>> >   winstate->nfaContextTail = NULL;
>> >   winstate->nfaContextFree = NULL;
>> >   winstate->nfaStateFree = NULL;
>> >   winstate->nfaLastProcessedRow = -1;
>> >   winstate->nfaStatesActive = 0;        // Add this
>> >   winstate->nfaContextsActive = 0;      // Add this
>> >
>> > Would you like me to include this change in the next patch?
>>
>> Yes, please.
>>
> 
> 
> Done. Please see the attached patch.

Looks good to me.

BTW, I noticed that following test now succeeds with v42 patch.
In rpr_explain.sql test 11.3:

-- Test 11.3: Consecutive increasing values (using PREV)
-- FIXME: The original pattern was:
--   DEFINE A AS v > PREV(v) OR PREV(v) IS NULL
-- This causes "ERROR: unrecognized node type: 15" (T_FuncExpr) because
-- NullTest(FuncExpr(PREV)) is not properly handled somewhere in the planner.
-- The expression v > PREV(v) works fine, but PREV(v) IS NULL fails.
-- Using COALESCE(PREV(v), 0) as a workaround until the bug is fixed.
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM generate_series(1, 50) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A{3,})
    DEFINE A AS v > COALESCE(PREV(v), 0)
);

I changed 
   DEFINE A AS v > COALESCE(PREV(v), 0)
to
   DEFINE A AS v > PREV(v) OR PREV(v) IS NULL
 and get following result.

EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM generate_series(1, 50) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A{3,})
    DEFINE A AS v > PREV(v) OR PREV(v) IS NULL
);
                              QUERY PLAN                              
----------------------------------------------------------------------
 WindowAgg (actual rows=50.00 loops=1)
   Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
   Pattern: a{3,}"
   Storage: Memory  Maximum Storage: 18kB
   NFA States: 3 peak, 99 total, 0 merged
   NFA Contexts: 2 peak, 51 total, 0 pruned
   NFA: 1 matched (len 50/50/50.0), 0 mismatched
   NFA: 49 absorbed (len 1/1/1.0), 0 skipped
   ->  Function Scan on generate_series s (actual rows=50.00 loops=1)
(9 rows)

Probably we can restore 11.3 test in v43?

Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp