Session settings

Session settings only apply to the currently connected client session.

Usage

To configure a modifiable session setting, use SET, for example:

cr> SET search_path TO myschema, doc;
SET OK, 0 rows affected (... sec)

Alternatively, session settings can be modified permanently from their default values for a user, with the use of ALTER ROLE.

To retrieve the current value of a session setting, use SHOW e.g:

SHOW search_path;

Besides using SHOW, it is also possible to use the current_setting scalar function.

Note

All the active settings for a session can also be retrieved from the sys.sessions table.

Note

Default values for session settings can set per role using ALTER ROLE.

Supported session settings

search_path

Default: pg_catalog, doc

Modifiable: yes

The list of schemas to be searched when a relation is referenced without a schema.

CrateDB will try to resolve an unqualified relation name against the configured search_path by iterating over the configured schemas in the order they were declared. The first matching relation in the search_path is used. CrateDB will report an error if there is no match.

Note

This setting mirrors the PostgreSQL search_path setting.

Some PostgreSQL clients require access to various tables in the pg_catalog schema. Usually, this is to extract information about built-in data types or functions.

CrateDB implements the system pg_catalog schema and it automatically includes it in the search_path before the configured schemas, unless it is already explicitly in the schema configuration.

application_name

Default: null

Modifiable: yes

An arbitrary application name that can be set to identify an application that connects to a CrateDB node.

Some clients set this implicitly to their client name.

statement_timeout

Default: '0'

Modifiable: yes

The maximum duration of any statement in milliseconds before it gets cancelled. If 0 (the default), queries are allowed to run infinitely and don’t get cancelled automatically.

The value is an INTERVAL with a maximum of 2147483647 milliseconds. That’s roughly 24 days.

Example statement to update the default value to 50 seconds, i.e. 50,000ms:

cr> SET LOCAL statement_timeout = '50000ms';
SET OK, 0 rows affected (... sec)

memory.operation_limit

Default: 0

Modifiable: yes

This is an experimental expert setting defining the maximal amount of memory in bytes that an individual operation can consume before triggering an error.

0 means unlimited. In that case only the global circuit breaker limits apply.

There is no 1:1 mapping from SQL statement to operation. Some SQL statements have no corresponding operation. Other SQL statements can have more than one operation. You can use the sys.operations view to get some insights, but keep in mind that both, operations which are used to execute a query, and their name could change with any release, including hotfix releases.

Example statement to update the default value to 1GB, i.e. 1073741824 bytes:

cr> SET LOCAL "memory.operation_limit" = '1073741824';
SET OK, 0 rows affected (... sec)

Operations that hit this memory limit will trigger a CircuitBreakerException that can be handled in the application to inform the user about too much memory consumption for the particular query.

enable_hashjoin

Default: true

Modifiable: yes

An experimental setting which enables CrateDB to consider whether a JOIN operation should be evaluated using the HashJoin implementation instead of the Nested-Loops implementation.

Note

It is not always possible or efficient to use the HashJoin implementation. Having this setting enabled, will only add the option of considering it, it will not guarantee it. See also the available join algorithms for more insights on this topic.

error_on_unknown_object_key

Default: true

Modifiable: yes

This setting controls the behaviour of querying unknown object keys to dynamic objects. CrateDB will throw an error by default if any of the queried object keys are unknown or will return a null if the setting is set to false.

datestyle

Default: ISO

Modifiable: yes

Shows the display format for date and time values. Only the ISO style is supported. Optionally provided pattern conventions for the order of date parts (Day, Month, Year) are ignored.

Note

The session setting currently has no effect in CrateDB and exists for compatibility with PostgreSQL. Trying to set this to a date format style other than ISO will raise an exception.

max_index_keys

Default: 32

Modifiable: no

Shows the maximum number of index keys.

Note

The session setting has no effect in CrateDB and exists for compatibility with PostgreSQL.

max_identifier_length

Default: 255

Modifiable: no

Shows the maximum length of identifiers in bytes.

server_version_num

Default: 100500

Modifiable: no

Shows the emulated PostgreSQL server version.

server_version

Default: 10.5

Modifiable: no

Shows the emulated PostgreSQL server version.

standard_conforming_strings

Default: on

Modifiable: no

Causes '...' strings to treat backslashes literally.

optimizer

Default: true

Modifiable: yes

This setting indicates whether a query optimizer rule is activated. The name of the query optimizer rule has to be provided as a suffix as part of the setting e.g. SET optimizer_rewrite_collect_to_get = false.

Note

The optimizer setting is for advanced use only and can significantly impact the performance behavior of the queries.

optimizer_eliminate_cross_join

Default: true

Modifiable: yes

This setting indicates if the cross join elimination rule of the optimizer rule is activated.

Warning

Experimental session settings might be removed in the future even in minor feature releases.

insert_select_fail_fast

Default: false

Modifiable: yes This setting enables partial failures of INSERT FROM SELECT statements. If enabled, the first error encountered gets propagated and the operation is terminated. The number of already written records is not reported.

client_encoding

Default: UTF8

Modifiable: yes

Sets the client encoding. Only supported value is UTF8.