Session Parameters

Session-level configuration with SET/SHOW.

SET variable = value;           -- session-scoped
SET LOCAL variable = value;     -- transaction-scoped
SHOW variable;
SHOW ALL;                       -- list all parameters
RESET variable;
RESET ALL;

-- Functions
SELECT current_setting('variable_name');
SELECT set_config('variable_name', 'value', false);

SHOW ALL is the authoritative list — it reports every parameter this server recognises, with its current value and, where one exists, a description.

SHOW never errors on a namespaced parameter — even a misspelled one

For an unqualified name, SHOW rejects what it does not know:

SHOW bogus_control_xyz;
-- ERROR:  unrecognized configuration parameter "bogus_control_xyz"

But any name containing a dot — the db9.*, embedding.* and hnsw.* families below — is treated as a custom namespace, so SHOW returns an empty string instead of failing:

SHOW db9.max_sort_bytes;   -- 134217728
SHOW db9.max_sort_byte;    -- (empty) — typo, no error
SHOW anything.at.all;      -- (empty) — no error

A typo therefore reads as “set to nothing” rather than “does not exist”. Use current_setting('db9.max_sort_byte'), which raises unrecognized configuration parameter, when you need to confirm a namespaced parameter actually exists.

PostgreSQL-Compatible Parameters

Parameter	Default	Mutable	Description
`application_name`	`''`	Yes	Application name for the session
`bytea_output`	`hex`	Yes	Output format for bytea values
`client_encoding`	`UTF8`	Yes	Client-side character encoding
`client_min_messages`	`notice`	Yes	Minimum message severity sent to client
`datestyle`	`ISO, MDY`	No	Date display format
`default_transaction_isolation`	`read committed`	Yes	Default isolation level for subsequent transactions
`default_transaction_read_only`	`off`	Yes	Default read-only state for new transactions; writes are rejected with SQLSTATE `25006` — see Transactions
`extra_float_digits`	`1`	Yes	Extra precision for floating-point output
`search_path`	`$user, public`	Yes	Schema search order
`server_encoding`	`UTF8`	No	Server-side character encoding
`server_version`	`16.0`	No	Reported PostgreSQL version
`standard_conforming_strings`	`on`	No	Treat backslashes literally in strings. `SET standard_conforming_strings = off` is rejected — the parser always treats backslashes literally
`statement_timeout`	`60000ms`	Yes	Query timeout (0 = no limit)
`timezone`	`UTC`	Yes	Session timezone
`transaction_isolation`	`read committed`	Yes	Current transaction isolation level
`lock_timeout`	`0`	Yes	Lock acquisition timeout
`password_encryption`	`scram-sha-256`	Yes	Password hashing algorithm
`max_identifier_length`	`63`	No	Maximum identifier length
`lc_messages`	`C`	Yes	Locale for messages

Other accepted PostgreSQL parameters: default_table_access_method, default_tablespace, default_text_search_config, default_transaction_deferrable, idle_in_transaction_session_timeout, in_hot_standby, integer_datetimes, intervalstyle, lc_monetary, lc_numeric, lc_time, max_index_keys, row_security, xmloption.

Also readable with SHOW, reporting server state rather than a session preference: is_superuser, listen_addresses, max_connections (1000), port (5432), role, server_version_num (160000), session_authorization, transaction_deferrable, and transaction_read_only.

Two parameters are accepted but inert: work_mem (4MB) and check_function_bodies (on). A SET succeeds and SHOW reports the value you just set, but neither changes any behaviour — the same pattern as db9.use_optimizer below. For sort memory the parameter that does take effect is db9.max_sort_bytes:

SET work_mem = '64MB';            -- accepted; SHOW returns 64MB; changes nothing
SET db9.max_sort_bytes = 1048576; -- this is the real knob (default 134217728)

See sort memory limits.

check_function_bodies has nothing to switch off: DB9 does not validate a LANGUAGE sql function body at CREATE FUNCTION time under either setting. A body that cannot parse is stored, and fails only when the function is first called, so test every function you create:

