Thread: Execution order of CTEs / set_config and current_setting in the same query
Execution order of CTEs / set_config and current_setting in the same query
From
Wolfgang Walther
Date:
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
Wolfgang Walther <walther@technowledgy.de> writes: > 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. I think you're on fairly shaky ground here. Generally speaking, a CTE will be executed/read only when the parent query needs the next row from it. Your examples ensure that the CTE is read before the parent query's results are computed; but in realistic usage you'd presumably be joining the CTE with some other table(s), and then the execution order is going to be a lot harder to predict. This approach is also going to fundamentally not work for settings that need to apply during planning of the query (which, notably, probably includes "role"). You'd be far better off to investigate ways to send SET LOCAL first, without incurring a separate network round trip for that. If you're using simple query mode that's easy, you can just do res = PQexec("SET LOCAL ... ; <real query>"); In extended query mode you can't get away with that, but you might be able to issue the SET LOCAL without immediately waiting for the result. regards, tom lane
Re: Execution order of CTEs / set_config and current_setting in the same query
From
Wolfgang Walther
Date:
Tom Lane: > I think you're on fairly shaky ground here. Generally speaking, a CTE > will be executed/read only when the parent query needs the next row from > it. Your examples ensure that the CTE is read before the parent query's > results are computed; but in realistic usage you'd presumably be joining > the CTE with some other table(s), and then the execution order is going > to be a lot harder to predict. This approach is also going to > fundamentally not work for settings that need to apply during planning > of the query (which, notably, probably includes "role"). Ok, thanks for confirming that. > You'd be far better off to investigate ways to send SET LOCAL first, > without incurring a separate network round trip for that. If you're > using simple query mode that's easy, you can just do > > res = PQexec("SET LOCAL ... ; <real query>"); > > In extended query mode you can't get away with that, but you might be able > to issue the SET LOCAL without immediately waiting for the result. Yes, that's what we did so far. We switched to set_config to parametrize the query. Is there any way to not wait for a SELECT? I don't care about the resultset, so I need something like PERFORM but for SQL, not plpgsql, I think?