From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kirill Reshke <reshkekirill@gmail.com>
cc: pgsql-bugs@postgresql.org, Alvaro Herrera <alvherre@alvh.no-ip.org>
Subject: Re: Command order bug in pg_dump
In-reply-to: 
 <CALdSSPhGitjpTfzEMJN-Y2x+Q-5QChSxAsmSJ1-E8mQJLkHOqQ@mail.gmail.com>
References: 
 <CALdSSPhGitjpTfzEMJN-Y2x+Q-5QChSxAsmSJ1-E8mQJLkHOqQ@mail.gmail.com>
Comments: In-reply-to Kirill Reshke <reshkekirill@gmail.com>
	message dated "Mon, 21 Apr 2025 15:56:39 +0500"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <592471.1745247403.1@sss.pgh.pa.us>
Date: Mon, 21 Apr 2025 10:56:43 -0400
Message-ID: <592472.1745247403@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/592472.1745247403%40sss.pgh.pa.us>
Precedence: bulk

Kirill Reshke <reshkekirill@gmail.com> writes:
> psql:dump-p.sql:120: ERROR:  constraint "tt_i_fkey" for relation "tt"
> already exists

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
db1=# ALTER TABLE public.tt drop CONSTRAINT tt_a_fkey;
ALTER TABLE
db1=# ALTER TABLE public.tt                           
    ADD CONSTRAINT tt_i_fkey FOREIGN KEY (i) REFERENCES public.tfk(i);
ALTER TABLE

Doesn't seem to be a new problem, either ... this trace is against
v13.

			regards, tom lane