Re: Getting ERROR with FOR UPDATE/SHARE for partitioned table. - Mailing list pgsql-hackers

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

I guess the workaround is to declare the cursor such that no
partitions/children are pruned/excluded.

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

[1] https://www.postgresql.org/message-id/468c85d9-540e-66a2-1dde-fec2b741e688%40lab.ntt.co.jp