Re: foreign table creation and NOT VALID check constraints

On 2017/08/01 17:54, Simon Riggs wrote:
> On 1 August 2017 at 08:37, Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>> On 2017/08/01 15:22, Simon Riggs wrote:
>>> On 1 August 2017 at 07:16, Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)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');

Thanks,
Amit