Date: Mon, 22 Jan 2024 14:51:49 +0900
From: NINGWEI CHEN <chen@sraoss.co.jp>
To: Tatsuo Ishii <ishii@sraoss.co.jp>
Cc: pgsql-hackers@postgresql.org
Subject: Re: Row pattern recognition
Message-Id: <20240122145149.3e8bde1f384b303533c990a3@sraoss.co.jp>
In-Reply-To: <20231209.072258.1171973265340692754.t-ishii@sranhm.sra.co.jp>
References: 
 <CAGu=u8gr-DREbxJJc+UKwYz2t7nmecr+FeYSaqMk8vqFqQmbpg@mail.gmail.com>
	<20231108.163705.1531753254147888242.t-ishii@sranhm.sra.co.jp>
	<20231208.101613.1940702231121175044.t-ishii@sranhm.sra.co.jp>
	<20231209.072258.1171973265340692754.t-ishii@sranhm.sra.co.jp>
Content-Type: multipart/mixed;
 boundary="Multipart=_Mon__22_Jan_2024_14_51_49_+0900_KM_ehc.BgVuU9.tF"
MIME-Version: 1.0
X-MS-Exchange-AntiSpam-MessageData-ChunkCount: 1
X-MS-Exchange-AntiSpam-MessageData-0: 
	=?us-ascii?Q?t96cND+V2eoURcM9KHpZ+54XPoa8QKcCM1fj6lec5FImfhu7qxoueyYxVRf7?=
 =?us-ascii?Q?NCyEsHUrWIvZn2NhCjF4P8mozUHke5daCuNGyvkEcRED1zAipSLuTGtdmurE?=
 =?us-ascii?Q?iD59dKoazMRZoe6m9pCSBzGmU1GFyBo2cZlrAAcIRJ5niWed4Rn/irIV9DOP?=
 =?us-ascii?Q?prZ5bM9a3R7Cj9DQVs6wr0iGK5VHYEEM/9X8oxym3DqEzRvVLZLtKt4IEf/A?=
 =?us-ascii?Q?TkicCv0P6st1rv59o51sbn7N7ybiy8nILLzkDGyBty4/jNUTGUwEkOrscYPA?=
 =?us-ascii?Q?NREysvFI/w0hColYoDuQo/gyJxmlto/iFRLdBmHKHjH2d1s6x7CBaYmAVBAD?=
 =?us-ascii?Q?4ARK/4aE3dS+RzQ6c3SsT//nT4YA2x/sS3lejMWUKo26RhV5+i7KSZI0XPZT?=
 =?us-ascii?Q?acrXi5DMrNcWNgdvkjEVKjOm2M687LhN2pYdNNaYLMBBH/ZExFwL2Xu/fkLD?=
 =?us-ascii?Q?OLndmTs/j9SRuntSHYHmkcYI0DYUZtzLf7m3mok6ug5GhDr4zJKGZB13MR4B?=
 =?us-ascii?Q?4z8rkn6BFg2KvAu48YjCdRz3y5dz7WjGKlEpfjSBPwcSEZY2UOrRO/JBBVpK?=
 =?us-ascii?Q?WA3hgANF4jUPPSDpHL2uUcJL6TqK6LYm/1DprfrIBYKqDUR8H8b+OUpcPTOi?=
 =?us-ascii?Q?u25/Bd/V5ybKDQSzSmOJxwO01YRibBKpnrWUXsKrkLVNUcVZofPf5dIQ28uy?=
 =?us-ascii?Q?VBWXy+MmzWY5grHyj+NvMQDhgdHqAkwXwHuD37+bcK+mNRjiqkNGakHajGxc?=
 =?us-ascii?Q?KkJHIXX97c1m5U5i2fPTEBvYrgU+a1f1J8KYQqYRVcGsNr0HVRLfc+Uz14+w?=
 =?us-ascii?Q?uaT0N+Vf8r/VABqeyHj4SGUOc8ucu2fEugumRQOUZ1cSFuTLmtnvrX8oVRvu?=
 =?us-ascii?Q?xJ64jSTpOAB/qp6IN57K0nUOSaZwmoueY6E7aMTXcTf0QPv4KIy3fKRfeTPU?=
 =?us-ascii?Q?P82va5F1iMmrVdyJtAqGwWVipLPzp1HlwxlTMpVazBGy1m/K+A3Xk/MjeqHE?=
 =?us-ascii?Q?TmKky5BDsqtSvLFxXPMPmmTE9KGebLiBsvn+5mDkpQgXER1tcpnUFM8rpYtp?=
 =?us-ascii?Q?bWU4bnv/TTUkuvTYOc8hgA2voE4py1/GiYC+T54/bDkMkMSnwFwydOCoZq8e?=
 =?us-ascii?Q?Ylz+NK0W25oewt7J/IXlC7wsse7zCuwK6fjuZ0lYEm/EVnry6JftYvbqS76p?=
 =?us-ascii?Q?4q+8Lyvl8rLPUvg9Tk1it5SyuyV+IF4wvB+V8h+mH6qr8uZyJUPSs5D/yy22?=
 =?us-ascii?Q?EAyFq/Ww3kVqHAIuryhHPbhgnNlX3BtPBWf2AeYhfuFbzJfRAPdRVHRq/HlA?=
 =?us-ascii?Q?otQZPVVNxDtQN7FrlkqdGByeWasbO2a2ZaQ6RCLIYX/Gtn2B5jX901mNLB8f?=
 =?us-ascii?Q?QNaHhIuMy7qVXrq+MZeekUHJWxR6OmAwRApFBxY0P9ezVOUjsi8ZO1Mxnhek?=
 =?us-ascii?Q?RNNZ0nxp9alrx6e79kJuN45ieqmZXT7cXABOF9W0xlodZFTn57B2Sc0bF58O?=
 =?us-ascii?Q?kOLlBFV3B81PlHexvcajvxfoc7ccCAzriDA4+9w53ugKJFUm5Kg6esZZ3aEX?=
 =?us-ascii?Q?ndxX/vs6FxvR7jNOVg0EGmJQiS7uzrI3KLUHAMl0?=
