Re: BUG #19698: IMPORT FOREIGN SCHEMA treats a NOT VALID NOT NULL constraint as validated

On Sat, 19 Sept 2026 at 01:03, Kirill Reshke <reshkekirill(at)gmail(dot)com> wrote:
>
> On Fri, 18 Sept 2026 at 13:25, PG Bug reporting form
> <noreply(at)postgresql(dot)org> wrote:
> >
> > The following bug has been logged on the website:
>
> > Description
> > -----------
> > When IMPORT FOREIGN SCHEMA imports a remote table having a NOT NULL
> > constraint declared NOT VALID, postgres_fdw creates trusted local NOT NULL
> > metadata. The remote table can still contain NULL values because its
> > constraint has not been validated. With constraint_exclusion enabled,
> > PostgreSQL relies on the imported metadata and incorrectly excludes a query
> > that would find such a row. Queries through the imported foreign table can
> > therefore silently omit existing rows.
>
>
> I think this analysis is correct. Thanks.
>
> One simple fix can be simply importing NOT NULL NOT VALID as a
> nullable column, but this probably would make some people unhappy.
>
> Another option is to actually declare the column as NOT NULL NOT
> VALID. This patch is required to support NOT VALID constr during
> create DDL.
> NOT VALID contrs are impossible for regular relation since they are
> created empty, but that's not the case for FDW. I have done this in
> simple POC
>
> PFA both patches.
>
> --
> Best regards,
> Kirill Reshke

So, I was thinking about this more, and looks like there are 2 related
issues (both with and without my fix#2) here:

1) CREATE FOREIGN table LIKE drops NOT VALID
CREATE FOREIGN TABLE local_s.src (id integer,
CONSTRAINT src_nn NOT NULL id NOT VALID)
SERVER loopback OPTIONS (schema_name 'remote_s', table_name 't');

SELECT contype, convalidated FROM pg_constraint
WHERE conrelid = 'local_s.src'::regclass;
-- n | f (ok)

CREATE FOREIGN TABLE local_s.cp (LIKE local_s.src INCLUDING ALL)
SERVER loopback;
ALTER FOREIGN TABLE local_s.cp OPTIONS (ADD schema_name 'remote_s',
ADD table_name 't');

SELECT contype, convalidated FROM pg_constraint
WHERE conrelid = 'local_s.cp'::regclass;
-- n | t (not true)

2) table inheritance does not copy NOT VALID - even without FOREIGN

CREATE TABLE inh_parent (a int);
ALTER TABLE inh_parent ADD CONSTRAINT inn NOT NULL a NOT VALID;
CREATE TABLE inh_child () INHERITS (inh_parent);
SELECT contype, convalidated FROM pg_constraint
WHERE conrelid = 'inh_child'::regclass; -- n | t (bad)

select count(1) from inh_parent where a is null; -- 1 (ok)

select count(1) from inh_child where a is null; -- 0 (bad)

This issue needs to be fixed for local INHERIT relations IMO, for
FOREIGN INHERIT relations - not sure.

Also ALTER TABLE .. VALIDATE CONSTRAINT (NOT NULL NOT VALID); does not
scan remote relation, and sets dubious convalidated.
But looks like this is by-design, because we do not control remote
relations, so convalidated may become stale when remote constraint
dropped.

So, given this, I think that maybe planner should never trust NN
constraints for remote relations?

--
Best regards,
Kirill Reshke