CREATE FUNCTION broken() RETURNS int AS $$ this is not valid sql $$ LANGUAGE sql;
-- CREATE FUNCTION  (accepted with check_function_bodies both on and off)

SELECT broken();
-- ERROR: syntax error: sql parser error: Expected an SQL statement, found: this  (42601)

PostgreSQL rejects that CREATE FUNCTION outright when check_function_bodies is on.

LANGUAGE plpgsql bodies are checked for block structure and for the types named in declarations, but not for the SQL inside them. A missing BEGIN, an IF without END IF, an assignment with no expression, or a misspelled RAISE level is rejected at CREATE FUNCTION with 42601 (for example PL/pgSQL function must have BEGIN block), and a DECLARE of an unknown type with 42704; in either case the function is not created. The SQL statements inside the block are not checked, so the same advice applies:

CREATE FUNCTION broken_pl() RETURNS int AS $$ BEGIN SELEC 1; RETURN 1; END $$ LANGUAGE plpgsql;
-- CREATE FUNCTION

SELECT broken_pl();
-- ERROR: syntax error: sql parser error: Expected an SQL statement, found: SELEC  (42601)

PostgreSQL rejects that CREATE FUNCTION too.

session_replication_role is rejected, not honoured

SET session_replication_role = 'replica' — the usual way to suppress triggers during a bulk load — fails with:

ERROR:  session_replication_role is not supported; triggers always fire as in "origin" mode

Only origin is accepted (as a no-op). ALTER TABLE ... DISABLE TRIGGER is not available either (syntax error ... found: DISABLE), and pg_trigger.tgenabled is always O. Dropping the trigger for the duration of the load and recreating it afterwards is the only route.

DB9-Specific Parameters

Parameter	Default	Description
`db9.hash_join_work_mem`	`67108864` (64 MB)	Memory limit for the hash join build side (0 = unlimited)
`db9.dml_table_scan_max_rows`	`10000`	Max rows per auxiliary source for UPDATE FROM / DELETE USING (0 = unlimited)
`db9.max_sort_bytes`	`134217728` (128 MB)	Memory limit for sort operations
`db9.prepared_plan_cache_size`	`128`	Prepared statement plan cache size
`db9.prepared_plan_cache_min_exec`	`5`	Min executions before plan cache promotion
`db9.retry_max_attempts`	`64`	Max retry attempts for autocommit DML/DDL on write conflict
`db9.retry_timeout`	`0`	Max wall-time for retries per statement (0 = no limit)
`db9.use_optimizer`	`on`	Cost-based optimizer (always on; `SET ... = off` is accepted but is a no-op)
`db9.password_grace_seconds`	`0`	Grace period in seconds during which the old password still works after `ALTER ROLE ... WITH PASSWORD` (0 = immediate replacement)
`db9.enable_cop_pushdown`	`off`	Enable coprocessor pushdown planning

Embedding Parameters

Parameter	Default	Description
`embedding.provider`	platform-provided	Embedding provider: `openai` or `bedrock` (session override)
`embedding.endpoint`	platform-provided	Embedding service endpoint URL (session override)
`embedding.model`	platform-provided	Model name
`embedding.api_key`	platform-provided, redacted as `****`	Embedding service API key (session override)
`embedding.dimensions`	platform-provided	Output vector dimensions
`embedding.concurrency`	`5`	Max concurrent embedding calls
`embedding.max_calls`	`100`	Max embedding calls per statement

Every embedding.* parameter already has a working value on a new database — the built-in embedding service needs no configuration. Setting them overrides the platform default for the session. Read the live values with SHOW embedding.provider / SHOW embedding.model rather than assuming they are unset; embedding.api_key always reads back as ****.

Vector Index Parameters

Parameter	Default	Description
`hnsw.ef_search`	`40`	Dynamic candidate list size for HNSW index search