Feedback-ID: ie3de48e3:Fastmail
Date: Sat, 21 Feb 2026 12:56:24 +0100
From: =?utf-8?Q?=C3=81lvaro?= Herrera <alvherre@kurilemu.de>
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: huseyin.d3r@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19393: pg_upgrade fails with duplicate key violation when
 CHECK constraint named *_not_null exists
Message-ID: <202602211127.yhfjy43if2kk@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <61c535617992fff830961ecc09a9c20096bc1f36.camel@cybertec.at>
Archived-At: <https://www.postgresql.org/message-id/202602211127.yhfjy43if2kk%40alvherre.pgsql>
Precedence: bulk

On 2026-Feb-05, Laurenz Albe wrote:

> On Thu, 2026-02-05 at 15:58 +0100, I wrote:
> > The bug is actually not in pg_upgrade, but in CREATE TABLE.  The attached patch
> > fixes the problem for me by avoiding given constraint names when generating
> > the names for NOT NULL constraints.
> 
> ... and here is v2, including a regression test.

Thanks for this!  I have pushed it now to 18 and master (right before
the embargo for next week's release -- not really apologizing about
that, since this is clearly something that's going to bite users as they
move up to 18).  Two notes:

1. this will cause an ABI break report for AddRelationNotNullConstraints
in branch 18.  I considered the idea of adding a shim function
preserving the original API, but I think this is not a function likely
to be used by third-party code.  So I'll address this by adding an entry
to .abi-compliance-history instead.

2. I moved this foreach loop

> @@ -2905,6 +2907,12 @@ AddRelationNotNullConstraints(Relation rel, List *constraints,
>  	 * system-generated name conflicts we just generate another.
>  	 */
>  	nnnames = NIL;
> +	foreach_ptr(CookedConstraint, cons, existing_constraints)
> +	{
> +		if (cons->name != NULL)
> +			nnnames = lappend(nnnames, cons->name);
> +	}
> +
>  	givennames = NIL;

from AddRelationNotNullConstraints to DefineRelation; it seems more
natural for the former to receive a list of constraint names than a list
of CookedConstraints.

Thanks Hüseyin for the report and Laurenz for the fix!

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
"How strange it is to find the words "Perl" and "saner" in such close
proximity, with no apparent sense of irony. I doubt that Larry himself
could have managed it."         (ncm, http://lwn.net/Articles/174769/)