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

On Fri, 18 Sept 2026 at 13:25, PG Bug reporting form
<noreply@postgresql.org> wrote:

The following bug has been logged on the website:

Bug reference: 19698
Logged by: Qifan Liu
Email address: imchifan@163.com
PostgreSQL version: 18.6
Operating system: Linux/amd64
Description:

PostgreSQL version: PostgreSQL 18.6
Operating system: Linux/amd64

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.

Steps to reproduce
------------------
Run the following input with psql:

\set ON_ERROR_STOP on

CREATE DATABASE fdw_not_valid_test;
\connect fdw_not_valid_test

CREATE EXTENSION postgres_fdw;
CREATE SCHEMA remote_schema;
CREATE SCHEMA local_schema;

CREATE TABLE remote_schema.t (id integer);
INSERT INTO remote_schema.t VALUES (NULL), (1);
ALTER TABLE remote_schema.t
ADD CONSTRAINT remote_nn NOT NULL id NOT VALID;

CREATE SERVER loopback_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (dbname 'fdw_not_valid_test');
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback_server;

IMPORT FOREIGN SCHEMA remote_schema LIMIT TO (t)
FROM SERVER loopback_server INTO local_schema;

SELECT a.attnotnull AS imported_attnotnull,
c.convalidated AS imported_constraint_validated
FROM pg_attribute a
JOIN pg_constraint c
ON c.conrelid = a.attrelid AND a.attnum = ANY (c.conkey)
WHERE a.attrelid = 'local_schema.t'::regclass
AND a.attname = 'id'
AND c.contype = 'n';

SET constraint_exclusion = on;
SELECT count(*) AS null_rows_visible_through_import
FROM local_schema.t
WHERE id IS NULL;

ALTER FOREIGN TABLE local_schema.t ALTER COLUMN id DROP NOT NULL;
SELECT count(*) AS null_rows_after_correcting_metadata
FROM local_schema.t
WHERE id IS NULL;

Actual result
-------------
imported_attnotnull | imported_constraint_validated
---------------------+-------------------------------
t | t

null_rows_visible_through_import
----------------------------------
0

null_rows_after_correcting_metadata
-------------------------------------
1

The imported constraint is represented as validated NOT NULL metadata. The
query initially reports no NULL rows, but reports the existing NULL row
after that metadata is removed.

Expected result
---------------
The imported foreign table must not advertise the remote NOT VALID
constraint as a validated NOT NULL invariant. The query through the foreign
table should return a count of 1, matching the result after the incorrect
local metadata is removed, because the remote NULL row remains valid and
visible.

This reproduces on current master

--
Best regards,
Kirill Reshke

On Fri, 18 Sept 2026 at 13:25, PG Bug reporting form
<noreply@postgresql.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

On Sat, 19 Sept 2026 at 01:03, Kirill Reshke <reshkekirill@gmail.com> wrote:

On Fri, 18 Sept 2026 at 13:25, PG Bug reporting form
<noreply@postgresql.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