Thread: Detached partitioning tables with RF keys in latest minor version is changed

Detached partitioning tables with RF keys in latest minor version is changed

From

Shay Patel

Date:

14 April, 22:25:38

Hello PostgreSQL SQL DEV team,

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.

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?

Now , our automation script stopped working and there is no single catalog table can give this information. Is there any catalog table which can query RF keys and it can list the it’s tables?

It’s frustrating that script stopped working due to minor version upgrade made those changes of dropping rf keys before detaching the partitioning tables.

Example :

--Create and set schema for the session

CREATE SCHEMA test_part;

SET SEARCH_PATH=test_part;

--Create a partitioned table

CREATE TABLE logs (

id integer not null,

log_date date not null,

message text

) PARTITION BY RANGE (log_date);

--Add primary key constraints in parent partition table

ALTER TABLE ONLY logs ADD primary key (id,log_date);

--Define partition for each month

CREATE TABLE logs_2024_01 PARTITION OF logs FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE logs_2024_02 PARTITION OF logs FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

--Create a Child partition table

CREATE TABLE logs_child (

id integer,

log_date date,

message text,

logs_parent_id integer

) PARTITION BY RANGE (log_date);

--Add constraints

ALTER TABLE ONLY logs_child ADD primary key (id,log_date);

ALTER TABLE logs_child add constraint logs_child_fk foreign key(logs_parent_id,log_date) references logs(id,log_date) ON DELETE CASCADE;

--Define a partition for each month

CREATE TABLE logs_child_2024_01 PARTITION OF logs_child FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE logs_child_2024_02 PARTITION OF logs_child FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

--Insert data into the parent partition table :

INSERT INTO logs (id,log_date, message) VALUES (1,'2024-01-15', 'Log message 1');

INSERT INTO logs (id,log_date, message) VALUES (11,'2024-01-15', 'Log message 1');

INSERT INTO logs (id,log_date, message) VALUES (2,'2024-02-15', 'Log message 2');

INSERT INTO logs (id,log_date, message) VALUES (22,'2024-02-15', 'Log message 2');

--Insert data into child partition table:

INSERT INTO logs_child values (1,'2024-01-15', 'Log message 1',1);

INSERT INTO logs_child values (2,'2024-01-15', 'Log message 1',1);

INSERT INTO logs_child values (5,'2024-02-15', 'Log message 2',22);

INSERT INTO logs_child values (6,'2024-02-15', 'Log message 2',2);

--Review data using Select

SELECT * FROM logs;

SELECT * FROM logs_2024_01;

SELECT * FROM logs_2024_02;

SELECT * FROM logs_child_2024_01;

SELECT * FROM logs_child_2024_02;

To find the keys there is no single catalog table can list the keys and tables the key belongs to. Can use distinct but it’s not reliable, also can’t find the partitioned table name where it belongs to.

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--Detach partitioning table

ALTER TABLE logs_child DETACH PARTITION logs_child_2024_02;

ALTER TABLE logs_child_2024_02 DROP CONSTRAINT logs_child_fk;

ALTER TABLE logs DETACH PARTITION logs_2024_02;

Best,

Shay Patel

Attachment

image001.png

Re: Detached partitioning tables with RF keys in latest minor version is changed

From

Álvaro Herrera

Date:

10 May, 22:13:13

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)