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

On Wed, May 27, 2020 at 12:53 PM Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
>
> On Fri, May 22, 2020 at 9:09 PM amul sul <sulamul(at)gmail(dot)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.
--
Best Wishes,
Ashutosh Bapat