Re: Unexpected result count from update statement on partitioned table

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