agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedFrom: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: imchifan@163.com
Subject: BUG #19698: IMPORT FOREIGN SCHEMA treats a NOT VALID NOT NULL constraint as validated
Date: Fri, 18 Sep 2026 07:11:23 +0000
Message-ID: <19698-28fd44978ce03a4e@postgresql.org> (raw)
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.
view thread (3+ messages) latest in thread
Message-ID: <19698-28fd44978ce03a4e@postgresql.org>
Permalink: ../19698-28fd44978ce03a4e@postgresql.org/
Also on: postgresql.org/message-id/19698-28fd44978ce03a4e@postgresql.org
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, imchifan@163.com
Subject: Re: BUG #19698: IMPORT FOREIGN SCHEMA treats a NOT VALID NOT NULL constraint as validated
In-Reply-To: <19698-28fd44978ce03a4e@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox