Search Postgresql Archives

Re: Unexpected result count from update statement on partitioned table

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux