Execution order of CTEs / set_config and current_setting in the same query

Поиск
Список
Период
Сортировка
От Wolfgang Walther
Тема Execution order of CTEs / set_config and current_setting in the same query
Дата
Msg-id cf303847-0bd1-4eca-2b3c-3055416df8bb@technowledgy.de
обсуждение исходный текст
Ответы Re: Execution order of CTEs / set_config and current_setting in the same query
Список pgsql-general
Hi,

with PostgREST [1] we are translating HTTP requests into SQL queries. 
For each request we are setting some metadata (headers, ...) as GUCs.

We used to do it like this:
SET LOCAL request.headers.x = 'y';
...

Since this is user-provided data, we want to use parametrized/prepared 
statements. This is not possible with SET, so we switched to:

SELECT set_config($1, $2, true), ...;

Both these queries are preceding our main query. The SELECT set_config 
is a bit slower than the SET LOCAL, probably because of more overhead on 
the SELECT.

Now, we are wondering: To reduce overhead, can we move the set_config 
calls to a CTE as part of the main query? The values would need to be 
available with current_setting(...) in the remaining query.

Of course we would need to ensure execution order, so that this CTE will 
always be fully executed, before all the other parts of the query.

Is this possible to do?

We did some basic testing, that seemed to be successful:

WITH set AS (
   SELECT set_config('pgrst.hello', 'world', true)
)
SELECT current_setting('pgrst.hello')
FROM set;

or

WITH set AS (
   SELECT set_config('pgrst.hello', 'world', true)
),
main AS (
   SELECT
     current_setting('pgrst.hello') AS hello,
     <other columns>
   FROM set, <other tables>
)
SELECT
   current_setting('pgrst.hello'),
   main.hello,
   <other columns>
FROM set, main, <other tables>;


Queries like this seem to have set the GUC correctly. But is this 
guaranteed? What would need to be done to guarantee it?

I have a feeling that even though this works for those simple cases, 
there is some risk involved...

Additional question: If this can be guaranteed - what about using 
set_config('role', 'xxx', true) in the same way? Putting this into those 
examples above and checking with CURRENT_USER seems to work as well. How 
likely would this lead to problems with privileges / permissions?

Any input/insight would be helpful.

Thanks

Wolfgang

[1]: https://postgrest.org



В списке pgsql-general по дате отправления:

Предыдущее
От: hubert depesz lubaczewski
Дата:
Сообщение: Re: Tools showing table partitions as tables in listings of tables
Следующее
От: electrotype
Дата:
Сообщение: Re: JDBC driver - is "getGeneratedKeys()" guaranteed to return the ids in the same order a batch insert was made?