How to fix "SET LOCAL can only be used in transaction blocks" in PostgreSQL

What the warning means

PostgreSQL prints this when SET LOCAL runs outside a transaction block:

WARNING:  SET LOCAL can only be used in transaction blocks

It is a warning, not an error, so nothing fails. But the command does nothing. SET LOCAL changes a setting only until the end of the current transaction, and without BEGIN each statement is its own transaction, which ends the moment the SET LOCAL finishes. The next statement runs with the old value.

That is why this warning matters: a timeout you thought you set is not set, or a row-level security setting such as a tenant id is missing. SET TRANSACTION (isolation level, read only) fails the same way, with “SET TRANSACTION can only be used in transaction blocks”.

1. Confirm the setting did nothing

Run the statements one at a time, as most tools and drivers send them:

SET LOCAL statement_timeout = '5s';
-- WARNING:  SET LOCAL can only be used in transaction blocks

SHOW statement_timeout;
-- 0  (the old value: the SET LOCAL had no effect)

psql sends each statement separately even when they are on one line, so you see the warning there too.

2. Why there was no transaction

Something between your code and the server is running each statement on its own. The usual causes:

• Autocommit. Most drivers and ORMs commit after every statement unless you start a transaction. Calling execute("SET LOCAL ...") and then execute("SELECT ...") runs two transactions.
• A SQL console or GUI tool that runs each statement in a script separately in autocommit mode.
• Connection setup hooks, such as an “on connect” callback in a pool, which run outside any transaction.
• Separate messages. When several statements are sent together in one simple query string, PostgreSQL runs them as one implicit transaction and SET LOCAL lasts until the end of that string. Drivers that send each statement as its own message (the extended protocol) do not get that, so the same text can work in one tool and warn in another.

3. Fix: wrap it in a transaction

Start the transaction yourself and put SET LOCAL first. The value applies to every statement until COMMIT or ROLLBACK, then goes back to the session value.

BEGIN;
SET LOCAL statement_timeout = '5s';
SHOW statement_timeout;          -- 5s
SELECT count(*) FROM orders;     -- runs with the 5 second limit
COMMIT;

SHOW statement_timeout;          -- back to 0

In application code, use the driver's transaction API (for example a transaction() block or turning autocommit off) and issue SET LOCAL on the same connection inside it.

For transaction settings, put them on BEGIN itself instead of a separate SET TRANSACTION:

BEGIN ISOLATION LEVEL REPEATABLE READ;
SHOW transaction_isolation;      -- repeatable read
COMMIT;

4. Fix: set_config in the same transaction

set_config(name, value, true) is the function form of SET LOCAL; the third argument true means “for this transaction only”. It is the usual way to pass a tenant or user id to row-level security policies, and it has the same rule: it must run in the same transaction as the queries that read it.

BEGIN;
SELECT set_config('app.tenant_id', '42', true);
SELECT id, total
FROM orders
WHERE tenant_id = current_setting('app.tenant_id')::int;
COMMIT;

After the transaction ends, the setting does not disappear: it reads as an empty string. A policy that casts it directly then fails with invalid input syntax for type integer: "". Turn the empty string into NULL so a missing tenant id matches no rows instead of raising an error:

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::int);

5. Fix: settings that should always apply

If a value should apply to every statement a role or a function runs, set it there instead of repeating SET LOCAL:

-- every new session for app_user starts with this
ALTER ROLE app_user SET statement_timeout = '30s';

-- applies while the function runs, then the caller's value returns
CREATE FUNCTION tenant_report(p_tenant int)
RETURNS bigint
LANGUAGE sql
SET statement_timeout = '5s'
SET search_path = public, pg_temp
AS $$ SELECT count(*) FROM orders WHERE tenant_id = p_tenant $$;

A role setting is picked up by new sessions only; existing connections keep their value until they reconnect. To see what is set:

SELECT r.rolname, s.setconfig
FROM pg_db_role_setting AS s
JOIN pg_roles AS r ON r.oid = s.setrole
WHERE r.rolname = 'app_user';

6. Connection poolers

With a pooler such as PgBouncer in transaction mode, consecutive transactions from one client can run on different server connections. That changes which form is safe:

• Plain SET (session level) stays on the server connection after your transaction, so the next client that gets that connection inherits it, and your next transaction may not have it.
• SET LOCAL inside BEGIN ... COMMIT is the safe form: it applies to exactly one transaction on one server connection.

So the fix for this warning behind a pooler is the same as step 3, and it is not to switch to plain SET.

7. Check what the driver sends

If you cannot tell whether your code opens a transaction, log the statements for that role on a staging server and look for BEGIN before the SET LOCAL:

ALTER ROLE app_user SET log_statement = 'all';
-- reconnect the application, reproduce, read the server log, then:
ALTER ROLE app_user RESET log_statement;

The same steps in Quorum

In Quorum's Query Studio the target connection and environment are printed beside Run, so you know which database and session a SET LOCAL is going to, and completion comes from the live connection.

• Target and environment beside Run, so a setting meant for staging is not run against production.
• Completion from the live connection for setting names, roles and tables.
• A shared query library and runbooks to keep the correct transaction pattern in one place for the team.