On Tue, Aug 1, 2017 at 2:41 PM, Amit Langote <langote_amit...@lab.ntt.co.jp> wrote: > On 2017/08/01 17:54, Simon Riggs wrote: >> On 1 August 2017 at 08:37, Amit Langote <langote_amit...@lab.ntt.co.jp> >> wrote: >>> On 2017/08/01 15:22, Simon Riggs wrote: >>>> On 1 August 2017 at 07:16, Amit Langote <langote_amit...@lab.ntt.co.jp> >>>> wrote: >>>>> In f27a6b15e656 (9.6 & later), we decided to "Mark CHECK constraints >>>>> declared NOT VALID valid if created with table." In retrospect, >>>>> constraints on foreign tables should have been excluded from consideration >>>>> in that commit, because the thinking behind the aforementioned commit >>>>> (that the constraint is trivially validated because the newly created >>>>> table contains no data) does not equally apply to the foreign tables case. >>>>> >>>>> Should we do something about that? >>>> >>>> In what way does it not apply? Do you have a failure case? >>> >>> Sorry for not mentioning the details. >>> >>> I was thinking that a foreign table starts containing the data of the >>> remote object it points to the moment it's created (unlike local tables >>> which contain no data to begin with). If a user is not sure whether a >>> particular constraint being created in the same command holds for the >>> remote data, they may mark it as NOT VALID and hope that the system treats >>> the constraint as such until such a time that they mark it valid by >>> running ALTER TABLE VALIDATE CONSTRAINT. Since the planner is the only >>> consumer of pg_constraint.convalidated, that means the user expects the >>> planner to ignore such a constraint. Since f27a6b15e656, users are no >>> longer able to expect so. >> >> For Foreign Tables, it sounds like an issue. Don't think it exists for >> normal tables. > > Yes. I was saying in my first email that we should not disregard user's > request to mark a constraint NOT VALID if the table in question is a > *foreign table*. > > So, it's OK that commit f27a6b15e656 changed things to ignore NOT VALID if > it's in the following command: > > create table foo (a int, constraint check_a check (a > 0) not valid); > > But, not OK in the following command: > > create foreign table ffoo ( > a int, > constraint check_a check (a > 0) not valid > ) server loopback options (table_name 'foo'); >
If the user has specified "not valid" for a constraint on the foreign table, there is high chance that s/he is aware of the fact that the remote table that the foreign table points to has some rows which will violet the constraint. So, +1. -- Best Wishes, Ashutosh Bapat EnterpriseDB Corporation The Postgres Database Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
