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

On Wed, May 27, 2020 at 9:11 PM Ashutosh Bapat
<ashutosh(dot)bapat(dot)oss(at)gmail(dot)com> wrote:
> 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.

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