public inbox for [email protected]help / color / mirror / Atom feed
[PATCH 1/8] Make syntax changes for custom compression methods 4+ messages / 3 participants [nested] [flat]
* [PATCH 1/8] Make syntax changes for custom compression methods @ 2018-06-18 09:25 Ildus Kurbangaliev <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: Ildus Kurbangaliev @ 2018-06-18 09:25 UTC (permalink / raw) Signed-off-by: Ildus Kurbangaliev <[email protected]> --- src/backend/parser/gram.y | 76 ++++++++++++++++++++++++++++++---- src/include/catalog/pg_am.h | 1 + src/include/nodes/nodes.h | 1 + src/include/nodes/parsenodes.h | 19 ++++++++- src/include/parser/kwlist.h | 1 + 5 files changed, 89 insertions(+), 9 deletions(-) diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 0279013120..5f4194f6e3 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -311,6 +311,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); opt_grant_grant_option opt_grant_admin_option opt_nowait opt_if_exists opt_with_data %type <ival> opt_nowait_or_skip +%type <ival> AccessMethodType %type <list> OptRoleList AlterOptRoleList %type <defelt> CreateOptRoleElem AlterOptRoleElem @@ -399,6 +400,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); transform_element_list transform_type_list TriggerTransitions TriggerReferencing publication_name_list + optCompressionParameters vacuum_relation_list opt_vacuum_relation_list %type <list> group_by_list @@ -584,6 +586,10 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type <list> hash_partbound %type <defelt> hash_partbound_elem +%type <node> optColumnCompression alterColumnCompression +%type <str> compressionClause +%type <list> optCompressionPreserve + /* * Non-keyword token types. These are hard-wired into the "flex" lexer. * They must be listed first so that their numeric codes do not depend on @@ -616,9 +622,9 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); CACHE CALL CALLED CASCADE CASCADED CASE CAST CATALOG_P CHAIN CHAR_P CHARACTER CHARACTERISTICS CHECK CHECKPOINT CLASS CLOSE CLUSTER COALESCE COLLATE COLLATION COLUMN COLUMNS COMMENT COMMENTS COMMIT - COMMITTED CONCURRENTLY CONFIGURATION CONFLICT CONNECTION CONSTRAINT - CONSTRAINTS CONTENT_P CONTINUE_P CONVERSION_P COPY COST CREATE - CROSS CSV CUBE CURRENT_P + COMMITTED COMPRESSION CONCURRENTLY CONFIGURATION CONFLICT + CONNECTION CONSTRAINT CONSTRAINTS CONTENT_P CONTINUE_P CONVERSION_P COPY + COST CREATE CROSS CSV CUBE CURRENT_P CURRENT_CATALOG CURRENT_DATE CURRENT_ROLE CURRENT_SCHEMA CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE @@ -2213,6 +2219,15 @@ alter_table_cmd: n->missing_ok = true; $$ = (Node *)n; } + /* ALTER TABLE <name> ALTER [COLUMN] <colname> SET (COMPRESSION <cm> [WITH (<options>)]) */ + | ALTER opt_column ColId SET alterColumnCompression + { + AlterTableCmd *n = makeNode(AlterTableCmd); + n->subtype = AT_SetCompression; + n->name = $3; + n->def = $5; + $$ = (Node *)n; + } /* ALTER TABLE <name> DROP [COLUMN] IF EXISTS <colname> [RESTRICT|CASCADE] */ | DROP opt_column IF_P EXISTS ColId opt_drop_behavior { @@ -3313,11 +3328,12 @@ TypedTableElement: | TableConstraint { $$ = $1; } ; -columnDef: ColId Typename create_generic_options ColQualList +columnDef: ColId Typename optColumnCompression create_generic_options ColQualList { ColumnDef *n = makeNode(ColumnDef); n->colname = $1; n->typeName = $2; + n->compression = (ColumnCompression *) $3; n->inhcount = 0; n->is_local = true; n->is_not_null = false; @@ -3326,8 +3342,8 @@ columnDef: ColId Typename create_generic_options ColQualList n->raw_default = NULL; n->cooked_default = NULL; n->collOid = InvalidOid; - n->fdwoptions = $3; - SplitColQualList($4, &n->constraints, &n->collClause, + n->fdwoptions = $4; + SplitColQualList($5, &n->constraints, &n->collClause, yyscanner); n->location = @1; $$ = (Node *)n; @@ -3372,6 +3388,43 @@ columnOptions: ColId ColQualList } ; +optCompressionPreserve: + PRESERVE '(' name_list ')' { $$ = $3; } + | /*EMPTY*/ { $$ = NULL; } + ; + +optCompressionParameters: + WITH '(' generic_option_list ')' { $$ = $3; } + | /*EMPTY*/ { $$ = NULL; } + ; + +compressionClause: + COMPRESSION name { $$ = pstrdup($2); } + ; + +optColumnCompression: + compressionClause optCompressionParameters + { + ColumnCompression *n = makeNode(ColumnCompression); + n->amname = $1; + n->options = (List *) $2; + n->preserve = NIL; + $$ = (Node *) n; + } + | /*EMPTY*/ { $$ = NULL; } + ; + +alterColumnCompression: + compressionClause optCompressionParameters optCompressionPreserve + { + ColumnCompression *n = makeNode(ColumnCompression); + n->amname = $1; + n->options = (List *) $2; + n->preserve = (List *) $3; + $$ = (Node *) n; + } + ; + ColQualList: ColQualList ColConstraint { $$ = lappend($1, $2); } | /*EMPTY*/ { $$ = NIL; } @@ -3577,6 +3630,7 @@ TableLikeOption: | INDEXES { $$ = CREATE_TABLE_LIKE_INDEXES; } | STATISTICS { $$ = CREATE_TABLE_LIKE_STATISTICS; } | STORAGE { $$ = CREATE_TABLE_LIKE_STORAGE; } + | COMPRESSION { $$ = CREATE_TABLE_LIKE_COMPRESSION; } | ALL { $$ = CREATE_TABLE_LIKE_ALL; } ; @@ -5253,12 +5307,17 @@ row_security_cmd: * *****************************************************************************/ -CreateAmStmt: CREATE ACCESS METHOD name TYPE_P INDEX HANDLER handler_name +AccessMethodType: + INDEX { $$ = AMTYPE_INDEX; } + | COMPRESSION { $$ = AMTYPE_COMPRESSION; } + ; + +CreateAmStmt: CREATE ACCESS METHOD name TYPE_P AccessMethodType HANDLER handler_name { CreateAmStmt *n = makeNode(CreateAmStmt); n->amname = $4; n->handler_name = $8; - n->amtype = AMTYPE_INDEX; + n->amtype = $6; $$ = (Node *) n; } ; @@ -14986,6 +15045,7 @@ unreserved_keyword: | COMMENTS | COMMIT | COMMITTED + | COMPRESSION | CONFIGURATION | CONFLICT | CONNECTION diff --git a/src/include/catalog/pg_am.h b/src/include/catalog/pg_am.h index eb3495c36a..b341e9e88e 100644 --- a/src/include/catalog/pg_am.h +++ b/src/include/catalog/pg_am.h @@ -53,6 +53,7 @@ typedef FormData_pg_am *Form_pg_am; * Allowed values for amtype */ #define AMTYPE_INDEX 'i' /* index access method */ +#define AMTYPE_COMPRESSION 'c' /* compression access method */ #endif /* EXPOSE_TO_CLIENT_CODE */ diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h index f9389257c6..9bc8c444ca 100644 --- a/src/include/nodes/nodes.h +++ b/src/include/nodes/nodes.h @@ -475,6 +475,7 @@ typedef enum NodeTag T_PartitionBoundSpec, T_PartitionRangeDatum, T_PartitionCmd, + T_ColumnCompression, T_VacuumRelation, /* diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index a7e859dc90..fe54b80b72 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -622,6 +622,20 @@ typedef struct RangeTableSample int location; /* method name location, or -1 if unknown */ } RangeTableSample; +/* + * ColumnCompression - compression parameters for some attribute + * + * This represents compression information defined using clause: + * .. COMPRESSION <compression method> WITH (<params>) PRESERVE <compression AMs> + */ +typedef struct ColumnCompression +{ + NodeTag type; + char *amname; + List *options; + List *preserve; +} ColumnCompression; + /* * ColumnDef - column definition (used in various creates) * @@ -645,6 +659,7 @@ typedef struct ColumnDef NodeTag type; char *colname; /* name of column */ TypeName *typeName; /* type of column */ + ColumnCompression *compression; int inhcount; /* number of times column is inherited */ bool is_local; /* column has local (non-inherited) def'n */ bool is_not_null; /* NOT NULL constraint specified? */ @@ -681,6 +696,7 @@ typedef enum TableLikeOption CREATE_TABLE_LIKE_INDEXES = 1 << 4, CREATE_TABLE_LIKE_STATISTICS = 1 << 5, CREATE_TABLE_LIKE_STORAGE = 1 << 6, + CREATE_TABLE_LIKE_COMPRESSION = 1 << 7, CREATE_TABLE_LIKE_ALL = PG_INT32_MAX } TableLikeOption; @@ -1811,7 +1827,8 @@ typedef enum AlterTableType AT_DetachPartition, /* DETACH PARTITION */ AT_AddIdentity, /* ADD IDENTITY */ AT_SetIdentity, /* SET identity column options */ - AT_DropIdentity /* DROP IDENTITY */ + AT_DropIdentity, /* DROP IDENTITY */ + AT_SetCompression /* SET COMPRESSION */ } AlterTableType; typedef struct ReplicaIdentityStmt diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index f05444008c..a722891c99 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -87,6 +87,7 @@ PG_KEYWORD("comment", COMMENT, UNRESERVED_KEYWORD) PG_KEYWORD("comments", COMMENTS, UNRESERVED_KEYWORD) PG_KEYWORD("commit", COMMIT, UNRESERVED_KEYWORD) PG_KEYWORD("committed", COMMITTED, UNRESERVED_KEYWORD) +PG_KEYWORD("compression", COMPRESSION, UNRESERVED_KEYWORD) PG_KEYWORD("concurrently", CONCURRENTLY, TYPE_FUNC_NAME_KEYWORD) PG_KEYWORD("configuration", CONFIGURATION, UNRESERVED_KEYWORD) PG_KEYWORD("conflict", CONFLICT, UNRESERVED_KEYWORD) -- 2.20.1 --MP_/IoARF=uydinlHTRfc3S.uiA Content-Type: text/x-patch Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename=0002-Add-compression-catalog-tables-and-the-basic-inf-v21.patch ^ permalink raw reply [nested|flat] 4+ messages in thread
* [PATCH 1/8] Make syntax changes for custom compression methods @ 2018-06-18 09:25 Ildus Kurbangaliev <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: Ildus Kurbangaliev @ 2018-06-18 09:25 UTC (permalink / raw) Signed-off-by: Ildus Kurbangaliev <[email protected]> --- src/backend/parser/gram.y | 67 +++++++++++++++++++++++++++++++--- src/include/catalog/pg_am.h | 1 + src/include/nodes/nodes.h | 1 + src/include/nodes/parsenodes.h | 19 +++++++++- src/include/parser/kwlist.h | 1 + 5 files changed, 82 insertions(+), 7 deletions(-) diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index e23e68fdb3..cd2f791ea9 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -402,6 +402,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); transform_element_list transform_type_list TriggerTransitions TriggerReferencing publication_name_list + optCompressionParameters vacuum_relation_list opt_vacuum_relation_list %type <list> group_by_list @@ -587,6 +588,10 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type <list> hash_partbound %type <defelt> hash_partbound_elem +%type <node> optColumnCompression alterColumnCompression +%type <str> compressionClause +%type <list> optCompressionPreserve + /* * Non-keyword token types. These are hard-wired into the "flex" lexer. * They must be listed first so that their numeric codes do not depend on @@ -619,9 +624,9 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); CACHE CALL CALLED CASCADE CASCADED CASE CAST CATALOG_P CHAIN CHAR_P CHARACTER CHARACTERISTICS CHECK CHECKPOINT CLASS CLOSE CLUSTER COALESCE COLLATE COLLATION COLUMN COLUMNS COMMENT COMMENTS COMMIT - COMMITTED CONCURRENTLY CONFIGURATION CONFLICT CONNECTION CONSTRAINT - CONSTRAINTS CONTENT_P CONTINUE_P CONVERSION_P COPY COST CREATE - CROSS CSV CUBE CURRENT_P + COMMITTED COMPRESSION CONCURRENTLY CONFIGURATION CONFLICT + CONNECTION CONSTRAINT CONSTRAINTS CONTENT_P CONTINUE_P CONVERSION_P COPY + COST CREATE CROSS CSV CUBE CURRENT_P CURRENT_CATALOG CURRENT_DATE CURRENT_ROLE CURRENT_SCHEMA CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE @@ -2216,6 +2221,15 @@ alter_table_cmd: n->missing_ok = true; $$ = (Node *)n; } + /* ALTER TABLE <name> ALTER [COLUMN] <colname> SET (COMPRESSION <cm> [WITH (<options>)]) */ + | ALTER opt_column ColId SET alterColumnCompression + { + AlterTableCmd *n = makeNode(AlterTableCmd); + n->subtype = AT_SetCompression; + n->name = $3; + n->def = $5; + $$ = (Node *)n; + } /* ALTER TABLE <name> DROP [COLUMN] IF EXISTS <colname> [RESTRICT|CASCADE] */ | DROP opt_column IF_P EXISTS ColId opt_drop_behavior { @@ -3323,11 +3337,12 @@ TypedTableElement: | TableConstraint { $$ = $1; } ; -columnDef: ColId Typename create_generic_options ColQualList +columnDef: ColId Typename optColumnCompression create_generic_options ColQualList { ColumnDef *n = makeNode(ColumnDef); n->colname = $1; n->typeName = $2; + n->compression = (ColumnCompression *) $3; n->inhcount = 0; n->is_local = true; n->is_not_null = false; @@ -3336,8 +3351,8 @@ columnDef: ColId Typename create_generic_options ColQualList n->raw_default = NULL; n->cooked_default = NULL; n->collOid = InvalidOid; - n->fdwoptions = $3; - SplitColQualList($4, &n->constraints, &n->collClause, + n->fdwoptions = $4; + SplitColQualList($5, &n->constraints, &n->collClause, yyscanner); n->location = @1; $$ = (Node *)n; @@ -3382,6 +3397,43 @@ columnOptions: ColId ColQualList } ; +optCompressionPreserve: + PRESERVE '(' name_list ')' { $$ = $3; } + | /*EMPTY*/ { $$ = NULL; } + ; + +optCompressionParameters: + WITH '(' generic_option_list ')' { $$ = $3; } + | /*EMPTY*/ { $$ = NULL; } + ; + +compressionClause: + COMPRESSION name { $$ = pstrdup($2); } + ; + +optColumnCompression: + compressionClause optCompressionParameters + { + ColumnCompression *n = makeNode(ColumnCompression); + n->amname = $1; + n->options = (List *) $2; + n->preserve = NIL; + $$ = (Node *) n; + } + | /*EMPTY*/ { $$ = NULL; } + ; + +alterColumnCompression: + compressionClause optCompressionParameters optCompressionPreserve + { + ColumnCompression *n = makeNode(ColumnCompression); + n->amname = $1; + n->options = (List *) $2; + n->preserve = (List *) $3; + $$ = (Node *) n; + } + ; + ColQualList: ColQualList ColConstraint { $$ = lappend($1, $2); } | /*EMPTY*/ { $$ = NIL; } @@ -3587,6 +3639,7 @@ TableLikeOption: | INDEXES { $$ = CREATE_TABLE_LIKE_INDEXES; } | STATISTICS { $$ = CREATE_TABLE_LIKE_STATISTICS; } | STORAGE { $$ = CREATE_TABLE_LIKE_STORAGE; } + | COMPRESSION { $$ = CREATE_TABLE_LIKE_COMPRESSION; } | ALL { $$ = CREATE_TABLE_LIKE_ALL; } ; @@ -5285,6 +5338,7 @@ CreateAmStmt: CREATE ACCESS METHOD name TYPE_P am_type HANDLER handler_name am_type: INDEX { $$ = AMTYPE_INDEX; } | TABLE { $$ = AMTYPE_TABLE; } + | COMPRESSION { $$ = AMTYPE_COMPRESSION; } ; /***************************************************************************** @@ -15015,6 +15069,7 @@ unreserved_keyword: | COMMENTS | COMMIT | COMMITTED + | COMPRESSION | CONFIGURATION | CONFLICT | CONNECTION diff --git a/src/include/catalog/pg_am.h b/src/include/catalog/pg_am.h index 706b5e81cb..8199f5c05d 100644 --- a/src/include/catalog/pg_am.h +++ b/src/include/catalog/pg_am.h @@ -54,6 +54,7 @@ typedef FormData_pg_am *Form_pg_am; */ #define AMTYPE_INDEX 'i' /* index access method */ #define AMTYPE_TABLE 't' /* table access method */ +#define AMTYPE_COMPRESSION 'c' /* compression access method */ #endif /* EXPOSE_TO_CLIENT_CODE */ diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h index ffb4cd4bcc..2451d650ab 100644 --- a/src/include/nodes/nodes.h +++ b/src/include/nodes/nodes.h @@ -475,6 +475,7 @@ typedef enum NodeTag T_PartitionBoundSpec, T_PartitionRangeDatum, T_PartitionCmd, + T_ColumnCompression, T_VacuumRelation, /* diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index fe35783359..6800fcf7c4 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -622,6 +622,20 @@ typedef struct RangeTableSample int location; /* method name location, or -1 if unknown */ } RangeTableSample; +/* + * ColumnCompression - compression parameters for some attribute + * + * This represents compression information defined using clause: + * .. COMPRESSION <compression method> WITH (<params>) PRESERVE <compression AMs> + */ +typedef struct ColumnCompression +{ + NodeTag type; + char *amname; + List *options; + List *preserve; +} ColumnCompression; + /* * ColumnDef - column definition (used in various creates) * @@ -645,6 +659,7 @@ typedef struct ColumnDef NodeTag type; char *colname; /* name of column */ TypeName *typeName; /* type of column */ + ColumnCompression *compression; int inhcount; /* number of times column is inherited */ bool is_local; /* column has local (non-inherited) def'n */ bool is_not_null; /* NOT NULL constraint specified? */ @@ -681,6 +696,7 @@ typedef enum TableLikeOption CREATE_TABLE_LIKE_INDEXES = 1 << 4, CREATE_TABLE_LIKE_STATISTICS = 1 << 5, CREATE_TABLE_LIKE_STORAGE = 1 << 6, + CREATE_TABLE_LIKE_COMPRESSION = 1 << 7, CREATE_TABLE_LIKE_ALL = PG_INT32_MAX } TableLikeOption; @@ -1811,7 +1827,8 @@ typedef enum AlterTableType AT_DetachPartition, /* DETACH PARTITION */ AT_AddIdentity, /* ADD IDENTITY */ AT_SetIdentity, /* SET identity column options */ - AT_DropIdentity /* DROP IDENTITY */ + AT_DropIdentity, /* DROP IDENTITY */ + AT_SetCompression /* SET COMPRESSION */ } AlterTableType; typedef struct ReplicaIdentityStmt diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index f05444008c..a722891c99 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -87,6 +87,7 @@ PG_KEYWORD("comment", COMMENT, UNRESERVED_KEYWORD) PG_KEYWORD("comments", COMMENTS, UNRESERVED_KEYWORD) PG_KEYWORD("commit", COMMIT, UNRESERVED_KEYWORD) PG_KEYWORD("committed", COMMITTED, UNRESERVED_KEYWORD) +PG_KEYWORD("compression", COMPRESSION, UNRESERVED_KEYWORD) PG_KEYWORD("concurrently", CONCURRENTLY, TYPE_FUNC_NAME_KEYWORD) PG_KEYWORD("configuration", CONFIGURATION, UNRESERVED_KEYWORD) PG_KEYWORD("conflict", CONFLICT, UNRESERVED_KEYWORD) -- 2.21.0 --MP_//XvyHCr_hJMvh/tp2R3=uJa Content-Type: text/x-patch Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename=0002-Add-compression-catalog-tables-and-the-basic-inf-v22.patch ^ permalink raw reply [nested|flat] 4+ messages in thread
* [PATCH v7 5/7] Row pattern recognition patch (docs). @ 2023-09-22 04:53 Tatsuo Ishii <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: Tatsuo Ishii @ 2023-09-22 04:53 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 24ad87f910..9c99dda4ae 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 0ee0cc7e64..056768b330 100644 --- a/doc/src/sgml/ref/select.sgml +++ b/doc/src/sgml/ref/select.sgml @@ -966,8 +966,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> @@ -1074,6 +1074,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(Fri_Sep_22_14_16_40_2023_530)-- Content-Type: Text/X-Patch; charset=us-ascii Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="v7-0006-Row-pattern-recognition-patch-tests.patch" ^ permalink raw reply [nested|flat] 4+ messages in thread
* Deadlock due to locking order violation while inserting into a leaf relation @ 2024-09-08 10:20 Dilip Kumar <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: Dilip Kumar @ 2024-09-08 10:20 UTC (permalink / raw) To: PostgreSQL Hackers <[email protected]> Basically, when we are inserting into a leaf relation (or lower level relation of the partitioned relation), we acquire the lock on the leaf relation during parsing time itself whereas parent lock is acquire during generate_partition_qual(). Now concurrently if we try to drop the partition root then it will acquire the lock in reverse order, i.e. parent first and then child so this will create a deadlock. Below example reproduce this case. Setup: -------- CREATE TABLE test(a int, b int) partition by range(a); CREATE TABLE test1 partition of test for values from (1) to (100000); Test: ------ --Session1: INSERT INTO test1 VALUES (1, 4); -- let session is lock the relation test1 and make it wait before it locks test (put breakpoint in ExecInitModifyTable) --Session2: -- try to drop the top table which will try to take AccessExclusive lock on all partitions DROP TABLE test; --session3 -- see PG_LOCKS -- we can see that session1 has locked locked root table test(16384) waiting on test1(16387) as session1 is holding that lock locktype | database | relation | pid | mode | granted ---------------+----------+---------------+-------+---------------------+------------ relation | 5 | 16387 | 30368 | RowExclusiveLock | t relation | 5 | 16387 | 30410 | AccessExclusiveLock | f relation | 5 | 16384 | 30410 | AccessExclusiveLock | t (11 rows) --Session1, now as soon as you continue in gdb in session 1 it will hit the deadlock ERROR: 40P01: deadlock detected DETAIL: Process 30368 waits for AccessShareLock on relation 16384 of database 5; blocked by process 30410. Process 30410 waits for AccessExclusiveLock on relation 16387 of database 5; blocked by process 30368. HINT: See server log for query details. LOCATION: DeadLockReport, deadlock.c:1135 -- Regards, Dilip Kumar EnterpriseDB: http://www.enterprisedb.com ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2024-09-08 10:20 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2018-06-18 09:25 [PATCH 1/8] Make syntax changes for custom compression methods Ildus Kurbangaliev <[email protected]> 2018-06-18 09:25 [PATCH 1/8] Make syntax changes for custom compression methods Ildus Kurbangaliev <[email protected]> 2023-09-22 04:53 [PATCH v7 5/7] Row pattern recognition patch (docs). Tatsuo Ishii <[email protected]> 2024-09-08 10:20 Deadlock due to locking order violation while inserting into a leaf relation Dilip Kumar <[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