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

Wolfgang Walther <walther(at)technowledgy(dot)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