Feedback-ID: ie3de48e3:Fastmail
Date: Sat, 10 May 2025 15:13:13 -0400
From: =?utf-8?Q?=C3=81lvaro?= Herrera <alvherre@kurilemu.de>
To: Shay Patel <Shital.Modi@microsoft.com>
Cc: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Subject: Re: Detached partitioning tables with RF keys in latest minor
 version is changed
Message-ID: <202505101913.ebamsfg4flpf@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CH3PR21MB444484FF4ED0A83E17E97FC5EBB32@CH3PR21MB4444.namprd21.prod.outlook.com>
Archived-At: 
 <https://www.postgresql.org/message-id/202505101913.ebamsfg4flpf%40alvherre.pgsql>
Precedence: bulk

Hello,

On 2025-Apr-14, Shay Patel wrote:

> Prior to PostgreSQL version 15.7 AND 16.2, we were able to detach the
> partitioning table with RF keys without dropping the keys ,handled
> automatically.

I don't understand your problem.  I took your script and ran it in 15.8
and found that it runs to completion with no errors.  What problem are
you facing exactly?  If you run a command that gets an error, what's the
command that you run and what's the error?

(You use the term "RF keys" which is not one I know, but I assume you
mean foreign key constraints).

> Afterwards version must drop the RF keys to detach the partition, why
> it's not automatically handled like it's handled while attaching the
> partitioning tables with rf keys?

We recently made some changes on the behavior of foreign keys for
partitioned tables, to fix some bugs.  Maybe you've been affected for
that; but 1) perhaps the situation you know face is the behavior that we
had always intended and is now correctly implemented, or alternatively
2) you're facing a different problem that I don't understand.

> Now , our automation script stopped working and there is no single
> catalog table can give this information.

We try very hard not to break behavior in stable branches, because we
know it's problematic for users to find this kind of glitch after an
upgrade.  Maybe we overlooked something in this case.

> Is there any catalog table which can query RF keys and it can list the
> it's tables?

Yes, you can query the pg_catalog.pg_constraint internal table; for
instance:

SELECT conname, confrelid::regclass
  FROM pg_constraint
 WHERE contype = 'f' AND conrelid = 'logs_child_2024_02'::regclass;

would give you the constraint names of all foreign keys on table
logs_child_2024_02, and also the table that it references.  Feel free to
ask if you want more guidance on the contents of the catalogs, but you
can see the documentation,
https://www.postgresql.org/docs/15/catalogs.html
(I'm offline at the moment so I can't verify that the URL is fully
correct.)

> It's frustrating that script stopped working due to minor version
> upgrade

Yes, I understand how this can be frustrating. 

Regards

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
"E pur si muove" (Galileo Galilei)