*** a/doc/src/sgml/config.sgml --- b/doc/src/sgml/config.sgml *************** *** 158,163 **** SET ENABLE_SEQSCAN TO OFF; --- 158,176 ---- require superuser permission to change via SET or ALTER. + + + Another way to change configuration parameters persistently is by + use of + command, for example: + + SET PERSISTENT max_connections To 10; + + This command will allow users to change values persistently + through SQL command. The values will be effective after reload of server configuration + (SIGHUP) or server startup. The effect of this command is similar to when + user manually changes values in postgresql.conf. + *** a/doc/src/sgml/keywords.sgml --- b/doc/src/sgml/keywords.sgml *************** *** 3388,3393 **** --- 3388,3400 ---- + PERSISTENT + non-reserved + + + + + PLACING reserved non-reserved *** a/doc/src/sgml/ref/set.sgml --- b/doc/src/sgml/ref/set.sgml *************** *** 21,28 **** PostgreSQL documentation ! SET [ SESSION | LOCAL ] configuration_parameter { TO | = } { value | 'value' | DEFAULT } ! SET [ SESSION | LOCAL ] TIME ZONE { timezone | LOCAL | DEFAULT } --- 21,28 ---- ! SET [ SESSION | LOCAL | PERSISTENT ] configuration_parameter { TO | = } { value | 'value' | DEFAULT } ! SET [ SESSION | LOCAL | PERSISTENT ] TIME ZONE { timezone | LOCAL | DEFAULT } *************** *** 50,55 **** SET [ SESSION | LOCAL ] TIME ZONE { timezone + SET PERSISTENT writes the configuration parameter + values to the postgresql.auto.conf file. With + DEFAULT, it removes a configuration entry from + postgresql.auto.conf file. The values will be + effective after reload of server configuration (SIGHUP) or server startup. + + + The effects of SET LOCAL last only till the end of the current transaction, whether committed or not. A special case is SET followed by SET LOCAL within *************** *** 98,105 **** SET [ SESSION | LOCAL ] TIME ZONE { timezone Specifies that the command takes effect for the current session. ! (This is the default if neither SESSION nor ! LOCAL appears.) --- 106,113 ---- Specifies that the command takes effect for the current session. ! (This is the default option, if any of SESSION, ! LOCAL or PERSISTENT are not provided.) *************** *** 119,124 **** SET [ SESSION | LOCAL ] TIME ZONE { timezone + PERSISTENT + + + Writes updated configuration parameters to postgresql.auto.conf. + These configuration parameters takes effect after reload of + configuration file (SIGHUP) or in next server start based on + the type of configuration parameter modified. + + + + + configuration_parameter *************** *** 154,160 **** SET [ SESSION | LOCAL ] TIME ZONE { timezone SCHEMA ! SET SCHEMA 'value' is an alias for SET search_path TO value. Only one schema can be specified using this syntax. --- 174,180 ---- SCHEMA ! SET [ PERSISTENT ] SCHEMA 'value' is an alias for SET search_path TO value. Only one schema can be specified using this syntax. *************** *** 164,170 **** SET [ SESSION | LOCAL ] TIME ZONE { timezone NAMES ! SET NAMES value is an alias for SET client_encoding TO value. --- 184,190 ---- NAMES ! SET [ PERSISTENT ] NAMES value is an alias for SET client_encoding TO value. *************** *** 192,198 **** SELECT setseed(value); TIME ZONE ! SET TIME ZONE value is an alias for SET timezone TO value. The syntax SET TIME ZONE allows special syntax for the time zone specification. Here are examples of valid --- 212,218 ---- TIME ZONE ! SET [ PERSISTENT ] TIME ZONE value is an alias for SET timezone TO value. The syntax SET TIME ZONE allows special syntax for the time zone specification. Here are examples of valid *** a/doc/src/sgml/storage.sgml --- b/doc/src/sgml/storage.sgml *************** *** 34,40 **** these required items, the cluster configuration files postgresql.conf, pg_hba.conf, and pg_ident.conf are traditionally stored in PGDATA (although in PostgreSQL 8.0 and ! later, it is possible to place them elsewhere). --- 34,41 ---- postgresql.conf, pg_hba.conf, and pg_ident.conf are traditionally stored in PGDATA (although in PostgreSQL 8.0 and ! later, it is possible to place them elsewhere). By default the directory auto.conf.d is stored ! in PGDATA, however it should be kept along with postgresql.conf.
*************** *** 57,62 **** Item --- 58,68 ---- + auto.conf.d + Subdirectory containing automatically generated configuration files + + + base Subdirectory containing per-database subdirectories *************** *** 253,258 **** where PPP is the PID of the owning backend and --- 259,269 ---- NNN distinguishes different temporary files of that backend. + + Configuration variables changed by command SET PERSISTENT will be stored in + PGDATA/auto.conf.d. + + *** a/src/backend/nodes/copyfuncs.c --- b/src/backend/nodes/copyfuncs.c *************** *** 3265,3270 **** _copyVariableSetStmt(const VariableSetStmt *from) --- 3265,3271 ---- COPY_STRING_FIELD(name); COPY_NODE_FIELD(args); COPY_SCALAR_FIELD(is_local); + COPY_SCALAR_FIELD(is_persistent); return newnode; } *** a/src/backend/nodes/equalfuncs.c --- b/src/backend/nodes/equalfuncs.c *************** *** 1556,1561 **** _equalVariableSetStmt(const VariableSetStmt *a, const VariableSetStmt *b) --- 1556,1562 ---- COMPARE_STRING_FIELD(name); COMPARE_NODE_FIELD(args); COMPARE_SCALAR_FIELD(is_local); + COMPARE_SCALAR_FIELD(is_persistent); return true; } *** a/src/backend/parser/gram.y --- b/src/backend/parser/gram.y *************** *** 395,401 **** static void processCASbits(int cas_bits, int location, const char *constrType, %type insert_rest ! %type set_rest set_rest_more SetResetClause FunctionSetResetClause %type TableElement TypedTableElement ConstraintElem TableFuncElement ForeignTableElement --- 395,401 ---- %type insert_rest ! %type set_rest set_rest_more set_persistent SetResetClause FunctionSetResetClause %type TableElement TypedTableElement ConstraintElem TableFuncElement ForeignTableElement *************** *** 565,572 **** static void processCASbits(int cas_bits, int location, const char *constrType, OBJECT_P OF OFF OFFSET OIDS ON ONLY OPERATOR OPTION OPTIONS OR ORDER OUT_P OUTER_P OVER OVERLAPS OVERLAY OWNED OWNER ! PARSER PARTIAL PARTITION PASSING PASSWORD PLACING PLANS POSITION ! PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY PRIOR PRIVILEGES PROCEDURAL PROCEDURE QUOTE --- 565,572 ---- OBJECT_P OF OFF OFFSET OIDS ON ONLY OPERATOR OPTION OPTIONS OR ORDER OUT_P OUTER_P OVER OVERLAPS OVERLAY OWNED OWNER ! PARSER PARTIAL PARTITION PASSING PASSWORD PERSISTENT PLACING PLANS ! POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY PRIOR PRIVILEGES PROCEDURAL PROCEDURE QUOTE *************** *** 1294,1299 **** VariableSetStmt: --- 1294,1305 ---- n->is_local = false; $$ = (Node *) n; } + | SET PERSISTENT set_persistent + { + VariableSetStmt *n = $3; + n->is_persistent = true; + $$ = (Node *) n; + } ; set_rest: *************** *** 1317,1322 **** set_rest: --- 1323,1373 ---- ; set_rest_more: /* Generic SET syntaxes: */ + CATALOG_P Sconst + { + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("current database cannot be changed"), + parser_errposition(@2))); + $$ = NULL; /*not reached*/ + } + | ROLE ColId_or_Sconst + { + VariableSetStmt *n = makeNode(VariableSetStmt); + n->kind = VAR_SET_VALUE; + n->name = "role"; + n->args = list_make1(makeStringConst($2, @2)); + $$ = n; + } + | SESSION AUTHORIZATION ColId_or_Sconst + { + VariableSetStmt *n = makeNode(VariableSetStmt); + n->kind = VAR_SET_VALUE; + n->name = "session_authorization"; + n->args = list_make1(makeStringConst($3, @3)); + $$ = n; + } + | SESSION AUTHORIZATION DEFAULT + { + VariableSetStmt *n = makeNode(VariableSetStmt); + n->kind = VAR_SET_DEFAULT; + n->name = "session_authorization"; + $$ = n; + } + /* Special syntaxes invented by PostgreSQL: */ + | TRANSACTION SNAPSHOT Sconst + { + VariableSetStmt *n = makeNode(VariableSetStmt); + n->kind = VAR_SET_MULTI; + n->name = "TRANSACTION SNAPSHOT"; + n->args = list_make1(makeStringConst($3, @3)); + $$ = n; + } + | set_persistent + ; + + /* common SET/SET PERSISTENT syntaxes */ + set_persistent: var_name TO var_list { VariableSetStmt *n = makeNode(VariableSetStmt); *************** *** 1366,1379 **** set_rest_more: /* Generic SET syntaxes: */ n->kind = VAR_SET_DEFAULT; $$ = n; } - | CATALOG_P Sconst - { - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("current database cannot be changed"), - parser_errposition(@2))); - $$ = NULL; /*not reached*/ - } | SCHEMA Sconst { VariableSetStmt *n = makeNode(VariableSetStmt); --- 1417,1422 ---- *************** *** 1393,1421 **** set_rest_more: /* Generic SET syntaxes: */ n->kind = VAR_SET_DEFAULT; $$ = n; } - | ROLE ColId_or_Sconst - { - VariableSetStmt *n = makeNode(VariableSetStmt); - n->kind = VAR_SET_VALUE; - n->name = "role"; - n->args = list_make1(makeStringConst($2, @2)); - $$ = n; - } - | SESSION AUTHORIZATION ColId_or_Sconst - { - VariableSetStmt *n = makeNode(VariableSetStmt); - n->kind = VAR_SET_VALUE; - n->name = "session_authorization"; - n->args = list_make1(makeStringConst($3, @3)); - $$ = n; - } - | SESSION AUTHORIZATION DEFAULT - { - VariableSetStmt *n = makeNode(VariableSetStmt); - n->kind = VAR_SET_DEFAULT; - n->name = "session_authorization"; - $$ = n; - } | XML_P OPTION document_or_content { VariableSetStmt *n = makeNode(VariableSetStmt); --- 1436,1441 ---- *************** *** 1424,1439 **** set_rest_more: /* Generic SET syntaxes: */ n->args = list_make1(makeStringConst($3 == XMLOPTION_DOCUMENT ? "DOCUMENT" : "CONTENT", @3)); $$ = n; } ! /* Special syntaxes invented by PostgreSQL: */ ! | TRANSACTION SNAPSHOT Sconst ! { ! VariableSetStmt *n = makeNode(VariableSetStmt); ! n->kind = VAR_SET_MULTI; ! n->name = "TRANSACTION SNAPSHOT"; ! n->args = list_make1(makeStringConst($3, @3)); ! $$ = n; ! } ! ; var_name: ColId { $$ = $1; } | var_name '.' ColId --- 1444,1450 ---- n->args = list_make1(makeStringConst($3 == XMLOPTION_DOCUMENT ? "DOCUMENT" : "CONTENT", @3)); $$ = n; } ! ; var_name: ColId { $$ = $1; } | var_name '.' ColId *************** *** 12591,12596 **** unreserved_keyword: --- 12602,12608 ---- | PARTITION | PASSING | PASSWORD + | PERSISTENT | PLANS | PRECEDING | PREPARE *** a/src/backend/replication/basebackup.c --- b/src/backend/replication/basebackup.c *************** *** 755,760 **** sendDir(char *path, int basepathlen, bool sizeonly) --- 755,766 ---- strlen(PG_TEMP_FILE_PREFIX)) == 0) continue; + /* skip auto conf temporary file */ + if (strncmp(de->d_name, + PG_AUTOCONF_FILENAME ".", + sizeof(PG_AUTOCONF_FILENAME)) == 0) + continue; + /* * If there's a backup_label file, it belongs to a backup started by * the user with pg_start_backup(). It is *not* correct for this *** a/src/backend/tcop/utility.c --- b/src/backend/tcop/utility.c *************** *** 1165,1171 **** standard_ProcessUtility(Node *parsetree, break; case T_VariableSetStmt: ! ExecSetVariableStmt((VariableSetStmt *) parsetree); break; case T_VariableShowStmt: --- 1165,1178 ---- break; case T_VariableSetStmt: ! { ! VariableSetStmt *stmt = (VariableSetStmt *) parsetree; ! ! if (stmt->is_persistent) ! PreventTransactionChain(isTopLevel, "SET PERSISTENT"); ! ! ExecSetVariableStmt(stmt); ! } break; case T_VariableShowStmt: *** a/src/backend/utils/misc/guc-file.l --- b/src/backend/utils/misc/guc-file.l *************** *** 41,46 **** enum { --- 41,55 ---- GUC_ERROR = 100 }; + /* + * variable is_config_dir_parsed is used to check the accessibility + * of postgresql.auto.conf file. File cannot be accessed due to any + * of below reasons: + * a. either postgresql.auto.conf file doesn't exist or + * b. folder containing postgresql.auto.conf doesn't exist or + * c. include_dir for auto.conf.d has been removed from postgresql.auto.conf file. + */ + static bool is_config_dir_parsed = false; static unsigned int ConfigFileLineno; static const char *GUC_flex_fatal_errmsg; static sigjmp_buf *GUC_flex_fatal_jmp; *************** *** 137,142 **** ProcessConfigFile(GucContext context) --- 146,153 ---- /* Parse the file into a list of option names and values */ head = tail = NULL; + is_config_dir_parsed = false; + if (!ParseConfigFile(ConfigFileName, NULL, true, 0, elevel, &head, &tail)) { /* Syntax error(s) detected in the file, so bail out */ *************** *** 144,149 **** ProcessConfigFile(GucContext context) --- 155,170 ---- goto cleanup_list; } + if (!is_config_dir_parsed) + { + ereport(WARNING, + (errmsg("File \"%s\" file is not accessible, either file \"%s\" or folder \"%s\" doesn't exist. " + "or default \"%s\" is not present in postgresql.conf.\n" + "Configuration parameters changed before start/reload of server with SET PERSISTENT command" + "will not be effective.", PG_AUTOCONF_FILENAME, PG_AUTOCONF_FILENAME, PG_AUTOCONF_DIR, + PG_AUTOCONF_DIR))); + } + /* * Mark all extant GUC variables as not present in the config file. * We need this so that we can tell below which ones have been removed *************** *** 411,416 **** ParseConfigFile(const char *config_file, const char *calling_file, bool strict, --- 432,439 ---- { bool OK = true; FILE *fp; + char *ConfigAutoFileName; + char Filename[MAXPGPATH]; /* * Reject too-deep include nesting depth. This is just a safety check *************** *** 427,432 **** ParseConfigFile(const char *config_file, const char *calling_file, bool strict, --- 450,466 ---- } config_file = AbsoluteConfigLocation(config_file,calling_file); + + /* + * record if the file currently being parsed is postgresql.auto.conf, + * so that it can be later used to give warning if it doesn't parse + * it. + */ + snprintf(Filename,sizeof(Filename),"%s/%s", PG_AUTOCONF_DIR, PG_AUTOCONF_FILENAME); + ConfigAutoFileName = AbsoluteConfigLocation(Filename, ConfigFileName); + if (depth == 1 && strcmp(ConfigAutoFileName, config_file) == 0) + is_config_dir_parsed = true; + fp = AllocateFile(config_file, "r"); if (!fp) { *************** *** 533,538 **** ParseConfigFp(FILE *fp, const char *config_file, int depth, int elevel, --- 567,573 ---- { char *opt_name = NULL; char *opt_value = NULL; + bool is_string = false; ConfigVariable *item; if (token == GUC_EOL) /* empty or comment line */ *************** *** 556,562 **** ParseConfigFp(FILE *fp, const char *config_file, int depth, int elevel, --- 591,600 ---- token != GUC_UNQUOTED_STRING) goto parse_error; if (token == GUC_STRING) /* strip quotes and escapes */ + { opt_value = GUC_scanstr(yytext); + is_string = true; + } else opt_value = pstrdup(yytext); *************** *** 616,625 **** ParseConfigFp(FILE *fp, const char *config_file, int depth, int elevel, } else { ! /* ordinary variable, append to list */ item = palloc(sizeof *item); item->name = opt_name; item->value = opt_value; item->filename = pstrdup(config_file); item->sourceline = ConfigFileLineno-1; item->next = NULL; --- 654,668 ---- } else { ! /* ! * ordinary variable, append to list. ! * mark string variables by setting is_quoted, so that it can be ! * used in write_auto_conf_file to quote the string variables. ! */ item = palloc(sizeof *item); item->name = opt_name; item->value = opt_value; + item->is_quoted = is_string; item->filename = pstrdup(config_file); item->sourceline = ConfigFileLineno-1; item->next = NULL; *** a/src/backend/utils/misc/guc.c --- b/src/backend/utils/misc/guc.c *************** *** 57,68 **** --- 57,70 ---- #include "postmaster/postmaster.h" #include "postmaster/syslogger.h" #include "postmaster/walwriter.h" + #include "port.h" #include "replication/syncrep.h" #include "replication/walreceiver.h" #include "replication/walsender.h" #include "storage/bufmgr.h" #include "storage/standby.h" #include "storage/fd.h" + #include "storage/lwlock.h" #include "storage/proc.h" #include "storage/predicate.h" #include "tcop/tcopprot.h" *************** *** 3348,3353 **** static void ShowAllGUCConfig(DestReceiver *dest); --- 3350,3360 ---- static char *_ShowOption(struct config_generic * record, bool use_units); static bool validate_option_array_item(const char *name, const char *value, bool skipIfNoPermissions); + static void write_auto_conf_file(int fd, const char *filename, + ConfigVariable **head_p); + static void replace_auto_config_value(ConfigVariable **head_p, ConfigVariable **tail_p, + char *config_file, char *name, char *value, bool is_quoted); + static void set_config_file(VariableSetStmt *setstmt); /* *************** *** 5094,5099 **** config_enum_get_options(struct config_enum * record, const char *prefix, --- 5101,5284 ---- return retstr.data; } + /* + * Validates configuration parameter and value, incase of source is default + * returns the default value in string format. It validates if the parameter + * value given is in range of expected predefined value for that parameter. + */ + struct config_generic * + validate_conf_option(const char *name, char *value) + { + struct config_generic *record; + + record = find_option(name, true, LOG); + if (record == NULL) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("unrecognized configuration parameter \"%s\"", name))); + + if (record->context == PGC_INTERNAL) + ereport(ERROR, + (errcode(ERRCODE_CANT_CHANGE_RUNTIME_PARAM), + errmsg("parameter \"%s\" cannot be changed", + name))); + + /* + * Validate the value for the record found, if it is in expected range + * then return the record found. + */ + switch (record->vartype) + { + + case PGC_BOOL: + { + struct config_bool *conf = (struct config_bool *) record; + bool newval; + void *newextra = NULL; + + if (value != NULL) + { + if (!parse_bool(value, &newval)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("parameter \"%s\" requires a Boolean value", + name))); + + if (!call_bool_check_hook(conf, &newval, &newextra, + PGC_S_FILE, LOG)) + return NULL; + } + } + break; + case PGC_INT: + { + struct config_int *conf = (struct config_int *) record; + int newval; + void *newextra = NULL; + + if (value != NULL) + { + const char *hintmsg; + + if (!parse_int(value, &newval, conf->gen.flags, &hintmsg)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("invalid value for parameter \"%s\": \"%s\"", + name, value), + hintmsg ? errhint("%s", _(hintmsg)) : 0)); + + if (newval < conf->min || newval > conf->max) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("%d is outside the valid range for parameter \"%s\" (%d .. %d)", + newval, name, conf->min, conf->max))); + + if (!call_int_check_hook(conf, &newval, &newextra, + PGC_S_FILE, LOG)) + return NULL; + } + } + break; + case PGC_REAL: + { + struct config_real *conf = (struct config_real *) record; + double newval; + void *newextra = NULL; + + if (value != NULL) + { + if (!parse_real(value, &newval)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("parameter \"%s\" requires a numeric value", + name))); + + if (newval < conf->min || newval > conf->max) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("%g is outside the valid range for parameter \"%s\" (%g .. %g)", + newval, name, conf->min, conf->max))); + + if (!call_real_check_hook(conf, &newval, &newextra, + PGC_S_FILE, LOG)) + return NULL; + } + } + break; + case PGC_STRING: + { + struct config_string *conf = (struct config_string *) record; + char *newval; + void *newextra = NULL; + + if (value != NULL) + { + /* + * The value passed by the caller could be transient, so + * we always strdup it. + */ + newval = guc_strdup(LOG, value); + if (newval == NULL) + return NULL; + + /* + * The only built-in "parsing" check we have is to apply + * truncation if GUC_IS_NAME. + */ + if (conf->gen.flags & GUC_IS_NAME) + truncate_identifier(newval, strlen(newval), true); + + if (!call_string_check_hook(conf, &newval, &newextra, + PGC_S_FILE, LOG)) + { + free(newval); + return NULL; + } + + /* Free the malloc'd data if any */ + if (newval != NULL) + free(newval); + if (newextra != NULL) + free(newextra); + } + } + break; + case PGC_ENUM: + { + struct config_enum *conf = (struct config_enum *) record; + int newval; + void *newextra = NULL; + + if (value != NULL) + { + if (!config_enum_lookup_by_name(conf, value, &newval)) + { + char *hintmsg; + + hintmsg = config_enum_get_options(conf, + "Available values: ", + ".", ", "); + + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("invalid value for parameter \"%s\": \"%s\"", + name, value), + hintmsg ? errhint("%s", _(hintmsg)) : 0)); + + if (hintmsg != NULL) + pfree(hintmsg); + return NULL; + } + if (!call_enum_check_hook(conf, &newval, &newextra, + PGC_S_FILE, LOG)) + return NULL; + } + } + } + + return record; + } + /* * Sets option `name' to given value. *************** *** 5897,5902 **** set_config_sourcefile(const char *name, char *sourcefile, int sourceline) --- 6082,6373 ---- } /* + * Writes updated configuration parameter values into + * postgresql.auto.conf.temp file. It traverses the list of parameters + * and quote the string values before writing them to temporaray file. + */ + static void + write_auto_conf_file(int fd, const char *filename, ConfigVariable **head_p) + { + ConfigVariable *item; + StringInfoData buf; + + initStringInfo(&buf); + + /* + * traverse the list of parameters, quote the string parameter and write + * it file. Once all parameters are written fsync the file. + */ + + for (item = *head_p; item != NULL; item = item->next) + { + appendStringInfoString(&buf, item->name); + appendStringInfoString(&buf, " = "); + + if (true == item->is_quoted) + { + char *escaped; + + appendStringInfoString(&buf, "\'"); + escaped = escape_single_quotes_ascii(item->value); + appendStringInfoString(&buf, escaped); + free(escaped); + appendStringInfoString(&buf, "\'"); + } + else + appendStringInfoString(&buf, item->value); + + appendStringInfoString(&buf, "\n"); + + if (write(fd, buf.data, buf.len) < 0) + ereport(ERROR, + (errmsg("failed to write to \"%s\" file", + filename))); + + resetStringInfo(&buf); + } + + if (pg_fsync(fd) != 0) + ereport(ERROR, + (errcode_for_file_access(), + errmsg("could not fsync file \"%s\": %m", + filename))); + + pfree(buf.data); + } + + + /* + * This function takes list of all configuration parameters in postgresql.auto.conf + * and parameter to be updated as input arguments and replace the updated + * configuration parameter value in a list. + * If the parameter to be updated is new then it is appended to the + * list of parameters. + */ + static void + replace_auto_config_value(ConfigVariable **head_p, ConfigVariable **tail_p, + char *config_file, + char *name, char *value, + bool is_quoted) + { + ConfigVariable *item, + *prev = NULL; + + if (*head_p != NULL) + { + for (item = *head_p; item != NULL; item = item->next) + { + if (strcmp(item->name, name) == 0) + { + pfree(item->value); + if (value != NULL) + /* update the parameter value */ + item->value = pstrdup(value); + else + { + /* delete the configuration parameter from list */ + if (*head_p == item) + *head_p = item->next; + else + prev->next = item->next; + + if (*tail_p == item) + *tail_p = prev; + + pfree(item->name); + pfree(item->filename); + pfree(item); + } + return; + } + prev = item; + } + } + + if (value == NULL) + return; + + item = palloc(sizeof *item); + item->name = pstrdup(name); + item->value = pstrdup(value); + item->is_quoted = is_quoted; + item->filename = pstrdup(config_file); + item->next = NULL; + + if (*head_p == NULL) + { + item->sourceline = 1; + *head_p = item; + } + else + { + item->sourceline = (*tail_p)->sourceline + 1; + (*tail_p)->next = item; + } + + *tail_p = item; + + return; + } + + + /* + * Persist the configuration parameter value. + * + * This function takes all previously set persistent + * configuration parameters and the currently set ones + * and write them all to the automatic configuration file. + * + * The configuration parameters are written to a temporary + * file then renamed to the final name. The template for the + * temporary file is postgresql.auto.conf.temp. + * + * An LWLock is used to serialize writing to the same file. + * + * In case of an error, we leave the original automatic + * configuration file (postgresql.auto.conf) intact. + * A stale temporary file may be left behind in case we crash. + * Such files are removed on the next server restart. + */ + static void + set_config_file(VariableSetStmt *setstmt) + { + char *name; + char *value; + int Tmpfd = -1; + FILE *infile; + struct config_generic *record; + ConfigVariable *head_p = NULL; + ConfigVariable *tail_p = NULL; + char ConfigFileDir[MAXPGPATH]; + char AutoConfFileName[MAXPGPATH]; + char AutoConfTmpFileName[MAXPGPATH]; + struct stat st; + + if (!superuser()) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + (errmsg("must be superuser to execute SET PERSISTENT command")))); + + /* + * Validate the name and arguments [value1, value2 ... ]. + */ + name = setstmt->name; + + switch (setstmt->kind) + { + case VAR_SET_VALUE: + case VAR_SET_CURRENT: + value = ExtractSetVariableArgs(setstmt); + break; + + case VAR_SET_DEFAULT: + value = NULL; + break; + default: + elog(ERROR, "unrecognized set persistent stmt type: %d", + setstmt->kind); + break; + } + + record = validate_conf_option(name, value); + if (record == NULL) + ereport(ERROR, + (errmsg("invalid value for parameter \"%s\": \"%s\"", name, value))); + + /* + * The "auto.conf.d" directory should follow the postgresql.conf file + * directory not the data_directory. The same is validated while parsing + * the postgresql.conf configuration file. So while framing the + * postgresql.auto.conf and it's temp file we need to follow the + * postgresql.conf file directory. + */ + StrNCpy(ConfigFileDir, ConfigFileName, sizeof(ConfigFileDir)); + get_parent_directory(ConfigFileDir); + + /* Frame auto conf name and auto conf sample temp file name */ + snprintf(AutoConfFileName, sizeof(AutoConfFileName), "%s/%s/%s", + ConfigFileDir, + PG_AUTOCONF_DIR, + PG_AUTOCONF_FILENAME); + snprintf(AutoConfTmpFileName, sizeof(AutoConfTmpFileName), "%s/%s/%s.%s", + ConfigFileDir, + PG_AUTOCONF_DIR, + PG_AUTOCONF_FILENAME, + "temp"); + + /* + * one backend is allowed to operate on .auto.conf file, to ensure that we + * need to update the contents of the file with SetPersistentLock. To + * ensure crash safety, first the contents are written to temporary file + * and then rename it to postgresql.auto.conf + */ + + LWLockAcquire(SetPersistentLock, LW_EXCLUSIVE); + + Tmpfd = open(AutoConfTmpFileName, O_CREAT | O_RDWR | O_TRUNC, S_IRUSR | S_IWUSR); + if (Tmpfd < 0) + ereport(ERROR, + (errcode_for_file_access(), + errmsg("Failed to open auto conf temp file \"%s\": %m ", + AutoConfTmpFileName))); + + PG_TRY(); + { + if (stat(AutoConfFileName, &st) == 0) + { + /* open postgresql.auto.conf file */ + infile = AllocateFile(AutoConfFileName, "r"); + if (infile == NULL) + ereport(ERROR, + (errmsg("Failed to open auto conf file \"%s\": %m ", + AutoConfFileName))); + + /* Parse the postgresql.auto.conf file */ + ParseConfigFp(infile, AutoConfFileName, 0, LOG, &head_p, &tail_p); + + FreeFile(infile); + } + + /* + * replace with new value if the configuration parameter already + * exists OR add it as a new cofiguration parameter in the file. + */ + replace_auto_config_value(&head_p, &tail_p, AutoConfFileName, name, + value, (record->vartype == PGC_STRING) ? true : false); + + /* Write and sync the New contents to postgresql.auto.conf.temp file */ + write_auto_conf_file(Tmpfd, AutoConfTmpFileName, &head_p); + + close(Tmpfd); + Tmpfd = -1; + + /* + * As the rename is atomic operation, if any problem occurs after this + * at max it can lead to either lost the last set persistent command. + */ + if (rename(AutoConfTmpFileName, AutoConfFileName) < 0) + ereport(ERROR, + (errcode_for_file_access(), + errmsg("could not rename file \"%s\" to \"%s\" : %m", + AutoConfTmpFileName, AutoConfFileName))); + } + PG_CATCH(); + { + if (Tmpfd >= 0) + close(Tmpfd); + + unlink(AutoConfTmpFileName); + PG_RE_THROW(); + } + PG_END_TRY(); + + LWLockRelease(SetPersistentLock); + return; + } + + + /* * Set a config option to the given value. * * See also set_config_option; this is just the wrapper to be called from *************** *** 6168,6180 **** ExecSetVariableStmt(VariableSetStmt *stmt) { case VAR_SET_VALUE: case VAR_SET_CURRENT: ! (void) set_config_option(stmt->name, ! ExtractSetVariableArgs(stmt), (superuser() ? PGC_SUSET : PGC_USERSET), ! PGC_S_SESSION, ! action, ! true, ! 0); break; case VAR_SET_MULTI: --- 6639,6654 ---- { case VAR_SET_VALUE: case VAR_SET_CURRENT: ! if (stmt->is_persistent) ! set_config_file(stmt); ! else ! (void) set_config_option(stmt->name, ! ExtractSetVariableArgs(stmt), (superuser() ? PGC_SUSET : PGC_USERSET), ! PGC_S_SESSION, ! action, ! true, ! 0); break; case VAR_SET_MULTI: *************** *** 6246,6251 **** ExecSetVariableStmt(VariableSetStmt *stmt) --- 6720,6731 ---- stmt->name); break; case VAR_SET_DEFAULT: + if (stmt->is_persistent) + { + set_config_file(stmt); + break; + } + /* FALLTHROUGH */ case VAR_RESET: (void) set_config_option(stmt->name, NULL, *** a/src/backend/utils/misc/postgresql.conf.sample --- b/src/backend/utils/misc/postgresql.conf.sample *************** *** 581,586 **** --- 581,595 ---- #include_if_exists = 'exists.conf' # include file only if it exists #include = 'special.conf' # include file + # This includes the default configuration directory included to support + # SET PERSISTENT statement. + # + # WARNING: User should not remove below include_dir or directory auto.conf.d, + # as both are required to make SET PERSISTENT command work. + # Any configuration parameter values specified after this line + # will override the values set by SET PERSISTENT statement. + #include_dir = 'auto.conf.d' + #------------------------------------------------------------------------------ # CUSTOMIZED OPTIONS #------------------------------------------------------------------------------ *** a/src/bin/initdb/initdb.c --- b/src/bin/initdb/initdb.c *************** *** 192,198 **** const char *subdirs[] = { "base", "base/1", "pg_tblspc", ! "pg_stat_tmp" }; --- 192,199 ---- "base", "base/1", "pg_tblspc", ! "pg_stat_tmp", ! PG_AUTOCONF_DIR }; *************** *** 1209,1214 **** setup_config(void) --- 1210,1216 ---- char repltok[MAXPGPATH]; char path[MAXPGPATH]; const char *default_timezone; + FILE *autofile; fputs(_("creating configuration files ... "), stdout); fflush(stdout); *************** *** 1291,1301 **** setup_config(void) --- 1293,1331 ---- conflines = replace_token(conflines, "#log_timezone = 'GMT'", repltok); } + snprintf(repltok, sizeof(repltok), "include_dir = '" PG_AUTOCONF_DIR "'"); + conflines = replace_token(conflines, "#include_dir = 'auto.conf.d'", repltok); + snprintf(path, sizeof(path), "%s/postgresql.conf", pg_data); writefile(path, conflines); chmod(path, S_IRUSR | S_IWUSR); + /* + * create the automatic configuration file to store the configuration + * parameters set by SET PERSISTENT command. This file will be included in + * postgresql.conf and the parameters present in this file will override + * the values of parameters that exists before include of this file. + */ + sprintf(path, "%s/%s/%s", pg_data, PG_AUTOCONF_DIR, PG_AUTOCONF_FILENAME); + autofile = fopen(path, PG_BINARY_W); + if (autofile == NULL) + { + fprintf(stderr, _("%s: could not open file \"%s\" for writing: %s\n"), + progname, path, strerror(errno)); + exit_nicely(); + } + + if (fclose(autofile)) + { + fprintf(stderr, _("%s: could not write file \"%s\": %s\n"), + progname, path, strerror(errno)); + exit_nicely(); + } + + chmod(path, S_IRUSR | S_IWUSR); + + free(conflines); *** a/src/include/nodes/parsenodes.h --- b/src/include/nodes/parsenodes.h *************** *** 1435,1440 **** typedef struct VariableSetStmt --- 1435,1441 ---- char *name; /* variable to be set */ List *args; /* List of A_Const nodes */ bool is_local; /* SET LOCAL? */ + bool is_persistent; /* SET PERSISTENT? */ } VariableSetStmt; /* ---------------------- *** a/src/include/parser/kwlist.h --- b/src/include/parser/kwlist.h *************** *** 279,284 **** PG_KEYWORD("partial", PARTIAL, UNRESERVED_KEYWORD) --- 279,285 ---- PG_KEYWORD("partition", PARTITION, UNRESERVED_KEYWORD) PG_KEYWORD("passing", PASSING, UNRESERVED_KEYWORD) PG_KEYWORD("password", PASSWORD, UNRESERVED_KEYWORD) + PG_KEYWORD("persistent", PERSISTENT, UNRESERVED_KEYWORD) PG_KEYWORD("placing", PLACING, RESERVED_KEYWORD) PG_KEYWORD("plans", PLANS, UNRESERVED_KEYWORD) PG_KEYWORD("position", POSITION, COL_NAME_KEYWORD) *** a/src/include/pg_config_manual.h --- b/src/include/pg_config_manual.h *************** *** 266,268 **** --- 266,275 ---- /* #define HEAPDEBUGALL */ /* #define ACLDEBUG */ /* #define RTDEBUG */ + + /* + * Automatic configuration directory and file name + * for SET PERSISTENT. + */ + #define PG_AUTOCONF_DIR "auto.conf.d" + #define PG_AUTOCONF_FILENAME "postgresql.auto.conf" *** a/src/include/storage/lwlock.h --- b/src/include/storage/lwlock.h *************** *** 79,84 **** typedef enum LWLockId --- 79,85 ---- SerializablePredicateLockListLock, OldSerXidLock, SyncRepLock, + SetPersistentLock, /* Individual lock IDs end here */ FirstBufMappingLock, FirstLockMgrLock = FirstBufMappingLock + NUM_BUFFER_PARTITIONS, *** a/src/include/utils/guc.h --- b/src/include/utils/guc.h *************** *** 105,110 **** typedef struct ConfigVariable --- 105,111 ---- { char *name; char *value; + bool is_quoted; char *filename; int sourceline; struct ConfigVariable *next; *** a/src/include/utils/guc_tables.h --- b/src/include/utils/guc_tables.h *************** *** 260,265 **** extern void build_guc_variables(void); extern const char *config_enum_lookup_by_value(struct config_enum * record, int val); extern bool config_enum_lookup_by_name(struct config_enum * record, const char *value, int *retval); ! #endif /* GUC_TABLES_H */ --- 260,265 ---- extern const char *config_enum_lookup_by_value(struct config_enum * record, int val); extern bool config_enum_lookup_by_name(struct config_enum * record, const char *value, int *retval); ! extern struct config_generic *validate_conf_option(const char *name, char *value); #endif /* GUC_TABLES_H */ *** /dev/null --- b/src/test/regress/expected/persistent.out *************** *** 0 **** --- 1,580 ---- + -- parameter which cannot be set after connection start. + set persistent log_connections=on; + -- parameter can only be set at during server start. + set persistent max_connections To 10; + set persistent wal_level = hot_standby; + -- parameter can set by the backend + set persistent authentication_timeout = 10; + set persistent vacuum_cost_delay To 200; + ERROR: 200 is outside the valid range for parameter "vacuum_cost_delay" (0 .. 100) + set persistent bgwriter_lru_multiplier = 3.2; + set persistent full_page_writes = off; + set persistent synchronous_commit To off; + set persistent synchronous_standby_names = 'standby1, standby2'; + set persistent seq_page_cost = 0.9; + set persistent autovacuum_naptime To 30; + set persistent search_path = 'public'; + set persistent default_tablespace = ''; + set persistent checkpoint_timeout = 1; + ERROR: 1 is outside the valid range for parameter "checkpoint_timeout" (30 .. 3600) + set persistent checkpoint_timeout = 3700; + ERROR: 3700 is outside the valid range for parameter "checkpoint_timeout" (30 .. 3600) + set persistent checkpoint_timeout = 90; + set persistent restart_after_crash TO on; + show log_connections; + log_connections + ----------------- + off + (1 row) + + show max_connections; + max_connections + ----------------- + 100 + (1 row) + + show wal_level; + wal_level + ----------- + minimal + (1 row) + + show authentication_timeout; + authentication_timeout + ------------------------ + 1min + (1 row) + + show vacuum_cost_delay; + vacuum_cost_delay + ------------------- + 0 + (1 row) + + show bgwriter_lru_multiplier; + bgwriter_lru_multiplier + ------------------------- + 2 + (1 row) + + show full_page_writes; + full_page_writes + ------------------ + on + (1 row) + + show synchronous_commit; + synchronous_commit + -------------------- + on + (1 row) + + show synchronous_standby_names; + synchronous_standby_names + --------------------------- + + (1 row) + + show seq_page_cost; + seq_page_cost + --------------- + 1 + (1 row) + + show autovacuum_naptime; + autovacuum_naptime + -------------------- + 1min + (1 row) + + show search_path; + search_path + ---------------- + "$user",public + (1 row) + + show default_tablespace; + default_tablespace + -------------------- + + (1 row) + + show checkpoint_timeout; + checkpoint_timeout + -------------------- + 5min + (1 row) + + show restart_after_crash; + restart_after_crash + --------------------- + on + (1 row) + + -- reload the new configurations. + select pg_reload_conf(); + pg_reload_conf + ---------------- + t + (1 row) + + select pg_sleep(1); + pg_sleep + ---------- + + (1 row) + + show log_connections; + log_connections + ----------------- + off + (1 row) + + show max_connections; + max_connections + ----------------- + 100 + (1 row) + + show wal_level; + wal_level + ----------- + minimal + (1 row) + + show authentication_timeout; + authentication_timeout + ------------------------ + 10s + (1 row) + + show vacuum_cost_delay; + vacuum_cost_delay + ------------------- + 0 + (1 row) + + show bgwriter_lru_multiplier; + bgwriter_lru_multiplier + ------------------------- + 3.2 + (1 row) + + show full_page_writes; + full_page_writes + ------------------ + off + (1 row) + + show synchronous_commit; + synchronous_commit + -------------------- + off + (1 row) + + show synchronous_standby_names; + synchronous_standby_names + --------------------------- + standby1, standby2 + (1 row) + + show seq_page_cost; + seq_page_cost + --------------- + 0.9 + (1 row) + + show autovacuum_naptime; + autovacuum_naptime + -------------------- + 30s + (1 row) + + show search_path; + search_path + ------------- + public + (1 row) + + show default_tablespace; + default_tablespace + -------------------- + + (1 row) + + show checkpoint_timeout; + checkpoint_timeout + -------------------- + 90s + (1 row) + + show restart_after_crash; + restart_after_crash + --------------------- + on + (1 row) + + -- reconnect to see the new configurations + \c + select name, setting from pg_settings where name = 'log_connections'; + name | setting + -----------------+--------- + log_connections | on + (1 row) + + select name, setting from pg_settings where name = 'max_connections'; + name | setting + -----------------+--------- + max_connections | 100 + (1 row) + + select name, setting from pg_settings where name = 'wal_level'; + name | setting + -----------+--------- + wal_level | minimal + (1 row) + + select name, setting from pg_settings where name = 'authentication_timeout'; + name | setting + ------------------------+--------- + authentication_timeout | 10 + (1 row) + + select name, setting from pg_settings where name = 'vacuum_cost_delay'; + name | setting + -------------------+--------- + vacuum_cost_delay | 0 + (1 row) + + select name, setting from pg_settings where name = 'bgwriter_lru_multiplier'; + name | setting + -------------------------+--------- + bgwriter_lru_multiplier | 3.2 + (1 row) + + select name, setting from pg_settings where name = 'full_page_writes'; + name | setting + ------------------+--------- + full_page_writes | off + (1 row) + + select name, setting from pg_settings where name = 'synchronous_commit'; + name | setting + --------------------+--------- + synchronous_commit | off + (1 row) + + select name, setting from pg_settings where name = 'synchronous_standby_names'; + name | setting + ---------------------------+-------------------- + synchronous_standby_names | standby1, standby2 + (1 row) + + select name, setting from pg_settings where name = 'seq_page_cost'; + name | setting + ---------------+--------- + seq_page_cost | 0.9 + (1 row) + + select name, setting from pg_settings where name = 'autovacuum_naptime'; + name | setting + --------------------+--------- + autovacuum_naptime | 30 + (1 row) + + select name, setting from pg_settings where name = 'search_path'; + name | setting + -------------+--------- + search_path | public + (1 row) + + select name, setting from pg_settings where name = 'default_tablespace'; + name | setting + --------------------+--------- + default_tablespace | + (1 row) + + select name, setting from pg_settings where name = 'checkpoint_timeout'; + name | setting + --------------------+--------- + checkpoint_timeout | 90 + (1 row) + + select name, setting from pg_settings where name = 'restart_after_crash'; + name | setting + ---------------------+--------- + restart_after_crash | on + (1 row) + + -- Reset the some of the parameters which are set in above + set persistent log_connections = default; + set persistent max_connections To default; + set persistent authentication_timeout To default; + set persistent synchronous_commit = default; + set persistent restart_after_crash To default; + -- reload the new configurations. + select pg_reload_conf(); + pg_reload_conf + ---------------- + t + (1 row) + + select pg_sleep(1); + pg_sleep + ---------- + + (1 row) + + select name, setting from pg_settings where name = 'log_connections'; + name | setting + -----------------+--------- + log_connections | on + (1 row) + + select name, setting from pg_settings where name = 'max_connections'; + name | setting + -----------------+--------- + max_connections | 100 + (1 row) + + select name, setting from pg_settings where name = 'authentication_timeout'; + name | setting + ------------------------+--------- + authentication_timeout | 60 + (1 row) + + select name, setting from pg_settings where name = 'seq_page_cost'; + name | setting + ---------------+--------- + seq_page_cost | 0.9 + (1 row) + + select name, setting from pg_settings where name = 'restart_after_crash'; + name | setting + ---------------------+--------- + restart_after_crash | on + (1 row) + + -- reconnect to see the new configurations + \c + show log_connections; + log_connections + ----------------- + off + (1 row) + + show max_connections; + max_connections + ----------------- + 100 + (1 row) + + show authentication_timeout; + authentication_timeout + ------------------------ + 1min + (1 row) + + show seq_page_cost; + seq_page_cost + --------------- + 0.9 + (1 row) + + show restart_after_crash; + restart_after_crash + --------------------- + on + (1 row) + + -- set the parameters again which are reset above. + set persistent log_connections To yes; + set persistent max_connections = 50; + set persistent authentication_timeout = 20; + set persistent synchronous_commit To off; + set persistent restart_after_crash To yes; + -- reload the new configurations. + select pg_reload_conf(); + pg_reload_conf + ---------------- + t + (1 row) + + select pg_sleep(1); + pg_sleep + ---------- + + (1 row) + + show log_connections; + log_connections + ----------------- + off + (1 row) + + show max_connections; + max_connections + ----------------- + 100 + (1 row) + + show authentication_timeout; + authentication_timeout + ------------------------ + 20s + (1 row) + + show seq_page_cost; + seq_page_cost + --------------- + 0.9 + (1 row) + + show restart_after_crash; + restart_after_crash + --------------------- + on + (1 row) + + -- reconnect to see the new configurations + \c + show log_connections; + log_connections + ----------------- + on + (1 row) + + show max_connections; + max_connections + ----------------- + 100 + (1 row) + + show authentication_timeout; + authentication_timeout + ------------------------ + 20s + (1 row) + + show seq_page_cost; + seq_page_cost + --------------- + 0.9 + (1 row) + + show restart_after_crash; + restart_after_crash + --------------------- + on + (1 row) + + -- set a configuration param inside a function + create or replace function persistent_func() returns integer as + $$ + begin + set persistent enable_hashagg=off; + return 1; + end; + $$ Language plpgsql; + select persistent_func(); + ERROR: SET PERSISTENT cannot be executed from a function or multi-command string + CONTEXT: SQL statement "set persistent enable_hashagg=off" + PL/pgSQL function persistent_func() line 3 at SQL statement + show enable_hashagg; + enable_hashagg + ---------------- + on + (1 row) + + select pg_reload_conf(); + pg_reload_conf + ---------------- + t + (1 row) + + select pg_sleep(1); + pg_sleep + ---------- + + (1 row) + + show enable_hashagg; + enable_hashagg + ---------------- + on + (1 row) + + create or replace function persistent_func() returns integer as + $$ + begin + set persistent enable_hashagg=default; + return 1; + end; + $$ Language plpgsql; + select persistent_func(); + ERROR: SET PERSISTENT cannot be executed from a function or multi-command string + CONTEXT: SQL statement "set persistent enable_hashagg=default" + PL/pgSQL function persistent_func() line 3 at SQL statement + select pg_reload_conf(); + pg_reload_conf + ---------------- + t + (1 row) + + select pg_sleep(1); + pg_sleep + ---------- + + (1 row) + + show enable_hashagg; + enable_hashagg + ---------------- + on + (1 row) + + Drop function persistent_func(); + -- Reset all parameters which are set at above. + set persistent log_connections To default; + set persistent max_connections = default; + set persistent wal_level To default; + set persistent authentication_timeout To default; + set persistent vacuum_cost_delay = default; + set persistent bgwriter_lru_multiplier To default; + set persistent full_page_writes To default; + set persistent synchronous_commit = default; + set persistent synchronous_standby_names To default; + set persistent seq_page_cost To default; + set persistent autovacuum_naptime = default; + set persistent search_path To default; + set persistent default_tablespace To default; + set persistent checkpoint_timeout To default; + set persistent restart_after_crash = default; + select pg_reload_conf(); + pg_reload_conf + ---------------- + t + (1 row) + + select pg_sleep(1); + pg_sleep + ---------- + + (1 row) + + -- Normal user tries to set + create user test password 'test'; + SET SESSION AUTHORIZATION test; + set persistent enable_seqscan=off; + ERROR: must be superuser to execute SET PERSISTENT command + \c - + drop user test; + -- In a transaction block tries to set + start transaction; + set persistent enable_hashagg=off; + ERROR: SET PERSISTENT cannot run inside a transaction block + commit; + start transaction; + set persistent enable_hashagg=default; + ERROR: SET PERSISTENT cannot run inside a transaction block + commit; *** a/src/test/regress/parallel_schedule --- b/src/test/regress/parallel_schedule *************** *** 108,111 **** test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combo test: plancache limit plpgsql copy2 temp domain rangefuncs prepare without_oid conversion truncate alter_table sequence polymorphism rowtypes returning largeobject with xml # run stats by itself because its delay may be insufficient under heavy load ! test: stats --- 108,114 ---- test: plancache limit plpgsql copy2 temp domain rangefuncs prepare without_oid conversion truncate alter_table sequence polymorphism rowtypes returning largeobject with xml # run stats by itself because its delay may be insufficient under heavy load ! test: stats ! ! # run persistent by itself because it modifies the configuration parameters ! test: persistent *** a/src/test/regress/serial_schedule --- b/src/test/regress/serial_schedule *************** *** 135,137 **** test: largeobject --- 135,138 ---- test: with test: xml test: stats + test: persistent *** /dev/null --- b/src/test/regress/sql/persistent.sql *************** *** 0 **** --- 1,215 ---- + -- parameter which cannot be set after connection start. + set persistent log_connections=on; + + -- parameter can only be set at during server start. + set persistent max_connections To 10; + set persistent wal_level = hot_standby; + + -- parameter can set by the backend + set persistent authentication_timeout = 10; + set persistent vacuum_cost_delay To 200; + set persistent bgwriter_lru_multiplier = 3.2; + set persistent full_page_writes = off; + set persistent synchronous_commit To off; + set persistent synchronous_standby_names = 'standby1, standby2'; + set persistent seq_page_cost = 0.9; + set persistent autovacuum_naptime To 30; + set persistent search_path = 'public'; + set persistent default_tablespace = ''; + set persistent checkpoint_timeout = 1; + set persistent checkpoint_timeout = 3700; + set persistent checkpoint_timeout = 90; + set persistent restart_after_crash TO on; + + + show log_connections; + + show max_connections; + show wal_level; + + show authentication_timeout; + show vacuum_cost_delay; + show bgwriter_lru_multiplier; + show full_page_writes; + show synchronous_commit; + show synchronous_standby_names; + show seq_page_cost; + show autovacuum_naptime; + show search_path; + show default_tablespace; + show checkpoint_timeout; + show restart_after_crash; + + -- reload the new configurations. + select pg_reload_conf(); + select pg_sleep(1); + + show log_connections; + + show max_connections; + show wal_level; + + show authentication_timeout; + show vacuum_cost_delay; + show bgwriter_lru_multiplier; + show full_page_writes; + show synchronous_commit; + show synchronous_standby_names; + show seq_page_cost; + show autovacuum_naptime; + show search_path; + show default_tablespace; + show checkpoint_timeout; + show restart_after_crash; + + + -- reconnect to see the new configurations + \c + + select name, setting from pg_settings where name = 'log_connections'; + + select name, setting from pg_settings where name = 'max_connections'; + select name, setting from pg_settings where name = 'wal_level'; + + select name, setting from pg_settings where name = 'authentication_timeout'; + select name, setting from pg_settings where name = 'vacuum_cost_delay'; + select name, setting from pg_settings where name = 'bgwriter_lru_multiplier'; + select name, setting from pg_settings where name = 'full_page_writes'; + select name, setting from pg_settings where name = 'synchronous_commit'; + select name, setting from pg_settings where name = 'synchronous_standby_names'; + select name, setting from pg_settings where name = 'seq_page_cost'; + select name, setting from pg_settings where name = 'autovacuum_naptime'; + select name, setting from pg_settings where name = 'search_path'; + select name, setting from pg_settings where name = 'default_tablespace'; + select name, setting from pg_settings where name = 'checkpoint_timeout'; + select name, setting from pg_settings where name = 'restart_after_crash'; + + -- Reset the some of the parameters which are set in above + set persistent log_connections = default; + + set persistent max_connections To default; + + set persistent authentication_timeout To default; + set persistent synchronous_commit = default; + set persistent restart_after_crash To default; + + -- reload the new configurations. + select pg_reload_conf(); + select pg_sleep(1); + + select name, setting from pg_settings where name = 'log_connections'; + select name, setting from pg_settings where name = 'max_connections'; + select name, setting from pg_settings where name = 'authentication_timeout'; + select name, setting from pg_settings where name = 'seq_page_cost'; + select name, setting from pg_settings where name = 'restart_after_crash'; + + -- reconnect to see the new configurations + \c + + show log_connections; + show max_connections; + show authentication_timeout; + show seq_page_cost; + show restart_after_crash; + + -- set the parameters again which are reset above. + set persistent log_connections To yes; + + set persistent max_connections = 50; + + set persistent authentication_timeout = 20; + set persistent synchronous_commit To off; + set persistent restart_after_crash To yes; + + -- reload the new configurations. + select pg_reload_conf(); + select pg_sleep(1); + + show log_connections; + show max_connections; + show authentication_timeout; + show seq_page_cost; + show restart_after_crash; + + -- reconnect to see the new configurations + \c + show log_connections; + show max_connections; + show authentication_timeout; + show seq_page_cost; + show restart_after_crash; + + -- set a configuration param inside a function + create or replace function persistent_func() returns integer as + $$ + begin + set persistent enable_hashagg=off; + return 1; + end; + $$ Language plpgsql; + + select persistent_func(); + + show enable_hashagg; + + select pg_reload_conf(); + select pg_sleep(1); + + show enable_hashagg; + + create or replace function persistent_func() returns integer as + $$ + begin + set persistent enable_hashagg=default; + return 1; + end; + $$ Language plpgsql; + + select persistent_func(); + + select pg_reload_conf(); + select pg_sleep(1); + + show enable_hashagg; + + Drop function persistent_func(); + + -- Reset all parameters which are set at above. + set persistent log_connections To default; + + set persistent max_connections = default; + set persistent wal_level To default; + + set persistent authentication_timeout To default; + set persistent vacuum_cost_delay = default; + set persistent bgwriter_lru_multiplier To default; + set persistent full_page_writes To default; + set persistent synchronous_commit = default; + set persistent synchronous_standby_names To default; + set persistent seq_page_cost To default; + set persistent autovacuum_naptime = default; + set persistent search_path To default; + set persistent default_tablespace To default; + set persistent checkpoint_timeout To default; + set persistent restart_after_crash = default; + + select pg_reload_conf(); + select pg_sleep(1); + + + -- Normal user tries to set + create user test password 'test'; + SET SESSION AUTHORIZATION test; + set persistent enable_seqscan=off; + \c - + drop user test; + + -- In a transaction block tries to set + start transaction; + set persistent enable_hashagg=off; + commit; + + start transaction; + set persistent enable_hashagg=default; + commit; +