Alpha

These pages are built from main, turborel 0.0.0. Anything may change from one build to the next: SQL, API, protocol and files alike.

SET DATABASE

Statements

NAME

SET DATABASE: change a database setting (extension)

SYNOPSIS

<set database statement> ::=
    SET DATABASE <setting name> { = | TO } { <value specification> | DEFAULT }

<setting name> ::= <identifier> [ { . <identifier> }... ]

The <value specification> is a literal, USER, CURRENT_USER, SESSION_USER or SYSTEM_USER, or, in a statement prepared, a ?.

DESCRIPTION

SET DATABASE sets the database's value of a setting, for every session, and keeps it with the database's definitions, so it holds when the database is opened again. DEFAULT gives the setting its default value again. A setting's name is taken in any case. A session that set its own value keeps it, over the database's.

When a change takes effect depends on the setting (INFORMATION_SCHEMA.SETTINGS, TAKES_EFFECT): for each statement or transaction that begins after it; for each connection the server takes after it; when the server starts next or the database is next opened, which IS_PENDING then shows waiting; or, for a few, only as the database is created in its directory, after which they are fixed, and never of a database kept in memory. Appendix C lists every setting.

RULES

  1. Its user is the database's owner.
  2. No transaction is active.
  3. The setting exists, and is not one fixed once the database is created, nor one fixed at creation of a database kept in memory that holds a schema.
  4. The value is of the setting's kind and range; a name it gives, of an engine, an index method, a hash function or encodings, is registered.

PRIVILEGES

The database's owner alone.

ERRORS

SQLSTATE SQLCODE When
2A000 -104 Its user is not the database's owner
25000 -917 A transaction is active
2A000 -103 There is no such setting, or it is fixed once the database is created
22000 -301 The value is not of the setting's kind or range, or names what is not registered

EXAMPLES

Run by the database's owner, ANN:

SET DATABASE STATEMENT.TIMEOUT_MS = 60000;
SELECT SETTING_VALUE, VALUE_SOURCE FROM INFORMATION_SCHEMA.SETTINGS WHERE SETTING_NAME = 'STATEMENT.TIMEOUT_MS';
COMMIT;
SET DATABASE STATEMENT.TIMEOUT_MS TO DEFAULT;
OK
 SETTING_VALUE                                                | VALUE_SOURCE
--------------------------------------------------------------+--------------
 60000                                                        | DATABASE
(1 row)
OK
OK

The SELECT began a transaction, so COMMIT ends it before SET DATABASE runs again.

Run by BOB, who does not own the database:

SET DATABASE STATEMENT.TIMEOUT_MS = 1;
turborel: SQLCODE -104 (SQLSTATE 2A000): only the database's owner runs SET DATABASE, and BOB is not it

STANDARD

An extension: the standards define no settings.

SEE ALSO

SET, CREATE USER, Appendix C: Settings, Settings