Thread: Not able to purge partition
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
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',
);
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
On Thu, 2024-03-21 at 11:07 +0530, veem v wrote: > 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); > > [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
----- Mail original ----- > De: "Laurenz Albe" <laurenz.albe@cybertec.at> > À: "veem v" <veema0000@gmail.com>, "pgsql-general" <pgsql-general@lists.postgresql.org> > Envoyé: Jeudi 21 Mars 2024 13:48:19 > Objet: Re: Not able to purge partition > On Thu, 2024-03-21 at 11:07 +0530, veem v wrote: >> 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); >> >> [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 Also, as an aside, test_part_drop_child_idx index is redundant with test_part_drop_child_pk index created for the primarykey Regards Gilles
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.
With the existing foreign key setup, the drop partitions works fine for the child tables when they are triggered through the cron job by calling "run_maintenance_proc". But its failing for parent table partition drop. And as you mentioned if we do the detach and then drop it works fine for the parent table.
However, we are creating partitions using the create_parent procedure something as below ,and thus it creates the default template table based on the main table structure informations and also the subsequent partitions are created/dropped accordingly using the scheduled cron as below.
So when you mentioned "to create the foreign keys *not* between the partitioned table but between the individual partitions" , can that be done using the same "partman.create_parent" procedure and automated cron job schedule or has to be done any other way manually ?
Additionally , do we have to give call to the cron job in orderly fashion, i.e child first and then parent table? As because, currently we were just , scheduling "run_maintenance_proc" once and all the partition tables maintenance(i.e. creation of new partition and dropping of old partitions) was being taken care automatically by that one call.
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='true', retention_keep_index='true',retention_schema='public'
where parent_table = 'schema1.test_part_drop_child';
SELECT cron.schedule('@hourly',
);
On Thu, 2024-03-21 at 22:50 +0530, veem v wrote: > So when you mentioned "to create the foreign keys *not* between the > partitioned table but between the individual partitions" , can that > be done using the same "partman.create_parent" procedure and automated > cron job schedule or has to be done any other way manually ? I don't know the capabilities of partmen, but I would be surprised if it could automatically create foreign keys on the partitions. Yours, Laurenz Albe
On Thu, 2024-03-21 at 22:50 +0530, veem v wrote:
> So when you mentioned "to create the foreign keys *not* between the
> partitioned table but between the individual partitions" , can that
> be done using the same "partman.create_parent" procedure and automated
> cron job schedule or has to be done any other way manually ?
I don't know the capabilities of partmen, but I would be surprised if
it could automatically create foreign keys on the partitions.
On Thu, 21 Mar 2024 at 23:39, Laurenz Albe <laurenz.albe@cybertec.at> wrote:On Thu, 2024-03-21 at 22:50 +0530, veem v wrote:
> So when you mentioned "to create the foreign keys *not* between the
> partitioned table but between the individual partitions" , can that
> be done using the same "partman.create_parent" procedure and automated
> cron job schedule or has to be done any other way manually ?
I don't know the capabilities of partmen, but I would be surprised if
it could automatically create foreign keys on the partitions.Yes, the constraints on each of the child partitions and parent partitions were getting created automatically. As I see from information_schema.table_constraints, it shows one foreign key constraint in each of the child partitions created through the partman procedure.It works smoothly without any issue, until we noticed this issue while trying to purge the partition from the parent table partition. But I believe this extension is extensively used , so I'm just wondering if I am missing something here with regards to foreign key creation using this automated partition creation/partman extension functionality.
2)As we are using pg_partman for maintaining the partitions, So do we need to schedule multiple crons (one for parent table and other for child table), so that first it will drop the child table partition and then the parent table partition(As because detaching/dropping parent partition first will error out) called in order? OR only one cron job call like below can do the job for us and make sure the dependency between the tables is taken care of automatically?
On Sat, 2024-03-23 at 22:41 +0530, veem v wrote: > 1)As we see having foreign key defined is making the detach partition run > for minutes(in our case 5-10minutes for 60 million rows partition), so > how to make the parent table partition detach and drop work fast in such > a scenario while maintaining the foreign key intact? I told you: don't do it. Instead, use foreign keys between the partitions. Yours, Laurenz Albe
On Sat, 2024-03-23 at 22:41 +0530, veem v wrote:
> 1)As we see having foreign key defined is making the detach partition run
> for minutes(in our case 5-10minutes for 60 million rows partition), so
> how to make the parent table partition detach and drop work fast in such
> a scenario while maintaining the foreign key intact?
I told you: don't do it.
Instead, use foreign keys between the partitions.
On Sat, 23 Mar 2024 at 23:08, Laurenz Albe <laurenz.albe@cybertec.at> wrote:On Sat, 2024-03-23 at 22:41 +0530, veem v wrote:
> 1)As we see having foreign key defined is making the detach partition run
> for minutes(in our case 5-10minutes for 60 million rows partition), so
> how to make the parent table partition detach and drop work fast in such
> a scenario while maintaining the foreign key intact?
I told you: don't do it.
Instead, use foreign keys between the partitions.I am struggling to understand how to maintain those partitions then? As because we were planning to use pg_partman for creating and dropping partitions automatically without much hassle. So do you mean to say do the partition maintenance(create/drop) by creating our own jobs and not to use the pg_partman extension for this.Say for example in our case the parent table has 3-4 child table and all are partitioned on same keys/columns, so how we can identify the child partitions and then create all foreign keys to the respective parent table partitions and attach those partitions to parent table and also make this process automated? Appreciate any guidance on this.Actually, using pg_partman was taking care of everything starting from creating partitions with different names and creating respective indexes, constraints also with different names for each partitions without us being worrying anything about those.
On Sun, 2024-03-24 at 00:37 +0530, veem v wrote: > > Instead, use foreign keys between the partitions. > > I am struggling to understand how to maintain those partitions then? > As because we were planning to use pg_partman for creating and dropping > partitions automatically without much hassle. I understand. But do you want to pursue a way that is not working well, just because the tool you chose cannot handle it? Yours, Laurenz Albe
On Sun, 2024-03-24 at 00:37 +0530, veem v wrote:
> > Instead, use foreign keys between the partitions.
>
> I am struggling to understand how to maintain those partitions then?
> As because we were planning to use pg_partman for creating and dropping
> partitions automatically without much hassle.
I understand.
But do you want to pursue a way that is not working well, just because
the tool you chose cannot handle it?
Yours,
Laurenz Albe
On Mon, 2024-03-25 at 02:54 +0530, veem v wrote: > Can you please suggest some docs which shows the way we should > do the partition maintenance (if not using pg_partman)? man cron > Actually , I am not able to visualize the steps here. Do you mean > to say , we will just create the partition tables without any foreign > key constraints first. Then create parent table future partitions first > (say 10 days partitions) and then child table future 10 days partitions > manually and during that define the foreign key constraints , indexes > etc. Here , how to exactly find the exact matching parent partitions > so as to create the foreign keys one to one I don't see what's so hard about it. When the time is there to create a new partition, create a partition for both the parent and the child table and define a foreign key between them. Yours, Laurenz Albe
> [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.
On Tue, 2024-04-02 at 01:41 +0530, sud wrote: > On Thu, Mar 21, 2024 at 6:18 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote: > > > [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 dependon it > > > CONTEXT: SQL statement "DROP TABLE schema1.test_part_drop_parent_p2024_02_01" > > 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)? The query checking the foreign key consistency should be somewhat more expensive, since it has to undergo partition pruning to figure out which partition to query. Yours, Laurenz Albe