Обсуждение: Transaction local custom settings set to '' rather than removedentirely after transaction ends
Before creating a custom local setting, current_setting(string) will raise an error:
select current_setting('foo.bar');
ERROR: unrecognized configuration parameter "foo.bar"
After assigning a local value during a transaction and finishing the transaction, the setting becomes recognised, has an empty string value:
begin;
set local "foo.bar" to 'baz';
rollback;
select current_setting('foo.bar');
current_setting
select pg_typeof(current_setting('foo.bar'));
pg_typeof
text
This behaviour lasts for the duration of the connection, and returns to the initial state after reconnecting.
This caused me problems, as I was using the new 'missing_ok' parameter for current_setting (added in PostgreSQL 9.6) to return null, e.g.
select current_setting('foo.bar.baz', true);
NULL
I am using local settings to store an (e.g. application user UUID), which I then use in RLS policies and also refer to in a DEFAULT clause for a table column, e.g.
create table doc (
id uuid primary key,
title text,
author uuid default current_setting('app.user.id', true)::uuid
);
However app.user.id is only set if a user is performing a query, it's also possible for a app service role to connect to the database and insert rows too. For this to work I rely on current_setting returning null (rather than an empty string).
I would like the presence of a local setting to not leak out of a transaction.
--
Cheers,
Brad