public inbox for [email protected]
help / color / mirror / Atom feed[PATCH v9 5/7] Row pattern recognition patch (docs).
5+ messages / 3 participants
[nested] [flat]
* [PATCH v9 5/7] Row pattern recognition patch (docs).
@ 2023-10-04 05:51 Tatsuo Ishii <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: Tatsuo Ishii @ 2023-10-04 05:51 UTC (permalink / raw)
---
doc/src/sgml/advanced.sgml | 52 ++++++++++++++++++++++++++++++++++
doc/src/sgml/func.sgml | 54 ++++++++++++++++++++++++++++++++++++
doc/src/sgml/ref/select.sgml | 38 +++++++++++++++++++++++--
3 files changed, 142 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/advanced.sgml b/doc/src/sgml/advanced.sgml
index 755c9f1485..f39ec8f2d5 100644
--- a/doc/src/sgml/advanced.sgml
+++ b/doc/src/sgml/advanced.sgml
@@ -537,6 +537,58 @@ WHERE pos < 3;
<literal>rank</literal> less than 3.
</para>
+ <para>
+ Row pattern common syntax can be used with row pattern common syntax to
+ perform row pattern recognition in a query. Row pattern common syntax
+ includes two sub clauses. <literal>DEFINE</literal> defines definition
+ variables along with an expression. The expression must be a logical
+ expression, which means it must
+ return <literal>TRUE</literal>, <literal>FALSE</literal>
+ or <literal>NULL</literal>. Moreover if the expression comprises a column
+ reference, it must be the argument of <function>rpr</function>. An example
+ of <literal>DEFINE</literal> is as follows.
+
+<programlisting>
+DEFINE
+ LOWPRICE AS price <= 100,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+</programlisting>
+
+ Note that <function>PREV</function> returns price column in the previous
+ row if it's called in a context of row pattern recognition. So in the
+ second line means the definition variable "UP" is <literal>TRUE</literal>
+ when price column in the current row is greater than the price column in
+ the previous row. Likewise, "DOWN" is <literal>TRUE</literal> when when
+ price column in the current row is lower than the price column in the
+ previous row.
+ </para>
+ <para>
+ Once <literal>DEFINE</literal> exists, <literal>PATTERN</literal> can be
+ used. <literal>PATTERN</literal> defines a sequence of rows that satisfies
+ certain conditions. For example following <literal>PATTERN</literal>
+ defines that a row starts with the condition "LOWPRICE", then one or more
+ rows satisfy "UP" and finally one or more rows satisfy "DOWN". If a
+ sequence of rows found, rpr returns the column at the starting row.
+ Example of a <literal>SELECT</literal> using the <literal>DEFINE</literal>
+ and <literal>PATTERN</literal> clause is as follows.
+
+<programlisting>
+SELECT company, tdate, price, max(price) OVER w FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ INITIAL
+ PATTERN (LOWPRICE UP+ DOWN+)
+ DEFINE
+ LOWPRICE AS price <= 100,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+</programlisting>
+ </para>
+
<para>
When a query involves multiple window functions, it is possible to write
out each one with a separate <literal>OVER</literal> clause, but this is
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index f1ad64c3d6..6c46ea4355 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -21780,6 +21780,7 @@ SELECT count(*) FROM sometable;
returns <literal>NULL</literal> if there is no such row.
</para></entry>
</row>
+
</tbody>
</tgroup>
</table>
@@ -21819,6 +21820,59 @@ SELECT count(*) FROM sometable;
Other frame specifications can be used to obtain other effects.
</para>
+ <para>
+ Row pattern recognition navigation functions are listed in
+ <xref linkend="functions-rpr-navigation-table"/>. These functions
+ can be used to describe DEFINE clause of Row pattern recognition.
+ </para>
+
+ <table id="functions-rpr-navigation-table">
+ <title>Row Pattern Navigation Functions</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ Function
+ </para>
+ <para>
+ Description
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>prev</primary>
+ </indexterm>
+ <function>prev</function> ( <parameter>value</parameter> <type>anyelement</type> )
+ <returnvalue>anyelement</returnvalue>
+ </para>
+ <para>
+ Returns the column value at the previous row;
+ returns NULL if there is no previous row in the window frame.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>next</primary>
+ </indexterm>
+ <function>next</function> ( <parameter>value</parameter> <type>anyelement</type> )
+ <returnvalue>anyelement</returnvalue>
+ </para>
+ <para>
+ Returns the column value at the next row;
+ returns NULL if there is no next row in the window frame.
+ </para></entry>
+ </row>
+
+ </tbody>
+ </tgroup>
+ </table>
+
<note>
<para>
The SQL standard defines a <literal>RESPECT NULLS</literal> or
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index 42d78913cf..522ad9dd70 100644
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -969,8 +969,8 @@ WINDOW <replaceable class="parameter">window_name</replaceable> AS ( <replaceabl
The <replaceable class="parameter">frame_clause</replaceable> can be one of
<synopsis>
-{ RANGE | ROWS | GROUPS } <replaceable>frame_start</replaceable> [ <replaceable>frame_exclusion</replaceable> ]
-{ RANGE | ROWS | GROUPS } BETWEEN <replaceable>frame_start</replaceable> AND <replaceable>frame_end</replaceable> [ <replaceable>frame_exclusion</replaceable> ]
+{ RANGE | ROWS | GROUPS } <replaceable>frame_start</replaceable> [ <replaceable>frame_exclusion</replaceable> ] [row_pattern_common_syntax]
+{ RANGE | ROWS | GROUPS } BETWEEN <replaceable>frame_start</replaceable> AND <replaceable>frame_end</replaceable> [ <replaceable>frame_exclusion</replaceable> ] [row_pattern_common_syntax]
</synopsis>
where <replaceable>frame_start</replaceable>
@@ -1077,6 +1077,40 @@ EXCLUDE NO OTHERS
a given peer group will be in the frame or excluded from it.
</para>
+ <para>
+ The
+ optional <replaceable class="parameter">row_pattern_common_syntax</replaceable>
+ defines the <firstterm>row pattern recognition condition</firstterm> for
+ this
+ window. <replaceable class="parameter">row_pattern_common_syntax</replaceable>
+ includes following subclauses. <literal>AFTER MATCH SKIP PAST LAST
+ ROW</literal> or <literal>AFTER MATCH SKIP TO NEXT ROW</literal> controls
+ how to proceed to next row position after a match
+ found. With <literal>AFTER MATCH SKIP PAST LAST ROW</literal> (the
+ default) next row position is next to the last row of previous match. On
+ the other hand, with <literal>AFTER MATCH SKIP TO NEXT ROW</literal> next
+ row position is always next to the last row of previous
+ match. <literal>DEFINE</literal> defines definition variables along with a
+ boolean expression. <literal>PATTERN</literal> defines a sequence of rows
+ that satisfies certain conditions using variables defined
+ in <literal>DEFINE</literal> clause. If the variable is not defined in
+ the <literal>DEFINE</literal> clause, it is implicitly assumed
+ following is defined in the <literal>DEFINE</literal> clause.
+
+<synopsis>
+<literal>variable_name</literal> AS TRUE
+</synopsis>
+
+ Note that the maximu number of variables defined
+ in <literal>DEFINE</literal> clause is 26.
+
+<synopsis>
+[ AFTER MATCH SKIP PAST LAST ROW | AFTER MATCH SKIP TO NEXT ROW ]
+PATTERN <replaceable class="parameter">pattern_variable_name</replaceable>[+] [, ...]
+DEFINE <replaceable class="parameter">definition_varible_name</replaceable> AS <replaceable class="parameter">expression</replaceable> [, ...]
+</synopsis>
+ </para>
+
<para>
The purpose of a <literal>WINDOW</literal> clause is to specify the
behavior of <firstterm>window functions</firstterm> appearing in the query's
--
2.25.1
----Next_Part(Wed_Oct__4_15_03_28_2023_821)--
Content-Type: Text/X-Patch; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="v9-0006-Row-pattern-recognition-patch-tests.patch"
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SQLFunctionCache and generic plans
@ 2025-04-04 21:47 Tom Lane <[email protected]>
2025-04-04 22:00 ` Re: SQLFunctionCache and generic plans Alexander Lakhin <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2025-04-04 21:47 UTC (permalink / raw)
To: Alexander Lakhin <[email protected]>; +Cc: Alexander Pyhalov <[email protected]>; Pavel Stehule <[email protected]>; Alexander Korotkov <[email protected]>; [email protected]; Ronan Dunklau <[email protected]>
Alexander Lakhin <[email protected]> writes:
> I've stumbled upon another defect introduced with 0dca5d68d:
> CREATE FUNCTION f(VARIADIC ANYARRAY) RETURNS ANYELEMENT AS $$ SELECT x FROM generate_series(1,1) g(i) $$ LANGUAGE SQL
> IMMUTABLE;
> SELECT f(1);
> SELECT f(1);
Hmm, I see
regression=# CREATE FUNCTION f(VARIADIC ANYARRAY) RETURNS ANYELEMENT AS $$ SELECT x FROM generate_series(1,1) g(i) $$ LANGUAGE SQL IMMUTABLE;
CREATE FUNCTION
regression=# SELECT f(1);
ERROR: column "x" does not exist
LINE 1: SELECT x FROM generate_series(1,1) g(i)
^
QUERY: SELECT x FROM generate_series(1,1) g(i)
CONTEXT: SQL function "f" statement 1
regression=# SELECT f(1);
ERROR: unrecognized node type: 2139062143
CONTEXT: SQL function "f" statement 1
Did you intend the typo? The "unrecognized node type" does indicate
a problem, but your message doesn't seem to indicate that you're
expecting a syntax error.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SQLFunctionCache and generic plans
2025-04-04 21:47 Re: SQLFunctionCache and generic plans Tom Lane <[email protected]>
@ 2025-04-04 22:00 ` Alexander Lakhin <[email protected]>
2025-04-09 06:19 ` Re: SQLFunctionCache and generic plans Tom Lane <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Alexander Lakhin @ 2025-04-04 22:00 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Alexander Pyhalov <[email protected]>; Pavel Stehule <[email protected]>; Alexander Korotkov <[email protected]>; [email protected]; Ronan Dunklau <[email protected]>
05.04.2025 00:47, Tom Lane wrote:
> Alexander Lakhin<[email protected]> writes:
>> I've stumbled upon another defect introduced with 0dca5d68d:
>> CREATE FUNCTION f(VARIADIC ANYARRAY) RETURNS ANYELEMENT AS $$ SELECT x FROM generate_series(1,1) g(i) $$ LANGUAGE SQL
>> IMMUTABLE;
>> SELECT f(1);
>> SELECT f(1);
> Hmm, I see
>
> regression=# CREATE FUNCTION f(VARIADIC ANYARRAY) RETURNS ANYELEMENT AS $$ SELECT x FROM generate_series(1,1) g(i) $$ LANGUAGE SQL IMMUTABLE;
> CREATE FUNCTION
> regression=# SELECT f(1);
> ERROR: column "x" does not exist
> LINE 1: SELECT x FROM generate_series(1,1) g(i)
> ^
> QUERY: SELECT x FROM generate_series(1,1) g(i)
> CONTEXT: SQL function "f" statement 1
> regression=# SELECT f(1);
> ERROR: unrecognized node type: 2139062143
> CONTEXT: SQL function "f" statement 1
>
> Did you intend the typo? The "unrecognized node type" does indicate
> a problem, but your message doesn't seem to indicate that you're
> expecting a syntax error.
>
Yes, the typo is intended. With Valgrind, I get the "column does not exist"
error on the first call and the Valgrind complaint on the second one.
Best regards,
Alexander Lakhin
Neon (https://neon.tech)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SQLFunctionCache and generic plans
2025-04-04 21:47 Re: SQLFunctionCache and generic plans Tom Lane <[email protected]>
2025-04-04 22:00 ` Re: SQLFunctionCache and generic plans Alexander Lakhin <[email protected]>
@ 2025-04-09 06:19 ` Tom Lane <[email protected]>
2025-04-09 21:01 ` Re: SQLFunctionCache and generic plans Tom Lane <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2025-04-09 06:19 UTC (permalink / raw)
To: Alexander Lakhin <[email protected]>; +Cc: Alexander Pyhalov <[email protected]>; Pavel Stehule <[email protected]>; Alexander Korotkov <[email protected]>; [email protected]; Ronan Dunklau <[email protected]>
In the department of no-good-deed-goes-unpunished ...
I noticed that avocet and trilobite (two of our CLOBBER_CACHE_ALWAYS
animals) have started to fail on the deadlock-parallel isolation
test, with symptoms that look like they're timing out. Poking at
it here with a somewhat faster machine (a Mac M4), I see that under
debug_discard_caches=1 that test went from
ok 27 - deadlock-parallel 26362 ms
immediately before commit 0dca5d68d to
ok 27 - deadlock-parallel 267869 ms
immediately after. This is evidently because the test involves a lot
of evaluations of an intentionally-not-inline-able SQL function.
Previously we cached the plans for that function for the life of the
outer query, but now they're getting clobbered and rebuilt each time.
This is not wrong; the wrong behavior was hanging onto a
potentially-obsolete plan. But it's pretty unfortunate for the
runtime of this test under debug_discard_caches=1.
The simplest fix is to force that test to use debug_discard_caches=0,
but I don't love that answer. Anybody have a better idea?
It looks like the runtime of the infinite_recurse test on these
animals has taken a hit too for the same reason, and there may be
other places.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SQLFunctionCache and generic plans
2025-04-04 21:47 Re: SQLFunctionCache and generic plans Tom Lane <[email protected]>
2025-04-04 22:00 ` Re: SQLFunctionCache and generic plans Alexander Lakhin <[email protected]>
2025-04-09 06:19 ` Re: SQLFunctionCache and generic plans Tom Lane <[email protected]>
@ 2025-04-09 21:01 ` Tom Lane <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2025-04-09 21:01 UTC (permalink / raw)
To: Alexander Lakhin <[email protected]>; +Cc: Alexander Pyhalov <[email protected]>; Pavel Stehule <[email protected]>; Alexander Korotkov <[email protected]>; [email protected]; Ronan Dunklau <[email protected]>
I wrote:
> I noticed that avocet and trilobite (two of our CLOBBER_CACHE_ALWAYS
> animals) have started to fail on the deadlock-parallel isolation
> test, with symptoms that look like they're timing out.
> ...
> The simplest fix is to force that test to use debug_discard_caches=0,
> but I don't love that answer. Anybody have a better idea?
I thought of a better way: we can bypass the need to use a non-inlined
SQL function by declaring internal-language aliases for the
auxiliary-lock functions that are falsely marked parallel-safe.
It's a little bit ugly (see [1]) but I think it beats not running
this test under debug_discard_caches at all.
regards, tom lane
[1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=837cc73af29cd9c63515a6f2a36f54dd7...
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-04-09 21:01 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-10-04 05:51 [PATCH v9 5/7] Row pattern recognition patch (docs). Tatsuo Ishii <[email protected]>
2025-04-04 21:47 Re: SQLFunctionCache and generic plans Tom Lane <[email protected]>
2025-04-04 22:00 ` Re: SQLFunctionCache and generic plans Alexander Lakhin <[email protected]>
2025-04-09 06:19 ` Re: SQLFunctionCache and generic plans Tom Lane <[email protected]>
2025-04-09 21:01 ` Re: SQLFunctionCache and generic plans Tom Lane <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox