Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Subject: BUG #19698: IMPORT FOREIGN SCHEMA treats a NOT VALID NOT NULL
 constraint as validated
To: pgsql-bugs@lists.postgresql.org
From: PG Bug reporting form <noreply@postgresql.org>
Cc: imchifan@163.com
Reply-To: imchifan@163.com, pgsql-bugs@lists.postgresql.org
Date: Fri, 18 Sep 2026 07:11:23 +0000
Message-ID: <19698-28fd44978ce03a4e@postgresql.org>
Auto-Submitted: auto-generated
Archived-At: 
 <https://www.postgresql.org/message-id/19698-28fd44978ce03a4e%40postgresql.org>
Precedence: bulk

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:       =20

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 =3D a.attrelid AND a.attnum =3D ANY (c.conkey)
WHERE a.attrelid =3D 'local_schema.t'::regclass
  AND a.attname =3D 'id'
  AND c.contype =3D 'n';

SET constraint_exclusion =3D 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.