Feedback-ID: ia2694551:Fastmail
Date: Thu, 8 Sep 2022 13:25:15 +0200
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: [BUG] wrong FK constraint name when colliding name on ATTACH
Message-ID: <20220908112515.5pe5obtk4a63aiee@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <20220908094026.640a0224@karst>
Archived-At: 
 <https://www.postgresql.org/message-id/20220908112515.5pe5obtk4a63aiee%40alvherre.pgsql>
Precedence: bulk

On 2022-Sep-08, Jehan-Guillaume de Rorthais wrote:

> Hi there,
> 
> I believe this very small bug and its fix are really trivial and could be push
> out of the way quite quickly. It's just about a bad constraint name fixed by
> moving one assignation after the next one. This could easily be fixed for next
> round of releases.
> 
> Well, I hope I'm not wrong :)

I think you're right, so pushed, and backpatched to 12.  I added the
test case to regression also.

For 11, I adjusted the test case so that it didn't depend on an FK
pointing to a partitioned table (which is not supported there); it turns
out that the old code is not smart enough to get into the problem in the
first place.  Setup is

CREATE TABLE parted_fk_naming_pk (id bigint primary key);
CREATE TABLE parted_fk_naming (
    id_abc bigint,
    CONSTRAINT dummy_constr FOREIGN KEY (id_abc)
        REFERENCES parted_fk_naming_pk (id)
)
PARTITION BY LIST (id_abc);
CREATE TABLE parted_fk_naming_1 (
    id_abc bigint,
    CONSTRAINT dummy_constr CHECK (true)
);

and then
ALTER TABLE parted_fk_naming ATTACH PARTITION parted_fk_naming_1 FOR VALUES IN ('1');
throws this error:

ERROR:  duplicate key value violates unique constraint "pg_constraint_conrelid_contypid_conname_index"
DETALLE:  Key (conrelid, contypid, conname)=(686125, 0, dummy_constr) already exists.

It seems fair to say that this case, with pg11, is unsupported and
people should upgrade if they want better behavior.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"Those who use electric razors are infidels destined to burn in hell while
we drink from rivers of beer, download free vids and mingle with naked
well shaved babes." (http://slashdot.org/comments.pl?sid=44793&cid=4647152)