https://www.postgresql.org/docs/10/sql-set.html

The effects of SET LOCAL last only till the end of the current transaction, whether committed or not. A special case is SET followed by SET LOCAL within a single transaction: the SET LOCAL value will be seen until the end of the transaction, but afterwards (if the transaction is committed) the SET value will take effect.

The effects of SET or SET LOCAL are also canceled by rolling back to a savepoint that is earlier than the command.

This ticket also includes adding support for the set_config(setting_name, val, is_local) builtin: https://www.postgresql.org/docs/13/functions-admin.html

Epic CRDB-2507