From: gparc@free.fr
To: veem v <veema0000@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>
Subject: Re: Not able to purge partition
Date: Thu, 21 Mar 2024 15:54:57 +0100 (CET)
Message-ID: <1962146764.770571424.1711032897372.JavaMail.zimbra@free.fr> (raw)
In-Reply-To: <c07417320869cfc1f0bededec98cb92298c37df0.camel@cybertec.at>
References: <CAB+=1TU3kCsNM7xSHTCCu1AJAbL2-5phVmisLB_uTg-q2wEFvg@mail.gmail.com>
<c07417320869cfc1f0bededec98cb92298c37df0.camel@cybertec.at>
----- 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 primary key
Regards
Gilles
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: pgsql-general@postgresql.org
Cc: gparc@free.fr, veema0000@gmail.com, pgsql-general@lists.postgresql.org, laurenz.albe@cybertec.at
Subject: Re: Not able to purge partition
In-Reply-To: <1962146764.770571424.1711032897372.JavaMail.zimbra@free.fr>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox