Hi. The first attached patch (0001-Accept-SET-xyz-pqr-to-add-pqr-to-the-current-setting.patch) adds support for commands like the following, applicable to any configuration settings that are represented as a comma-separated list of strings (i.e., GUC_LIST_INPUT):
postgres=# SET search_path += octopus; SET postgres=# SET search_path += "giant squid", kraken, narwhal; -- [1] SET postgres=# SET search_path -= public, narwhal; SET postgres=# SHOW search_path; ┌─────────────────────────────────────────┐ │ search_path │ ├─────────────────────────────────────────┤ │ "$user", octopus, "giant squid", kraken │ └─────────────────────────────────────────┘ (1 row) The implementation extends to ALTER SYSTEM SET with next to no effort, so you can also add entries to shared_preload_libraries without having to know its current value: ALTER SYSTEM SET shared_preload_libraries += auto_explain; The second patch (0002-Support-SET-syntax-for-numeric-configuration-setting.patch) adds support to modify numeric configuration settings: postgres=# SET cpu_tuple_cost += 0.02; SET postgres=# SET effective_cache_size += '2GB'; SET postgres=# SHOW effective_cache_size; ┌──────────────────────┐ │ effective_cache_size │ ├──────────────────────┤ │ 6GB │ └──────────────────────┘ (1 row) postgres=# ALTER SYSTEM SET max_worker_processes += 4; ALTER SYSTEM Being able to safely modify shared_preload_libraries (in particular) and max_worker_processes during automated extension deployments is a problem I've struggled with more than once in the past. These patches do not affect configuration file parsing in any way: its use is limited to "SET" and "ALTER xxx SET". (After I started working on this, I came to know that this idea has been proposed in different forms in the past, and objections to those proposals centred around various difficulties involved in adding this syntax to configuration files. I'm not particularly fond of that idea, and it's not what I've done here.) (Another feature that could be implemented using this framework is to ensure the current setting is at least as large as a given value: ALTER SYSTEM SET shared_buffers >= '8GB'; This would not change shared_buffers if it were already larger than 8GB. I have not implemented this, pending feedback on what's already there, but it would be simple to do.) Comments welcome. -- Abhijit 1. This feature supports a wide variety of marine creatures, with no implied judgement about their status, real or mythical; however, adding them to shared_preload_libraries is not advisable.
>From b7f262cf98be76215a9b9968c8800831874cf1d7 Mon Sep 17 00:00:00 2001 From: Abhijit Menon-Sen <a...@2ndquadrant.com> Date: Sun, 27 Sep 2020 06:52:30 +0530 Subject: Accept "SET xyz += pqr" to add pqr to the current setting of xyz A new function called by ExtractSetVariableArgs() modifies the current value of a configuration setting represented as a comma-separated list of strings (e.g., search_path) by adding or removing each of the given arguments, based on new stmt->kind values of VAR_{ADD,SUBTRACT}_VALUE. Using += x will add x if it is not already present and do nothing otherwise, and -= x will remove x if it is present and do nothing otherwise. The implementation extends to ALTER SYSTEM SET and similar commands, so this can be used by extension creation scripts to add individual entries to shared_preload_libraries. Examples: SET search_path += my_schema, other_schema; SET search_path -= public; ALTER SYSTEM SET shared_preload_libraries += auto_explain; --- doc/src/sgml/ref/set.sgml | 18 ++++ src/backend/parser/gram.y | 22 +++++ src/backend/parser/scan.l | 23 ++++- src/backend/tcop/utility.c | 2 + src/backend/utils/misc/guc.c | 168 +++++++++++++++++++++++++++++++++ src/include/nodes/parsenodes.h | 4 +- src/include/parser/scanner.h | 1 + 7 files changed, 233 insertions(+), 5 deletions(-) diff --git a/doc/src/sgml/ref/set.sgml b/doc/src/sgml/ref/set.sgml index 63f312e812..e30e9b42f0 100644 --- a/doc/src/sgml/ref/set.sgml +++ b/doc/src/sgml/ref/set.sgml @@ -22,6 +22,7 @@ PostgreSQL documentation <refsynopsisdiv> <synopsis> SET [ SESSION | LOCAL ] <replaceable class="parameter">configuration_parameter</replaceable> { TO | = } { <replaceable class="parameter">value</replaceable> | '<replaceable class="parameter">value</replaceable>' | DEFAULT } +SET [ SESSION | LOCAL ] <replaceable class="parameter">configuration_parameter</replaceable> { += | -= } { <replaceable class="parameter">value</replaceable> | '<replaceable class="parameter">value</replaceable>' } SET [ SESSION | LOCAL ] TIME ZONE { <replaceable class="parameter">timezone</replaceable> | LOCAL | DEFAULT } </synopsis> </refsynopsisdiv> @@ -40,6 +41,14 @@ SET [ SESSION | LOCAL ] TIME ZONE { <replaceable class="parameter">timezone</rep session. </para> + <para> + For configuration parameters that accept a list of values, such as + <varname>search_path</varname>, you can modify the existing setting by adding + or removing individual elements with the <literal>+=</literal> and + <literal>-=</literal> syntax. The former will add a value if it is not + already present, while the latter will remove an existing value. + </para> + <para> If <command>SET</command> (or equivalently <command>SET SESSION</command>) is issued within a transaction that is later aborted, the effects of the @@ -284,6 +293,15 @@ SET search_path TO my_schema, public; </programlisting> </para> + <para> + Modify the contents of the existing search path: +<programlisting> +SET search_path += some_schema; +SET search_path += other_schema, yetanother_schema; +SET search_path -= some_schema, my_schema; +</programlisting> + </para> + <para> Set the style of date to traditional <productname>POSTGRES</productname> with <quote>day before month</quote> diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 17653ef3a7..455b29131f 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -600,6 +600,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type <partboundspec> PartitionBoundSpec %type <list> hash_partbound %type <defelt> hash_partbound_elem +%type <ival> set_operation /* * Non-keyword token types. These are hard-wired into the "flex" lexer. @@ -617,6 +618,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %token <ival> ICONST PARAM %token TYPECAST DOT_DOT COLON_EQUALS EQUALS_GREATER %token LESS_EQUALS GREATER_EQUALS NOT_EQUALS +%token PLUS_EQUALS MINUS_EQUALS /* * If you want to make any keyword changes, update the keyword table in @@ -741,6 +743,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %right NOT %nonassoc IS ISNULL NOTNULL /* IS sets precedence for IS NULL, etc */ %nonassoc '<' '>' '=' LESS_EQUALS GREATER_EQUALS NOT_EQUALS +%nonassoc PLUS_EQUALS MINUS_EQUALS %nonassoc BETWEEN IN_P LIKE ILIKE SIMILAR NOT_LA %nonassoc ESCAPE /* ESCAPE must be just above LIKE/ILIKE/SIMILAR */ /* @@ -1450,6 +1453,17 @@ set_rest: | set_rest_more ; +set_operation: + PLUS_EQUALS + { + $$ = VAR_ADD_VALUE; + } + | MINUS_EQUALS + { + $$ = VAR_SUBTRACT_VALUE; + } + ; + generic_set: var_name TO var_list { @@ -1467,6 +1481,14 @@ generic_set: n->args = $3; $$ = n; } + | var_name set_operation var_list + { + VariableSetStmt *n = makeNode(VariableSetStmt); + n->name = $1; + n->kind = $2; + n->args = $3; + $$ = n; + } | var_name TO DEFAULT { VariableSetStmt *n = makeNode(VariableSetStmt); diff --git a/src/backend/parser/scan.l b/src/backend/parser/scan.l index 4eab2980c9..8d5efaa91c 100644 --- a/src/backend/parser/scan.l +++ b/src/backend/parser/scan.l @@ -364,6 +364,8 @@ less_equals "<=" greater_equals ">=" less_greater "<>" not_equals "!=" +plus_equals "+=" +minus_equals "-=" /* * "self" is the set of chars that should be returned as single-character @@ -853,6 +855,16 @@ other . return NOT_EQUALS; } +{plus_equals} { + SET_YYLLOC(); + return PLUS_EQUALS; + } + +{minus_equals} { + SET_YYLLOC(); + return MINUS_EQUALS; + } + {self} { SET_YYLLOC(); return yytext[0]; @@ -933,10 +945,9 @@ other . strchr(",()[].;:+-*/%^<>=", yytext[0])) return yytext[0]; /* - * Likewise, if what we have left is two chars, and - * those match the tokens ">=", "<=", "=>", "<>" or - * "!=", then we must return the appropriate token - * rather than the generic Op. + * Likewise, if what we have left is two chars, + * there may be a more specific matching token + * to return. */ if (nchars == 2) { @@ -950,6 +961,10 @@ other . return NOT_EQUALS; if (yytext[0] == '!' && yytext[1] == '=') return NOT_EQUALS; + if (yytext[0] == '+' && yytext[1] == '=') + return PLUS_EQUALS; + if (yytext[0] == '-' && yytext[1] == '=') + return MINUS_EQUALS; } } diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c index 9a35147b26..075b43e989 100644 --- a/src/backend/tcop/utility.c +++ b/src/backend/tcop/utility.c @@ -2830,6 +2830,8 @@ CreateCommandTag(Node *parsetree) case VAR_SET_CURRENT: case VAR_SET_DEFAULT: case VAR_SET_MULTI: + case VAR_ADD_VALUE: + case VAR_SUBTRACT_VALUE: tag = CMDTAG_SET; break; case VAR_RESET: diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c index 596bcb7b84..9e56f64fec 100644 --- a/src/backend/utils/misc/guc.c +++ b/src/backend/utils/misc/guc.c @@ -7986,6 +7986,167 @@ flatten_set_variable_args(const char *name, List *args) return buf.data; } +/* + * alter_set_variable_args + * Given a parsenode List as emitted by the grammar for SET, + * convert to the flat string representation used by GUC, with the + * args added to or removed from the current value of the setting, + * depending on the desired operation + * + * The result is a palloc'd string. + */ +static char * +alter_set_variable_args(const char *name, VariableSetKind operation, List *args) +{ + StringInfoData value; + struct config_generic *record; + struct config_string *conf; + char *rawstring; + List *elemlist; + ListCell *l; + char **argstrings; + bool *argswanted; + int cur = 0; + int max; + + record = find_option(name, false, ERROR); + if (record == NULL) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("unrecognized configuration parameter \"%s\"", name))); + + /* + * At present, this function can operate only on a list represented + * as a comma-separated string. + */ + if (record->vartype != PGC_STRING || (record->flags & GUC_LIST_INPUT) == 0) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("SET %s cannot perform list operations", name))); + + /* + * To determine whether to add or remove each argument, we build an + * array of strings from the List of args, and an array of booleans + * to indicate whether the argument should or should not be present + * in the final return value. + */ + max = 8; + argstrings = guc_malloc(ERROR, max * sizeof(char *)); + argswanted = guc_malloc(ERROR, max * sizeof(bool)); + foreach(l, args) + { + Node *arg = (Node *) lfirst(l); + A_Const *con; + char *val; + int i; + + if (!IsA(arg, A_Const)) + elog(ERROR, "unrecognized node type: %d", (int) nodeTag(arg)); + + con = (A_Const *) arg; + if (nodeTag(&con->val) != T_String) + elog(ERROR, "unrecognized node type: %d", (int) nodeTag(&con->val)); + + val = strVal(&con->val); + + for (i = 0; i < cur; i++) + if (pg_strcasecmp(argstrings[i], val) == 0) + break; + + if (i < cur) + continue; + + argstrings[cur] = val; + argswanted[cur] = operation == VAR_ADD_VALUE; + if (++cur == max) + { + max *= 2; + argstrings = guc_realloc(ERROR, argstrings, max * sizeof(char *)); + argswanted = guc_realloc(ERROR, argswanted, max * sizeof(bool)); + } + } + + /* + * Split the current value of the GUC setting into a list, for + * comparison with the argstrings extracted above. + */ + conf = (struct config_string *) record; + rawstring = pstrdup(*conf->variable && **conf->variable ? *conf->variable : ""); + if (!SplitIdentifierString(rawstring, ',', &elemlist)) + { + list_free(elemlist); + pfree(rawstring); + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("SET %s cannot operate on an already-invalid list", name))); + } + + initStringInfo(&value); + + /* + * Iterate over the elements in the current value of the setting and + * either suppress them (if operation is SUBTRACT and the element is + * in argstrings) or include them in the final value. + */ + foreach(l, elemlist) + { + char *tok = (char *) lfirst(l); + int i = 0; + + /* + * Check if tok is in argstrings. If so, we don't need to add it + * later; and if we want to remove it, we must skip this entry. + */ + for (i = 0; i < cur; i++) + if (pg_strcasecmp(argstrings[i], tok) == 0) + break; + + if (i < cur) + { + if (operation == VAR_ADD_VALUE) + argswanted[i] = false; + else + continue; + } + + /* Retain this element of the current setting */ + + if (value.len > 0) + appendStringInfoString(&value, ", "); + + if (record->flags & GUC_LIST_QUOTE) + appendStringInfoString(&value, quote_identifier(tok)); + else + appendStringInfoString(&value, tok); + } + + pfree(rawstring); + list_free(elemlist); + + /* + * Finally, if operation is ADD, we iterate over argstrings and add + * any elements to the output that are still wanted. + */ + for (int i = 0; i < cur; i++) + { + if (!argswanted[i]) + continue; + + if (value.len > 0) + appendStringInfoString(&value, ", "); + + if (record->flags & GUC_LIST_QUOTE) + appendStringInfoString(&value, quote_identifier(argstrings[i])); + else + appendStringInfoString(&value, argstrings[i]); + } + + free(argstrings); + free(argswanted); + + return value.data; +} + /* * Write updated configuration parameter values into a temporary file. * This function traverses the list of parameters and quotes the string @@ -8154,6 +8315,8 @@ AlterSystemSetConfigFile(AlterSystemStmt *altersysstmt) switch (altersysstmt->setstmt->kind) { case VAR_SET_VALUE: + case VAR_ADD_VALUE: + case VAR_SUBTRACT_VALUE: value = ExtractSetVariableArgs(altersysstmt->setstmt); break; @@ -8361,6 +8524,8 @@ ExecSetVariableStmt(VariableSetStmt *stmt, bool isTopLevel) { case VAR_SET_VALUE: case VAR_SET_CURRENT: + case VAR_ADD_VALUE: + case VAR_SUBTRACT_VALUE: if (stmt->is_local) WarnNoTransactionBlock(isTopLevel, "SET LOCAL"); (void) set_config_option(stmt->name, @@ -8474,6 +8639,9 @@ ExtractSetVariableArgs(VariableSetStmt *stmt) { case VAR_SET_VALUE: return flatten_set_variable_args(stmt->name, stmt->args); + case VAR_ADD_VALUE: + case VAR_SUBTRACT_VALUE: + return alter_set_variable_args(stmt->name, stmt->kind, stmt->args); case VAR_SET_CURRENT: return GetConfigOptionByName(stmt->name, NULL, false); default: diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 60c2f45466..c9b24a1778 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -2035,7 +2035,9 @@ typedef enum VAR_SET_CURRENT, /* SET var FROM CURRENT */ VAR_SET_MULTI, /* special case for SET TRANSACTION ... */ VAR_RESET, /* RESET var */ - VAR_RESET_ALL /* RESET ALL */ + VAR_RESET_ALL, /* RESET ALL */ + VAR_ADD_VALUE, /* SET var += value */ + VAR_SUBTRACT_VALUE /* SET var -= value */ } VariableSetKind; typedef struct VariableSetStmt diff --git a/src/include/parser/scanner.h b/src/include/parser/scanner.h index a27352afc1..57f073b6ff 100644 --- a/src/include/parser/scanner.h +++ b/src/include/parser/scanner.h @@ -52,6 +52,7 @@ typedef union core_YYSTYPE * %token <ival> ICONST PARAM * %token TYPECAST DOT_DOT COLON_EQUALS EQUALS_GREATER * %token LESS_EQUALS GREATER_EQUALS NOT_EQUALS + * %token PLUS_EQUALS MINUS_EQUALS * The above token definitions *must* be the first ones declared in any * bison parser built atop this scanner, so that they will have consistent * numbers assigned to them (specifically, IDENT = 258 and so on). -- 2.27.0
>From 3be9e8866fb7a6634f27b85eb0bcbcbf8357fbe4 Mon Sep 17 00:00:00 2001 From: Abhijit Menon-Sen <a...@2ndquadrant.com> Date: Sun, 27 Sep 2020 17:00:53 +0530 Subject: Support SET +=/-= syntax for numeric configuration settings This allows SET to modify the value of PGC_INT and PGC_REAL settings with the += and -= operations introduced earlier, by adding/subtracting the argument (which may be an integer, a real, or a string representing one of those two with some appropriate unit). Examples: SET cpu_tuple_cost += 0.01; SET effective_cache_size += '2GB'; ALTER SYSTEM SET max_worker_processes += 4; --- doc/src/sgml/ref/set.sgml | 6 ++ src/backend/utils/misc/guc.c | 114 ++++++++++++++++++++++++++++++++++- 2 files changed, 118 insertions(+), 2 deletions(-) diff --git a/doc/src/sgml/ref/set.sgml b/doc/src/sgml/ref/set.sgml index e30e9b42f0..4da3e05396 100644 --- a/doc/src/sgml/ref/set.sgml +++ b/doc/src/sgml/ref/set.sgml @@ -49,6 +49,12 @@ SET [ SESSION | LOCAL ] TIME ZONE { <replaceable class="parameter">timezone</rep already present, while the latter will remove an existing value. </para> + <para> + You can also use <literal>+=</literal> and <literal>-=</literal> to change + numeric configuration parameters, such as <varname>cpu_tuple_cost</varname> + or <varname>effective_cache_size</varname>. + </para> + <para> If <command>SET</command> (or equivalently <command>SET SESSION</command>) is issued within a transaction that is later aborted, the effects of the diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c index 9e56f64fec..f8e74d8991 100644 --- a/src/backend/utils/misc/guc.c +++ b/src/backend/utils/misc/guc.c @@ -7986,6 +7986,108 @@ flatten_set_variable_args(const char *name, List *args) return buf.data; } +/* + * alter_set_number_args + * Given a parsenode List as emitted by the grammar for SET, + * convert to the flat string representation used by GUC, with the + * args added to or subtracted from the current numeric (integer or + * real) value of the setting, depending on the desired operation + * + * The result is a palloc'd string. + */ +static char * +alter_set_number_args(struct config_generic *record, VariableSetKind operation, + List *args) +{ + StringInfoData value; + Node *arg = (Node *) linitial(args); + NodeTag tag; + A_Const *con; + + if (!IsA(arg, A_Const)) + elog(ERROR, "unrecognized node type: %d", (int) nodeTag(arg)); + + con = (A_Const *) arg; + tag = nodeTag(&con->val); + + /* + * The single constant argument may be an integer, a floating point + * value, or a string representing one of those two things. Once we + * have the desired change (positive or negative), we can just add + * it to the current value. + */ + if (record->vartype == PGC_INT) + { + struct config_int *conf = (struct config_int *) record; + int64 current = *conf->variable; + int delta = 0; + + if (tag == T_Integer) + delta = intVal(&con->val); + else if (tag == T_String) + { + const char *value = strVal(&con->val); + const char *hintmsg; + + if (!parse_int(value, &delta, conf->gen.flags, &hintmsg)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("invalid value for parameter \"%s\": \"%s\"", + record->name, value), + hintmsg ? errhint("%s", _(hintmsg)) : 0)); + } + else + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("invalid value for parameter \"%s\"", + record->name))); + + delta = operation == VAR_ADD_VALUE ? delta : -delta; + if ((delta > 0 && current < PG_INT64_MAX - delta) + || (delta < 0 && current > PG_INT64_MIN - delta)) + current = current + delta; + + initStringInfo(&value); + appendStringInfo(&value, INT64_FORMAT, current); + } + else if (record->vartype == PGC_REAL) + { + struct config_real *conf = (struct config_real *) record; + double current = *conf->variable; + double delta = 0; + + if (tag == T_Float) + delta = floatVal(&con->val); + else if (tag == T_Integer) + delta = intVal(&con->val); + else if (tag == T_String) + { + const char *value = strVal(&con->val); + const char *hintmsg; + + if (!parse_real(value, &delta, conf->gen.flags, &hintmsg)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("invalid value for parameter \"%s\": \"%s\"", + record->name, value), + hintmsg ? errhint("%s", _(hintmsg)) : 0)); + } + else + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("invalid value for parameter \"%s\"", + record->name))); + + delta = operation == VAR_ADD_VALUE ? delta : -delta; + current = current + delta; + + initStringInfo(&value); + appendStringInfo(&value, "%g", current); + } + + return value.data; +} + /* * alter_set_variable_args * Given a parsenode List as emitted by the grammar for SET, @@ -8016,8 +8118,16 @@ alter_set_variable_args(const char *name, VariableSetKind operation, List *args) errmsg("unrecognized configuration parameter \"%s\"", name))); /* - * At present, this function can operate only on a list represented - * as a comma-separated string. + * If the setting is a number and there's only one argument, we deal + * with it separately. + */ + if ((record->vartype == PGC_INT || record->vartype == PGC_REAL) + && list_length(args) == 1) + return alter_set_number_args(record, operation, args); + + /* + * This function can operate only on a list represented as a + * comma-separated string. */ if (record->vartype != PGC_STRING || (record->flags & GUC_LIST_INPUT) == 0) ereport(ERROR, -- 2.27.0