X-OriginatorOrg: sraoss.co.jp
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 6d11999d-f7b4-4082-c728-08dc1b0e3c03
X-MS-Exchange-CrossTenant-AuthSource: OS3P286MB1878.JPNP286.PROD.OUTLOOK.COM
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 22 Jan 2024 05:51:53.1386
 (UTC)
X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted
X-MS-Exchange-CrossTenant-Id: 75db6c32-352e-4063-ae79-cafb8623f6db
X-MS-Exchange-CrossTenant-MailboxType: HOSTED
X-MS-Exchange-CrossTenant-UserPrincipalName: 
 Vf/M6Rvqz8Q/czFt3K1aa8LADPAFrzVPtfqIJMG599OD+0q8hz15Vzi5Eo75s1gr56QT0d3D5JClVCnyolxhZw==
X-MS-Exchange-Transport-CrossTenantHeadersStamped: TYWP286MB3237
List-Id: <pgsql-hackers.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-hackers@lists.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-hackers>
Archived-At: 
 <https://www.postgresql.org/message-id/20240122145149.3e8bde1f384b303533c990a3%40sraoss.co.jp>
Precedence: bulk

--Multipart=_Mon__22_Jan_2024_14_51_49_+0900_KM_ehc.BgVuU9.tF
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

On Sat, 09 Dec 2023 07:22:58 +0900 (JST)
Tatsuo Ishii <ishii@sraoss.co.jp> wrote:

