BUG #19393: pg_upgrade fails with duplicate key violation when CHECK constraint named *_not_null exists
The following bug has been logged on the website:
Bug reference: 19393
Logged by: Hüseyin Demir
Email address: huseyin.d3r@gmail.com
PostgreSQL version: 18.1
Operating system: CentOS 9
Description:
Hello,
While I was working on upgrading a PostgreSQL 13 cluster to PostgreSQL 18.1
I encountered the following problem. pg_upgrade fails if a table has both a
NOT NULL column and a CHECK constraint with the naming pattern
{table}_{column}_not_null.
For example consider the following table
```sql
-- On PostgreSQL 13
CREATE TABLE orders (
id BIGSERIAL,
customer_id INTEGER NOT NULL,
CONSTRAINT orders_customer_id_not_null CHECK (customer_id IS NOT NULL)
);
```
Root cause
Starting in PostgreSQL 17, NOT NULL constraints are stored in pg_constraint
(with contype = 'n') rather than only in pg_attribute.attnotnull. During
pg_upgrade, PostgreSQL auto-generates constraint names following the pattern
{table}_{column}_not_null. If a CHECK constraint with this name already
exists, the upgrade fails with a duplicate key violation.
I had to find all the constraints and rename them to mitigate the error but
I want to know if it's the only solution or is this a problem that can be
addressed inside pg_upgrade binary.
To reproduce a the issue
- Create the following table on PostgreSQL 13
```sql
-- On PostgreSQL 13
CREATE TABLE orders (
id BIGSERIAL,
customer_id INTEGER NOT NULL,
CONSTRAINT orders_customer_id_not_null CHECK (customer_id IS NOT NULL)
);
```
- Confirm the not null check constraint.
```sql
SELECT
c.conname,
c.conrelid::regclass AS table_name,
c.contype,
pg_get_constraintdef(c.oid) AS definition
FROM pg_constraint c
WHERE c.conrelid::regclass::text LIKE 'orders%'
ORDER BY c.conrelid::regclass, c.conname;
conname | table_name | contype | definition
-----------------------------+------------+---------+-----------------------------------
orders_customer_id_not_null | orders | c | CHECK ((customer_id IS
NOT NULL))
(1 row)
```
- Execute pg_upgrade with the following options.
```bash
[postgres@2001-1c00-5d82-ce00-c88f-20ff-fec5-d892 ~]$
/usr/pgsql-18/bin/pg_upgrade --link -b /usr/pgsql-13/bin/ -B
/usr/pgsql-18/bin/ -d /var/lib/postgresql/13/data -D
/var/lib/postgresql/18/data -r -j 5
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
Checking database connection settings ok
Checking database user is the install user ok
Checking for prepared transactions ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking data type usage ok
Checking for user-defined encoding conversions ok
Checking for user-defined postfix operators ok
Checking for incompatible polymorphic functions ok
Checking for not-null constraint inconsistencies ok
Creating dump of global objects ok
Creating dump of database schemas
ok
Checking for presence of required libraries ok
Checking database user is the install user ok
Checking for prepared transactions ok
Checking for new cluster tablespace directories ok
If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.
Performing Upgrade
------------------
Setting locale and encoding for new cluster ok
Analyzing all rows in the new cluster ok
Freezing all rows in the new cluster ok
Deleting files from new pg_xact ok
Copying old pg_xact to new server ok
Setting oldest XID for new cluster ok
Setting next transaction ID and epoch for new cluster ok
Deleting files from new pg_multixact/offsets ok
Copying old pg_multixact/offsets to new server ok
Deleting files from new pg_multixact/members ok
Copying old pg_multixact/members to new server ok
Setting next multixact ID and offset for new cluster ok
Resetting WAL archives ok
Setting the default char signedness for new cluster ok
Setting frozenxid and minmxid counters in new cluster ok
Restoring global objects in the new cluster ok
Restoring database schemas in the new cluster
benchmark_v1
*failure*
Consult the last few lines of
"/var/lib/postgresql/18/data/pg_upgrade_output.d/20260204T182753.404/log/pg_upgrade_dump_16384.log"
for
the probable cause of the failure.
Failure, exiting
*failure*
Consult the last few lines of
"/var/lib/postgresql/18/data/pg_upgrade_output.d/20260204T182753.404/log/pg_upgrade_dump_14448.log"
for
the probable cause of the failure.
Failure, exiting
child process exited abnormally: status 256
Failure, exiting
[postgres@2001-1c00-5d82-ce00-c88f-20ff-fec5-d892 ~]$
command: "/usr/pgsql-18/bin/pg_dump" --host /var/lib/pgsql --port 50432
--username postgres --no-data --sequence-data --quote-all-identifiers
--binary-upgrade --format=custom --statistics --no-sync
--file="/var/lib/postgresql/18/data/pg_upgrade_output.d/20260204T182753.404/dump/pg_upgrade_dump_16384.custom"
'dbname=benchmark_v1' >>
"/var/lib/postgresql/18/data/pg_upgrade_output.d/20260204T182753.404/log/pg_upgrade_dump_16384.log"
2>&1
command: "/usr/pgsql-18/bin/pg_restore" --host /var/lib/pgsql --port 50432
--username postgres --create --exit-on-error --verbose
--transaction-size=200 --dbname template1
"/var/lib/postgresql/18/data/pg_upgrade_output.d/20260204T182753.404/dump/pg_upgrade_dump_16384.custom"
"/var/lib/postgresql/18/data/pg_upgrade_output.d/20260204T182753.404/log/pg_upgrade_dump_16384.log"
2>&1
pg_restore: connecting to database for restore
pg_restore: executing SELECT pg_catalog.set_config('search_path', '',
false);
pg_restore: creating DATABASE "benchmark_v1"
pg_restore: connecting to new database "benchmark_v1"
pg_restore: executing SELECT pg_catalog.set_config('search_path', '',
false);
pg_restore: creating DATABASE PROPERTIES "benchmark_v1"
pg_restore: connecting to new database "benchmark_v1"
pg_restore: executing SELECT pg_catalog.set_config('search_path', '',
false);
pg_restore: creating pg_largeobject "pg_largeobject"
pg_restore: creating SCHEMA "public"
pg_restore: creating COMMENT "SCHEMA "public""
pg_restore: creating TABLE "public.orders"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 209; 1259 16413 TABLE orders postgres
pg_restore: error: could not execute query: ERROR: duplicate key value
violates unique constraint "pg_constraint_conrelid_contypid_conname_index"
DETAIL: Key (conrelid, contypid, conname)=(16413, 0,
orders_customer_id_not_null) already exists.
Command was:
-- For binary upgrade, must preserve pg_type oid
SELECT
pg_catalog.binary_upgrade_set_next_pg_type_oid('16415'::pg_catalog.oid);
-- For binary upgrade, must preserve pg_type array oid
SELECT
pg_catalog.binary_upgrade_set_next_array_pg_type_oid('16414'::pg_catalog.oid);
-- For binary upgrade, must preserve pg_class oids and relfilenodes
SELECT
pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('16413'::pg_catalog.oid);
SELECT
pg_catalog.binary_upgrade_set_next_heap_relfilenode('16413'::pg_catalog.oid);
CREATE TABLE "public"."orders" (
"id" bigint NOT NULL,
"customer_id" integer NOT NULL,
CONSTRAINT "orders_customer_id_not_null" CHECK (("customer_id" IS NOT
NULL))
);
-- For binary upgrade, set heap's relfrozenxid and relminmxid
UPDATE pg_catalog.pg_class
SET relfrozenxid = '502', relminmxid = '1'
WHERE oid = '"public"."orders"'::pg_catalog.regclass;
```
Expected Behavior
pg_upgrade --check should detect this naming collision before the upgrade
and report a warning, or pg_upgrade should generate an alternative
constraint name when a collision exists.
Workaround
Before upgrade, rename conflicting CHECK constraints:
```sql
ALTER TABLE orders
RENAME CONSTRAINT orders_customer_id_not_null
TO orders_customer_id_check;
```
After upgrade, the redundant CHECK constraint can be dropped since the
native NOT NULL constraint now exists.
Environment:
Source: PostgreSQL 13.23
Target: PostgreSQL 18.1
OS: CentOS9
I'm happy to provide additional details or test patches.
On Wed, 2026-02-04 at 17:33 +0000, PG Bug reporting form wrote:
PostgreSQL version: 18.1
While I was working on upgrading a PostgreSQL 13 cluster to PostgreSQL 18.1
I encountered the following problem. pg_upgrade fails if a table has both a
NOT NULL column and a CHECK constraint with the naming pattern
{table}_{column}_not_null.For example consider the following table
-- On PostgreSQL 13
CREATE TABLE orders (
id BIGSERIAL,
customer_id INTEGER NOT NULL,
CONSTRAINT orders_customer_id_not_null CHECK (customer_id IS NOT NULL)
);[...]
pg_restore: error: could not execute query: ERROR: duplicate key value
violates unique constraint "pg_constraint_conrelid_contypid_conname_index"
DETAIL: Key (conrelid, contypid, conname)=(16413, 0,
orders_customer_id_not_null) already exists.
Command was:
-- For binary upgrade, must preserve pg_type oid
SELECT
pg_catalog.binary_upgrade_set_next_pg_type_oid('16415'::pg_catalog.oid);-- For binary upgrade, must preserve pg_type array oid
SELECT
pg_catalog.binary_upgrade_set_next_array_pg_type_oid('16414'::pg_catalog.oid);-- For binary upgrade, must preserve pg_class oids and relfilenodes
SELECT
pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('16413'::pg_catalog.oid);
SELECT
pg_catalog.binary_upgrade_set_next_heap_relfilenode('16413'::pg_catalog.oid);CREATE TABLE "public"."orders" (
"id" bigint NOT NULL,
"customer_id" integer NOT NULL,
CONSTRAINT "orders_customer_id_not_null" CHECK (("customer_id" IS NOT
NULL))
);-- For binary upgrade, set heap's relfrozenxid and relminmxid
UPDATE pg_catalog.pg_class
SET relfrozenxid = '502', relminmxid = '1'
WHERE oid = '"public"."orders"'::pg_catalog.regclass;
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.
Yours,
Laurenz Albe
Attachments:
v1-0001-Avoid-name-collision-with-NOT-NULL-constraints.patchtext/x-patch; charset=ISO-8859-1; name=v1-0001-Avoid-name-collision-with-NOT-NULL-constraints.patchDownload+16-7
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.
Yours,
Laurenz Albe
Attachments:
v2-0001-Avoid-name-collision-with-NOT-NULL-constraints.patchtext/x-patch; charset=ISO-8859-1; name=v2-0001-Avoid-name-collision-with-NOT-NULL-constraints.patchDownload+29-7
On Thu, Feb 5, 2026 at 10:22 PM Laurenz Albe <laurenz.albe@cybertec.at> 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.
The fix LGTM. However I have one question, have you considered
validating the name selection logic for other constraint types as
well? I’m specifically thinking about AddRelationNewConstraints().
While I don't have a specific test case yet, is it possible for the
AddRelationNewConstraints to choose a name that is already in use when
adding a new column with a constraint?
--
Regards,
Dilip Kumar
Google
On Fri, 2026-02-06 at 12:53 +0530, Dilip Kumar wrote:
On Thu, Feb 5, 2026 at 10:22 PM Laurenz Albe <laurenz.albe@cybertec.at> 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.
The fix LGTM. However I have one question, have you considered
validating the name selection logic for other constraint types as
well? I’m specifically thinking about AddRelationNewConstraints().
While I don't have a specific test case yet, is it possible for the
AddRelationNewConstraints to choose a name that is already in use when
adding a new column with a constraint?
Thanks for having a look.
I am not sure what you mean by "adding a new column": do you mean an
ALTER TABLE that runs after the CREATE TABLE?
The following works fine in v18:
CREATE TABLE nulls (
y integer UNIQUE,
CONSTRAINT nulls_x_not_null FOREIGN KEY (y) REFERENCES nulls (y),
CONSTRAINT nulls_x_fkey CHECK (TRUE)
);
ALTER TABLE nulls ADD x integer REFERENCES nulls (y) NOT NULL;
Both the new foreign key and the new NOT NULL constraint get a name
that doesn't conflict with the existing constraints.
But I don't claim that my patch fixes all possible problems during a
pg_upgrade. If you define a table like this in v13:
CREATE TABLE nulls (
x integer UNIQUE NOT NULL,
CONSTRAINT nulls_x_not_null FOREIGN KEY (x) REFERENCES nulls (x)
);
then pg_dump --binary-upgrade will produce code like the following:
CREATE TABLE laurenz.nulls (
x integer NOT NULL
);
ALTER TABLE ONLY laurenz.nulls
ADD CONSTRAINT nulls_x_key UNIQUE (x);
ALTER TABLE ONLY laurenz.nulls
ADD CONSTRAINT nulls_x_not_null FOREIGN KEY (x) REFERENCES laurenz.nulls(x);
and the last statement will cause an error, because the constraint name will
conflict with the name for the NOT NULL constraint. In other words, my patch
only works for constraints that are dumped as part of the CREATE TABLE statement,
which I believe are only check constraints.
But my opinion is that it is very unlikely that anybody picks a name ending
in "_not_null" for a foreign key or unique constraint, while (as the bug report
demonstrates) there may be people who define (superfluous) check constraints
with such names.
So there is still the potential for pg_upgrade failures with my patch applied,
but it would fix the case most likely to occur in practice.
Yours,
Laurenz Albe
On Fri, Feb 6, 2026 at 2:40 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Fri, 2026-02-06 at 12:53 +0530, Dilip Kumar wrote:
On Thu, Feb 5, 2026 at 10:22 PM Laurenz Albe <laurenz.albe@cybertec.at> 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.
The fix LGTM. However I have one question, have you considered
validating the name selection logic for other constraint types as
well? I’m specifically thinking about AddRelationNewConstraints().
While I don't have a specific test case yet, is it possible for the
AddRelationNewConstraints to choose a name that is already in use when
adding a new column with a constraint?Thanks for having a look.
I am not sure what you mean by "adding a new column": do you mean an
ALTER TABLE that runs after the CREATE TABLE?The following works fine in v18:
CREATE TABLE nulls (
y integer UNIQUE,
CONSTRAINT nulls_x_not_null FOREIGN KEY (y) REFERENCES nulls (y),
CONSTRAINT nulls_x_fkey CHECK (TRUE)
);ALTER TABLE nulls ADD x integer REFERENCES nulls (y) NOT NULL;
Both the new foreign key and the new NOT NULL constraint get a name
that doesn't conflict with the existing constraints.
Right I see, I was talking about the similar case something like[1]
but I see it already handles the conflict and generates a conflicting
name if a constraint with the name already exists. So we are good,
thanks.
postgres[58251]=# CREATE TABLE two_not_null_constraints (
col integer, CONSTRAINT two_not_null_constraints_col1_check CHECK (col > 5)
);
CREATE TABLE
postgres[58251]=# ALTER TABLE two_not_null_constraints ADD COLUMN col1
int check (col1 > 0);
ALTER TABLE
postgres[58251]=# \d+ two_not_null_constraints
Table "public.two_not_null_constraints"
Column | Type | Collation | Nullable | Default | Storage |
Compression | Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
col | integer | | | | plain |
| |
col1 | integer | | | | plain |
| |
Check constraints:
"two_not_null_constraints_col1_check" CHECK (col > 5)
"two_not_null_constraints_col1_check1" CHECK (col1 > 0)
Access method: heap
--
Regards,
Dilip Kumar
Google
Hi Laurenz,
Thanks for the patch and LGTM for PostgreSQL 18.
I tried to create following table on PG18
benchmark=# CREATE TABLE two_not_null_constraints (
col integer NOT NULL,
CONSTRAINT two_not_null_constraints_col_not_null CHECK (col IS NOT NULL)
);
ERROR: duplicate key value violates unique constraint
"pg_constraint_conrelid_contypid_conname_index"
DETAIL: Key (conrelid, contypid, conname)=(16385, 0,
two_not_null_constraints_col_not_null) already exists.
In PG17 I was able to create the table.
benchmark=# CREATE TABLE two_not_null_constraints (
col integer NOT NULL,
CONSTRAINT two_not_null_constraints_col_not_null CHECK (col IS NOT NULL)
);
CREATE TABLE
benchmark=# SELECT conname, contype FROM pg_constraint
WHERE conrelid = 'two_not_null_constraints'::regclass
ORDER BY conname;
DROP TABLE two_not_null_constraints;
-[ RECORD 1 ]----------------------------------
conname | two_not_null_constraints_col_not_null
contype | c
One question during the tests should we confirm the output of pg_constraint
table ? It would make sense during the tests but the current test is also
good to proceed.
benchmark=# SELECT conname, contype FROM pg_constraint
WHERE conrelid = 'two_not_null_constraints'::regclass
ORDER BY conname;
conname | contype
----------------------------------------+---------
two_not_null_constraints_col_not_null | n
two_not_null_constraints_col_not_null1 | c
(2 rows)
Laurenz Albe <laurenz.albe@cybertec.at>, 5 Şub 2026 Per, 17:52 tarihinde
şunu yazdı:
Show quoted text
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.
Yours,
Laurenz Albe
On Mon, 2026-02-09 at 07:24 +0100, Hüseyin Demir wrote:
Thanks for the patch and LGTM for PostgreSQL 18.
Thanks for checking!
I tried to create following table on PG18
benchmark=# CREATE TABLE two_not_null_constraints (
col integer NOT NULL,
CONSTRAINT two_not_null_constraints_col_not_null CHECK (col IS NOT NULL)
);
ERROR: duplicate key value violates unique constraint "pg_constraint_conrelid_contypid_conname_index"
DETAIL: Key (conrelid, contypid, conname)=(16385, 0, two_not_null_constraints_col_not_null) already exists.In PG17 I was able to create the table.
Yes, because what causes your problemm is a new feature in v18.
One question during the tests should we confirm the output of pg_constraint table ?
It would make sense during the tests but the current test is also good to proceed.
I would prefer not to, but I don't have a strong opinion about it.
Which name PostgreSQL chooses for the generated NOT NULL constraint
is not important, as long as it doesn't conflict with the existing name.
Yours,
Laurenz Albe
Hi,
I would prefer not to, but I don't have a strong opinion about it.
Which name PostgreSQL chooses for the generated NOT NULL constraint
is not important, as long as it doesn't conflict with the existing name.
I also agree that it solves the problem in 18 which I already tested.
Thanks again for your help.
Laurenz Albe <laurenz.albe@cybertec.at>, 9 Şub 2026 Pzt, 12:01 tarihinde
şunu yazdı:
Show quoted text
On Mon, 2026-02-09 at 07:24 +0100, Hüseyin Demir wrote:
Thanks for the patch and LGTM for PostgreSQL 18.
Thanks for checking!
I tried to create following table on PG18
benchmark=# CREATE TABLE two_not_null_constraints (
col integer NOT NULL,
CONSTRAINT two_not_null_constraints_col_not_null CHECK (col IS NOTNULL)
);
ERROR: duplicate key value violates unique constraint"pg_constraint_conrelid_contypid_conname_index"
DETAIL: Key (conrelid, contypid, conname)=(16385, 0,
two_not_null_constraints_col_not_null) already exists.
In PG17 I was able to create the table.
Yes, because what causes your problemm is a new feature in v18.
One question during the tests should we confirm the output of
pg_constraint table ?
It would make sense during the tests but the current test is also good
to proceed.
Yours,
Laurenz Albe
On Fri, Feb 06, 2026 at 04:16:08PM +0530, Dilip Kumar wrote:
Right I see, I was talking about the similar case something like[1]
but I see it already handles the conflict and generates a conflicting
name if a constraint with the name already exists. So we are good,
thanks.
I had this patch marked on my tablets for a lookup, with the hope that
it would have been possible to get something done for this week's
release, unfortunately I got drifted away and lacked time. I'll look
into what you have here.
--
Michael
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/)
On 2026-Feb-21, Álvaro Herrera wrote:
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.
As expected, crake reported:
Leaf changes summary: 1 artifact changed
Changed leaf types summary: 0 leaf type changed
Removed/Changed/Added functions summary: 0 Removed, 1 Changed, 0 Added function (13 filtered out)
Removed/Changed/Added variables summary: 0 Removed, 0 Changed, 0 Added variable
1 function with incompatible sub-type changes:
[C] 'function List* AddRelationNotNullConstraints(Relation, List*, List*)' has some sub-type changes:
parameter 4 of type 'List*' was added
I have pushed the update to .abi-compliance-history, and crake is now green.
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
"Cómo ponemos nuestros dedos en la arcilla del otro. Eso es la amistad; jugar
al alfarero y ver qué formas se pueden sacar del otro" (C. Halloway en
La Feria de las Tinieblas, R. Bradbury)
On Sat, 2026-02-21 at 12:56 +0100, Álvaro Herrera 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).
Thank you, and thanks for the code improvements.
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.
I decided not to worry about changing the signature of that global function,
because it is only used in a single place and - like you - I deem it unlikely
to be useful elsewhere.
Yours,
Laurenz Albe
On Sat, Feb 21, 2026 at 12:56:24PM +0100, Alvaro Herrera wrote:
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.
While I get the feeling of urgency, I am wondering if this particular
fix should have been delayed and pushed for next May's release. We
are already doing one quick release for the regressions found in the
CVE fixes..
Based on my read of the fix, I feel rather safe that this is OK. But
as I have quoted upthread, the reason why I did not reply yet was to
wait for next week's release to be out before acting. That's your
code of course, so no objections from here, just a slight doubt about
the timing.
--
Michael
On 2026-Feb-22, Michael Paquier wrote:
While I get the feeling of urgency, I am wondering if this particular
fix should have been delayed and pushed for next May's release. We
are already doing one quick release for the regressions found in the
CVE fixes..
I debated that with myself before pushing. I thought the risk of
introducing a new bug or problem was pretty low; whereas the bug was
clearly already affecting real-world users in their attempts to migrate
to 18 from earlier versions, so the damage was real.
Based on my read of the fix, I feel rather safe that this is OK. But
as I have quoted upthread, the reason why I did not reply yet was to
wait for next week's release to be out before acting. That's your
code of course, so no objections from here, just a slight doubt about
the timing.
Hmm, what? The only response from you that I see in this thread is
/messages/by-id/aYp9tTWjUqS1ffAd@paquier.xyz
which was on Feb 10th, which is before the 18.2 release, so you couldn't
have been considering the 18.3 release yet.
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
"On the other flipper, one wrong move and we're Fatal Exceptions"
(T.U.X.: Term Unit X - http://www.thelinuxreview.com/TUX/)
On Mon, Feb 23, 2026 at 05:47:46PM +0100, Alvaro Herrera wrote:
On 2026-Feb-22, Michael Paquier wrote:
Based on my read of the fix, I feel rather safe that this is OK. But
as I have quoted upthread, the reason why I did not reply yet was to
wait for next week's release to be out before acting. That's your
code of course, so no objections from here, just a slight doubt about
the timing.Hmm, what? The only response from you that I see in this thread is
/messages/by-id/aYp9tTWjUqS1ffAd@paquier.xyz
which was on Feb 10th, which is before the 18.2 release, so you couldn't
have been considering the 18.3 release yet.
Well, you are not counting the week of the 18.2 release, this thread
being an item I had on my stack of items to look at once the tags were
applied. It didn't make to the top of the list. It does not matter
much at the end. Thanks for fixing the issue!
--
Michael