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

On Wed, May 27, 2020 at 9:11 PM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:
> On Wed, May 27, 2020 at 12:53 PM Amit Langote <amitlangote09@gmail.com> wrote:
> > On Fri, May 22, 2020 at 9:09 PM amul sul <sulamul@gmail.com> wrote:
> > > I tried similar things on inherit partitioning as follow and that looks fine:
> > >
> > > DROP TABLE tbl;
> > > CREATE TABLE tbl (c1 INT,c2 TEXT);
> > > CREATE TABLE tbl_null(check (c1 is NULL)) INHERITS (tbl);
> > > CREATE TABLE tbl_1 (check (c1 > 0 and c1 < 4)) INHERITS (tbl);
> > > INSERT INTO tbl_1 VALUES(generate_series(1,3));
> > >
> > > postgres=# SELECT func(10);
> > >  func
> > > ------
> > >    10
> > > (1 row)
> > >
> > > On looking further for declarative partition, I found that issue happens only if
> > > the partitioning pruning enabled, see this:
> > >
> > > -- Execute on original set of test case.
> > > postgres=# ALTER FUNCTION func SET enable_partition_pruning to off;
> > > ALTER FUNCTION
> > >
> > > postgres=# SELECT func(10);
> > >  func
> > > ------
> > >    10
> > > (1 row)
> > >
> > > I think we need some indication in execCurrentOf() to skip error if the relation
> > > is pruned.  Something like that we already doing for inheriting partitioning,
> > > see following comment execCurrentOf():
> > >
> > >         /*
> > >          * This table didn't produce the cursor's current row; some other
> > >          * inheritance child of the same parent must have.  Signal caller to
> > >          * do nothing on this table.
> > >          */
> >
> > Actually, if you declare the cursor without FOR SHARE/UPDATE, the case
> > would fail even with traditional inheritance:
> >
> > 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 where a = 1;
> > fetch c;
> > update p set a = a where current of c;
> > ERROR:  cursor "c" is not a simply updatable scan of table "c"
> > ROLLBACK
> >
> > When there are no RowMarks to use because no FOR SHARE/UPDATE clause
> > was specified when declaring the cursor, execCurrentOf() tries to find
> > the cursor's current table by looking up its Scan node in the plan
> > tree but will not find it if it was excluded in the cursor's query.
> >
> > With FOR SHARE/UPDATE, it seems to work because the planner delivers
> > the RowMarks of all the children irrespective of whether or not they
> > are present in the plan tree itself (something I had complained about
> > in past [1]).  execCurrentOf() doesn't complain as long as there is a
> > RowMark present even if it's never used.  For partitioning, the
> > planner doesn't make RowMarks for pruned partitions, so
> > execCurrentOf() can't find one if it's passed a pruned partition's
> > oid.
>
> I am missing something in this explanation. WHERE CURRENT OF works on
> the row that was last fetched from a cursor. How could a pruned
> partition's row be fetched and thus cause this error.

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;

You can see that the UPDATE will scan all partitions, whereas the
cursor's query does not.

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