public inbox for [email protected]  

help / color / mirror / Atom feed

From: Laurenz Albe <[email protected]>
To: veem v <[email protected]>
To: pgsql-general <[email protected]>
Subject: Re: Not able to purge partition
Date: Thu, 21 Mar 2024 13:48:19 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAB+=1TU3kCsNM7xSHTCCu1AJAbL2-5phVmisLB_uTg-q2wEFvg@mail.gmail.com>
References: <CAB+=1TU3kCsNM7xSHTCCu1AJAbL2-5phVmisLB_uTg-q2wEFvg@mail.gmail.com>

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

view thread (4+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Not able to purge partition
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox