agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedBUG #19698: IMPORT FOREIGN SCHEMA treats a NOT VALID NOT NULL constraint as validated
4+ messages / 2 participants
[nested] [flat]
* BUG #19698: IMPORT FOREIGN SCHEMA treats a NOT VALID NOT NULL constraint as validated
@ 2026-09-18 07:11 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 2 replies; 4+ messages in thread
From: PG Bug reporting form @ 2026-09-18 07:11 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: imchifan@163.com
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19698: IMPORT FOREIGN SCHEMA treats a NOT VALID NOT NULL constraint as validated
@ 2026-09-18 16:29 Kirill Reshke <reshkekirill@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 0 replies; 4+ messages in thread
From: Kirill Reshke @ 2026-09-18 16:29 UTC (permalink / raw)
To: imchifan@163.com; pgsql-bugs@lists.postgresql.org
On Fri, 18 Sept 2026 at 13:25, PG Bug reporting form
<noreply@postgresql.org> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19698: IMPORT FOREIGN SCHEMA treats a NOT VALID NOT NULL constraint as validated
@ 2026-09-18 20:03 Kirill Reshke <reshkekirill@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 1 reply; 4+ messages in thread
From: Kirill Reshke @ 2026-09-18 20:03 UTC (permalink / raw)
To: imchifan@163.com; pgsql-bugs@lists.postgresql.org
On Fri, 18 Sept 2026 at 13:25, PG Bug reporting form
<noreply@postgresql.org> 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
Attachments:
[application/octet-stream] v1-0001-Treat-NOT-NULL-NOT-VALID-as-nullable-during-IMPOR.patch (1.7K, ../../CALdSSPjL=bQoEeiaf=4nrG04-nG5rH1mEuUu5Y+-dWNTA4koPQ@mail.gmail.com/2-v1-0001-Treat-NOT-NULL-NOT-VALID-as-nullable-during-IMPOR.patch)
download | inline diff:
From 7bb83dfe4b84a808b7ecf1f84561bdf6fd05ab0a Mon Sep 17 00:00:00 2001
From: reshke <reshke@double.cloud>
Date: Fri, 18 Sep 2026 22:25:13 +0300
Subject: [PATCH v1] Treat NOT NULL NOT VALID as nullable during IMPORT SCHEMA
---
contrib/postgres_fdw/postgres_fdw.c | 23 +++++++++++++++++++++--
1 file changed, 21 insertions(+), 2 deletions(-)
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index fbe01fdde85..2ab5e2bd1e3 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -6601,8 +6601,27 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
appendStringInfoString(&buf,
"SELECT relname, "
" attname, "
- " format_type(atttypid, atttypmod), "
- " attnotnull, "
+ " format_type(atttypid, atttypmod), ");
+
+ /*
+ * NOT VALID NOT NULL constraints are supported since Postgres 18.
+ * pg_attribute.attnotnull is set for such constraints, but they are not
+ * yet validated, so the remote table might still contain NULLs.
+ */
+ if (PQserverVersion(conn) >= 180000)
+ appendStringInfoString(&buf,
+ " CASE WHEN EXISTS ("
+ " SELECT 1 FROM pg_catalog.pg_constraint con "
+ " WHERE con.conrelid = c.oid "
+ " AND con.contype = 'n' "
+ " AND a.attnum = ANY (con.conkey) "
+ " AND NOT con.convalidated) "
+ " THEN false ELSE a.attnotnull END, ");
+ else
+ appendStringInfoString(&buf,
+ " attnotnull, ");
+
+ appendStringInfoString(&buf,
" pg_get_expr(adbin, adrelid), ");
/* Generated columns are supported since Postgres 12 */
--
2.43.0
[application/octet-stream] v1-0001-Fix-NOT-NULL-NOT-VALID-constraints-import-in-FDW.patch (8.0K, ../../CALdSSPjL=bQoEeiaf=4nrG04-nG5rH1mEuUu5Y+-dWNTA4koPQ@mail.gmail.com/3-v1-0001-Fix-NOT-NULL-NOT-VALID-constraints-import-in-FDW.patch)
download | inline diff:
From 35aee67822e810a7c6a84e787d55cf13284d6e54 Mon Sep 17 00:00:00 2001
From: reshke <reshke@double.cloud>
Date: Fri, 18 Sep 2026 22:25:13 +0300
Subject: [PATCH v1] Fix NOT NULL NOT VALID constraints import in FDW
---
.../postgres_fdw/expected/postgres_fdw.out | 40 +++++++++++++++++++
contrib/postgres_fdw/postgres_fdw.c | 36 ++++++++++++++++-
contrib/postgres_fdw/sql/postgres_fdw.sql | 19 +++++++++
src/backend/catalog/heap.c | 2 +-
src/backend/parser/parse_utilcmd.c | 10 +++++
5 files changed, 104 insertions(+), 3 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 739f43af7bb..60fdbd6d55d 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -10989,6 +10989,46 @@ IMPORT FOREIGN SCHEMA import_source EXCEPT (t1, "x 4", nonesuch, t4_part)
import_dest4 | x 6 | loopback | (schema_name 'import_source', table_name 'x 6') |
(7 rows)
+-- NOT VALID NOT NULL constraints must be imported as NOT VALID
+CREATE TABLE import_source.t_notvalid (c1 int);
+INSERT INTO import_source.t_notvalid VALUES (NULL), (1);
+ALTER TABLE import_source.t_notvalid
+ ADD CONSTRAINT t_notvalid_nn NOT NULL c1 NOT VALID;
+CREATE SCHEMA import_dest6;
+IMPORT FOREIGN SCHEMA import_source LIMIT TO (t_notvalid)
+ FROM SERVER loopback INTO import_dest6;
+\d import_dest6.t_notvalid
+ Foreign table "import_dest6.t_notvalid"
+ Column | Type | Collation | Nullable | Default | FDW options
+--------+---------+-----------+----------+---------+--------------------
+ c1 | integer | | not null | | (column_name 'c1')
+Server: loopback
+FDW options: (schema_name 'import_source', table_name 't_notvalid')
+
+SELECT a.attnotnull, c.conname, c.convalidated
+ FROM pg_attribute a JOIN pg_constraint c
+ ON c.conrelid = a.attrelid AND a.attnum = ANY (c.conkey)
+ WHERE a.attrelid = 'import_dest6.t_notvalid'::regclass
+ AND a.attname = 'c1' AND c.contype = 'n';
+ attnotnull | conname | convalidated
+------------+------------------------+--------------
+ t | t_notvalid_c1_not_null | f
+(1 row)
+
+SET constraint_exclusion = on;
+SELECT * FROM import_dest6.t_notvalid WHERE c1 IS NULL;
+ c1
+----
+
+(1 row)
+
+SELECT * FROM import_dest6.t_notvalid WHERE c1 IS NOT NULL;
+ c1
+----
+ 1
+(1 row)
+
+RESET constraint_exclusion;
-- Assorted error cases
IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest4;
ERROR: relation "t1" already exists
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index fbe01fdde85..b3bf3e77e8a 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -6621,6 +6621,23 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
appendStringInfoString(&buf,
" NULL, NULL ");
+ /*
+ * Fetch a flag indicating whether the column's NOT NULL constraint is
+ * NOT VALID (supported since Postgres 18): the remote table might
+ * still contain NULLs.
+ */
+ if (PQserverVersion(conn) >= 180000)
+ appendStringInfoString(&buf,
+ ", EXISTS ("
+ " SELECT 1 FROM pg_catalog.pg_constraint con "
+ " WHERE con.conrelid = c.oid "
+ " AND con.contype = 'n' "
+ " AND a.attnum = ANY (con.conkey) "
+ " AND NOT con.convalidated) ");
+ else
+ appendStringInfoString(&buf,
+ ", false ");
+
appendStringInfoString(&buf,
"FROM pg_class c "
" JOIN pg_namespace n ON "
@@ -6693,8 +6710,10 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
{
char *tablename = PQgetvalue(res, i, 0);
bool first_item = true;
+ StringInfoData nn_not_valid;
resetStringInfo(&buf);
+ initStringInfo(&nn_not_valid);
appendStringInfo(&buf, "CREATE FOREIGN TABLE %s (\n",
quote_identifier(tablename));
@@ -6704,6 +6723,7 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
char *attname;
char *typename;
char *attnotnull;
+ char *attnotnull_not_valid;
char *attgenerated;
char *attdefault;
char *collname;
@@ -6716,6 +6736,7 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
attname = PQgetvalue(res, i, 1);
typename = PQgetvalue(res, i, 2);
attnotnull = PQgetvalue(res, i, 3);
+ attnotnull_not_valid = PQgetvalue(res, i, 8);
attdefault = PQgetisnull(res, i, 4) ? NULL :
PQgetvalue(res, i, 4);
attgenerated = PQgetisnull(res, i, 5) ? NULL :
@@ -6764,13 +6785,24 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
attdefault);
}
- /* Add NOT NULL if needed */
+ /* Add NOT NULL if needed; NOT VALID ones as table constraints */
if (import_not_null && attnotnull[0] == 't')
- appendStringInfoString(&buf, " NOT NULL");
+ {
+ if (attnotnull_not_valid[0] == 't')
+ appendStringInfo(&nn_not_valid, ",\nNOT NULL %s NOT VALID",
+ quote_identifier(attname));
+ else
+ appendStringInfoString(&buf, " NOT NULL");
+ }
}
while (++i < numrows &&
strcmp(PQgetvalue(res, i, 0), tablename) == 0);
+ /* Emit any NOT NULL NOT VALID constraints */
+ if (nn_not_valid.len > 0)
+ appendStringInfoString(&buf, nn_not_valid.data);
+ pfree(nn_not_valid.data);
+
/*
* Add server name and table-level options. We specify remote schema
* and table name as options (the latter to ensure that renaming the
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index f1ca3204382..d00db7d7803 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -3537,6 +3537,25 @@ IMPORT FOREIGN SCHEMA import_source EXCEPT (t1, "x 4", nonesuch, t4_part)
FROM SERVER loopback INTO import_dest4;
\det+ import_dest4.*
+-- NOT VALID NOT NULL constraints must be imported as NOT VALID
+CREATE TABLE import_source.t_notvalid (c1 int);
+INSERT INTO import_source.t_notvalid VALUES (NULL), (1);
+ALTER TABLE import_source.t_notvalid
+ ADD CONSTRAINT t_notvalid_nn NOT NULL c1 NOT VALID;
+CREATE SCHEMA import_dest6;
+IMPORT FOREIGN SCHEMA import_source LIMIT TO (t_notvalid)
+ FROM SERVER loopback INTO import_dest6;
+\d import_dest6.t_notvalid
+SELECT a.attnotnull, c.conname, c.convalidated
+ FROM pg_attribute a JOIN pg_constraint c
+ ON c.conrelid = a.attrelid AND a.attnum = ANY (c.conkey)
+ WHERE a.attrelid = 'import_dest6.t_notvalid'::regclass
+ AND a.attname = 'c1' AND c.contype = 'n';
+SET constraint_exclusion = on;
+SELECT * FROM import_dest6.t_notvalid WHERE c1 IS NULL;
+SELECT * FROM import_dest6.t_notvalid WHERE c1 IS NOT NULL;
+RESET constraint_exclusion;
+
-- Assorted error cases
IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest4;
IMPORT FOREIGN SCHEMA nonesuch FROM SERVER loopback INTO import_dest4;
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index 1c188b7a0ff..42c943ff5ed 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -3085,7 +3085,7 @@ AddRelationNotNullConstraints(Relation rel, List *constraints,
nnnames = lappend(nnnames, conname);
StoreRelNotNull(rel, conname,
- attnum, true, true,
+ attnum, constr->initially_valid, true,
inhcount, constr->is_no_inherit);
nncols = lappend_int(nncols, attnum);
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index f838311090b..60bbbb43c3f 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -369,6 +369,16 @@ transformCreateStmt(CreateStmt *stmt, const char *queryString)
*/
transformCheckConstraints(&cxt, !cxt.isforeign);
+ /* Likewise for not-null constraints: foreign tables can have data. */
+ if (!cxt.isforeign)
+ {
+ foreach_node(Constraint, nn, cxt.nnconstraints)
+ {
+ nn->skip_validation = false;
+ nn->initially_valid = true;
+ }
+ }
+
/*
* Output results.
*/
--
2.43.0
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19698: IMPORT FOREIGN SCHEMA treats a NOT VALID NOT NULL constraint as validated
@ 2026-09-19 04:31 Kirill Reshke <reshkekirill@gmail.com>
parent: Kirill Reshke <reshkekirill@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Kirill Reshke @ 2026-09-19 04:31 UTC (permalink / raw)
To: imchifan@163.com; pgsql-bugs@lists.postgresql.org
On Sat, 19 Sept 2026 at 01:03, Kirill Reshke <reshkekirill@gmail.com> wrote:
>
> On Fri, 18 Sept 2026 at 13:25, PG Bug reporting form
> <noreply@postgresql.org> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2026-09-19 04:31 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-09-18 07:11 BUG #19698: IMPORT FOREIGN SCHEMA treats a NOT VALID NOT NULL constraint as validated PG Bug reporting form <noreply@postgresql.org>
2026-09-18 16:29 ` Kirill Reshke <reshkekirill@gmail.com>
2026-09-18 20:03 ` Kirill Reshke <reshkekirill@gmail.com>
2026-09-19 04:31 ` Kirill Reshke <reshkekirill@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox