SET
Sets the value of a server configuration parameter. Use the SET command to override a setting for the duration of the current session or transaction only.
Use the RESET command to return a parameter to its default value.
You can change the server configuration parameters in several ways. For more information, see Modifying the server configuration.
Syntax
SET { [ SESSION | LOCAL ] { SEED | parameter_name } { TO | = } { value | 'value' | DEFAULT } | SEED TO value }
The following statement sets the value of a session context variable.
SET { [ SESSION | LOCAL ] variable_name { TO | = } { value | 'value' }
Parameters
- SESSION
-
Specifies that the setting is valid for the current session. Default value.
- variable_name
-
Specifies the name of the context variable set for the session.
The naming convention is a two-part name separated by a dot, for example identifier.identifier. Only one dot separator is allowed. Use an identifier that follows the standard identifier rules for Amazon Redshift For more information, see Names and identifiers. Delimited identifiers aren't allowed.
- LOCAL
-
Specifies that the setting is valid for the current transaction.
- SEED TO value
-
Sets an internal seed to be used by the RANDOM function for random number generation.
SET SEED takes a numeric value between 0 and 1, and multiplies this number by (231-1) for use with the RANDOM function function. If you use SET SEED before making multiple RANDOM calls, RANDOM generates numbers in a predictable sequence.
- parameter_name
-
Name of the parameter to set. See Modifying the server configuration for information about parameters.
- value
-
New parameter value. Use single quotation marks to set the value to a specific string. If using SET SEED, this parameter contains the SEED value.
- DEFAULT
-
Sets the parameter to the default value.
Examples
Changing a parameter for the current session
The following example sets the datestyle:
set datestyle to 'SQL,DMY';
Setting a query group for workload management
If query groups are listed in a queue definition as part of the cluster's WLM configuration, you can set the QUERY_GROUP parameter to a listed query group name. Subsequent queries are assigned to the associated query queue. The QUERY_GROUP setting remains in effect for the duration of the session or until a RESET QUERY_GROUP command is encountered.
This example runs two queries as part of the query group 'priority', then resets the query group.
set query_group to 'priority'; select tbl, count(*)from stv_blocklist; select query, elapsed, substring from svl_qlog order by query desc limit 5; reset query_group;
For more information, see Workload management.
Change the default identity namespace for the session
A database user can set default_identity_namespace. This sample shows how to use SET SESSION to override the
setting for the duration of the current session and then show the new identity provider value. This is used most commonly when you are using an identity provider
with Redshift and IAM Identity Center. For more information about using an identity provider with Redshift, see Connect Redshift with IAM Identity Center to give users a single sign-on
experience.
SET SESSION default_identity_namespace = 'MYCO'; SHOW default_identity_namespace;
After running the command, you can run a GRANT statement or a CREATE statement like the following:
GRANT SELECT ON TABLE mytable TO alice; GRANT UPDATE ON TABLE mytable TO salesrole; CREATE USER bob password 'md50c983d1a624280812631c5389e60d48c';
In this instance, the effect of setting the default identity namespace is equivalent to prefixing each identity with the namespace. In this example, alice is replaced
with MYCO:alice. For more information about settings that pertain to Redshift configuration with IAM Identity Center, see ALTER SYSTEM and ALTER IDENTITY PROVIDER.
Setting a label for a group of queries
The QUERY_GROUP parameter defines a label for one or more queries that are run in the same session after a SET command. In turn, this label is logged when queries are run and can be used to constrain results returned from the STL_QUERY and STV_INFLIGHT system tables and the SVL_QLOG view.
show query_group; query_group ------------- unset (1 row) set query_group to '6 p.m.'; show query_group; query_group ------------- 6 p.m. (1 row) select * from sales where salesid=500; salesid | listid | sellerid | buyerid | eventid | dateid | ... ---------+--------+----------+---------+---------+--------+----- 500 | 504 | 3858 | 2123 | 5871 | 2052 | ... (1 row) reset query_group; select query, trim(label) querygroup, pid, trim(querytxt) sql from stl_query where label ='6 p.m.'; query | querygroup | pid | sql -------+------------+-------+---------------------------------------- 57 | 6 p.m. | 30711 | select * from sales where salesid=500; (1 row)
Query group labels are a useful mechanism for isolating individual queries or groups of queries that are run as part of scripts. You don't need to identify and track queries by their IDs; you can track them by their labels.
Setting a seed value for random number generation
The following example uses the SEED option with SET to cause the RANDOM function to generate numbers in a predictable sequence.
First, return three RANDOM integers without setting the SEED value first:
select cast (random() * 100 as int); int4 ------ 6 (1 row) select cast (random() * 100 as int); int4 ------ 68 (1 row) select cast (random() * 100 as int); int4 ------ 56 (1 row)
Now, set the SEED value to .25, and return three more RANDOM numbers:
set seed to .25; select cast (random() * 100 as int); int4 ------ 21 (1 row) select cast (random() * 100 as int); int4 ------ 79 (1 row) select cast (random() * 100 as int); int4 ------ 12 (1 row)
Finally, reset the SEED value to .25, and verify that RANDOM returns the
same results as the previous three calls:
set seed to .25; select cast (random() * 100 as int); int4 ------ 21 (1 row) select cast (random() * 100 as int); int4 ------ 79 (1 row) select cast (random() * 100 as int); int4 ------ 12 (1 row)
The following example sets a customized context variable.
SET app_context.user_id TO 123; SET app_context.user_id TO 'sample_variable_value';