> > On 04.12.23 12:40, Tatsuo Ishii wrote:
> >> diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
> >> index d631ac89a9..5a77fca17f 100644
> >> --- a/src/backend/parser/gram.y
> >> +++ b/src/backend/parser/gram.y
> >> @@ -251,6 +251,8 @@ static Node *makeRecursiveViewSelect(char
> >> *relname, List *aliases, Node *query);
> >>   	DefElem	   *defelt;
> >>   	SortBy	   *sortby;
> >>   	WindowDef  *windef;
> >> +	RPCommonSyntax	*rpcom;
> >> +	RPSubsetItem	*rpsubset;
> >>   	JoinExpr   *jexpr;
> >>   	IndexElem  *ielem;
> >>   	StatsElem  *selem;
> >> @@ -278,6 +280,7 @@ static Node *makeRecursiveViewSelect(char
> >> *relname, List *aliases, Node *query);
> >>   	MergeWhenClause *mergewhen;
> >>   	struct KeyActions *keyactions;
> >>   	struct KeyAction *keyaction;
> >> +	RPSkipTo	skipto;
> >>   }
> >>     %type <node>	stmt toplevel_stmt schema_stmt routine_body_stmt
> > 
> > It is usually not the style to add an entry for every node type to the
> > %union.  Otherwise, we'd have hundreds of entries in there.
> 
> Ok, I have removed the node types and used existing node types.  Also
> I have moved RPR related %types to same place to make it easier to know
> what are added by RPR.
> 
> >> @@ -866,6 +878,7 @@ static Node *makeRecursiveViewSelect(char
> >> *relname, List *aliases, Node *query);
> >>   %nonassoc UNBOUNDED /* ideally would have same precedence as IDENT */
> >>   %nonassoc IDENT PARTITION RANGE ROWS GROUPS PRECEDING FOLLOWING CUBE
> >>   %ROLLUP
> >>   			SET KEYS OBJECT_P SCALAR VALUE_P WITH WITHOUT
> >> +%nonassoc	MEASURES AFTER INITIAL SEEK PATTERN_P
> >>   %left Op OPERATOR /* multi-character ops and user-defined operators */
> >>   %left		'+' '-'
> >>   %left		'*' '/' '%'
> > 
> > It was recently discussed that these %nonassoc should ideally all have
> > the same precedence.  Did you consider that here?
> 
> No, I didn't realize it. Thanks for pointing it out. I have removed
> %nonassoc so that MEASURES etc. have the same precedence as IDENT etc.
> 
> Attached is the new diff of gram.y against master branch.

Thank you very much for providing the patch for the RPR implementation.

After applying the v12-patches, I noticed an issue that
the rpr related parts in window clauses were not displayed in the
view definitions (the definition column of pg_views).

To address this, I have taken the liberty of adding an additional patch
that modifies the relevant rewriter source code.
I have attached the rewriter patch for your review and would greatly appreciate your feedback.

Thank you for your time and consideration.

-- 
SRA OSS LLC
Ningwei Chen <chen@sraoss.co.jp>
TEL: 03-5979-2701 FAX: 03-5979-2702

--Multipart=_Mon__22_Jan_2024_14_51_49_+0900_KM_ehc.BgVuU9.tF
Content-Type: text/plain;
 name="rewriter-patch.txt"
Content-Disposition: attachment;
 filename="rewriter-patch.txt"
Content-Transfer-Encoding: 7bit

diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 0b2a164057..baded88201 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -428,6 +428,10 @@ static void get_rule_groupingset(GroupingSet *gset, List *targetlist,
 								 bool omit_parens, deparse_context *context);
 static void get_rule_orderby(List *orderList, List *targetList,
 							 bool force_colno, deparse_context *context);
+static void get_rule_pattern(List *patternVariable, List *patternRegexp,
+							 bool force_colno, deparse_context *context);
+static void get_rule_define(List *defineClause, List *patternVariables,
+							bool force_colno, deparse_context *context);
 static void get_rule_windowclause(Query *query, deparse_context *context);
 static void get_rule_windowspec(WindowClause *wc, List *targetList,
 								deparse_context *context);
@@ -6459,6 +6463,64 @@ get_rule_orderby(List *orderList, List *targetList,
 	}
 }
 
