# Session settings

Session settings in CrateDB allow users to configure behavior and preferences **for the duration of the current client session**. These settings do not affect other sessions or users unless configured at the role level.

***

### Table of Contents

* Usage
* Viewing Current Settings
* Persisting Settings Across Sessions
* Supported Session Settings
  * Modifiable Settings
  * Read-only Settings

***

### Usage

To modify a session setting temporarily (for the current session only), use the `SET` command:

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

> 💡 You can also use `SET LOCAL` to limit the setting change to the duration of a single transaction block.

***

### Viewing Current Settings

To retrieve the current value of a setting, use:

```sql
SHOW search_path;
```

Alternatively, you can use the `current_setting()` scalar function:

```sql
SELECT current_setting('search_path');
```

***

### Persisting Settings Across Sessions

To **permanently override default values** for a specific user or role, use:

```sql
ALTER ROLE your_user_name SET search_path TO myschema, doc;
```

> 🔍 All active settings for the current session can be viewed in the `sys.sessions` system table.

***

### Supported Session Settings

#### Modifiable Settings

| Setting                          | Default           | Description                                                                                             |
| -------------------------------- | ----------------- | ------------------------------------------------------------------------------------------------------- |
| `search_path`                    | `pg_catalog, doc` | List of schemas to search for unqualified relation names. CrateDB checks them in order.                 |
| `application_name`               | `null`            | Optional name to identify the application connecting to CrateDB. Often set by clients automatically.    |
| `statement_timeout`              | `0`               | Maximum duration for any statement (in ms). A value of 0 means no timeout.                              |
| `memory.operation_limit`         | `0`               | Max memory (in bytes) allowed per operation. `0` means unlimited (only global circuit breaker applies). |
| `enable_hashjoin`                | `true`            | Enables evaluation of `JOIN` using Hash Join instead of Nested Loops. Experimental feature.             |
| `error_on_unknown_object_key`    | `true`            | Throws an error on unknown keys in dynamic objects; set to `false` to return `null` instead.            |
| `datestyle`                      | `ISO`             | Format for displaying date/time. Only ISO is supported; other formats will raise an error.              |
| `optimizer`                      | `true`            | Enables/disables specific optimizer rules. Must be set using the full rule name.                        |
| `optimizer_eliminate_cross_join` | `true`            | Enables/disables the rule that eliminates unnecessary cross joins.                                      |

**Examples**

* Set a statement timeout to 50 seconds:

  ```sql
  cr> SET LOCAL statement_timeout = '50000ms';
  ```
* Limit memory usage of a single operation to 1GB:

  ```sql
  cr> SET LOCAL "memory.operation_limit" = '1073741824';
  ```
* Disable a specific optimizer rule:

  ```sql
  cr> SET optimizer_rewrite_collect_to_get = false;
  ```

***

#### Read-only Settings

These are informational settings that **cannot be modified** during a session. They are available for compatibility or introspection.

| Setting                       | Default  | Description                                                                 |
| ----------------------------- | -------- | --------------------------------------------------------------------------- |
| `max_index_keys`              | `32`     | Maximum number of index keys. No effect in CrateDB. For compatibility only. |
| `max_identifier_length`       | `255`    | Maximum length (in bytes) of identifiers.                                   |
| `server_version_num`          | `100500` | Emulated PostgreSQL server version (numeric).                               |
| `server_version`              | `10.5`   | Emulated PostgreSQL server version (string).                                |
| `standard_conforming_strings` | `on`     | Treats `'...'` strings as literal, with backslashes taken literally.        |

***

### Notes & Compatibility

* `search_path` mirrors the PostgreSQL behavior and always includes `pg_catalog` unless already explicitly defined.
* `datestyle`, `max_index_keys`, and similar PostgreSQL compatibility settings are mostly **placeholders** in CrateDB and should not be relied upon for behavior customization.
* Experimental settings like `enable_hashjoin` and `memory.operation_limit` are powerful but should be used with caution.
