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.
