Date: Thu, 21 Mar 2024 15:54:57 +0100 (CET)
From: gparc@free.fr
To: veem v <veema0000@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>,
	Laurenz Albe <laurenz.albe@cybertec.at>
Message-ID: <1962146764.770571424.1711032897372.JavaMail.zimbra@free.fr>
In-Reply-To: <c07417320869cfc1f0bededec98cb92298c37df0.camel@cybertec.at>
References: 
 <CAB+=1TU3kCsNM7xSHTCCu1AJAbL2-5phVmisLB_uTg-q2wEFvg@mail.gmail.com>
 <c07417320869cfc1f0bededec98cb92298c37df0.camel@cybertec.at>
Subject: Re: Not able to purge partition
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Thread-Topic: Not able to purge partition
Thread-Index: gmtOcTTHlIQMegOcBRYi/Zel0iFRYQ==
Archived-At: 
 <https://www.postgresql.org/message-id/1962146764.770571424.1711032897372.JavaMail.zimbra%40free.fr>
Precedence: bulk

----- Mail original -----
> De: "Laurenz Albe" <laurenz.albe@cybertec.at>
> =C3=80: "veem v" <veema0000@gmail.com>, "pgsql-general" <pgsql-general@li=
sts.postgresql.org>
> Envoy=C3=A9: 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
>> (
>> =C2=A0c1 varchar(36) =C2=A0NOT NULL ,
>> =C2=A0c2_part_date timestamp with time zone =C2=A0NOT NULL ,
>> =C2=A0c3 =C2=A0numeric(12,0) =C2=A0NOT NULL ,
>> =C2=A0CONSTRAINT test_part_drop_parent_PK PRIMARY KEY (c1,c2_part_date)
>> ) PARTITION BY RANGE (c2_part_date);
>>=20
>> CREATE INDEX idx1_test_part_parent ON schema1.test_part_drop_parent( C3 =
ASC);
>>=20
>>=20
>> CREATE TABLE schema1.test_part_drop_child
>> (
>> =C2=A0C1_child =C2=A0 varchar(36) =C2=A0NOT NULL ,
>> =C2=A0C1 =C2=A0 varchar(36) =C2=A0NOT NULL ,
>> =C2=A0c2_part_date timestamp with time zone =C2=A0NOT NULL ,
>> =C2=A0C3 =C2=A0numeric(12,0) =C2=A0NOT NULL ,
>> =C2=A0CONSTRAINT test_part_drop_child_PK PRIMARY KEY (C1_child,c2_part_d=
ate),
>> =C2=A0CONSTRAINT test_part_drop_child_FK1 FOREIGN KEY (C1,c2_part_date) =
REFERENCES
>> =C2=A0schema1.test_part_drop_parent(c1,c2_part_date)
>> ) PARTITION BY RANGE (c2_part_date);
>>=20
>> CREATE INDEX test_part_drop_child_fk ON schema1.test_part_drop_child( c1=
,
>> c2_part_date);
>>=20
>> CREATE INDEX test_part_drop_child_idx ON schema1.test_part_drop_child( c=
1_child,
>> c2_part_date);
>>
>> [create some partitions, then drop a partition of the referenced table]
>>
>=20
>> SQL Error [P0001]: ERROR: cannot drop table
>> schema1.test_part_drop_parent_p2024_02_01 because other objects depend o=
n it
>> CONTEXT: SQL statement "DROP TABLE schema1.test_part_drop_parent_p2024_0=
2_01"
>=20
> That's normal.  If you create a foreign key constraint to a partitioned t=
able,
> you
> can no longer drop a partition of the referenced table.
>=20
> What you *can* do is detach the partition and then drop it, but detatchin=
g will
> be slow because PostgreSQL has to check for referencing rows.
>=20
> The best solution is to create the foreign key *not* between the partitio=
ned
> 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.
>=20
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com

Also, as an aside, test_part_drop_child_idx index is redundant with test_pa=
rt_drop_child_pk index created for the primary key

Regards
Gilles