Session settings¶
Table of contents
Session settings only apply to the currently connected client session.
Usage¶
To configure a modifiable session setting, use SET, for example:
SET search_path TO myschema, doc;
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.
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 thesearch_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 thesearch_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 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 of2147483647
milliseconds. That’s roughly 24 days.
- 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.
- enable_hashjoin
- Default:
true
Modifiable:yes
An experimental setting which enables CrateDB to consider whether a
JOIN
operation should be evaluated using theHashJoin
implementation instead of theNested-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 thanISO
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.