public inbox for [email protected]
help / color / mirror / Atom feedNot able to purge partition
4+ messages / 3 participants
[nested] [flat]
* Not able to purge partition
@ 2024-03-21 05:37 veem v <[email protected]>
0 siblings, 2 replies; 4+ messages in thread
From: veem v @ 2024-03-21 05:37 UTC (permalink / raw)
To: pgsql-general <[email protected]>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Not able to purge partition
@ 2024-03-21 12:16 veem v <[email protected]>
parent: veem v <[email protected]>
1 sibling, 0 replies; 4+ messages in thread
From: veem v @ 2024-03-21 12:16 UTC (permalink / raw)
To: pgsql-general <[email protected]>
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 protected]> wrote:
> 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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Not able to purge partition
@ 2024-03-21 12:48 Laurenz Albe <[email protected]>
parent: veem v <[email protected]>
1 sibling, 1 reply; 4+ messages in thread
From: Laurenz Albe @ 2024-03-21 12:48 UTC (permalink / raw)
To: veem v <[email protected]>; pgsql-general <[email protected]>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Not able to purge partition
@ 2024-04-01 20:11 sud <[email protected]>
parent: Laurenz Albe <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: sud @ 2024-04-01 20:11 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: veem v <[email protected]>; pgsql-general <[email protected]>
On Thu, Mar 21, 2024 at 6:18 PM Laurenz Albe <[email protected]>
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 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. *
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)?
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2024-04-01 20:11 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-03-21 05:37 Not able to purge partition veem v <[email protected]>
2024-03-21 12:16 ` veem v <[email protected]>
2024-03-21 12:48 ` Laurenz Albe <[email protected]>
2024-04-01 20:11 ` sud <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox