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?