skip to content

In PostgreSQL, how do ALTER DATABASE SET, ALTER ROLE SET, SET and SET LOCAL rank against postgresql.conf, and when does each take effect?

level: seniorimportance: should knowfreq 40%

answer

  1. layers above the files
  2. role-in-database beats role beats database
  3. defaults load at login
  4. SET LOCAL ends with the transaction

basics

~10 s

Each layer outranks the files: ALTER DATABASE SET, then ALTER ROLE SET, then ALTER ROLE IN DATABASE SET apply at login; SET lasts for the session; SET LOCAL lasts until the transaction ends.

solid answer

~40 s

Configuration files are the base layer. `ALTER DATABASE ... SET` and `ALTER ROLE ... SET` store defaults in `pg_db_role_setting` that override the files, and on conflict role-in-database beats role, which beats database. They are applied only when a session logs in, so open sessions keep their values, and `SET ROLE` does not load the target role's defaults. Inside a session, `SET` overrides everything until the session ends, but it is transactional: a rollback undoes it. `SET LOCAL` lasts only until the current transaction commits or rolls back, then the session's previous value returns; outside a transaction block it only warns and does nothing. That makes `SET LOCAL` the safe way to raise something like `work_mem` for one heavy report.

code

sql · 11 lines
sql
ALTER ROLE ticket_reports SET default_transaction_read_only = on;

BEGIN;
SET LOCAL work_mem = '256MB';
SELECT event_id, sum(price_cents) AS revenue_cents
FROM ticket_sales
GROUP BY event_id
ORDER BY revenue_cents DESC;
COMMIT;

SHOW work_mem;  -- back to the session's previous value

go deeper

for a junior

Remember that a role or database can carry its own setting defaults, and that SET changes a value only for your session.

for a middle

Explain the priority order and when each layer is applied: at login for role and database defaults, the session for SET, the transaction for SET LOCAL.

for a senior

Show you can trace a surprising value to a stale role default or a session SET, and choose SET LOCAL for per-query overrides that must not leak through pooled connections.

for a principal

Discuss which layer should own which kind of policy, and how to audit role-level overrides so they do not silently diverge from the configuration everyone reviews.

## The layers, lowest to highest PostgreSQL resolves a parameter's value from several **sources**, and a higher-priority source always wins over a lower one. `pg_settings.source` names the one that won: | Priority | Mechanism | Lasts | `source` | |---|---|---|---| | 1 | Built-in default | always | `default` | | 2 | `postgresql.conf`, includes, `postgresql.auto.conf` | until changed and reloaded | `configuration file` | | 3 | `postgres -c` at server start | until restart | `command line` | | 4 | `ALTER DATABASE tickets SET ...` | new sessions in that database | `database` | | 5 | `ALTER ROLE ticket_reports SET ...` | new sessions of that role | `user` | | 6 | `ALTER ROLE ... IN DATABASE ... SET ...` | that role in that database | `database user` | | 7 | Connection options (`PGOPTIONS`) | that session | `client` | | 8 | `SET` / `set_config(name, value, false)` | rest of the session | `session` | | 9 | `SET LOCAL` / `set_config(name, value, true)` | rest of the transaction | `session` | A consequence people miss: a reload of the files never overrides layers 3 to 9, because those sources rank higher. ## Role and database defaults - Stored in the catalog `pg_db_role_setting`; `\drds` in psql lists them. - **Applied only at login.** Running `ALTER ROLE ticket_reports SET default_transaction_read_only = on` changes nothing for sessions already open; the next login picks it up. - `SET ROLE` and `SET SESSION AUTHORIZATION` do **not** load the target role's defaults, so a connection that logs in as `app` and then switches role keeps `app`'s settings. - Parameters that only change at server start or on reload (`postmaster` and `sighup` context) cannot be set this way, and some can only be set by a superuser issuing the command. - `RESET` or `= DEFAULT` removes a stored default, so new sessions inherit the server-wide value again. ## `SET` and `SET LOCAL` `SET name = value` changes the value for the current session only. It is **transactional**: if the surrounding transaction rolls back, or you roll back to a savepoint taken earlier, the `SET` is undone. `SET LOCAL name = value` lasts only until the current transaction ends, committed or not. The rules worth knowing: 1. After `COMMIT` or `ROLLBACK`, the session-level value comes back, whatever it was before (a file value, a role default, or an earlier `SET`). 2. Outside a transaction block, `SET LOCAL` only emits a warning and has no effect. 3. If a session runs `SET` and then `SET LOCAL` in one transaction, the `SET LOCAL` value is seen until the end of the transaction; after a commit, the `SET` value takes over. 4. Inside a function declared with a `SET` clause for the same parameter, `SET LOCAL` is undone when the function exits. This is why `SET LOCAL` suits one-off overrides, such as giving a nightly settlement report more `work_mem` (what size to give it is a memory-tuning question) without changing the rest of the connection. It is also the form that survives connection pooling in transaction mode, where a plain `SET` can leak to the next client. ## Seeing who set it - `SELECT name, setting, source FROM pg_settings WHERE name = 'default_transaction_read_only';` shows the winner for your session. - `\drds ticket_reports` lists stored role and database defaults. - `SHOW` and `current_setting()` print only the value, not the source. - `RESET name` (or `SET name TO DEFAULT`) returns a session to its login-time value, which already includes role and database defaults, so it is not necessarily the file value; `pg_settings.reset_val` shows what `RESET` would restore. - `set_config('name', 'value', true)` is the function form of `SET LOCAL`, handy inside a query or a function body. ## Design guidance - Put cluster-wide policy in the files, per-application defaults on roles, and per-query exceptions in `SET LOCAL`. - Audit role defaults periodically; an old `ALTER ROLE ... SET` from an incident silently beats every later file change.

  • You ran ALTER ROLE app SET for a parameter and reloaded, but the app's open connections ignore it. Why?
    Role and database defaults are read only when a session logs in, and a reload does not re-read them. Open connections keep their values until they reconnect, so recycle the pool or wait for connections to be replaced.
  • A session runs SET LOCAL outside BEGIN ... COMMIT. What happens?
    Each statement then runs in its own implicit transaction, so PostgreSQL warns that `SET LOCAL` can only be used in transaction blocks and the value does not change for anything that follows.

Think of a thermostat in an office building: facilities set the building default (the files), a floor preset overrides it (ALTER DATABASE), your office preset overrides that (ALTER ROLE), and presets load only when you walk in (login). Turning the dial yourself lasts the day (SET); a meeting-room timer reverts when the meeting ends (SET LOCAL).

  • Expects a pg_reload_conf() to push a new ALTER ROLE ... SET value into open sessions.
  • Believes SET ROLE loads the target role's ALTER ROLE ... SET defaults.
  • Thinks SET LOCAL outside a transaction block changes the value for the session.
  • Says postgresql.conf beats ALTER DATABASE ... SET because the file is the server's own setting.
  • Claims a plain SET cannot be undone by rolling back its transaction.