Обсуждение: Not able to purge partition

Поиск
Список
Период
Сортировка

Not able to purge partition

От
veem v
Дата:
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

Re: Not able to purge partition

От
veem v
Дата:

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, <veema0000@gmail.com> 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

Re: Not able to purge partition

От
Laurenz Albe
Дата:
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

Re: Not able to purge partition

От
gparc@free.fr
Дата:
----- 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

Re: Not able to purge partition

От
veem v
Дата:
On Thu, 21 Mar, 2024, 6:18 pm Laurenz Albe, <laurenz.albe@cybertec.at> wrote:

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.


Thank you Laurenz.

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',
);

Re: Not able to purge partition

От
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.

Yours,
Laurenz Albe

Re: Not able to purge partition

От
veem v
Дата:
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.


Re: Not able to purge partition

От
veem v
Дата:
Can someone please confirm if this behavior of foreign key is expected with the partition created through partman extension and thus we need to have our manual process written for partition purge (in order of child first and then parent) , rather relying on partman partition maintenance to take care drop partition automatically for us? 

On Fri, 22 Mar, 2024, 12:42 am veem v, <veema0000@gmail.com> wrote:
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.


Re: Not able to purge partition

От
veem v
Дата:
Trying to consolidate the main questions here as below. 

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?

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?

SELECT cron.schedule('@daily',partman.run_maintenance());

Re: Not able to purge partition

От
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.

Yours,
Laurenz Albe

Re: Not able to purge partition

От
veem v
Дата:
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.

Re: Not able to purge partition

От
Lok P
Дата:
On Sun, Mar 24, 2024 at 12:38 AM veem v <veema0000@gmail.com> wrote:
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.

This appears to be a major issue , if it's taking minutes for dropping the parent table partitions and not allowing read operation during that time on the child table by taking locks on them. We have many databases in Oracle with such referential key constraints existing on partitioned tables and we were planning to move those to postgres. I think in Oracle, they were by default created partition to partition without need to check the whole table or all the child table partitions while dropping the parent partitions .
 

Re: Not able to purge partition

От
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

Re: Not able to purge partition

От
veem v
Дата:
On Sun, 24 Mar 2024 at 20:29, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
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


Thank you so much Laurenz.

Can you please suggest some docs which shows the way we should do the partition maintenance (if not using pg_partman)? 

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?

Re: Not able to purge partition

От
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

Re: Not able to purge partition

От
sud
Дата:

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 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)?
 

Re: Not able to purge partition

От
Laurenz Albe
Дата:
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