Subject: Not able to purge partition

From: veem v <[email-masked]>
Date: Thu, 21 Mar 2024 11:07:55 +0530

Hello All,
 We created two partitioned tables one being parent and other child. Both
are range partitioned and one table has a foreign key to the other parent
table. Now , when we run the partition maintenance job to drop the older
partitions which meet the partition retention criteria, it's not letting us
drop any of the partitions from the parent table. Child table works fine
though.

 And when we try using cascade , it drops the foreign key constraint from
all the partitions.  Can you please let me know how to make the partition
maintenance work in such a scenario while maintaining the foreign key
intact, as we are expecting the foreign key to be attached to the
respective partition only but not the full table?

 And this partition maintenance job which runs through cron scheduler in an
automated way is failing on a daily basis for us without being able to drop
any of the historical partitions from the parent partitioned table.

 Below is the test case:-

CREATE TABLE schema1.test_part_drop_parent
(
c1 varchar(36)  NOT NULL ,
c2_part_date timestamp with time zone  NOT NULL ,
c3  numeric(12,0)  NOT NULL ,
CONSTRAINT test_part_drop_parent_PK PRIMARY KEY (c1,c2_part_date)
) PARTITION BY RANGE (c2_part_date);

CREATE INDEX idx1_test_part_parent ON schema1.test_part_drop_parent( C3
ASC);

CREATE TABLE schema1.test_part_drop_child
(
C1_child   varchar(36)  NOT NULL ,
C1   varchar(36)  NOT NULL ,
c2_part_date timestamp with time zone  NOT NULL ,
C3  numeric(12,0)  NOT NULL ,
CONSTRAINT test_part_drop_child_PK PRIMARY KEY (C1_child,c2_part_date),
CONSTRAINT test_part_drop_child_FK1 FOREIGN KEY (C1,c2_part_date)
REFERENCES schema1.test_part_drop_parent(c1,c2_part_date)
) PARTITION BY RANGE (c2_part_date);

CREATE INDEX test_part_drop_child_fk ON schema1.test_part_drop_child( c1,
c2_part_date);

CREATE INDEX test_part_drop_child_idx ON schema1.test_part_drop_child(
c1_child, c2_part_date);

select partman.create_parent(
   p_parent_table := 'schema1.test_part_drop_child',
   p_control := 'c2_part_date',
   p_type := 'native',
   p_interval := '1 day',
   p_premake := 5,
   p_start_partition => '2024-02-01 00:00:00'
);

update partman.part_config set infinite_time_partitions = 'true' ,
retention = '1 months', retention_keep_table='false',
retention_keep_index='false'
where parent_table = 'schema1.test_part_drop_child';

select partman.create_parent(
   p_parent_table := 'schema1.test_part_drop_parent',
   p_control := 'c2_part_date',
   p_type := 'native',
   p_interval := '1 day',
   p_premake := 5,
   p_start_partition => '2024-02-01 00:00:00'
);

update partman.part_config set infinite_time_partitions = 'true' ,
retention = '1 months', retention_keep_table='false',
retention_keep_index='false'
where parent_table = 'schema1.test_part_drop_parent';

select partman.run_maintenance('schema1.test_part_drop_child');

select partman.run_maintenance('schema1.test_part_drop_parent');

SQL Error [P0001]: ERROR: cannot drop table
schema1.test_part_drop_parent_p2024_02_01 because other objects depend on it
CONTEXT: SQL statement "DROP TABLE
schema1.test_part_drop_parent_p2024_02_01"
PL/pgSQL function
drop_partition_time(text,interval,boolean,boolean,text,timestamp with time
zone) line 213 at EXECUTE
PL/pgSQL function partman.run_maintenance(text,boolean,boolean) line 195 at
assignment
DETAIL: constraint test_part_drop_child_fk1 on table
schema1.test_part_drop_child depends on table
schema1.test_part_drop_parent_p2024_02_01
HINT: Use DROP ... CASCADE to drop the dependent objects too.
CONTEXT: PL/pgSQL function
drop_partition_time(text,interval,boolean,boolean,text,timestamp with time
zone) line 308 at RAISE
PL/pgSQL function partman.run_maintenance(text,boolean,boolean) line 195 at
assignment
DETAIL:
HINT:
Where: PL/pgSQL function partman.run_maintenance(text,boolean,boolean) line
413 at RAISE

