From c945b06d5f03e30cec279df516688c70787b2b2c Mon Sep 17 00:00:00 2001 From: =?utf8?q?=C3=81lvaro=20Herrera?= Date: Sat, 18 Oct 2025 18:18:19 +0200 Subject: [PATCH] Fix determination of not-null constraint "locality" for inherited columns It is possible to have a non-inherited not-null constraint on an inherited column, but we were failing to preserve such constraints during pg_upgrade where the source is 17 or older, because of a bug in the pg_dump query for it. Oversight in commit 14e87ffa5c54. Fix that query. In passing, touch-up a bogus nearby comment introduced by the same commit. In version 17, make the regression tests leave a table in this situation, so that this scenario is tested in the cross-version upgrade tests of 18 and up. Author: Dilip Kumar Reported-by: Andrew Bille Bug: #19074 Backpatch-through: 18 Discussion: https://postgr.es/m/19074-ae2548458cf0195c@postgresql.org --- src/test/regress/expected/constraints.out | 4 ++++ src/test/regress/sql/constraints.sql | 5 +++++ 2 files changed, 9 insertions(+) diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out index a5b5795e587..d64128bc416 100644 --- a/src/test/regress/expected/constraints.out +++ b/src/test/regress/expected/constraints.out @@ -834,3 +834,7 @@ DROP ROLE regress_constraint_comments_noaccess; CREATE DOMAIN constraint_comments_dom AS int; ALTER DOMAIN constraint_comments_dom ADD CONSTRAINT inv_ck CHECK (value > 0) NOT VALID; COMMENT ON CONSTRAINT inv_ck ON DOMAIN constraint_comments_dom IS 'comment on invalid constraint'; +-- Create a table that exercises pg_upgrade +CREATE TABLE regress_notnull1 (a integer); +CREATE TABLE regress_notnull2 () INHERITS (regress_notnull1); +ALTER TABLE ONLY regress_notnull2 ALTER COLUMN a SET NOT NULL; diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql index c47db9469f5..b5e10f79202 100644 --- a/src/test/regress/sql/constraints.sql +++ b/src/test/regress/sql/constraints.sql @@ -638,3 +638,8 @@ CREATE DOMAIN constraint_comments_dom AS int; ALTER DOMAIN constraint_comments_dom ADD CONSTRAINT inv_ck CHECK (value > 0) NOT VALID; COMMENT ON CONSTRAINT inv_ck ON DOMAIN constraint_comments_dom IS 'comment on invalid constraint'; + +-- Create a table that exercises pg_upgrade +CREATE TABLE regress_notnull1 (a integer); +CREATE TABLE regress_notnull2 () INHERITS (regress_notnull1); +ALTER TABLE ONLY regress_notnull2 ALTER COLUMN a SET NOT NULL; -- 2.47.3