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

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.

> I am possibly shooting in dark, but this puzzles me. And it looks like
> we can cause wrong rows to be updated in non-partition inheritance
> where the ctids match?

I don't think that hazard exists, because the table OID is matched
before the TID. Consider this example:

drop table if exists p cascade;
create table p (a int);
create table c (check (a = 2)) inherits (p);
insert into p values (1);
insert into c values (2);
begin;
declare c cursor for select * from p;
fetch c;
update p set a = a where current of c;
QUERY PLAN
------------------------------------------------------------
Update on p (cost=0.00..8.02 rows=2 width=10)
Update on p
Update on c p_1
-> Tid Scan on p (cost=0.00..4.01 rows=1 width=10)
TID Cond: CURRENT OF c
-> Tid Scan on c p_1 (cost=0.00..4.01 rows=1 width=10)
TID Cond: CURRENT OF c
(7 rows)

Whenever the TID scan evaluates the CURRENT OF qual, it passes the
table being scanned to execCurrentOf(). execCurrentOf() then fetches
the ExecRowMark or the ScanState for *that* table from the cursor's
("c") PlanState via its portal. Only if it confirms that such a
ExecRowMark or a ScanState exists and is valid/active that it returns
the TID found therein as the cursor's current TID.

--
Amit Langote
EnterpriseDB: http://www.enterprisedb.com