Session Parameters

Session Parameters

Session-level configuration with SET/SHOW.

SQL

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 lists every parameter this server recognises, with its current value and, where one exists, a description. It is not a check for whether a name is real, though — once a session has SET an unrecognised name, SHOW ALL reports that name too. Query pg_settings when you need the recognised-only list — see the caution below on misspelled names.

Session state belongs to a connection, and the HTTP SQL API opens a new one per request

Everything on this page is per-connection state. Over the PostgreSQL wire protocol that is a session in the familiar sense: a SET holds until you RESET it or disconnect. The HTTP SQL API — which is what db9 db sql uses — makes no such promise. Each request is its own connection, so a SET in one request is gone by the next:

Terminal

db9 db sql mydb -q "SET statement_timeout = 12345"   # SET
db9 db sql mydb -q "SHOW statement_timeout"          # 43200000ms — the default, not 12345

That is the boundary the HTTP API’s shape buys: requests are stateless and interchangeable, so no request has to be routed back to a particular backend and there is no pool affinity to manage. set_config('application_name', 'demo', false) and RESET are subject to the same rule.

When a statement depends on a parameter, send both in one request — the connection lives for the length of that request, so the SET applies to everything after it. Either spelling works:

Terminal

db9 db sql mydb -q "SET statement_timeout = 12345; SHOW statement_timeout"   # 12345ms
db9 db sql mydb -f session.sql                                               # same, from a file

For anything longer-lived than a single round trip — a migration that sets search_path, a bulk load that raises db9.max_sort_bytes — connect over the wire protocol instead (db9 db connect, psql, or any driver).

SET accepts a parameter name it does not recognise — including a misspelled one

SHOW is strict about an unqualified name it does not know, but SET is not, and whatever you wrote reads straight back:

SQL

SHOW bogus_control_xyz;
-- ERROR:  unrecognized configuration parameter "bogus_control_xyz"   (42704)

SET bogus_control_xyz = 'hello';   -- SET
SHOW bogus_control_xyz;            -- hello

So a typo in a SET is accepted silently while the parameter you meant keeps its old value:

SQL

SET statment_timeout = 1000;   -- SET — note the missing "e"
SHOW statment_timeout;         -- 1000
SHOW statement_timeout;        -- 43200000ms — the real parameter never changed

PostgreSQL rejects that same statement with unrecognized configuration parameter "statment_timeout".

Reading is looser again for any name containing a dot — the db9.*, embedding.* and hnsw.* families below. A dotted name is treated as a custom namespace, so SHOW returns an empty string rather than failing:

SQL

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

current_setting() is stricter, but only until something writes the name. In a fresh session SELECT current_setting('db9.max_sort_byte') raises unrecognized configuration parameter; after a typo’d SET db9.max_sort_byte = 5 in that same session it returns 5, so it confirms the typo instead of catching it.

The one check that does not degrade is pg_settings, which lists only the names this server actually recognises. SHOW ALL is not a substitute here: it reports an invented name back to you for the rest of the session, exactly as SHOW and current_setting() do.

SQL

SET zz_typo_param = 'oops';
SELECT name FROM pg_settings WHERE name = 'zz_typo_param';   -- 0 rows      <- catches it
SHOW ALL;                                                    -- lists zz_typo_param | oops

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	No	Client-side character encoding. A SET is accepted, but UTF8 (any case) is the only value it will take — see the note after this table
client_min_messages	notice	Yes	Minimum message severity sent to client
datestyle	ISO, MDY	No	Date display format. A SET is accepted, but only for ISO, MDY or the bare ISO — see the note after this table
default_transaction_isolation	read committed	Yes	Default isolation level for subsequent transactions. serializable is accepted but downgraded to repeatable read — over the wire protocol with WARNING 01000, over the HTTP SQL API silently. See 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	Accepted and echoed by SHOW, but inert — float output is always shortest-round-trip. Out-of-range values are accepted too, where PostgreSQL limits it to -15 .. 3
search_path	"$user", public	Yes	Schema search order. "$user" is reported literally, quotes included, and is not expanded to the current role — see the note after this table
server_encoding	UTF8	No	Server-side character encoding
server_version	17.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	43200000ms (12 h)	Yes	Query timeout; PostgreSQL’s default is 0 (no limit). For a role that is not a superuser, SET statement_timeout to 0 or above 12 h fails with 42501; a superuser such as admin can set either. Over the HTTP SQL API a request returns Error 504 after about 15 seconds whatever the setting
timezone	UTC	Yes	Session timezone. Governs wire output and to_char(), but not text casts — see the note after this table
transaction_isolation	read committed	Yes	Current transaction isolation level
lock_timeout	0	Yes	Lock acquisition timeout
password_encryption	scram-sha-256	No	Password hashing algorithm. A SET is accepted, but scram-sha-256 (any case) is the only value it will take — see the note after this table
max_identifier_length	63	No	Reported maximum identifier length. DB9 does not apply it: a longer table or column name is stored in full and must be referenced in full, where PostgreSQL truncates it to 63 bytes with a NOTICE
lc_messages	C	Yes	Locale for messages