---

From: veem v <[email-masked]>
Date: Thu, 21 Mar 2024 17:46:49 +0530

Also, i see, its not allowing to drop the parent table partition even all
the respective child partitions are dropped and no child record exists in
the child table. But this started working , when we detach parent partition
and then drop the partitions. So it means if we just change the setup of
the parent partition table in the part_config as
retention_keep_table='true', retention_keep_index='true' and
retention_schema=<retention_schema>, it will work without giving error. And
then we have to drop the partitions from that retention_schema through
another procedure may be. Correct me if anyone has done with some other
workaround.

However , i have one question now, if we have child record exists in child
partition table, it wont even allow the detach the respective parent
partition, so is the "CALL partman.run_maintenance_proc()" automatically
ensures the ordering of child and parent table which will be passed through
the partition maintenance ? Or for that also we need to write our own
procedure and schedule through the cron?

Something as below,

CREATE PROCEDURE part_maintenance_proc()
LANGUAGE SQL
AS $$
declare
drop_partition_cursor CURSOR FOR
table_name from information_schema.tables where table_name like
'%test_part_drop_parent%' and table_schema like '%retention_schema%';
drop_partition_record RECORD;

Begin
partman.run_maintenance('cpod.test_part_drop_child');
partman.run_maintenance('cpod.test_part_drop_parent');

OPEN drop_partition_cursor
loop

FETCH NEXT FROM drop_partition_cursor INTO drop_partition_record;
EXIT WHEN NOT FOUND;

drop table drop_partition_record.table_name;
end loop;
close drop_partition_cursor;
END;
$$;

SELECT cron.schedule('@hourly',
);

On Thu, 21 Mar, 2024, 11:07 am veem v, <[email-masked]> wrote:

>  And this partition maintenance job which runs through cron scheduler in
> an automated way is failing on a daily basis for us without being able to
> drop any of the historical partitions from the parent partitioned table.
>
>  Below is the test case:-
>
> PL/pgSQL function partman.run_maintenance(text,boolean,boolean) line 195
> at assignment
> PL/pgSQL function partman.run_maintenance(text,boolean,boolean) line 195
> at assignment
> DETAIL:
> HINT:
> Where: PL/pgSQL function partman.run_maintenance(text,boolean,boolean)
> line 413 at RAISE
>

---

From: Laurenz Albe <[email-masked]>
Date: Thu, 21 Mar 2024 13:48:19 +0100

On Thu, 2024-03-21 at 11:07 +0530, veem v wrote:
> CREATE INDEX idx1_test_part_parent ON schema1.test_part_drop_parent( C3 ASC);
> 
> 
>  CONSTRAINT test_part_drop_child_FK1 FOREIGN KEY (C1,c2_part_date) REFERENCES schema1.test_part_drop_parent(c1,c2_part_date)
> CREATE INDEX test_part_drop_child_fk ON schema1.test_part_drop_child( c1, c2_part_date);
> 
> CREATE INDEX test_part_drop_child_idx ON schema1.test_part_drop_child( c1_child, c2_part_date);
>
> [create some partitions, then drop a partition of the referenced table]
>

> SQL Error [P0001]: ERROR: cannot drop table schema1.test_part_drop_parent_p2024_02_01 because other objects depend on it
> CONTEXT: SQL statement "DROP TABLE schema1.test_part_drop_parent_p2024_02_01"

That's normal.  If you create a foreign key constraint to a partitioned table, you
can no longer drop a partition of the referenced table.

What you *can* do is detach the partition and then drop it, but detatching will
be slow because PostgreSQL has to check for referencing rows.

The best solution is to create the foreign key *not* between the partitioned
tables, but between the individual table partitions.  That should be easy if
you have the same partition boundaries for both.
Then you can simply drop a partition from both tables at the same time.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

---

From: sud <[email-masked]>
Date: Tue, 2 Apr 2024 01:41:40 +0530

*The best solution is to create the foreign key *not* between the
partitioned*
*tables, but between the individual table partitions.  *

Interesting, even my thought was that the detach+drop parent partition will
only look into the specific child partition but not the whole child table.

However, out of curiosity, does this default foreign key setup i.e. foreign
keys between the table (but not between the partitions) also make the data
load into the child partitions slower ( as it must be then looking and
validating the presence of the keys across all the partitions of the parent
table)?