Michael Lewis <mlewis@entrata.com> writes:
> On Fri, Dec 18, 2020 at 12:16 PM Tom Lane <tgl@sss.pgh.pa.us> 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