Re: [HACKERS] Mishandling of WCO constraints in direct foreign table modification

2017-07-25 Thread Etsuro Fujita 

On 2017/07/25 5:35, Robert Haas wrote:

On Fri, Jul 21, 2017 at 6:21 AM, Etsuro Fujita
 wrote:

I mean constraints derived from WITH CHECK OPTIONs specified for parent
views.  We use the words "WITH CHECK OPTION constraints" in comments in
nodeModifyTable.c, so the expression "CHECK OPTION constrains" doesn't sound
not that bad to me.  (I used "CHECK OPTION", not "WITH CHECK OPTION",
because we use "CHECK OPTION" a lot more in the documentation than "WITH
CHECK OPTION".)


Yeah, it seems OK to me, too; if the consensus is otherwise, we also
have the option to change it later.

Agreed.


Committed and back-patched as you
had it, but I removed a spurious comma.


Thanks for that, Robert!  Thanks for reviewing, Horiguchi-san!

Best regards,
Etsuro Fujita



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Mishandling of WCO constraints in direct foreign table modification

2017-07-24 Thread Robert Haas 
On Fri, Jul 21, 2017 at 6:21 AM, Etsuro Fujita
 wrote:
> I mean constraints derived from WITH CHECK OPTIONs specified for parent
> views.  We use the words "WITH CHECK OPTION constraints" in comments in
> nodeModifyTable.c, so the expression "CHECK OPTION constrains" doesn't sound
> not that bad to me.  (I used "CHECK OPTION", not "WITH CHECK OPTION",
> because we use "CHECK OPTION" a lot more in the documentation than "WITH
> CHECK OPTION".)

Yeah, it seems OK to me, too; if the consensus is otherwise, we also
have the option to change it later.  Committed and back-patched as you
had it, but I removed a spurious comma.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Mishandling of WCO constraints in direct foreign table modification

2017-07-21 Thread Etsuro Fujita 

On 2017/07/21 17:18, Kyotaro HORIGUCHI wrote:

At Fri, 21 Jul 2017 12:00:03 +0900, Etsuro Fujita  wrote 
in <15aa9936-9bd8-c9e3-7ca1-394861073...@lab.ntt.co.jp>

Attached is the second version which updated docs in postgres-fdw.sgml
as well.


!no local joins for the query, no row-level local BEFORE or
!AFTER triggers on the target table, and no
!CHECK OPTION constraints from parent views.
!In UPDATE,

Might be a silly question, is CHECK OPTION a "constraint"?


I mean constraints derived from WITH CHECK OPTIONs specified for parent 
views.  We use the words "WITH CHECK OPTION constraints" in comments in 
nodeModifyTable.c, so the expression "CHECK OPTION constrains" doesn't 
sound not that bad to me.  (I used "CHECK OPTION", not "WITH CHECK 
OPTION", because we use "CHECK OPTION" a lot more in the documentation 
than "WITH CHECK OPTION".)


Best regards,
Etsuro Fujita



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Mishandling of WCO constraints in direct foreign table modification

2017-07-21 Thread Kyotaro HORIGUCHI 
At Fri, 21 Jul 2017 12:00:03 +0900, Etsuro Fujita  
wrote in <15aa9936-9bd8-c9e3-7ca1-394861073...@lab.ntt.co.jp>
> On 2017/07/21 3:24, Robert Haas wrote:
> > I think that's reasonable.  This should be committed and back-patched
> > to 9.6, right?
> 
> Yeah, because direct modify was introduced in 9.6.
> 
> Attached is the second version which updated docs in postgres-fdw.sgml
> as well.

!no local joins for the query, no row-level local BEFORE or
!AFTER triggers on the target table, and no
!CHECK OPTION constraints from parent views.
!In UPDATE,

Might be a silly question, is CHECK OPTION a "constraint"?

regards,

-- 
Kyotaro Horiguchi
NTT Open Source Software Center



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Mishandling of WCO constraints in direct foreign table modification

2017-07-20 Thread Etsuro Fujita 

On 2017/07/21 3:24, Robert Haas wrote:

I think that's reasonable.  This should be committed and back-patched
to 9.6, right?


Yeah, because direct modify was introduced in 9.6.

Attached is the second version which updated docs in postgres-fdw.sgml 
as well.


Best regards,
Etsuro Fujita
*** a/contrib/postgres_fdw/expected/postgres_fdw.out
--- b/contrib/postgres_fdw/expected/postgres_fdw.out
***
*** 5856,5861  INSERT INTO ft1(c1, c2) VALUES(, 2);
--- 5856,5921 
  UPDATE ft1 SET c2 = c2 + 1 WHERE c1 = 1;
  ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c2negative;
  -- ===
+ -- test WITH CHECK OPTION constraints
+ -- ===
+ CREATE TABLE base_tbl (a int, b int);
+ CREATE FOREIGN TABLE foreign_tbl (a int, b int)
+   SERVER loopback OPTIONS(table_name 'base_tbl');
+ CREATE VIEW rw_view AS SELECT * FROM foreign_tbl
+   WHERE a < b WITH CHECK OPTION;
+ \d+ rw_view
+View "public.rw_view"
+  Column |  Type   | Collation | Nullable | Default | Storage | Description 
+ +-+---+--+-+-+-
+  a  | integer |   |  | | plain   | 
+  b  | integer |   |  | | plain   | 
+ View definition:
+  SELECT foreign_tbl.a,
+ foreign_tbl.b
+FROM foreign_tbl
+   WHERE foreign_tbl.a < foreign_tbl.b;
+ Options: check_option=cascaded
+ 
+ INSERT INTO rw_view VALUES (0, 10); -- ok
+ INSERT INTO rw_view VALUES (10, 0); -- should fail
+ ERROR:  new row violates check option for view "rw_view"
+ DETAIL:  Failing row contains (10, 0).
+ EXPLAIN (VERBOSE, COSTS OFF)
+ UPDATE rw_view SET b = 20 WHERE a = 0; -- not pushed down
+ QUERY PLAN

+ 
--
+  Update on public.foreign_tbl
+Remote SQL: UPDATE public.base_tbl SET b = $2 WHERE ctid = $1
+->  Foreign Scan on public.foreign_tbl
+  Output: foreign_tbl.a, 20, foreign_tbl.ctid
+  Remote SQL: SELECT a, ctid FROM public.base_tbl WHERE ((a < b)) AND 
((a = 0)) FOR UPDATE
+ (5 rows)
+ 
+ UPDATE rw_view SET b = 20 WHERE a = 0; -- ok
+ EXPLAIN (VERBOSE, COSTS OFF)
+ UPDATE rw_view SET b = -20 WHERE a = 0; -- not pushed down
+ QUERY PLAN

+ 
--
+  Update on public.foreign_tbl
+Remote SQL: UPDATE public.base_tbl SET b = $2 WHERE ctid = $1
+->  Foreign Scan on public.foreign_tbl
+  Output: foreign_tbl.a, '-20'::integer, foreign_tbl.ctid
+  Remote SQL: SELECT a, ctid FROM public.base_tbl WHERE ((a < b)) AND 
((a = 0)) FOR UPDATE
+ (5 rows)
+ 
+ UPDATE rw_view SET b = -20 WHERE a = 0; -- should fail
+ ERROR:  new row violates check option for view "rw_view"
+ DETAIL:  Failing row contains (0, -20).
+ SELECT * FROM foreign_tbl;
+  a | b  
+ ---+
+  0 | 20
+ (1 row)
+ 
+ DROP FOREIGN TABLE foreign_tbl CASCADE;
+ NOTICE:  drop cascades to view rw_view
+ DROP TABLE base_tbl;
+ -- ===
  -- test serial columns (ie, sequence-based defaults)
  -- ===
  create table loc1 (f1 serial, f2 text);
*** a/contrib/postgres_fdw/sql/postgres_fdw.sql
--- b/contrib/postgres_fdw/sql/postgres_fdw.sql
***
*** 1158,1163  UPDATE ft1 SET c2 = c2 + 1 WHERE c1 = 1;
--- 1158,1187 
  ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c2negative;
  
  -- ===
+ -- test WITH CHECK OPTION constraints
+ -- ===
+ 
+ CREATE TABLE base_tbl (a int, b int);
+ CREATE FOREIGN TABLE foreign_tbl (a int, b int)
+   SERVER loopback OPTIONS(table_name 'base_tbl');
+ CREATE VIEW rw_view AS SELECT * FROM foreign_tbl
+   WHERE a < b WITH CHECK OPTION;
+ \d+ rw_view
+ 
+ INSERT INTO rw_view VALUES (0, 10); -- ok
+ INSERT INTO rw_view VALUES (10, 0); -- should fail
+ EXPLAIN (VERBOSE, COSTS OFF)
+ UPDATE rw_view SET b = 20 WHERE a = 0; -- not pushed down
+ UPDATE rw_view SET b = 20 WHERE a = 0; -- ok
+ EXPLAIN (VERBOSE, COSTS OFF)
+ UPDATE rw_view SET b = -20 WHERE a = 0; -- not pushed down
+ UPDATE rw_view SET b = -20 WHERE a = 0; -- should fail
+ SELECT * FROM foreign_tbl;
+ 
+ DROP FOREIGN TABLE foreign_tbl CASCADE;
+ DROP TABLE base_tbl;
+ 
+ -- ===
  -- test serial columns (ie, sequence-based defaults)
  -- ===
  create table loc1 (f1 serial, f2 text);
***

Re: [HACKERS] Mishandling of WCO constraints in direct foreign table modification

2017-07-20 Thread Robert Haas 
On Thu, Jul 20, 2017 at 7:40 AM, Etsuro Fujita
 wrote:
> Here is an example for $subject using postgres_fdw:
>
> postgres=# create foreign table foreign_tbl (a int, b int) server loopback
> options (table_name 'base_tbl');
> CREATE FOREIGN TABLE
> postgres=# create view rw_view as select * from foreign_tbl where a < b with
> check option;
> CREATE VIEW
> postgres=# insert into rw_view values (0, 10);
> INSERT 0 1
> postgres=# explain verbose update rw_view set a = 20 where b = 10;
>   QUERY PLAN
> --
>  Update on public.foreign_tbl  (cost=100.00..146.21 rows=4 width=14)
>->  Foreign Update on public.foreign_tbl  (cost=100.00..146.21 rows=4
> width=14)
>  Remote SQL: UPDATE public.base_tbl SET a = 20 WHERE ((a < b)) AND
> ((b = 10))
> (3 rows)
>
> postgres=# update rw_view set a = 20 where b = 10;
> UPDATE 1
>
> This is wrong!  This should fail.  The reason for that is; direct modify is
> overlooking checking WITH CHECK OPTION constraints from parent views.  I
> think we could do direct modify, even if there are any WITH CHECK OPTIONs,
> in some way or other, but I think that is a feature.  So, I'd like to
> propose to fix this by just giving up direct modify if there are any WITH
> CHECK OPTIONs.  Attached is a patch for that.  I'll add it to the next
> commitfest.

I think that's reasonable.  This should be committed and back-patched
to 9.6, right?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Mishandling of WCO constraints in direct foreign table modification

2017-07-20 Thread Etsuro Fujita 

Here is an example for $subject using postgres_fdw:

postgres=# create foreign table foreign_tbl (a int, b int) server 
loopback options (table_name 'base_tbl');

CREATE FOREIGN TABLE
postgres=# create view rw_view as select * from foreign_tbl where a < b 
with check option;

CREATE VIEW
postgres=# insert into rw_view values (0, 10);
INSERT 0 1
postgres=# explain verbose update rw_view set a = 20 where b = 10;
  QUERY PLAN
--
 Update on public.foreign_tbl  (cost=100.00..146.21 rows=4 width=14)
   ->  Foreign Update on public.foreign_tbl  (cost=100.00..146.21 
rows=4 width=14)
 Remote SQL: UPDATE public.base_tbl SET a = 20 WHERE ((a < b)) 
AND ((b = 10))

(3 rows)

postgres=# update rw_view set a = 20 where b = 10;
UPDATE 1

This is wrong!  This should fail.  The reason for that is; direct modify 
is overlooking checking WITH CHECK OPTION constraints from parent views. 
 I think we could do direct modify, even if there are any WITH CHECK 
OPTIONs, in some way or other, but I think that is a feature.  So, I'd 
like to propose to fix this by just giving up direct modify if there are 
any WITH CHECK OPTIONs.  Attached is a patch for that.  I'll add it to 
the next commitfest.


Best regards,
Etsuro Fujita
*** a/contrib/postgres_fdw/expected/postgres_fdw.out
--- b/contrib/postgres_fdw/expected/postgres_fdw.out
***
*** 5856,5861  INSERT INTO ft1(c1, c2) VALUES(, 2);
--- 5856,5921 
  UPDATE ft1 SET c2 = c2 + 1 WHERE c1 = 1;
  ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c2negative;
  -- ===
+ -- test WITH CHECK OPTION constraints
+ -- ===
+ CREATE TABLE base_tbl (a int, b int);
+ CREATE FOREIGN TABLE foreign_tbl (a int, b int)
+   SERVER loopback OPTIONS(table_name 'base_tbl');
+ CREATE VIEW rw_view AS SELECT * FROM foreign_tbl
+   WHERE a < b WITH CHECK OPTION;
+ \d+ rw_view
+View "public.rw_view"
+  Column |  Type   | Collation | Nullable | Default | Storage | Description 
+ +-+---+--+-+-+-
+  a  | integer |   |  | | plain   | 
+  b  | integer |   |  | | plain   | 
+ View definition:
+  SELECT foreign_tbl.a,
+ foreign_tbl.b
+FROM foreign_tbl
+   WHERE foreign_tbl.a < foreign_tbl.b;
+ Options: check_option=cascaded
+ 
+ INSERT INTO rw_view VALUES (0, 10); -- ok
+ INSERT INTO rw_view VALUES (10, 0); -- should fail
+ ERROR:  new row violates check option for view "rw_view"
+ DETAIL:  Failing row contains (10, 0).
+ EXPLAIN (VERBOSE, COSTS OFF)
+ UPDATE rw_view SET b = 20 WHERE a = 0; -- not pushed down
+ QUERY PLAN

+ 
--
+  Update on public.foreign_tbl
+Remote SQL: UPDATE public.base_tbl SET b = $2 WHERE ctid = $1
+->  Foreign Scan on public.foreign_tbl
+  Output: foreign_tbl.a, 20, foreign_tbl.ctid
+  Remote SQL: SELECT a, ctid FROM public.base_tbl WHERE ((a < b)) AND 
((a = 0)) FOR UPDATE
+ (5 rows)
+ 
+ UPDATE rw_view SET b = 20 WHERE a = 0; -- ok
+ EXPLAIN (VERBOSE, COSTS OFF)
+ UPDATE rw_view SET b = -20 WHERE a = 0; -- not pushed down
+ QUERY PLAN

+ 
--
+  Update on public.foreign_tbl
+Remote SQL: UPDATE public.base_tbl SET b = $2 WHERE ctid = $1
+->  Foreign Scan on public.foreign_tbl
+  Output: foreign_tbl.a, '-20'::integer, foreign_tbl.ctid
+  Remote SQL: SELECT a, ctid FROM public.base_tbl WHERE ((a < b)) AND 
((a = 0)) FOR UPDATE
+ (5 rows)
+ 
+ UPDATE rw_view SET b = -20 WHERE a = 0; -- should fail
+ ERROR:  new row violates check option for view "rw_view"
+ DETAIL:  Failing row contains (0, -20).
+ SELECT * FROM foreign_tbl;
+  a | b  
+ ---+
+  0 | 20
+ (1 row)
+ 
+ DROP FOREIGN TABLE foreign_tbl CASCADE;
+ NOTICE:  drop cascades to view rw_view
+ DROP TABLE base_tbl;
+ -- ===
  -- test serial columns (ie, sequence-based defaults)
  -- ===
  create table loc1 (f1 serial, f2 text);
*** a/contrib/postgres_fdw/sql/postgres_fdw.sql
--- b/contrib/postgres_fdw/sql/postgres_fdw.sql
***
*** 1158,1163  UPDATE ft1 SET c2 = c2 + 1 WHERE c1 = 1;
--- 1158,1187 
  ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c2negative;
  
  -- ===
+ -- test WITH CHECK OPTION