From: | Etsuro Fujita <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Mishandling of WCO constraints in direct foreign table modification |
Date: | 2017-07-20 11:40:31 |
Message-ID: | f8a48f54-6f02-9c8a-5250-9791603171ee@lab.ntt.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
Attachment | Content-Type | Size |
---|---|---|
fix-direct-modify.patch | text/plain | 5.7 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Ashutosh Bapat | 2017-07-20 11:47:04 | Re: <> join selectivity estimate question |
Previous Message | Ashutosh Sharma | 2017-07-20 11:09:26 | Re: [PATCH] pageinspect function to decode infomasks |