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
>
>
I am not sure I understood the point, you'll see the same error with
declarative
partitioning as well.

> 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.
>
>
Right.

> I don't see a way to avoid these errors. How does execCurrentOf()
> distinguish a table that could *never* be present in a cursor from a
> table that could be had it not been pruned/excluded? If it can do
> that, then give an error for the former and return false for the
> latter.
>

Yeah. I haven't thought much about this; I was thinking initially just to
skip
error by assuming that the table that we are looking might have pruned, but
I am
not sure how bad or good approach it is.

> I guess the workaround is to declare the cursor such that no
> partitions/children are pruned/excluded.
>
>
Disabling pruning as well -- at-least for the statement or function.

Regards,
Amul

--
> Amit Langote
> EnterpriseDB: http://www.enterprisedb.com
>
> [1]
> https://www.postgresql.org/message-id/468c85d9-540e-66a2-1dde-fec2b741e688%40lab.ntt.co.jp
>