From: | Wolfgang Walther <walther(at)technowledgy(dot)de> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Execution order of CTEs / set_config and current_setting in the same query |
Date: | 2020-12-09 16:37:47 |
Message-ID: | b8c22ed1-ac2f-461e-3f7c-c2665085c3b1@technowledgy.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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?
From | Date | Subject | |
---|---|---|---|
Next Message | Tomas Vondra | 2020-12-09 17:01:32 | Re: Potential BRIN Index Corruption |
Previous Message | David G. Johnston | 2020-12-09 15:41:12 | Re: JDBC driver - is "getGeneratedKeys()" guaranteed to return the ids in the same order a batch insert was made? |