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]
Subject: Re: Row pattern recognition
Date: Thu, 05 Feb 2026 10:25:19 +0900 (JST)
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAAAe_zA=BDm=e04aNFYe3Az0CzPipK+HHzu1KGHiLOg+r_z8XQ@mail.gmail.com>
References: <CAAAe_zC95wT9EdBu_nBn6mWVB7-xNNuJ4N8wPr0LrWJBdwmxiQ@mail.gmail.com>
	<[email protected]>
	<CAAAe_zA=BDm=e04aNFYe3Az0CzPipK+HHzu1KGHiLOg+r_z8XQ@mail.gmail.com>

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

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