From: | Rajkumar Raghuwanshi <rajkumar(dot)raghuwanshi(at)enterprisedb(dot)com> |
---|---|
To: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Getting ERROR with FOR UPDATE/SHARE for partitioned table. |
Date: | 2020-05-22 11:30:02 |
Message-ID: | CAKcux6k2KoQ=WjvNdW_Jyct7HuoVvqdgj-bEiXavS1BqOPzi+w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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);
postgres=# SELECT func(10);
ERROR: cursor "cur" does not have a FOR UPDATE/SHARE reference to table
"tbl_null"
CONTEXT: SQL statement "UPDATE tbl SET c2='aa' WHERE CURRENT OF cur"
PL/pgSQL function func(integer) line 10 at SQL statement
Thanks & Regards,
Rajkumar Raghuwanshi
From | Date | Subject | |
---|---|---|---|
Next Message | amul sul | 2020-05-22 12:08:41 | Re: Getting ERROR with FOR UPDATE/SHARE for partitioned table. |
Previous Message | Amit Kapila | 2020-05-22 11:16:42 | Re: PATCH: logical_work_mem and logical streaming of large in-progress transactions |