Re: BUG #19393: pg_upgrade fails with duplicate key violation when CHECK constraint named *_not_null exists

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/)