+/*
+ * Display a PATTERN clause.
+ */
+static void
+get_rule_pattern(List *patternVariable, List *patternRegexp,
+				 bool force_colno, deparse_context *context)
+{
+	StringInfo  buf = context->buf;
+	const char *sep;
+	ListCell   *lc_var, *lc_reg = list_head(patternRegexp);
+
+	sep = "";
+	appendStringInfoChar(buf, '(');
+	foreach(lc_var, patternVariable)
+	{
+		char *variable = strVal((String *) lfirst(lc_var));
+		char *regexp = NULL;
+
+		if (lc_reg != NULL)
+		{
+			regexp = strVal((String *) lfirst(lc_reg));
+			lc_reg = lnext(patternRegexp, lc_reg);
+		}
+
+		appendStringInfo(buf, "%s%s", sep, variable);
+		if (regexp != NULL)
+			appendStringInfoString(buf, regexp);
+
+		sep = " ";
+	}
+	appendStringInfoChar(buf, ')');
+}
+
+/*
+ * Display a DEFINE clause.
+ */
+static void
+get_rule_define(List *defineClause, List *patternVariables,
+				bool force_colno, deparse_context *context)
+{
+	StringInfo  buf = context->buf;
+	const char *sep;
+	ListCell   *lc_var, *lc_def;
+
+	sep = "  ";
+	Assert(list_length(patternVariables) == list_length(defineClause));
+
+	forboth(lc_var, patternVariables, lc_def, defineClause)
+	{
+		char *varName = strVal(lfirst(lc_var));
+		TargetEntry *te = (TargetEntry *) lfirst(lc_def);
+
+		appendStringInfo(buf, "%s%s AS ", sep, varName);
+		get_rule_expr((Node *) te->expr, context, false);
+		sep = ",\n  ";
+	}
+}
+
 /*
  * Display a WINDOW clause.
  *
@@ -6596,6 +6658,34 @@ get_rule_windowspec(WindowClause *wc, List *targetList,
 			appendStringInfoString(buf, "EXCLUDE GROUP ");
 		else if (wc->frameOptions & FRAMEOPTION_EXCLUDE_TIES)
 			appendStringInfoString(buf, "EXCLUDE TIES ");
+		/* RPR */
+		if (wc->rpSkipTo == ST_NEXT_ROW)
+			appendStringInfoString(buf, "\n  AFTER MATCH SKIP TO NEXT ROW ");
+		else if (wc->rpSkipTo == ST_PAST_LAST_ROW)
+			appendStringInfoString(buf, "\n  AFTER MATCH SKIP PAST LAST ROW ");
+		else if (wc->rpSkipTo == ST_FIRST_VARIABLE)
+			appendStringInfo(buf, "\n  AFTER MATCH SKIP TO FIRST %s ", wc->rpSkipVariable);
+		else if (wc->rpSkipTo == ST_LAST_VARIABLE)
+			appendStringInfo(buf, "\n  AFTER MATCH SKIP TO LAST %s ", wc->rpSkipVariable);
+		else if (wc->rpSkipTo == ST_VARIABLE)
+			appendStringInfo(buf, "\n  AFTER MATCH SKIP TO %s ", wc->rpSkipVariable);
+
+		if (wc->initial)
+			appendStringInfoString(buf, "\n  INITIAL");
+
+		if (wc->patternVariable)
+		{
+			appendStringInfoString(buf, "\n  PATTERN ");
+			get_rule_pattern(wc->patternVariable, wc->patternRegexp, false, context);
+		}
+
+		if (wc->defineClause)
+		{
+			appendStringInfoString(buf, "\n  DEFINE\n");
+			get_rule_define(wc->defineClause, wc->patternVariable, false, context);
+			appendStringInfoChar(buf, ' ');
+		}
+
 		/* we will now have a trailing space; remove it */
 		buf->len--;
 	}
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index 2b4c36a15f..104c0105c5 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -3851,6 +3851,9 @@ transformRPR(ParseState *pstate, WindowClause *wc, WindowDef *windef, List **tar
 	/* Transform AFTER MACH SKIP TO clause */
 	wc->rpSkipTo = windef->rpCommonSyntax->rpSkipTo;
 
+	/* Transform AFTER MACH SKIP TO variable */
+	wc->rpSkipVariable = windef->rpCommonSyntax->rpSkipVariable;
+
 	/* Transform SEEK or INITIAL clause */
 	wc->initial = windef->rpCommonSyntax->initial;
 
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 5a9b162463..64e9df0a48 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1554,6 +1554,7 @@ typedef struct WindowClause
 	bool		copiedOrder pg_node_attr(query_jumble_ignore);
 	/* Row Pattern AFTER MACH SKIP clause */
 	RPSkipTo	rpSkipTo;		/* Row Pattern Skip To type */
+	char        *rpSkipVariable;/* Row Pattern Skip To variable */
 	bool		initial;		/* true if <row pattern initial or seek> is initial */
 	/* Row Pattern DEFINE clause (list of TargetEntry) */
 	List		*defineClause;

--Multipart=_Mon__22_Jan_2024_14_51_49_+0900_KM_ehc.BgVuU9.tF--