Re: 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(at)postgresql(dot)org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference: 19698
> Logged by: Qifan Liu
> Email address: imchifan(at)163(dot)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