Re: Getting ERROR with FOR UPDATE/SHARE for partitioned table.

2020-06-16 Thread Ashutosh Bapat
On Tue, 16 Jun 2020 at 11:45, Amit Langote wrote: > On Fri, Jun 12, 2020 at 9:22 PM Ashutosh Bapat > wrote: > > On Wed, Jun 3, 2020 at 12:44 PM Amit Langote > wrote: > > > Are you saying that the planner should take into account the state of > > > the cursor specified in WHERE CURRENT OF to

Re: Getting ERROR with FOR UPDATE/SHARE for partitioned table.

2020-06-16 Thread Amit Langote
On Fri, Jun 12, 2020 at 9:22 PM Ashutosh Bapat wrote: > On Wed, Jun 3, 2020 at 12:44 PM Amit Langote wrote: > > Are you saying that the planner should take into account the state of > > the cursor specified in WHERE CURRENT OF to determine which of the > > tables to scan for the UPDATE? Note

Re: Getting ERROR with FOR UPDATE/SHARE for partitioned table.

2020-06-12 Thread Ashutosh Bapat
On Wed, Jun 3, 2020 at 12:44 PM Amit Langote wrote: > > On Thu, May 28, 2020 at 11:08 PM Ashutosh Bapat > wrote: > > On Wed, May 27, 2020 at 6:51 PM Amit Langote > > wrote: > > > So in Rajkumar's example, the cursor is declared as: > > > > > > CURSOR IS SELECT * FROM tbl WHERE c1< 5 FOR

Re: Getting ERROR with FOR UPDATE/SHARE for partitioned table.

2020-06-08 Thread Alvaro Herrera
On 2020-Jun-03, Amit Langote wrote: > Are you saying that the planner should take into account the state of > the cursor specified in WHERE CURRENT OF to determine which of the > tables to scan for the UPDATE? Note that neither partition pruning > nor constraint exclusion know that CurrentOfExpr

Re: Getting ERROR with FOR UPDATE/SHARE for partitioned table.

2020-06-03 Thread Amit Langote
On Thu, May 28, 2020 at 11:08 PM Ashutosh Bapat wrote: > On Wed, May 27, 2020 at 6:51 PM Amit Langote wrote: > > So in Rajkumar's example, the cursor is declared as: > > > > CURSOR IS SELECT * FROM tbl WHERE c1< 5 FOR UPDATE; > > > > and the WHERE CURRENT OF query is this: > > > > UPDATE tbl

Re: Getting ERROR with FOR UPDATE/SHARE for partitioned table.

2020-05-28 Thread Ashutosh Bapat
On Wed, May 27, 2020 at 6:51 PM Amit Langote wrote: > > So in Rajkumar's example, the cursor is declared as: > > CURSOR IS SELECT * FROM tbl WHERE c1< 5 FOR UPDATE; > > and the WHERE CURRENT OF query is this: > > UPDATE tbl SET c2='aa' WHERE CURRENT OF cur; Thanks for the clarification. So it

Re: Getting ERROR with FOR UPDATE/SHARE for partitioned table.

2020-05-28 Thread amul sul
On Thu, May 28, 2020 at 3:06 PM Amit Langote wrote: > On Thu, May 28, 2020 at 1:36 PM amul sul wrote: > > On Wed, May 27, 2020 at 12:53 PM Amit Langote > wrote: > >> Actually, if you declare the cursor without FOR SHARE/UPDATE, the case > >> would fail even with traditional inheritance: > >> >

Re: Getting ERROR with FOR UPDATE/SHARE for partitioned table.

2020-05-28 Thread Amit Langote
On Thu, May 28, 2020 at 1:36 PM amul sul wrote: > On Wed, May 27, 2020 at 12:53 PM Amit Langote wrote: >> 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. Now *that*

Re: Getting ERROR with FOR UPDATE/SHARE for partitioned table.

2020-05-28 Thread Amit Langote
On Thu, May 28, 2020 at 1:36 PM amul sul wrote: > On Wed, May 27, 2020 at 12:53 PM Amit Langote wrote: >> 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); >>

Re: Getting ERROR with FOR UPDATE/SHARE for partitioned table.

2020-05-27 Thread amul sul
On Wed, May 27, 2020 at 12:53 PM Amit Langote wrote: > On Fri, May 22, 2020 at 9:09 PM amul sul 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))

Re: Getting ERROR with FOR UPDATE/SHARE for partitioned table.

2020-05-27 Thread Amit Langote
On Wed, May 27, 2020 at 9:11 PM Ashutosh Bapat wrote: > On Wed, May 27, 2020 at 12:53 PM Amit Langote wrote: > > On Fri, May 22, 2020 at 9:09 PM amul sul wrote: > > > I tried similar things on inherit partitioning as follow and that looks > > > fine: > > > > > > DROP TABLE tbl; > > > CREATE

Re: Getting ERROR with FOR UPDATE/SHARE for partitioned table.

2020-05-27 Thread Ashutosh Bapat
On Wed, May 27, 2020 at 12:53 PM Amit Langote wrote: > > On Fri, May 22, 2020 at 9:09 PM amul sul 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

Re: Getting ERROR with FOR UPDATE/SHARE for partitioned table.

2020-05-27 Thread Amit Langote
On Fri, May 22, 2020 at 9:09 PM amul sul 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))

Re: Getting ERROR with FOR UPDATE/SHARE for partitioned table.

2020-05-22 Thread amul sul
On Fri, May 22, 2020 at 5:00 PM Rajkumar Raghuwanshi < rajkumar.raghuwan...@enterprisedb.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

Getting ERROR with FOR UPDATE/SHARE for partitioned table.

2020-05-22 Thread Rajkumar Raghuwanshi
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