Re: Getting ERROR with FOR UPDATE/SHARE for partitioned table.

On Wed, Jun 3, 2020 at 12:44 PM Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
>
> On Thu, May 28, 2020 at 11:08 PM Ashutosh Bapat
> <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com> wrote:
> > On Wed, May 27, 2020 at 6:51 PM Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
> > > So in Rajkumar's example, the cursor is declared as:
> > >
> > > CURSOR IS SELECT * FROM tbl WHERE c1< 5 FOR UPDATE;
> > >
> > > and the WHERE CURRENT OF query is this:
> > >
> > > UPDATE tbl SET c2='aa' WHERE CURRENT OF cur;
> >
> > Thanks for the clarification. So it looks like we expand UPDATE on
> > partitioned table to UPDATE on each partition (inheritance_planner for
> > DML) and then execute each of those. If CURRENT OF were to save the
> > table oid or something we could run the UPDATE only on that partition.
>
> Are you saying that the planner should take into account the state of
> the cursor specified in WHERE CURRENT OF to determine which of the
> tables to scan for the UPDATE? Note that neither partition pruning
> nor constraint exclusion know that CurrentOfExpr can possibly allow to
> exclude children of the UPDATE target.

Yes. But it may not be possible to know the value of current of at the
time of planning since that need not be a plan time constant. This
pruning has to happen at run time. But as Alvaro has mentioned in his
reply for a user this is a surprising behaviour and should be fixed.

--
Best Wishes,
Ashutosh Bapat