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: <592472.1745247403@sss.pgh.pa.us>
References: 
 <CALdSSPhGitjpTfzEMJN-Y2x+Q-5QChSxAsmSJ1-E8mQJLkHOqQ@mail.gmail.com>
 <592472.1745247403@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
	message dated "Mon, 21 Apr 2025 10:56:43 -0400"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <769269.1745255285.1@sss.pgh.pa.us>
Date: Mon, 21 Apr 2025 13:08:05 -0400
Message-ID: <769270.1745255285@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/769270.1745255285%40sss.pgh.pa.us>
Precedence: bulk

I 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.

Oh, I see what's happening.  We create both the specified constraint
and an inherited child constraint on the named partitioned table.
If tt_a_fkey is added first, the name chosen for its child is
"tt_i_fkey", breaking the later attempt by the user to use that name.
Apparently there is some hack in psql to not show that child
constraint entry, because you don't see it in \d output, which
is what confused me before (and would confuse most people
hitting this).

We could fool around with the generation rule for the child
constraint's name, but fundamentally we're infringing on user
namespace here, so I think that's likely to just move the problem
cases around.  Why do we need this child pg_constraint entry at all?

If we can't avoid having it, probably choosing a name like
"tt_a_fkey1" would be marginally less likely to cause trouble
... but only marginally.

In any case, it's pretty awful that we make these entries but
\d does not show them.

			regards, tom lane