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