Mutable: No covers two different refusals, and the error tells you which one you hit. A parameter that reports fixed server state rejects every value, including the one it already holds:

SQL

SET server_encoding = 'LATIN1';
-- ERROR:  parameter "server_encoding" cannot be changed   (55P02)

server_version and max_identifier_length behave the same way. client_encoding, datestyle, standard_conforming_strings and password_encryption are different: the SET path works, and succeeds for the value the parameter already holds, but every other value is refused:

SQL

SET client_encoding = 'UTF8';            -- SET  (also 'utf8')
SET client_encoding = 'LATIN1';
-- ERROR:  unsupported client_encoding 'LATIN1'; only UTF8 is supported   (0A000)

SET datestyle = 'ISO, MDY';              -- SET  (also 'ISO', any case)
SET datestyle = 'SQL, MDY';
-- ERROR:  invalid value for parameter "datestyle": "SQL, MDY"   (22023)

SET standard_conforming_strings = on;    -- SET
SET standard_conforming_strings = off;
-- ERROR:  standard_conforming_strings = off is not supported; the parser always treats backslashes literally   (0A000)

SET password_encryption = 'scram-sha-256';   -- SET
SET password_encryption = 'md5';
-- ERROR:  only password_encryption = scram-sha-256 is supported   (0A000)

PostgreSQL accepts SET client_encoding = 'LATIN1', every datestyle spelling, and password_encryption = 'md5'. DB9 stores and serves text as UTF-8 and emits ISO dates, and holding those two decisions constant is what lets it skip per-connection encoding conversion and date-format state altogether — the cost is that a client which wants another encoding has to convert on its own side. A driver that emits SET client_encoding = 'UTF8' on connect, or a pg_dump script that emits it in its preamble, passes through unchanged.

search_path is mutable, but "$user" is not a substitution — DB9 stores and reports the token literally and never resolves it to the current role:

SQL

SELECT current_user;               -- admin
SET search_path = "$user";
SHOW search_path;                  -- "$user"
SELECT * FROM my_table;            -- ERROR:  relation "my_table" does not exist   (42P01)
                                   -- even when admin.my_table exists
SET search_path = admin;           -- name the schema and it resolves

So name the schema you mean. "$user" is skipped rather than resolved, and what that leaves behind depends on what sits beside it:

SQL

SET search_path = "$user";              -- only entry: lookup still finds public
SET search_path = "$user", app;         -- resolves app, and no longer public
SET search_path = no_such_schema;       -- resolves nothing; not a fallback case

"$user" alone is the trap worth knowing: it looks like it scopes queries to your own schema, and on a default database — where everything lives in public — every query keeps working, against public. Pairing it with anything else — a real schema, or a name that does not exist — is what silently drops public out of the path.

timezone does not reach a text cast

timezone governs the wire format and to_char(), but timestamptz::text, CAST(x AS text), ::varchar and '' || ts all render the UTC instant and drop the offset — identically whatever the session timezone is:

SQL

SET timezone = 'America/New_York';
SELECT '2026-01-15 12:00:00+00'::timestamptz                AS wire,
       ('2026-01-15 12:00:00+00'::timestamptz)::text        AS as_text;
-- wire: 2026-01-15 07:00:00-05   as_text: 2026-01-15 12:00:00

