Reference: Set/Show Setting Statement

The SHOW, SET, and RESET statements allow you to inspect and change database and session settings. Changes made with SET are transient. To persist a configuration change across restarts, use ALTER SYSTEM instead. To set defaults for a role or database, use ALTER ROLE ... SET or ALTER DATABASE ... SET.

Usage example:

SHOW TimeZone;

   timezone    
---------------
 Europe/Berlin
(1 row)

SET timezone='US/Pacific';

To restore a setting to its default value, use RESET or SET ... TO DEFAULT:

RESET TimeZone;
SET timezone TO DEFAULT;

If a default for the setting is configured for the current role or database, RESET restores that default.

Syntax

SET [ SESSION ] <parameter> { TO | = } { <value> | '<value>' | DEFAULT }
SET TIME ZONE { '<time_zone>' | LOCAL | DEFAULT }
RESET { <parameter> | ALL }
SHOW { <parameter> | ALL }

A setting stays in effect until the end of the session, or until you change it again. RESET ALL restores all session settings to their defaults. SET is transactional: if the surrounding transaction or savepoint rolls back, the previous value is restored.

BEGIN;
SET statement_timeout = '3s';
ROLLBACK;
SHOW statement_timeout;

 statement_timeout 
-------------------
 0ms
(1 row)

SET TIME ZONE 'UTC' is an alternative spelling of SET timezone = 'UTC'.

Some parameters are read-only, e.g., server_version or standard_conforming_strings, and fail with cannot change configuration parameter "<name>".

Functions and views

current_setting('<parameter>') returns the value of a setting, and set_config('<parameter>', '<value>', false) changes it, like SET:

SELECT set_config('search_path', 'public', false);
SELECT current_setting('search_path');

current_setting('<parameter>', true) returns an empty string instead of failing when the parameter does not exist.

The pg_settings system view lists all parameters with their current values.

Timeouts

CedarDB enforces these timeouts:

SET statement_timeout = '30s';

Values without a unit are milliseconds. Both timeouts default to 0 (disabled).

Show all settings

SHOW ALL;

              name              |        setting        |                       description
--------------------------------+-----------------------+---------------------------------------------------------
 allow_system_table_mods        | off                   | Allows modifications of the structure of system tables.
 ...
 default_transaction_isolation  | repeatable read       | Sets the transaction isolation level of each new transaction.
 ...
 timezone                       | Europe/Berlin         | Sets the time zone for displaying and interpreting time stamps.
 ...
(n rows)

Permissions

As in PostgreSQL, the context column of pg_settings shows who can change a setting:

• user: any role can change the setting, e.g., statement_timeout, search_path, or TimeZone.
• superuser: changing the setting requires superuser privileges. This applies to CedarDB’s debug settings (debug.*), and to settings such as verbosity or compilationmode.
• internal: the setting cannot be changed in a session.

PostgreSQL Differences

• SET LOCAL and transaction-local set_config(..., true) are not supported.
• Custom parameters with a prefix, such as SET myapp.tenant = '42', are not supported.
• current_setting('<parameter>', true) returns an empty string, not NULL, for unknown parameters.
• SET NAMES and SET TIME ZONE INTERVAL ... are not supported. client_encoding only supports UTF8.
• Some parameters cannot be changed per session and fail with cannot change configuration parameter, e.g., work_mem, lock_timeout, default_transaction_isolation, transaction_isolation, enable_seqscan, and synchronous_commit.
• idle_session_timeout is not supported.
• In pg_settings, the columns category, vartype, source, unit, min_val, max_val, boot_val, and reset_val are empty.