Michael Lewis <mlewis@xxxxxxxxxxx> writes: > On Fri, Dec 18, 2020 at 12:16 PM Tom Lane <tgl@xxxxxxxxxxxxx> wrote: >> Yeah, this is a fairly fundamental shortcoming in inheritance_planner(): >> it supposes that it can duplicate the whole query for each target table. > Are there other examples of gotchas with this? Would it be any volatile > function (or behavior like skip locked) in a sub-query? Right, anything that causes multiple executions to not deliver identical results. The different executions will use the same snapshot, so there's not a hazard from external changes to the DB, but internal sources of nonrepeatability are a problem. > ... what is the > factor that means the sub-query would be executed multiple times? If it's in the FROM clause of an UPDATE or DELETE on a table with inheritance children (either traditional inheritance or partitioning). Actually, after further thought, I'm not entirely sure that the issue is confined to inherited UPDATE/DELETE. If you had such a sub-SELECT in an ordinary join, and the planner chose to put it on the inside of a nestloop, you'd have a problem. I do not think there's any check to avoid doing that just because the subquery's results are potentially volatile. Probably evaluation-cost considerations would discourage such a plan in most cases, but there's no direct defense AFAIR. > With the behavior change for CTEs to no longer be materialized by default > in PG12... why does the CTE still mean it is executed only once? Is it > because it is NOT side effect free (locking) so it cannot be in-lined? Exactly. regards, tom lane
