On Wed, May 27, 2020 at 12:53 PM Amit Langote <amitlangot...@gmail.com> wrote:
> On Fri, May 22, 2020 at 9:09 PM amul sul <sula...@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 > > 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 >
