From: | amul sul <sulamul(at)gmail(dot)com> |
---|---|
To: | Rajkumar Raghuwanshi <rajkumar(dot)raghuwanshi(at)enterprisedb(dot)com> |
Cc: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Getting ERROR with FOR UPDATE/SHARE for partitioned table. |
Date: | 2020-05-22 12:08:41 |
Message-ID: | CAAJ_b96RGEF1HwerhAw2YDvT-jf3WdLwquLBKNrDJPrx3zvifQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Fri, May 22, 2020 at 5:00 PM Rajkumar Raghuwanshi <
rajkumar(dot)raghuwanshi(at)enterprisedb(dot)com> wrote:
> Hi All,
>
> I am getting ERROR when using the "FOR UPDATE" clause for the partitioned
> table. below is a reproducible test case for the same.
>
> CREATE TABLE tbl (c1 INT,c2 TEXT) PARTITION BY LIST (c1);
> CREATE TABLE tbl_null PARTITION OF tbl FOR VALUES IN (NULL);
> CREATE TABLE tbl_1 PARTITION OF tbl FOR VALUES IN (1,2,3);
>
> INSERT INTO tbl SELECT i,i FROM generate_series(1,3) i;
>
> CREATE OR REPLACE FUNCTION func(i int) RETURNS int
> AS $$
> DECLARE
> v_var tbl%ROWTYPE;
> cur CURSOR IS SELECT * FROM tbl WHERE c1< 5 FOR UPDATE;
> BEGIN
> OPEN cur;
> LOOP
> FETCH cur INTO v_var;
> EXIT WHEN NOT FOUND;
> UPDATE tbl SET c2='aa' WHERE CURRENT OF cur;
> END LOOP;
> CLOSE cur;
> RETURN 10;
> END;
> $$ LANGUAGE PLPGSQL;
>
> SELECT func(10);
>
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.
*/
Regards,
Amul
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Eisentraut | 2020-05-22 12:32:41 | Re: SEARCH and CYCLE clauses |
Previous Message | Rajkumar Raghuwanshi | 2020-05-22 11:30:02 | Getting ERROR with FOR UPDATE/SHARE for partitioned table. |