agora inbox for [email protected]
help / color / mirror / Atom feed[PATCH v4] Avoid repetitive log of PREPARE during EXECUTE of prepared statements
8+ messages / 5 participants
[nested] [flat]
* [PATCH v4] Avoid repetitive log of PREPARE during EXECUTE of prepared statements
@ 2019-02-10 00:20 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: Justin Pryzby @ 2019-02-10 00:20 UTC (permalink / raw)
---
src/backend/tcop/postgres.c | 70 ++++++++++++---------------------------------
1 file changed, 18 insertions(+), 52 deletions(-)
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index 44a59e1..348343c 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -182,7 +182,6 @@ static int ReadCommand(StringInfo inBuf);
static void forbidden_in_wal_sender(char firstchar);
static List *pg_rewrite_query(Query *query);
static bool check_log_statement(List *stmt_list);
-static int errdetail_execute(List *raw_parsetree_list);
static int errdetail_params(ParamListInfo params);
static int errdetail_abort(void);
static int errdetail_recovery_conflict(void);
@@ -1041,8 +1040,7 @@ exec_simple_query(const char *query_string)
{
ereport(LOG,
(errmsg("statement: %s", query_string),
- errhidestmt(true),
- errdetail_execute(parsetree_list)));
+ errhidestmt(true)));
was_logged = true;
}
@@ -1292,8 +1290,7 @@ exec_simple_query(const char *query_string)
ereport(LOG,
(errmsg("duration: %s ms statement: %s",
msec_str, query_string),
- errhidestmt(true),
- errdetail_execute(parsetree_list)));
+ errhidestmt(true)));
break;
}
@@ -1326,6 +1323,7 @@ exec_parse_message(const char *query_string, /* string to execute */
bool is_named;
bool save_log_statement_stats = log_statement_stats;
char msec_str[32];
+ bool was_logged = false;
/*
* Report query to various monitoring facilities.
@@ -1339,11 +1337,6 @@ exec_parse_message(const char *query_string, /* string to execute */
if (save_log_statement_stats)
ResetUsage();
- ereport(DEBUG2,
- (errmsg("parse %s: %s",
- *stmt_name ? stmt_name : "<unnamed>",
- query_string)));
-
/*
* Start up a transaction command so we can run parse analysis etc. (Note
* that this will normally change current memory context.) Nothing happens
@@ -1404,6 +1397,14 @@ exec_parse_message(const char *query_string, /* string to execute */
Query *query;
bool snapshot_set = false;
+ if (check_log_statement(parsetree_list)) {
+ ereport( LOG, // else log as DEBUG2 ?
+ (errmsg("parse %s: %s",
+ *stmt_name ? stmt_name : "<unnamed>",
+ query_string)));
+ was_logged = true;
+ }
+
raw_parse_tree = linitial_node(RawStmt, parsetree_list);
/*
@@ -1544,7 +1545,7 @@ exec_parse_message(const char *query_string, /* string to execute */
/*
* Emit duration logging if appropriate.
*/
- switch (check_log_duration(msec_str, false))
+ switch (check_log_duration(msec_str, was_logged))
{
case 1:
ereport(LOG,
@@ -1920,12 +1921,11 @@ exec_bind_message(StringInfo input_message)
break;
case 2:
ereport(LOG,
- (errmsg("duration: %s ms bind %s%s%s: %s",
+ (errmsg("duration: %s ms bind %s%s%s",
msec_str,
*stmt_name ? stmt_name : "<unnamed>",
*portal_name ? "/" : "",
- *portal_name ? portal_name : "",
- psrc->query_string),
+ *portal_name ? portal_name : ""),
errhidestmt(true),
errdetail_params(params)));
break;
@@ -2053,14 +2053,13 @@ exec_execute_message(const char *portal_name, long max_rows)
if (check_log_statement(portal->stmts))
{
ereport(LOG,
- (errmsg("%s %s%s%s: %s",
+ (errmsg("%s %s%s%s",
execute_is_fetch ?
_("execute fetch from") :
_("execute"),
prepStmtName,
*portal_name ? "/" : "",
- *portal_name ? portal_name : "",
- sourceText),
+ *portal_name ? portal_name : ""),
errhidestmt(true),
errdetail_params(portalParams)));
was_logged = true;
@@ -2141,15 +2140,14 @@ exec_execute_message(const char *portal_name, long max_rows)
break;
case 2:
ereport(LOG,
- (errmsg("duration: %s ms %s %s%s%s: %s",
+ (errmsg("duration: %s ms %s %s%s%s",
msec_str,
execute_is_fetch ?
_("execute fetch from") :
_("execute"),
prepStmtName,
*portal_name ? "/" : "",
- *portal_name ? portal_name : "",
- sourceText),
+ *portal_name ? portal_name : ""),
errhidestmt(true),
errdetail_params(portalParams)));
break;
@@ -2260,38 +2258,6 @@ check_log_duration(char *msec_str, bool was_logged)
}
/*
- * errdetail_execute
- *
- * Add an errdetail() line showing the query referenced by an EXECUTE, if any.
- * The argument is the raw parsetree list.
- */
-static int
-errdetail_execute(List *raw_parsetree_list)
-{
- ListCell *parsetree_item;
-
- foreach(parsetree_item, raw_parsetree_list)
- {
- RawStmt *parsetree = lfirst_node(RawStmt, parsetree_item);
-
- if (IsA(parsetree->stmt, ExecuteStmt))
- {
- ExecuteStmt *stmt = (ExecuteStmt *) parsetree->stmt;
- PreparedStatement *pstmt;
-
- pstmt = FetchPreparedStatement(stmt->name, false);
- if (pstmt)
- {
- errdetail("prepare: %s", pstmt->plansource->query_string);
- return 0;
- }
- }
- }
-
- return 0;
-}
-
-/*
* errdetail_params
*
* Add an errdetail() line showing bind-parameter data, if available.
--
2.1.4
--7AUc2qLy4jB3hD7Z--
^ permalink raw reply [nested|flat] 8+ messages in thread
* [PATCH v2] Avoid repetitive log of PREPARE during EXECUTE of prepared statements
@ 2019-02-10 00:20 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: Justin Pryzby @ 2019-02-10 00:20 UTC (permalink / raw)
---
src/backend/tcop/postgres.c | 13 +++++--------
1 file changed, 5 insertions(+), 8 deletions(-)
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index 8b4d94c9a1..dac5362c81 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -1041,8 +1041,7 @@ exec_simple_query(const char *query_string)
{
ereport(LOG,
(errmsg("statement: %s", query_string),
- errhidestmt(true),
- errdetail_execute(parsetree_list)));
+ errhidestmt(true)));
was_logged = true;
}
@@ -2062,14 +2061,13 @@ exec_execute_message(const char *portal_name, long max_rows)
if (check_log_statement(portal->stmts))
{
ereport(LOG,
- (errmsg("%s %s%s%s: %s",
+ (errmsg("%s %s%s%s",
execute_is_fetch ?
_("execute fetch from") :
_("execute"),
prepStmtName,
*portal_name ? "/" : "",
- *portal_name ? portal_name : "",
- sourceText),
+ *portal_name ? portal_name : ""),
errhidestmt(true),
errdetail_params(portalParams)));
was_logged = true;
@@ -2150,15 +2148,14 @@ exec_execute_message(const char *portal_name, long max_rows)
break;
case 2:
ereport(LOG,
- (errmsg("duration: %s ms %s %s%s%s: %s",
+ (errmsg("duration: %s ms %s %s%s%s",
msec_str,
execute_is_fetch ?
_("execute fetch from") :
_("execute"),
prepStmtName,
*portal_name ? "/" : "",
- *portal_name ? portal_name : "",
- sourceText),
+ *portal_name ? portal_name : ""),
errhidestmt(true),
errdetail_params(portalParams)));
break;
--
2.12.2
--r7U+bLA8boMOj+mD--
^ permalink raw reply [nested|flat] 8+ messages in thread
* [PATCH v3] Avoid repetitive log of PREPARE during EXECUTE of prepared statements
@ 2019-02-10 00:20 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: Justin Pryzby @ 2019-02-10 00:20 UTC (permalink / raw)
---
src/backend/tcop/postgres.c | 54 +++++++--------------------------------------
1 file changed, 8 insertions(+), 46 deletions(-)
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index 8b4d94c9a1..f348475ea3 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -182,7 +182,6 @@ static int ReadCommand(StringInfo inBuf);
static void forbidden_in_wal_sender(char firstchar);
static List *pg_rewrite_query(Query *query);
static bool check_log_statement(List *stmt_list);
-static int errdetail_execute(List *raw_parsetree_list);
static int errdetail_params(ParamListInfo params);
static int errdetail_abort(void);
static int errdetail_recovery_conflict(void);
@@ -1041,8 +1040,7 @@ exec_simple_query(const char *query_string)
{
ereport(LOG,
(errmsg("statement: %s", query_string),
- errhidestmt(true),
- errdetail_execute(parsetree_list)));
+ errhidestmt(true)));
was_logged = true;
}
@@ -1292,8 +1290,7 @@ exec_simple_query(const char *query_string)
ereport(LOG,
(errmsg("duration: %s ms statement: %s",
msec_str, query_string),
- errhidestmt(true),
- errdetail_execute(parsetree_list)));
+ errhidestmt(true)));
break;
}
@@ -1929,12 +1926,11 @@ exec_bind_message(StringInfo input_message)
break;
case 2:
ereport(LOG,
- (errmsg("duration: %s ms bind %s%s%s: %s",
+ (errmsg("duration: %s ms bind %s%s%s",
msec_str,
*stmt_name ? stmt_name : "<unnamed>",
*portal_name ? "/" : "",
- *portal_name ? portal_name : "",
- psrc->query_string),
+ *portal_name ? portal_name : ""),
errhidestmt(true),
errdetail_params(params)));
break;
@@ -2062,14 +2058,13 @@ exec_execute_message(const char *portal_name, long max_rows)
if (check_log_statement(portal->stmts))
{
ereport(LOG,
- (errmsg("%s %s%s%s: %s",
+ (errmsg("%s %s%s%s",
execute_is_fetch ?
_("execute fetch from") :
_("execute"),
prepStmtName,
*portal_name ? "/" : "",
- *portal_name ? portal_name : "",
- sourceText),
+ *portal_name ? portal_name : ""),
errhidestmt(true),
errdetail_params(portalParams)));
was_logged = true;
@@ -2150,15 +2145,14 @@ exec_execute_message(const char *portal_name, long max_rows)
break;
case 2:
ereport(LOG,
- (errmsg("duration: %s ms %s %s%s%s: %s",
+ (errmsg("duration: %s ms %s %s%s%s",
msec_str,
execute_is_fetch ?
_("execute fetch from") :
_("execute"),
prepStmtName,
*portal_name ? "/" : "",
- *portal_name ? portal_name : "",
- sourceText),
+ *portal_name ? portal_name : ""),
errhidestmt(true),
errdetail_params(portalParams)));
break;
@@ -2267,38 +2261,6 @@ check_log_duration(char *msec_str, bool was_logged)
}
/*
- * errdetail_execute
- *
- * Add an errdetail() line showing the query referenced by an EXECUTE, if any.
- * The argument is the raw parsetree list.
- */
-static int
-errdetail_execute(List *raw_parsetree_list)
-{
- ListCell *parsetree_item;
-
- foreach(parsetree_item, raw_parsetree_list)
- {
- RawStmt *parsetree = lfirst_node(RawStmt, parsetree_item);
-
- if (IsA(parsetree->stmt, ExecuteStmt))
- {
- ExecuteStmt *stmt = (ExecuteStmt *) parsetree->stmt;
- PreparedStatement *pstmt;
-
- pstmt = FetchPreparedStatement(stmt->name, false);
- if (pstmt)
- {
- errdetail("prepare: %s", pstmt->plansource->query_string);
- return 0;
- }
- }
- }
-
- return 0;
-}
-
-/*
* errdetail_params
*
* Add an errdetail() line showing bind-parameter data, if available.
--
2.12.2
--+pHx0qQiF2pBVqBT--
^ permalink raw reply [nested|flat] 8+ messages in thread
* [PATCH v6 6/7] Row pattern recognition patch (tests).
@ 2023-09-12 05:22 Tatsuo Ishii <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: Tatsuo Ishii @ 2023-09-12 05:22 UTC (permalink / raw)
---
src/test/regress/expected/rpr.out | 594 +++++++++++++++++++++++++++++
src/test/regress/parallel_schedule | 2 +-
src/test/regress/sql/rpr.sql | 292 ++++++++++++++
3 files changed, 887 insertions(+), 1 deletion(-)
create mode 100644 src/test/regress/expected/rpr.out
create mode 100644 src/test/regress/sql/rpr.sql
diff --git a/src/test/regress/expected/rpr.out b/src/test/regress/expected/rpr.out
new file mode 100644
index 0000000000..63bed05f05
--- /dev/null
+++ b/src/test/regress/expected/rpr.out
@@ -0,0 +1,594 @@
+--
+-- Test for row pattern definition clause
+--
+CREATE TEMP TABLE stock (
+ company TEXT,
+ tdate DATE,
+ price INTEGER
+ );
+INSERT INTO stock VALUES ('company1', '2023-07-01', 100);
+INSERT INTO stock VALUES ('company1', '2023-07-02', 200);
+INSERT INTO stock VALUES ('company1', '2023-07-03', 150);
+INSERT INTO stock VALUES ('company1', '2023-07-04', 140);
+INSERT INTO stock VALUES ('company1', '2023-07-05', 150);
+INSERT INTO stock VALUES ('company1', '2023-07-06', 90);
+INSERT INTO stock VALUES ('company1', '2023-07-07', 110);
+INSERT INTO stock VALUES ('company1', '2023-07-08', 130);
+INSERT INTO stock VALUES ('company1', '2023-07-09', 120);
+INSERT INTO stock VALUES ('company1', '2023-07-10', 130);
+INSERT INTO stock VALUES ('company2', '2023-07-01', 50);
+INSERT INTO stock VALUES ('company2', '2023-07-02', 2000);
+INSERT INTO stock VALUES ('company2', '2023-07-03', 1500);
+INSERT INTO stock VALUES ('company2', '2023-07-04', 1400);
+INSERT INTO stock VALUES ('company2', '2023-07-05', 1500);
+INSERT INTO stock VALUES ('company2', '2023-07-06', 60);
+INSERT INTO stock VALUES ('company2', '2023-07-07', 1100);
+INSERT INTO stock VALUES ('company2', '2023-07-08', 1300);
+INSERT INTO stock VALUES ('company2', '2023-07-09', 1200);
+INSERT INTO stock VALUES ('company2', '2023-07-10', 1300);
+SELECT * FROM stock;
+ company | tdate | price
+----------+------------+-------
+ company1 | 07-01-2023 | 100
+ company1 | 07-02-2023 | 200
+ company1 | 07-03-2023 | 150
+ company1 | 07-04-2023 | 140
+ company1 | 07-05-2023 | 150
+ company1 | 07-06-2023 | 90
+ company1 | 07-07-2023 | 110
+ company1 | 07-08-2023 | 130
+ company1 | 07-09-2023 | 120
+ company1 | 07-10-2023 | 130
+ company2 | 07-01-2023 | 50
+ company2 | 07-02-2023 | 2000
+ company2 | 07-03-2023 | 1500
+ company2 | 07-04-2023 | 1400
+ company2 | 07-05-2023 | 1500
+ company2 | 07-06-2023 | 60
+ company2 | 07-07-2023 | 1100
+ company2 | 07-08-2023 | 1300
+ company2 | 07-09-2023 | 1200
+ company2 | 07-10-2023 | 1300
+(20 rows)
+
+-- basic test using PREV
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w,
+ nth_value(tdate, 2) OVER w AS nth_second
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+ company | tdate | price | first_value | last_value | nth_second
+----------+------------+-------+-------------+------------+------------
+ company1 | 07-01-2023 | 100 | 100 | 140 | 07-02-2023
+ company1 | 07-02-2023 | 200 | | |
+ company1 | 07-03-2023 | 150 | | |
+ company1 | 07-04-2023 | 140 | | |
+ company1 | 07-05-2023 | 150 | | |
+ company1 | 07-06-2023 | 90 | 90 | 120 | 07-07-2023
+ company1 | 07-07-2023 | 110 | | |
+ company1 | 07-08-2023 | 130 | | |
+ company1 | 07-09-2023 | 120 | | |
+ company1 | 07-10-2023 | 130 | | |
+ company2 | 07-01-2023 | 50 | 50 | 1400 | 07-02-2023
+ company2 | 07-02-2023 | 2000 | | |
+ company2 | 07-03-2023 | 1500 | | |
+ company2 | 07-04-2023 | 1400 | | |
+ company2 | 07-05-2023 | 1500 | | |
+ company2 | 07-06-2023 | 60 | 60 | 1200 | 07-07-2023
+ company2 | 07-07-2023 | 1100 | | |
+ company2 | 07-08-2023 | 1300 | | |
+ company2 | 07-09-2023 | 1200 | | |
+ company2 | 07-10-2023 | 1300 | | |
+(20 rows)
+
+-- last_value() should remain consistent
+SELECT company, tdate, price, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+ company | tdate | price | last_value
+----------+------------+-------+------------
+ company1 | 07-01-2023 | 100 | 140
+ company1 | 07-02-2023 | 200 |
+ company1 | 07-03-2023 | 150 |
+ company1 | 07-04-2023 | 140 |
+ company1 | 07-05-2023 | 150 |
+ company1 | 07-06-2023 | 90 | 120
+ company1 | 07-07-2023 | 110 |
+ company1 | 07-08-2023 | 130 |
+ company1 | 07-09-2023 | 120 |
+ company1 | 07-10-2023 | 130 |
+ company2 | 07-01-2023 | 50 | 1400
+ company2 | 07-02-2023 | 2000 |
+ company2 | 07-03-2023 | 1500 |
+ company2 | 07-04-2023 | 1400 |
+ company2 | 07-05-2023 | 1500 |
+ company2 | 07-06-2023 | 60 | 1200
+ company2 | 07-07-2023 | 1100 |
+ company2 | 07-08-2023 | 1300 |
+ company2 | 07-09-2023 | 1200 |
+ company2 | 07-10-2023 | 1300 |
+(20 rows)
+
+-- omit "START" in DEFINE but it is ok because "START AS TRUE" is
+-- implicitly defined. per spec.
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w,
+ nth_value(tdate, 2) OVER w AS nth_second
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+ company | tdate | price | first_value | last_value | nth_second
+----------+------------+-------+-------------+------------+------------
+ company1 | 07-01-2023 | 100 | 100 | 140 | 07-02-2023
+ company1 | 07-02-2023 | 200 | | |
+ company1 | 07-03-2023 | 150 | | |
+ company1 | 07-04-2023 | 140 | | |
+ company1 | 07-05-2023 | 150 | | |
+ company1 | 07-06-2023 | 90 | 90 | 120 | 07-07-2023
+ company1 | 07-07-2023 | 110 | | |
+ company1 | 07-08-2023 | 130 | | |
+ company1 | 07-09-2023 | 120 | | |
+ company1 | 07-10-2023 | 130 | | |
+ company2 | 07-01-2023 | 50 | 50 | 1400 | 07-02-2023
+ company2 | 07-02-2023 | 2000 | | |
+ company2 | 07-03-2023 | 1500 | | |
+ company2 | 07-04-2023 | 1400 | | |
+ company2 | 07-05-2023 | 1500 | | |
+ company2 | 07-06-2023 | 60 | 60 | 1200 | 07-07-2023
+ company2 | 07-07-2023 | 1100 | | |
+ company2 | 07-08-2023 | 1300 | | |
+ company2 | 07-09-2023 | 1200 | | |
+ company2 | 07-10-2023 | 1300 | | |
+(20 rows)
+
+-- the first row start with less than or equal to 100
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (LOWPRICE UP+ DOWN+)
+ DEFINE
+ LOWPRICE AS price <= 100,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+ company | tdate | price | first_value | last_value
+----------+------------+-------+-------------+------------
+ company1 | 07-01-2023 | 100 | 100 | 140
+ company1 | 07-02-2023 | 200 | |
+ company1 | 07-03-2023 | 150 | |
+ company1 | 07-04-2023 | 140 | |
+ company1 | 07-05-2023 | 150 | |
+ company1 | 07-06-2023 | 90 | 90 | 120
+ company1 | 07-07-2023 | 110 | |
+ company1 | 07-08-2023 | 130 | |
+ company1 | 07-09-2023 | 120 | |
+ company1 | 07-10-2023 | 130 | |
+ company2 | 07-01-2023 | 50 | 50 | 1400
+ company2 | 07-02-2023 | 2000 | |
+ company2 | 07-03-2023 | 1500 | |
+ company2 | 07-04-2023 | 1400 | |
+ company2 | 07-05-2023 | 1500 | |
+ company2 | 07-06-2023 | 60 | 60 | 1200
+ company2 | 07-07-2023 | 1100 | |
+ company2 | 07-08-2023 | 1300 | |
+ company2 | 07-09-2023 | 1200 | |
+ company2 | 07-10-2023 | 1300 | |
+(20 rows)
+
+-- second row raises 120%
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (LOWPRICE UP+ DOWN+)
+ DEFINE
+ LOWPRICE AS price <= 100,
+ UP AS price > PREV(price) * 1.2,
+ DOWN AS price < PREV(price)
+);
+ company | tdate | price | first_value | last_value
+----------+------------+-------+-------------+------------
+ company1 | 07-01-2023 | 100 | 100 | 140
+ company1 | 07-02-2023 | 200 | |
+ company1 | 07-03-2023 | 150 | |
+ company1 | 07-04-2023 | 140 | |
+ company1 | 07-05-2023 | 150 | |
+ company1 | 07-06-2023 | 90 | |
+ company1 | 07-07-2023 | 110 | |
+ company1 | 07-08-2023 | 130 | |
+ company1 | 07-09-2023 | 120 | |
+ company1 | 07-10-2023 | 130 | |
+ company2 | 07-01-2023 | 50 | 50 | 1400
+ company2 | 07-02-2023 | 2000 | |
+ company2 | 07-03-2023 | 1500 | |
+ company2 | 07-04-2023 | 1400 | |
+ company2 | 07-05-2023 | 1500 | |
+ company2 | 07-06-2023 | 60 | |
+ company2 | 07-07-2023 | 1100 | |
+ company2 | 07-08-2023 | 1300 | |
+ company2 | 07-09-2023 | 1200 | |
+ company2 | 07-10-2023 | 1300 | |
+(20 rows)
+
+-- using NEXT
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START UPDOWN)
+ DEFINE
+ START AS TRUE,
+ UPDOWN AS price > PREV(price) AND price > NEXT(price)
+);
+ company | tdate | price | first_value | last_value
+----------+------------+-------+-------------+------------
+ company1 | 07-01-2023 | 100 | 100 | 200
+ company1 | 07-02-2023 | 200 | |
+ company1 | 07-03-2023 | 150 | |
+ company1 | 07-04-2023 | 140 | 140 | 150
+ company1 | 07-05-2023 | 150 | |
+ company1 | 07-06-2023 | 90 | |
+ company1 | 07-07-2023 | 110 | 110 | 130
+ company1 | 07-08-2023 | 130 | |
+ company1 | 07-09-2023 | 120 | |
+ company1 | 07-10-2023 | 130 | |
+ company2 | 07-01-2023 | 50 | 50 | 2000
+ company2 | 07-02-2023 | 2000 | |
+ company2 | 07-03-2023 | 1500 | |
+ company2 | 07-04-2023 | 1400 | 1400 | 1500
+ company2 | 07-05-2023 | 1500 | |
+ company2 | 07-06-2023 | 60 | |
+ company2 | 07-07-2023 | 1100 | 1100 | 1300
+ company2 | 07-08-2023 | 1300 | |
+ company2 | 07-09-2023 | 1200 | |
+ company2 | 07-10-2023 | 1300 | |
+(20 rows)
+
+-- using AFTER MATCH SKIP TO NEXT ROW
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ INITIAL
+ PATTERN (START UPDOWN)
+ DEFINE
+ START AS TRUE,
+ UPDOWN AS price > PREV(price) AND price > NEXT(price)
+);
+ company | tdate | price | first_value | last_value
+----------+------------+-------+-------------+------------
+ company1 | 07-01-2023 | 100 | 100 | 200
+ company1 | 07-02-2023 | 200 | |
+ company1 | 07-03-2023 | 150 | |
+ company1 | 07-04-2023 | 140 | 140 | 150
+ company1 | 07-05-2023 | 150 | |
+ company1 | 07-06-2023 | 90 | |
+ company1 | 07-07-2023 | 110 | 110 | 130
+ company1 | 07-08-2023 | 130 | |
+ company1 | 07-09-2023 | 120 | |
+ company1 | 07-10-2023 | 130 | |
+ company2 | 07-01-2023 | 50 | 50 | 2000
+ company2 | 07-02-2023 | 2000 | |
+ company2 | 07-03-2023 | 1500 | |
+ company2 | 07-04-2023 | 1400 | 1400 | 1500
+ company2 | 07-05-2023 | 1500 | |
+ company2 | 07-06-2023 | 60 | |
+ company2 | 07-07-2023 | 1100 | 1100 | 1300
+ company2 | 07-08-2023 | 1300 | |
+ company2 | 07-09-2023 | 1200 | |
+ company2 | 07-10-2023 | 1300 | |
+(20 rows)
+
+-- match everything
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ INITIAL
+ PATTERN (A+)
+ DEFINE
+ A AS TRUE
+);
+ company | tdate | price | first_value | last_value
+----------+------------+-------+-------------+------------
+ company1 | 07-01-2023 | 100 | 100 | 130
+ company1 | 07-02-2023 | 200 | |
+ company1 | 07-03-2023 | 150 | |
+ company1 | 07-04-2023 | 140 | |
+ company1 | 07-05-2023 | 150 | |
+ company1 | 07-06-2023 | 90 | |
+ company1 | 07-07-2023 | 110 | |
+ company1 | 07-08-2023 | 130 | |
+ company1 | 07-09-2023 | 120 | |
+ company1 | 07-10-2023 | 130 | |
+ company2 | 07-01-2023 | 50 | 50 | 1300
+ company2 | 07-02-2023 | 2000 | |
+ company2 | 07-03-2023 | 1500 | |
+ company2 | 07-04-2023 | 1400 | |
+ company2 | 07-05-2023 | 1500 | |
+ company2 | 07-06-2023 | 60 | |
+ company2 | 07-07-2023 | 1100 | |
+ company2 | 07-08-2023 | 1300 | |
+ company2 | 07-09-2023 | 1200 | |
+ company2 | 07-10-2023 | 1300 | |
+(20 rows)
+
+-- backtracking with reclassification of rows
+-- using AFTER MATCH SKIP PAST LAST ROW
+SELECT company, tdate, price, first_value(tdate) OVER w, last_value(tdate) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ INITIAL
+ PATTERN (A+ B+)
+ DEFINE
+ A AS price > 100,
+ B AS price > 100
+);
+ company | tdate | price | first_value | last_value
+----------+------------+-------+-------------+------------
+ company1 | 07-01-2023 | 100 | |
+ company1 | 07-02-2023 | 200 | 07-02-2023 | 07-05-2023
+ company1 | 07-03-2023 | 150 | |
+ company1 | 07-04-2023 | 140 | |
+ company1 | 07-05-2023 | 150 | |
+ company1 | 07-06-2023 | 90 | |
+ company1 | 07-07-2023 | 110 | 07-07-2023 | 07-10-2023
+ company1 | 07-08-2023 | 130 | |
+ company1 | 07-09-2023 | 120 | |
+ company1 | 07-10-2023 | 130 | |
+ company2 | 07-01-2023 | 50 | |
+ company2 | 07-02-2023 | 2000 | 07-02-2023 | 07-05-2023
+ company2 | 07-03-2023 | 1500 | |
+ company2 | 07-04-2023 | 1400 | |
+ company2 | 07-05-2023 | 1500 | |
+ company2 | 07-06-2023 | 60 | |
+ company2 | 07-07-2023 | 1100 | 07-07-2023 | 07-10-2023
+ company2 | 07-08-2023 | 1300 | |
+ company2 | 07-09-2023 | 1200 | |
+ company2 | 07-10-2023 | 1300 | |
+(20 rows)
+
+-- backtracking with reclassification of rows
+-- using AFTER MATCH SKIP TO NEXT ROW
+SELECT company, tdate, price, first_value(tdate) OVER w, last_value(tdate) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ INITIAL
+ PATTERN (A+ B+)
+ DEFINE
+ A AS price > 100,
+ B AS price > 100
+);
+ company | tdate | price | first_value | last_value
+----------+------------+-------+-------------+------------
+ company1 | 07-01-2023 | 100 | |
+ company1 | 07-02-2023 | 200 | 07-02-2023 | 07-05-2023
+ company1 | 07-03-2023 | 150 | 07-03-2023 | 07-05-2023
+ company1 | 07-04-2023 | 140 | 07-04-2023 | 07-05-2023
+ company1 | 07-05-2023 | 150 | |
+ company1 | 07-06-2023 | 90 | |
+ company1 | 07-07-2023 | 110 | 07-07-2023 | 07-10-2023
+ company1 | 07-08-2023 | 130 | 07-08-2023 | 07-10-2023
+ company1 | 07-09-2023 | 120 | 07-09-2023 | 07-10-2023
+ company1 | 07-10-2023 | 130 | |
+ company2 | 07-01-2023 | 50 | |
+ company2 | 07-02-2023 | 2000 | 07-02-2023 | 07-05-2023
+ company2 | 07-03-2023 | 1500 | 07-03-2023 | 07-05-2023
+ company2 | 07-04-2023 | 1400 | 07-04-2023 | 07-05-2023
+ company2 | 07-05-2023 | 1500 | |
+ company2 | 07-06-2023 | 60 | |
+ company2 | 07-07-2023 | 1100 | 07-07-2023 | 07-10-2023
+ company2 | 07-08-2023 | 1300 | 07-08-2023 | 07-10-2023
+ company2 | 07-09-2023 | 1200 | 07-09-2023 | 07-10-2023
+ company2 | 07-10-2023 | 1300 | |
+(20 rows)
+
+--
+-- Aggregates
+--
+-- using AFTER MATCH SKIP PAST LAST ROW
+SELECT company, tdate, price,
+ first_value(price) OVER w,
+ last_value(price) OVER w,
+ max(price) OVER w,
+ min(price) OVER w,
+ sum(price) OVER w,
+ avg(price) OVER w,
+ count(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 (START UP+ DOWN+)
+DEFINE
+START AS TRUE,
+UP AS price > PREV(price),
+DOWN AS price < PREV(price)
+);
+ company | tdate | price | first_value | last_value | max | min | sum | avg | count
+----------+------------+-------+-------------+------------+------+-----+------+-----------------------+-------
+ company1 | 07-01-2023 | 100 | 100 | 140 | 200 | 100 | 590 | 147.5000000000000000 | 4
+ company1 | 07-02-2023 | 200 | | | | | | |
+ company1 | 07-03-2023 | 150 | | | | | | |
+ company1 | 07-04-2023 | 140 | | | | | | |
+ company1 | 07-05-2023 | 150 | | | | | | |
+ company1 | 07-06-2023 | 90 | 90 | 120 | 130 | 90 | 450 | 112.5000000000000000 | 4
+ company1 | 07-07-2023 | 110 | | | | | | |
+ company1 | 07-08-2023 | 130 | | | | | | |
+ company1 | 07-09-2023 | 120 | | | | | | |
+ company1 | 07-10-2023 | 130 | | | | | | |
+ company2 | 07-01-2023 | 50 | 50 | 1400 | 2000 | 50 | 4950 | 1237.5000000000000000 | 4
+ company2 | 07-02-2023 | 2000 | | | | | | |
+ company2 | 07-03-2023 | 1500 | | | | | | |
+ company2 | 07-04-2023 | 1400 | | | | | | |
+ company2 | 07-05-2023 | 1500 | | | | | | |
+ company2 | 07-06-2023 | 60 | 60 | 1200 | 1300 | 60 | 3660 | 915.0000000000000000 | 4
+ company2 | 07-07-2023 | 1100 | | | | | | |
+ company2 | 07-08-2023 | 1300 | | | | | | |
+ company2 | 07-09-2023 | 1200 | | | | | | |
+ company2 | 07-10-2023 | 1300 | | | | | | |
+(20 rows)
+
+-- using AFTER MATCH SKIP TO NEXT ROW
+SELECT company, tdate, price,
+ first_value(price) OVER w,
+ last_value(price) OVER w,
+ max(price) OVER w,
+ min(price) OVER w,
+ sum(price) OVER w,
+ avg(price) OVER w,
+ count(price) OVER w
+FROM stock
+WINDOW w AS (
+PARTITION BY company
+ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+AFTER MATCH SKIP TO NEXT ROW
+INITIAL
+PATTERN (START UP+ DOWN+)
+DEFINE
+START AS TRUE,
+UP AS price > PREV(price),
+DOWN AS price < PREV(price)
+);
+ company | tdate | price | first_value | last_value | max | min | sum | avg | count
+----------+------------+-------+-------------+------------+------+------+------+-----------------------+-------
+ company1 | 07-01-2023 | 100 | 100 | 140 | 200 | 100 | 590 | 147.5000000000000000 | 4
+ company1 | 07-02-2023 | 200 | | | | | | | 0
+ company1 | 07-03-2023 | 150 | | | | | | | 0
+ company1 | 07-04-2023 | 140 | 140 | 90 | 150 | 90 | 380 | 126.6666666666666667 | 3
+ company1 | 07-05-2023 | 150 | | | | | | | 0
+ company1 | 07-06-2023 | 90 | 90 | 120 | 130 | 90 | 450 | 112.5000000000000000 | 4
+ company1 | 07-07-2023 | 110 | 110 | 120 | 130 | 110 | 360 | 120.0000000000000000 | 3
+ company1 | 07-08-2023 | 130 | | | | | | | 0
+ company1 | 07-09-2023 | 120 | | | | | | | 0
+ company1 | 07-10-2023 | 130 | | | | | | | 0
+ company2 | 07-01-2023 | 50 | 50 | 1400 | 2000 | 50 | 4950 | 1237.5000000000000000 | 4
+ company2 | 07-02-2023 | 2000 | | | | | | | 0
+ company2 | 07-03-2023 | 1500 | | | | | | | 0
+ company2 | 07-04-2023 | 1400 | 1400 | 60 | 1500 | 60 | 2960 | 986.6666666666666667 | 3
+ company2 | 07-05-2023 | 1500 | | | | | | | 0
+ company2 | 07-06-2023 | 60 | 60 | 1200 | 1300 | 60 | 3660 | 915.0000000000000000 | 4
+ company2 | 07-07-2023 | 1100 | 1100 | 1200 | 1300 | 1100 | 3600 | 1200.0000000000000000 | 3
+ company2 | 07-08-2023 | 1300 | | | | | | | 0
+ company2 | 07-09-2023 | 1200 | | | | | | | 0
+ company2 | 07-10-2023 | 1300 | | | | | | | 0
+(20 rows)
+
+--
+-- Error cases
+--
+-- row pattern definition variable name must not appear more than once
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ ORDER BY tdate
+ INITIAL
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price),
+ UP AS price > PREV(price)
+);
+ERROR: syntax error at or near "ORDER"
+LINE 6: ORDER BY tdate
+ ^
+-- pattern variable name must appear in DEFINE
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START UP+ DOWN+ END)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+ERROR: syntax error at or near "END"
+LINE 8: PATTERN (START UP+ DOWN+ END)
+ ^
+-- FRAME must start at current row when row patttern recognition is used
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+ERROR: FRAME must start at current row when row patttern recognition is used
+-- SEEK is not supported
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ SEEK
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+ERROR: SEEK is not supported
+LINE 8: SEEK
+ ^
+HINT: Use INITIAL.
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 4df9d8503b..896531002b 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -98,7 +98,7 @@ test: publication subscription
# Another group of parallel tests
# select_views depends on create_view
# ----------
-test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combocid tsearch tsdicts foreign_data window xmlmap functional_deps advisory_lock indirect_toast equivclass
+test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combocid tsearch tsdicts foreign_data window xmlmap functional_deps advisory_lock indirect_toast equivclass rpr
# ----------
# Another group of parallel tests (JSON related)
diff --git a/src/test/regress/sql/rpr.sql b/src/test/regress/sql/rpr.sql
new file mode 100644
index 0000000000..bdefd20647
--- /dev/null
+++ b/src/test/regress/sql/rpr.sql
@@ -0,0 +1,292 @@
+--
+-- Test for row pattern definition clause
+--
+
+CREATE TEMP TABLE stock (
+ company TEXT,
+ tdate DATE,
+ price INTEGER
+ );
+INSERT INTO stock VALUES ('company1', '2023-07-01', 100);
+INSERT INTO stock VALUES ('company1', '2023-07-02', 200);
+INSERT INTO stock VALUES ('company1', '2023-07-03', 150);
+INSERT INTO stock VALUES ('company1', '2023-07-04', 140);
+INSERT INTO stock VALUES ('company1', '2023-07-05', 150);
+INSERT INTO stock VALUES ('company1', '2023-07-06', 90);
+INSERT INTO stock VALUES ('company1', '2023-07-07', 110);
+INSERT INTO stock VALUES ('company1', '2023-07-08', 130);
+INSERT INTO stock VALUES ('company1', '2023-07-09', 120);
+INSERT INTO stock VALUES ('company1', '2023-07-10', 130);
+INSERT INTO stock VALUES ('company2', '2023-07-01', 50);
+INSERT INTO stock VALUES ('company2', '2023-07-02', 2000);
+INSERT INTO stock VALUES ('company2', '2023-07-03', 1500);
+INSERT INTO stock VALUES ('company2', '2023-07-04', 1400);
+INSERT INTO stock VALUES ('company2', '2023-07-05', 1500);
+INSERT INTO stock VALUES ('company2', '2023-07-06', 60);
+INSERT INTO stock VALUES ('company2', '2023-07-07', 1100);
+INSERT INTO stock VALUES ('company2', '2023-07-08', 1300);
+INSERT INTO stock VALUES ('company2', '2023-07-09', 1200);
+INSERT INTO stock VALUES ('company2', '2023-07-10', 1300);
+
+SELECT * FROM stock;
+
+-- basic test using PREV
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w,
+ nth_value(tdate, 2) OVER w AS nth_second
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+
+-- last_value() should remain consistent
+SELECT company, tdate, price, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+
+-- omit "START" in DEFINE but it is ok because "START AS TRUE" is
+-- implicitly defined. per spec.
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w,
+ nth_value(tdate, 2) OVER w AS nth_second
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+
+-- the first row start with less than or equal to 100
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (LOWPRICE UP+ DOWN+)
+ DEFINE
+ LOWPRICE AS price <= 100,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+
+-- second row raises 120%
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (LOWPRICE UP+ DOWN+)
+ DEFINE
+ LOWPRICE AS price <= 100,
+ UP AS price > PREV(price) * 1.2,
+ DOWN AS price < PREV(price)
+);
+
+-- using NEXT
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START UPDOWN)
+ DEFINE
+ START AS TRUE,
+ UPDOWN AS price > PREV(price) AND price > NEXT(price)
+);
+
+-- using AFTER MATCH SKIP TO NEXT ROW
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ INITIAL
+ PATTERN (START UPDOWN)
+ DEFINE
+ START AS TRUE,
+ UPDOWN AS price > PREV(price) AND price > NEXT(price)
+);
+
+-- match everything
+
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ INITIAL
+ PATTERN (A+)
+ DEFINE
+ A AS TRUE
+);
+
+-- backtracking with reclassification of rows
+-- using AFTER MATCH SKIP PAST LAST ROW
+SELECT company, tdate, price, first_value(tdate) OVER w, last_value(tdate) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ INITIAL
+ PATTERN (A+ B+)
+ DEFINE
+ A AS price > 100,
+ B AS price > 100
+);
+
+-- backtracking with reclassification of rows
+-- using AFTER MATCH SKIP TO NEXT ROW
+SELECT company, tdate, price, first_value(tdate) OVER w, last_value(tdate) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ INITIAL
+ PATTERN (A+ B+)
+ DEFINE
+ A AS price > 100,
+ B AS price > 100
+);
+
+--
+-- Aggregates
+--
+
+-- using AFTER MATCH SKIP PAST LAST ROW
+SELECT company, tdate, price,
+ first_value(price) OVER w,
+ last_value(price) OVER w,
+ max(price) OVER w,
+ min(price) OVER w,
+ sum(price) OVER w,
+ avg(price) OVER w,
+ count(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 (START UP+ DOWN+)
+DEFINE
+START AS TRUE,
+UP AS price > PREV(price),
+DOWN AS price < PREV(price)
+);
+
+-- using AFTER MATCH SKIP TO NEXT ROW
+SELECT company, tdate, price,
+ first_value(price) OVER w,
+ last_value(price) OVER w,
+ max(price) OVER w,
+ min(price) OVER w,
+ sum(price) OVER w,
+ avg(price) OVER w,
+ count(price) OVER w
+FROM stock
+WINDOW w AS (
+PARTITION BY company
+ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+AFTER MATCH SKIP TO NEXT ROW
+INITIAL
+PATTERN (START UP+ DOWN+)
+DEFINE
+START AS TRUE,
+UP AS price > PREV(price),
+DOWN AS price < PREV(price)
+);
+
+--
+-- Error cases
+--
+
+-- row pattern definition variable name must not appear more than once
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ ORDER BY tdate
+ INITIAL
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price),
+ UP AS price > PREV(price)
+);
+
+-- pattern variable name must appear in DEFINE
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START UP+ DOWN+ END)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+
+-- FRAME must start at current row when row patttern recognition is used
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+
+-- SEEK is not supported
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ SEEK
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
--
2.25.1
----Next_Part(Tue_Sep_12_15_18_43_2023_359)--
Content-Type: Text/X-Patch; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="v6-0007-Allow-to-print-raw-parse-tree.patch"
^ permalink raw reply [nested|flat] 8+ messages in thread
* s/pg_attribute_always_inline/pg_always_inline/?
@ 2026-04-08 21:09 Andres Freund <[email protected]>
2026-04-08 21:30 ` Re: s/pg_attribute_always_inline/pg_always_inline/? Peter Geoghegan <[email protected]>
2026-04-09 00:00 ` Re: s/pg_attribute_always_inline/pg_always_inline/? Peter Eisentraut <[email protected]>
0 siblings, 2 replies; 8+ messages in thread
From: Andres Freund @ 2026-04-08 21:09 UTC (permalink / raw)
To: pgsql-hackers
Hi,
When reviewing the index prefetching patch I got a bit dismayed at how long
pg_attribute_always_inline is due to the way it triggers pgindent to format
stuff.
I propose that we remove the _attribute_ part of the name.
Given that it is implemented for compilers that don't use the
__attribute__((always_inline)) spelling, so the shorter name seems better
anyway.
Thoughts?
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: s/pg_attribute_always_inline/pg_always_inline/?
2026-04-08 21:09 s/pg_attribute_always_inline/pg_always_inline/? Andres Freund <[email protected]>
@ 2026-04-08 21:30 ` Peter Geoghegan <[email protected]>
1 sibling, 0 replies; 8+ messages in thread
From: Peter Geoghegan @ 2026-04-08 21:30 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: pgsql-hackers
On Wed, Apr 8, 2026 at 5:09 PM Andres Freund <[email protected]> wrote:
> When reviewing the index prefetching patch I got a bit dismayed at how long
> pg_attribute_always_inline is due to the way it triggers pgindent to format
> stuff.
I had to significantly reorder function prototypes to avoid overly
long prototypes that had pg_attribute_always_inline. While perhaps not
strictly necessary, we shouldn't have to work around the fact that
pg_attribute_always_inline is an absurdly verbose symbol name.
> I propose that we remove the _attribute_ part of the name.
+1
--
Peter Geoghegan
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: s/pg_attribute_always_inline/pg_always_inline/?
2026-04-08 21:09 s/pg_attribute_always_inline/pg_always_inline/? Andres Freund <[email protected]>
@ 2026-04-09 00:00 ` Peter Eisentraut <[email protected]>
2026-04-09 14:40 ` Re: s/pg_attribute_always_inline/pg_always_inline/? Andres Freund <[email protected]>
1 sibling, 1 reply; 8+ messages in thread
From: Peter Eisentraut @ 2026-04-09 00:00 UTC (permalink / raw)
To: Andres Freund <[email protected]>; pgsql-hackers
On 08.04.26 23:09, Andres Freund wrote:
> Hi,
>
> When reviewing the index prefetching patch I got a bit dismayed at how long
> pg_attribute_always_inline is due to the way it triggers pgindent to format
> stuff.
>
> I propose that we remove the _attribute_ part of the name.
>
> Given that it is implemented for compilers that don't use the
> __attribute__((always_inline)) spelling, so the shorter name seems better
> anyway.
Yes, and the existing name is also kind of wrong even on GCC because the
macro does not expand to only an attribute but also includes the "inline".
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: s/pg_attribute_always_inline/pg_always_inline/?
2026-04-08 21:09 s/pg_attribute_always_inline/pg_always_inline/? Andres Freund <[email protected]>
2026-04-09 00:00 ` Re: s/pg_attribute_always_inline/pg_always_inline/? Peter Eisentraut <[email protected]>
@ 2026-04-09 14:40 ` Andres Freund <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: Andres Freund @ 2026-04-09 14:40 UTC (permalink / raw)
To: Peter Eisentraut <[email protected]>; +Cc: pgsql-hackers
Hi,
On 2026-04-09 02:00:37 +0200, Peter Eisentraut wrote:
> On 08.04.26 23:09, Andres Freund wrote:
> > Hi,
> >
> > When reviewing the index prefetching patch I got a bit dismayed at how long
> > pg_attribute_always_inline is due to the way it triggers pgindent to format
> > stuff.
> >
> > I propose that we remove the _attribute_ part of the name.
> >
> > Given that it is implemented for compilers that don't use the
> > __attribute__((always_inline)) spelling, so the shorter name seems better
> > anyway.
>
> Yes, and the existing name is also kind of wrong even on GCC because the
> macro does not expand to only an attribute but also includes the "inline".
It didn't start out that way, that only came in a bit later, in
434e6e148441...
Created a CF entry, to reduce the chances of me forgetting about committing
this early in the 20 cycle.
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2026-04-09 14:40 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-02-10 00:20 [PATCH v4] Avoid repetitive log of PREPARE during EXECUTE of prepared statements Justin Pryzby <[email protected]>
2019-02-10 00:20 [PATCH v2] Avoid repetitive log of PREPARE during EXECUTE of prepared statements Justin Pryzby <[email protected]>
2019-02-10 00:20 [PATCH v3] Avoid repetitive log of PREPARE during EXECUTE of prepared statements Justin Pryzby <[email protected]>
2023-09-12 05:22 [PATCH v6 6/7] Row pattern recognition patch (tests). Tatsuo Ishii <[email protected]>
2026-04-08 21:09 s/pg_attribute_always_inline/pg_always_inline/? Andres Freund <[email protected]>
2026-04-08 21:30 ` Re: s/pg_attribute_always_inline/pg_always_inline/? Peter Geoghegan <[email protected]>
2026-04-09 00:00 ` Re: s/pg_attribute_always_inline/pg_always_inline/? Peter Eisentraut <[email protected]>
2026-04-09 14:40 ` Re: s/pg_attribute_always_inline/pg_always_inline/? Andres Freund <[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