From f0811a74b37427d7ee5eee56b00f7f2ea323d7d6 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Fri, 17 May 2002 01:19:19 +0000 Subject: Merge the last few variable.c configuration variables into the generic GUC support. It's now possible to set datestyle, timezone, and client_encoding from postgresql.conf and per-database or per-user settings. Also, implement rollback of SET commands that occur in a transaction that later fails. Create a SET LOCAL var = value syntax that sets the variable only for the duration of the current transaction. All per previous discussions in pghackers. --- doc/src/sgml/ref/alter_database.sgml | 12 +-- doc/src/sgml/ref/alter_user.sgml | 19 ++-- doc/src/sgml/ref/reset.sgml | 13 ++- doc/src/sgml/ref/set.sgml | 191 +++++++++++++++++++++------------ doc/src/sgml/ref/set_session_auth.sgml | 12 ++- doc/src/sgml/ref/show.sgml | 23 +--- doc/src/sgml/release.sgml | 8 +- doc/src/sgml/runtime.sgml | 56 +++++++++- 8 files changed, 218 insertions(+), 116 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/ref/alter_database.sgml b/doc/src/sgml/ref/alter_database.sgml index a7e81d22d9a..76a4ac014af 100644 --- a/doc/src/sgml/ref/alter_database.sgml +++ b/doc/src/sgml/ref/alter_database.sgml @@ -1,5 +1,5 @@ @@ -28,10 +28,9 @@ ALTER DATABASE name RESET ALTER DATABASE is used to change the session default of a run-time configuration variable for a PostgreSQL database. Whenever a new - session is subsequently started in that database, SET - variable TO - value is effectively executed - before the start of the session. The database-specific default + session is subsequently started in that database, the specified + value becomes the session default value. + The database-specific default overrides whatever setting is present in postgresql.conf or has been received from the postmaster. @@ -64,7 +63,8 @@ ALTER DATABASE name RESET value is DEFAULT or, equivalently, RESET is used, the - database-specific variable setting is removed and the default + database-specific variable setting is removed and the system-wide + default setting will be inherited in new sessions. Use RESET ALL to clear all settings. diff --git a/doc/src/sgml/ref/alter_user.sgml b/doc/src/sgml/ref/alter_user.sgml index d8461c4f4fb..3cc82371aa9 100644 --- a/doc/src/sgml/ref/alter_user.sgml +++ b/doc/src/sgml/ref/alter_user.sgml @@ -1,5 +1,5 @@ @@ -48,14 +48,13 @@ ALTER USER username RESET - The second and the third variant change a user's session default of + The second and the third variant change a user's session default for a specified configuration variable. Whenever the user subsequently - starts a new session, SET - variable TO - value is effectively executed - before the start of the session. Ordinary users can change their - own session defaults. Superusers can change anyone's session - defaults. + starts a new session, the specified value becomes the session default, + overriding whatever setting is present in postgresql.conf + or has been received from the postmaster. + Ordinary users can change their own session defaults. + Superusers can change anyone's session defaults. @@ -135,12 +134,12 @@ ALTER USER username RESET value - Set this user's session default of the specified configuration + Set this user's session default for the specified configuration variable to the given value. If value is DEFAULT or, equivalently, RESET is used, the user-specific variable setting is removed and the user will - inherit the default setting in new sessions. Use + inherit the system-wide default setting in new sessions. Use RESET ALL to clear all settings. diff --git a/doc/src/sgml/ref/reset.sgml b/doc/src/sgml/ref/reset.sgml index 69c1f861ee6..9f78d9df6c1 100644 --- a/doc/src/sgml/ref/reset.sgml +++ b/doc/src/sgml/ref/reset.sgml @@ -1,5 +1,5 @@ @@ -37,7 +37,7 @@ RESET ALL ALL - Resets all run-time parameters to default values. + Resets all settable run-time parameters to default values. @@ -53,11 +53,18 @@ RESET ALL RESET restores run-time parameters to their default values. Refer to - for details. RESET is an alternate form for + for details. RESET is an alternate spelling for SET variable TO DEFAULT + + The default value is defined as the value that the variable would + have had, had no SET ever been issued for it in the + current session. The actual source of this value might be a + compiled-in default, the postmaster's configuration file or command-line + switches, or per-database or per-user default settings. See the + Administrator's Guide for details. diff --git a/doc/src/sgml/ref/set.sgml b/doc/src/sgml/ref/set.sgml index 0dd4c44db16..f54f70c1979 100644 --- a/doc/src/sgml/ref/set.sgml +++ b/doc/src/sgml/ref/set.sgml @@ -1,5 +1,5 @@ @@ -14,14 +14,40 @@ PostgreSQL documentation -SET variable { TO | = } { value | 'value' | DEFAULT } -SET TIME ZONE { 'timezone' | LOCAL | DEFAULT } +SET [ SESSION | LOCAL ] variable { TO | = } { value | 'value' | DEFAULT } +SET [ SESSION | LOCAL ] TIME ZONE { timezone | LOCAL | DEFAULT } Inputs + + + + + + Specifies that the command takes effect for the current session. + (This is the default if neither + + + + + + + + Specifies that the command takes effect for only the current + transaction. After COMMIT or ROLLBACK, + the session-level setting takes effect again. Note that + SET LOCAL will appear to have no effect if it's + executed outside a BEGIN block, since the transaction + will end immediately. + + + + variable @@ -30,6 +56,7 @@ SET TIME ZONE { 'timezone' | LOCAL + value @@ -49,34 +76,49 @@ SET TIME ZONE { 'timezone' | LOCAL Description + The SET command changes run-time configuration - parameters. The following parameters can be altered: + parameters. Many of the run-time parameters listed in the + Administrator's Guide can be changed on-the-fly + with SET. (But some require superuser privileges + to change, and others cannot be changed after server or session start.) + Note that SET only affects the value used by the + current session. + - - - CLIENT_ENCODING - NAMES - - - Sets the multibyte client encoding. The specified encoding - must be supported by the backend. - + + If SET or SET SESSION is issued + within a transaction that is later aborted, the effects of the + SET command disappear when the transaction is rolled + back. (This behavior represents a change from + PostgreSQL versions prior to 7.3, where + the effects of SET would not roll back after a later + error.) Once the surrounding transaction is committed, the effects + will persist until the end of the session, unless overridden by another + SET. + - - This option is only available if - PostgreSQL is build with multibyte - support. - - - + + 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 + a single transaction: the SET LOCAL value will be + seen until the end of the transaction, but afterwards (if the transaction + is committed) the SET value will take effect. + + + + Here are additional details about a few of the parameters that can be set: + + DATESTYLE Choose the date/time representation style. Two separate - settings are made: the default date/time output and the + settings are involved: the default date/time output and the interpretation of ambiguous input. @@ -159,28 +201,47 @@ SET TIME ZONE { 'timezone' | LOCAL - Date format initialization may be done by: + SET DATESTYLE affects interpretation of + input and provides several standard output formats. For + applications needing different variations or tighter control + over input or output, consider using + the to_char family of + functions. + + + + There are several now-deprecated means for setting the datestyle + in addition to the normal methods of setting it via SET or + a configuration-file entry: - Setting the PGDATESTYLE environment variable. - If PGDATESTYLE is set in the frontend environment of a client - based on libpq, libpq will automatically set DATESTYLE to the - value of PGDATESTYLE during connection start-up. + Setting the postmaster's PGDATESTYLE environment + variable. (This will be overridden by any of the other methods.) Running postmaster using the option to - set dates to the European convention. + select the European conventions. + (This overrides environment variables and configuration-file + entries.) + + + Setting the client's PGDATESTYLE environment variable. + If PGDATESTYLE is set in the frontend environment of a client + based on libpq, libpq will automatically set DATESTYLE to the + value of PGDATESTYLE during connection start-up. This is + equivalent to a manually issued SET. + + + + + NAMES + - SET DATESTYLE affects interpretation of - input and provides several standard output formats. For - applications needing different variations or tighter control - over input or output, consider using - the to_char family of - functions. + SET NAMES is an alias for SET CLIENT_ENCODING. @@ -199,23 +260,22 @@ SET TIME ZONE { 'timezone' | LOCAL The value for the seed to be used by the random function. Allowed values are floating-point numbers between 0 and 1, which - are then multiplied by 231-1. This product will - silently overflow if a number outside the range is used. - - - - The seed can also be set by invoking the - setseed SQL function: - - -SELECT setseed(value); - + are then multiplied by 231-1. + + The seed can also be set by invoking the + setseed SQL function: + + +SELECT setseed(value); + + + @@ -223,13 +283,9 @@ SELECT setseed(value); SERVER_ENCODING - Sets the multibyte server encoding. - - - - This option is only available if - PostgreSQL was built with multibyte - support. + Shows the server-side multibyte encoding. (At present, this + parameter can be shown but not set, because the encoding is + determined at initdb time.) @@ -241,18 +297,18 @@ SELECT setseed(value); Sets the default time zone for your session. Arguments can be an SQL time interval constant, an integer or double precision - constant, or a string representing a time zone supported by - the host operating system. + constant, or a string representing a time zone name recognized + by the host operating system. - The possible values for time zone depends on your operating + The available time zone names depend on your operating system. For example, on Linux /usr/share/zoneinfo contains the database of time zones. - Here are some valid values for time zone: + Here are some typical values for time zone names: @@ -279,6 +335,14 @@ SELECT setseed(value); + + + + + In addition to time zone names, PostgreSQL + accepts these other methods of specifying a time zone: + + 7 @@ -310,7 +374,7 @@ SELECT setseed(value); - If an invalid time zone is specified, the time zone + If an invalid time zone name is specified, the time zone becomes GMT (on most systems anyway). @@ -324,14 +388,9 @@ SELECT setseed(value); - - An extended list of other run-time parameters can be found in the - Administrator's Guide. - - Use to show the - current setting of a parameters. + current setting of a parameter. @@ -363,7 +422,7 @@ SELECT setseed(value); ERROR: permission denied - You must be a superuser to have access to certain settings. + You must be a superuser to alter certain settings. @@ -394,7 +453,7 @@ SET DATESTYLE TO PostgreSQL,European; Set the time zone for Berkeley, California, using quotes to - preserve the uppercase attributes of the time zone specifier (note + preserve the uppercase spelling of the time zone name (note that the date style is PostgreSQL for this example): @@ -437,8 +496,8 @@ SELECT CURRENT_TIMESTAMP AS today; only numeric time zone offsets while PostgreSQL allows full time zone specifier strings as well. All other SET - features are a - PostgreSQL extension. + features are + PostgreSQL extensions. diff --git a/doc/src/sgml/ref/set_session_auth.sgml b/doc/src/sgml/ref/set_session_auth.sgml index 7cd0d7d1ec7..dfb20357005 100644 --- a/doc/src/sgml/ref/set_session_auth.sgml +++ b/doc/src/sgml/ref/set_session_auth.sgml @@ -1,4 +1,4 @@ - + 2001-04-21 @@ -16,8 +16,8 @@ -SET SESSION AUTHORIZATION username -SET SESSION AUTHORIZATION DEFAULT +SET [ SESSION | LOCAL ] SESSION AUTHORIZATION username +SET [ SESSION | LOCAL ] SESSION AUTHORIZATION DEFAULT RESET SESSION AUTHORIZATION @@ -51,6 +51,12 @@ RESET SESSION AUTHORIZATION specifies the authenticated username. + + The + The DEFAULT and RESET forms reset the session and current user identifiers to be the originally authenticated user diff --git a/doc/src/sgml/ref/show.sgml b/doc/src/sgml/ref/show.sgml index 37e7e856525..b752f86ee61 100644 --- a/doc/src/sgml/ref/show.sgml +++ b/doc/src/sgml/ref/show.sgml @@ -1,5 +1,5 @@ @@ -54,7 +54,7 @@ SHOW ALL SHOW will display the current setting of a run-time parameter. These variables can be set using the - SET statement or are determined at server start. + SET statement or are determined at session start. @@ -72,25 +72,6 @@ SHOW ALL - - - ERROR: permission denied - - - You must be a superuser to be allowed to see certain settings. - - - - - - WARNING: Time zone is unknown - - - If the TZ or PGTZ environment - variable is not set. - - - diff --git a/doc/src/sgml/release.sgml b/doc/src/sgml/release.sgml index 72157ff271b..889f2203f69 100644 --- a/doc/src/sgml/release.sgml +++ b/doc/src/sgml/release.sgml @@ -1,5 +1,5 @@ @@ -24,6 +24,9 @@ CDATA means the content is "SGML-free", so you can write without worries about funny characters. --> diff --git a/doc/src/sgml/runtime.sgml b/doc/src/sgml/runtime.sgml index 70131be3a27..78d6dec0aae 100644 --- a/doc/src/sgml/runtime.sgml +++ b/doc/src/sgml/runtime.sgml @@ -1,5 +1,5 @@ @@ -488,11 +488,13 @@ psql: could not connect to server: Connection refused # This is a comment log_connections = yes syslog = 2 +search_path = '$user, public' As you see, options are one per line. The equal sign between name and value is optional. Whitespace is insignificant and blank lines are ignored. Hash marks (#) introduce comments - anywhere. + anywhere. Parameter values that are not simple identifiers or + numbers should be single-quoted. @@ -526,7 +528,7 @@ postmaster -c log_connections=yes -c syslog=2 env PGOPTIONS='-c geqo=off' psql - (This works for any client application, not just + (This works for any libpq-based client application, not just psql.) Note that this won't work for options that are fixed when the server is started, such as the port number. @@ -539,11 +541,17 @@ env PGOPTIONS='-c geqo=off' psql => SET ENABLE_SEQSCAN TO OFF; See the SQL command language reference for details on the syntax. + + + Furthermore, it is possible to assign a set of option settings to a user or a database. Whenever a session is started, the default settings for the user and database involved are loaded. The commands ALTER DATABASE and ALTER - USER, respectively, are used to configure these. + USER, respectively, are used to configure these settings. + Such per-database settings override anything received from the postmaster + or the configuration file, and in turn are overridden by per-user + settings. @@ -1091,6 +1099,34 @@ env PGOPTIONS='-c geqo=off' psql + + CLIENT_ENCODING (string) + character set encoding + + + Sets the client-side encoding for multibyte character sets. + The default is to use the database encoding. + + + This option is only available if + PostgreSQL was built with multibyte + support. + + + + + + DATESTYLE (string) + date style + + + Sets the display format for dates, as well as the rules for + interpreting ambiguous input dates. + The default is ISO, US. + + + + deadlock @@ -1586,6 +1622,18 @@ dynamic_library_path = '/usr/local/lib/postgresql:/home/my_project/lib:$libdir' + + TIMEZONE (string) + time zone + + + Sets the time zone for displaying and interpreting timestamps. + The default is to use whatever the system environment + specifies as the timezone. + + + + TRANSFORM_NULL_EQUALS (boolean) IS NULL -- cgit v1.2.3