On Fri, Dec 18, 2020 at 12:16 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Laurenz Albe <laurenz.albe@cybertec.at> writes: > The subquery is executed twice, and the two executions obviously don't > return the same results. I am at a loss for an explanation ...
Yeah, this is a fairly fundamental shortcoming in inheritance_planner(): it supposes that it can duplicate the whole query for each target table. If you have a sub-SELECT that generates unstable results, then the duplicated copies don't necessarily generate the same results. And multiple executions of a sub-SELECT with "for update skip locked" are guaranteed to not give the same results, because the second one will skip the row(s) already locked by the first one.
Are there other examples of gotchas with this? Would it be any volatile function (or behavior like skip locked) in a sub-query? It isn't apparent to me why the subquery is executed twice for this example either and since that is a pre-req for hitting this unexpected situation... what is the factor that means the sub-query would be executed multiple times?
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? If it were a volatile function instead, might we have gotten more than 50 rows updated?