SET timezone = 'Asia/Tokyo';       -- same query
-- wire: 2026-01-15 21:00:00+09   as_text: 2026-01-15 12:00:00

PostgreSQL 16 returns 2026-01-15 07:00:00-05 and 2026-01-15 21:00:00+09 for as_text. Nothing errors, so a query that stores or compares ts::text silently writes UTC and loses the offset. Use to_char(ts, 'YYYY-MM-DD HH24:MI'), which does honour the session timezone, or compare the timestamptz values directly instead of their text forms.

A few parameters accept a value they do not recognise

The earlier caution covers an unrecognised parameter name. For a handful of recognised parameters the value is unchecked too — a misspelling reads straight back and the parameter keeps its old behaviour:

SQL

SET client_min_messages = 'bogus_level_zz';  SHOW client_min_messages;  -- bogus_level_zz
SET lc_messages = 'zz_GARBAGE';              SHOW lc_messages;          -- zz_GARBAGE

xmloption and default_table_access_method behave the same way. PostgreSQL rejects client_min_messages, xmloption and default_table_access_method with invalid value for parameter. (lc_messages is the exception on both engines: PostgreSQL hands the value to setlocale(), so whether a nonsense locale is refused depends on the server’s C library — a musl build accepts zz_GARBAGE exactly as DB9 does.)

This is not universal in DB9 — bytea_output, datestyle, intervalstyle, default_transaction_isolation and embedding.provider all validate and raise 22023:

SQL

SET bytea_output = 'bogus_zz';
-- ERROR: invalid value for parameter "bytea_output": "bogus_zz"; available values: "hex", "escape"

Other accepted PostgreSQL parameters: default_table_access_method, default_tablespace, default_text_search_config (default simple, where PostgreSQL ships english), default_transaction_deferrable, idle_in_transaction_session_timeout (default 300000ms, and enforced — a connection left idle inside a transaction for five minutes is dropped with FATAL: terminating connection due to idle-in-transaction timeout, as in PostgreSQL), in_hot_standby, integer_datetimes, intervalstyle, lc_monetary, lc_numeric, lc_time, log_min_messages (default warning), max_index_keys, row_security, xmloption.

Over the PostgreSQL wire protocol, SHOW ALL additionally lists the connection’s JWT claims as request.jwt.claim.* (plus the whole payload as request.jwt.claims) and the caller’s subject as auth.uid. These are per-connection values rather than server parameters, and they are absent over the HTTP SQL API, where current_setting('auth.uid', true) returns NULL. See Row-Level Security for how policies read them.

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 (170000), session_authorization, transaction_deferrable, and transaction_read_only.

Three parameters are accepted but inert: work_mem (4MB), check_function_bodies (on) and extra_float_digits (1). A SET succeeds and SHOW reports the value you just set, but none of them changes any behaviour. (db9.use_optimizer below is inert in a different way — it keeps reporting on whatever you set it to, so unlike these three it does not even echo the write back.) For sort memory the parameter that does take effect is db9.max_sort_bytes:

SQL

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:

SQL

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 at or near "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:

SQL

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

SELECT broken_pl();
-- ERROR: syntax error at or near "SELEC"  (42601)

PostgreSQL rejects that CREATE FUNCTION too.

session_replication_role is rejected — disable triggers per table instead

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). Suppress triggers per table instead — DB9 implements ALTER TABLE ... DISABLE TRIGGER, and USER is the form to reach for:

SQL

ALTER TABLE orders DISABLE TRIGGER USER;   -- ALTER TABLE
-- ... bulk load ...
ALTER TABLE orders ENABLE TRIGGER USER;    -- ALTER TABLE

A disabled trigger genuinely does not fire. This is catalog state rather than session state, so it outlives the connection that set it and applies to every other session at once — re-enable explicitly when the load finishes, and do not rely on a disconnect to undo it. Read the current state from pg_trigger.tgenabled:

Statement	tgenabled becomes	Fires?
(state after CREATE TRIGGER)	O	yes
DISABLE TRIGGER <name>	D on that trigger	no
DISABLE TRIGGER USER	D on every trigger you created on the table	no
DISABLE TRIGGER ALL	as USER — but only on a table no foreign key touches, see below	no
ENABLE TRIGGER <name> / USER	O	yes
ENABLE TRIGGER ALL	O — same foreign-key restriction as DISABLE TRIGGER ALL	yes
ENABLE REPLICA TRIGGER <name>	R	no — see below
ENABLE ALWAYS TRIGGER <name>	A	yes

REPLICA and ALWAYS are accepted and recorded faithfully, but because session_replication_role can never leave origin, an R trigger is simply one that never fires. Say DISABLE when that is what you mean.

Prefer USER over ALL. A foreign key installs internal RI_ConstraintTrigger_* rows on both tables it connects — the one holding the key and the one it points at — and ALL refuses to touch those, in either direction. Both orders and order_items are therefore affected, even though only order_items declares the key:

SQL

ALTER TABLE order_items DISABLE TRIGGER ALL;   -- ERROR:  internal error   (0A000)
ALTER TABLE orders      DISABLE TRIGGER ALL;   -- ERROR:  internal error   (0A000)
ALTER TABLE order_items ENABLE  TRIGGER ALL;   -- ERROR:  internal error   (0A000)
ALTER TABLE order_items DISABLE TRIGGER USER;  -- ALTER TABLE
ALTER TABLE order_items ENABLE  TRIGGER USER;  -- ALTER TABLE

ALL succeeds only on a table no foreign key touches at all, so treat it as the form you cannot rely on: USER is the one that works everywhere, and it is also the one you want.

USER leaves the internal rows at O, so referential integrity keeps being enforced through the load — a foreign key violation still raises 23503 while your own triggers are off. That is the boundary worth knowing: DB9 lets you switch off the logic you wrote, not the constraints it guarantees.

A name that is not a trigger on the table is rejected rather than ignored:

SQL

ALTER TABLE orders DISABLE TRIGGER no_such_trigger;
-- ERROR:  trigger "no_such_trigger" for table "public.orders" does not exist   (42704)

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 and combined cross-product cap for UPDATE FROM / DELETE USING (0 = unlimited) — see the note below
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
db9.sequence_windows	off	Draw CACHE 1 sequence values — including those of SERIAL and identity columns, but not of CYCLE sequences — from per-server-process windows. Set per database with ALTER DATABASE only — see the note below

db9.dml_table_scan_max_rows also caps the cross-product

Sizing each auxiliary source under the cap is not enough. UPDATE ... FROM a, b is rejected once a × b exceeds the limit, even when a and b are individually well inside it — with the cap at 3, two sources of two rows each already fail:

ERROR: DML auxiliary row limit exceeded; adjust parameter
       "db9.dml_table_scan_max_rows" or set it to 0 to disable the limit (54000)

The limit is enforced at execution time, so a LIMIT that is a bind parameter or a non-constant expression does not evade it. Join through a single source, narrow the sources with a WHERE clause, or raise the parameter for the session.

db9.sequence_windows is set per database, and changes sequence ordering

Only the database owner can change it, and only with ALTER DATABASE:

SQL

ALTER DATABASE postgres SET db9.sequence_windows = on;
ALTER DATABASE postgres RESET db9.sequence_windows;

A session reads the value when it connects, so the change reaches sessions opened afterwards, over both the wire protocol and the HTTP SQL API, but not the session that made it. SET, SET LOCAL, set_config(), RESET and ALTER ROLE ... SET all fail with 55P02 (parameter "db9.sequence_windows" can only be set with ALTER DATABASE ... SET), and a -c db9.sequence_windows=on connection option is ignored without an error.

With it off — the default — values from a CACHE 1 sequence follow call order across sessions. With it on, sessions served by different server processes draw from separate ranges, so values interleave out of order (one session can get 33, 34, 35 and the next one 10, 11, 12), and turning it off again leaves the unused part of each range as a gap. Values stay unique. Leave it off if anything depends on ids following the order in which they were drawn.

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. Accepted range 1-1000 inclusive; 0 or a value above 1000 fails with 22023 rather than clamping. A bare negative literal is a parser-level error instead — SET hnsw.ef_search = -1 gives 0A000 internal error, while the quoted '-1' gives the expected 22023. This is not specific to this parameter: SET <any name> = -1 fails the same way, including a name that does not exist