BUG #19698: IMPORT FOREIGN SCHEMA treats a NOT VALID NOT NULL constraint as validated
BUG #19698: IMPORT FOREIGN SCHEMA treats a NOT VALID NOT NULL constraint as validated
От:
PG Bug reporting form <noreply@postgresql.org>
Дата:
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.
Re: BUG #19698: IMPORT FOREIGN SCHEMA treats a NOT VALID NOT NULL constraint as validated
От:
Kirill Reshke <reshkekirill@gmail.com>
Дата:
On Fri, 18 Sept 2026 at 13:25, PG Bug reporting form 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
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 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
Re: BUG #19698: IMPORT FOREIGN SCHEMA treats a NOT VALID NOT NULL constraint as validated
От:
Kirill Reshke <reshkekirill@gmail.com>
Дата:
On Sat, 19 Sept 2026 at 01:03, Kirill Reshke wrote:
>
> On Fri, 18 Sept 2026 at 13:25, PG Bug reporting form
> 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
Re: BUG #19698: IMPORT FOREIGN SCHEMA treats a NOT VALID NOT NULL constraint as validated
От:
Andrey Borodin <x4mmm@yandex-team.ru>
Дата:
On 18 Sep 2026, Kirill Reshke wrote: > One simple fix can be simply importing NOT NULL NOT VALID as a > nullable column, but this probably would make some people unhappy. I support the simpler fix. Of the constraints the local planner could use to infer properties of the remote rows, postgres_fdw imports only NOT NULL. Importing only validated ones seems a reasonable policy. This also needs fixing on older branches importing from a PG18+ server. Could we adapt the query-result test from your second patch for the first one? Thank you! Best regards, Andrey Borodin.
Re: BUG #19698: IMPORT FOREIGN SCHEMA treats a NOT VALID NOT NULL constraint as validated
От:
Etsuro Fujita <etsuro.fujita@gmail.com>
Дата:
On Fri, Sep 18, 2026 at 5:25 PM PG Bug reporting form wrote: > 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. For the reported case, I think we should set the import_not_null option for IMPORT FOREIGN SCHEMA to false. That option is enabled by default, but should be disabled if it isn't ensured that the remote table never has NULL. Best regards, Etsuro Fujita
Re: BUG #19698: IMPORT FOREIGN SCHEMA treats a NOT VALID NOT NULL constraint as validated
От:
Etsuro Fujita <etsuro.fujita@gmail.com>
Дата:
On Mon, Sep 21, 2026 at 7:18 PM Andrey Borodin wrote: > On 18 Sep 2026, Kirill Reshke wrote: > > One simple fix can be simply importing NOT NULL NOT VALID as a > > nullable column, but this probably would make some people unhappy. > > I support the simpler fix. I don't think this is a bug. See [1]. Best regards, Etsuro Fujita [1] https://www.postgresql.org/message-id/CAPmGK15wJxr-TZ9gvptw%2BrCw12RoESt_HVFZhLFc1_P4k50dkQ%40mail.gmail.com