Feedback-ID: ia2694551:Fastmail
Date: Mon, 21 Apr 2025 19:44:27 +0200
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Kirill Reshke <reshkekirill@gmail.com>, pgsql-bugs@postgresql.org
Subject: Re: Command order bug in pg_dump
Message-ID: <202504211744.4mzfm7wytiye@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <592472.1745247403@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/202504211744.4mzfm7wytiye%40alvherre.pgsql>
Precedence: bulk

On 2025-Apr-21, Tom Lane wrote:

> I don't think this is pg_dump's fault: there is no such constraint
> when the ALTER TABLE starts.  Something inside the ALTER TABLE
> recursion seems to be messing up if there is already another
> similar FK constraint.  This trace is pretty interesting:
> 
> regression=# create database db1;
> CREATE DATABASE
> regression=# \c db1
> You are now connected to database "db1" as user "postgres".
> db1=# create table tfk (i int unique) partition by range (i );
> CREATE TABLE
> db1=# create table tfk_po partition of tfk for values from ( 0 ) to (1);
> CREATE TABLE
> db1=# create table tt (i int) partition by range (i );
> CREATE TABLE
> db1=# create table tt_po partition of tt for values from ( 0 ) to (1);
> CREATE TABLE
> db1=# ALTER TABLE public.tt
> db1-#     ADD CONSTRAINT tt_a_fkey FOREIGN KEY (i) REFERENCES public.tfk(i);
> ALTER TABLE
> db1=# ALTER TABLE public.tt
> db1-#     ADD CONSTRAINT tt_i_fkey FOREIGN KEY (i) REFERENCES public.tfk(i);
> ERROR:  constraint "tt_i_fkey" for relation "tt" already exists

Oh hah.  So constraint tt_a_fkey has chosen the name tt_i_fkey for its
internal sub-constraint object to point to the partitions, so when the
user wants to created tt_i_fkey, the name available anymore.

Maybe we can change the naming policy so that these internal constraint
objects have names that are unlikely to be chosen by users, maybe by
suffixing "fkey_int" instead of "fkey", or something like that.  (We
could even do "$1" and so on for this kind of constraint).  In
hindsight, it isn't such a great idea to let the system choose the best
name for an internal implementation object.

I'd probably not change this in versions 13 and 14 at all in any case,
because the code is too different.  I'm unsure whether this is enough of
a bug to consider backpatching to 15-17; maybe we should just change 18
at this point, since I haven't heard of a user complaining about this.

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
"Estoy de acuerdo contigo en que la verdad absoluta no existe...
El problema es que la mentira sí existe y tu estás mintiendo" (G. Lama)