----- Mail original ----- > De: "Laurenz Albe" <laurenz.albe@xxxxxxxxxxx> > À: "veem v" <veema0000@xxxxxxxxx>, "pgsql-general" <pgsql-general@xxxxxxxxxxxxxxxxxxxx> > 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 primary key Regards Gilles
