Re: foreign table creation and NOT VALID check constraints

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 example:

create extension postgres_fdw;

create server loopback
foreign data wrapper postgres_fdw
options (dbname 'postgres');

create table foo (a) as select 1;

create foreign table ffoo (
a int,
constraint check_a check (a = 0) not valid
) server loopback options (table_name 'foo');

set constraint_exclusion to on;

-- constraint check_a will exclude the table
select * from ffoo where a = 1;
a
---
(0 rows)

explain select * from ffoo where a = 1;
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.00 rows=0 width=4)
One-Time Filter: false
(2 rows)

The above "issue" doesn't occur if the constraint is created
after-the-fact, whereby it's possible to specify NOT VALID and have the
system actually store it in the catalog as such.

alter foreign table ffoo drop constraint check_a;
alter foreign table ffoo add constraint check_a check (a = 0) not valid;

select * from ffoo where a = 1;
a
---
1
(1 row)

explain select * from ffoo where a = 1;
QUERY PLAN
-------------------------------------------------------------
Foreign Scan on ffoo (cost=100.00..146.86 rows=15 width=4)

Maybe, this is not such a big matter though, because the core system
doesn't actually enforce any constraints of the foreign tables locally
anyway (which is a documented fact), but my concern is the possibility of
some considering this a POLA violation. Lack of complaints so far perhaps
means nobody did.

Thanks,
Amit