Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rnHM1-00CTg9-Lq for pgsql-general@arkaria.postgresql.org; Thu, 21 Mar 2024 12:17:06 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rnHM0-00Cznt-6w for pgsql-general@arkaria.postgresql.org; Thu, 21 Mar 2024 12:17:04 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rnHLz-00Czlo-JT for pgsql-general@lists.postgresql.org; Thu, 21 Mar 2024 12:17:04 +0000 Received: from mail-oa1-x30.google.com ([2001:4860:4864:20::30]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rnHLw-005fJ6-5t for pgsql-general@lists.postgresql.org; Thu, 21 Mar 2024 12:17:02 +0000 Received: by mail-oa1-x30.google.com with SMTP id 586e51a60fabf-22200c78d4fso481306fac.1 for ; Thu, 21 Mar 2024 05:17:01 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1711023420; x=1711628220; darn=lists.postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=UDIQYxf1ee3cNuEFqUrVK2s0buezCLE5RfPapmGH+TE=; b=RgerQMcM4FUawvlXGY4Q+v3hAsMRVnKoVzySZAX/Gg+Dm9LXtzonO71Wf7S7f8/CfU o5imIG64aoypJ78WIoiKrI5rH40CSc1PTWj5O0eMRuXksFbnSUss55ep9Kr8b5Pm1Zpe tphNDUafgaaY7gOMtXzXh5SEukpqbeouZXUyMiAEoFN/kwAmamD04hFmFRxh89VRLyJv fqi8w6ivs/zDuL9y1TLPu9lcpqZ7HtS6Kcf1yb08dTIfpbdg+2Q5Z4Aqr7kLPtZ4bvUl 6qHZeuhZZf30kcYHWZUdxL3WRYywIqOgms8WNSpd0/ioT3FImAqWs2h5qMB6pgQAi77Y 3RTQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1711023420; x=1711628220; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=UDIQYxf1ee3cNuEFqUrVK2s0buezCLE5RfPapmGH+TE=; b=WShP8Ds77sPYxGOciHOuCVbRhZ98x1fBZnUtnHdHByKG7gFGWf8KfXmgET+ICLMDUn vSoWD0jxug9BdIjQ0LPZ96/BKnbylPEXZHoQROm07FtK0qwbCE1dOeQ+K0IUDBkR5FBs wPOAdUOtZSviDkhfBoEZgcGG/hNize7JJeHddoxDkEoZA8jfnU88Jdo6XnEHnDV+u88b o5FqwSQ9ny3F0g+S/+npi8sO/LOx8dIeqsnywljtS8DoxMRnQ4USpz8Rr+MZ8/VA8O2K r8+yJIgkzNSe+4kF//okNmyv4b1K2bjZQ7tYT6GXKPEWg+rZofbvDYCjah4uvRCm9ix3 yH6w== X-Gm-Message-State: AOJu0YzP6sB5VOJikbJXPlX/h7XNS0sQR674g34Np7CrWmM/AmBVoxot v0KiwrgR7LfbKnYT2sfpetFAzTbZaA/XQf5g/vKZ4kAu614dqUlt718HodCsE+TAqz2Ea5xhC2f qeD74bFeJG9/erQcYwNgOwwbube52ov6i X-Google-Smtp-Source: AGHT+IHHRv5SNlvVneEkwfGs3c2QL7m+zcucTxSLFbl0IHbQqLU/0LGHib6eJSbM9NsubFtRiqm8fB9nZsvwc8HAVGM= X-Received: by 2002:a05:6870:b156:b0:221:9635:da6d with SMTP id a22-20020a056870b15600b002219635da6dmr2107520oal.47.1711023420415; Thu, 21 Mar 2024 05:17:00 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: veem v Date: Thu, 21 Mar 2024 17:46:49 +0530 Message-ID: Subject: Re: Not able to purge partition To: pgsql-general Content-Type: multipart/alternative; boundary="0000000000002d28eb06142aaf31" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000002d28eb06142aaf31 Content-Type: text/plain; charset="UTF-8" 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=, 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, 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 > --0000000000002d28eb06142aaf31 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

Also, i= see, its not allowing to drop the parent table partition even all the resp= ective 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=3D'true'= ;, retention_keep_index=3D'true' and retention_schema=3D<retenti= on_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 part= man.run_maintenance_proc()" automatically ensures the ordering of chil= d 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 t= he cron?

Something as below,

CREATE PROCEDURE part_maintena= nce_proc()
LANGUAGE SQL
AS $$
declare
drop_partition_cursor CURSOR FOR
table_name from information_schema.tables whe= re table_name like '%test_part_drop_parent%' and table_schema like = '%retention_schema%';
drop_partition_record RECORD;

Begin
partman.run_maintenance('cpod.test_part_d= rop_child');
partman.run_maintenance('cpod.test_part_d= rop_parent');

OPEN drop_partition_cursor
loop

FETCH NEXT FROM drop_partition= _cursor INTO drop_partition_record;
EXIT WHEN NOT FOUND;

drop table drop_partition_reco= rd.table_name;
end loop;
close drop_partition_cursor;
END;
$$;

SELECT cron.schedule('@hou= rly',
);



On Thu, 21 Mar, 2024, 11:07 am vee= m v, <veema0000@gmail.com>= wrote:
Hello All,=
=C2=A0We created two partitioned tables one being parent and other chil= d. 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 o= lder partitions which meet the partition retention criteria, it's not l= etting us drop any of the partitions from the parent table. Child table wor= ks fine though.
=C2=A0
=C2=A0And when we try using cascade , it drops= the foreign key constraint from all the partitions.=C2=A0 Can you please l= et me know how to make the partition maintenance work in such a scenario wh= ile 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?=C2=A0
=C2=A0And this partition maintenance job which runs through cron= scheduler in an automated way is failing on a daily basis for us without b= eing able to drop any of the historical partitions from the parent partitio= ned table.
=C2=A0
=C2=A0Below is the test case:-

CREATE TABLE schema1.test_part_drop_parent
(
c1 varchar= (36) =C2=A0NOT NULL ,
c2_part_date timestamp with time zone =C2=A0NOT N= ULL ,
c3 =C2=A0numeric(12,0) =C2=A0NOT NULL ,
CONSTRAINT test_part_= drop_parent_PK PRIMARY KEY (c1,c2_part_date)
) PARTITION BY RANGE (c2_pa= rt_date);

CREATE INDEX idx1_test_part_parent ON schema1.test_part_dr= op_parent( C3 ASC);


CREATE TABLE schema1.test_part_drop_child(
C1_child =C2=A0 varchar(36) =C2=A0NOT NULL ,
C1 =C2=A0 varchar= (36) =C2=A0NOT NULL ,
c2_part_date timestamp with time zone =C2=A0NOT N= ULL ,
C3 =C2=A0numeric(12,0) =C2=A0NOT 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.tes= t_part_drop_child( c1_child, c2_part_date);


select partman.crea= te_parent(
=C2=A0 =C2=A0p_parent_table :=3D 'schema1.test_part_drop_= child',
=C2=A0 =C2=A0p_control :=3D 'c2_part_date',
=C2= =A0 =C2=A0p_type :=3D 'native',
=C2=A0 =C2=A0p_interval :=3D = 9;1 day',
=C2=A0 =C2=A0p_premake :=3D 5,
=C2=A0 =C2=A0p_start_par= tition =3D> '2024-02-01 00:00:00'
);

update partman.pa= rt_config set infinite_time_partitions =3D 'true' , retention =3D &= #39;1 months', retention_keep_table=3D'false', retention_keep_i= ndex=3D'false'
where parent_table =3D 'schema1.test_part_dro= p_child';


select partman.create_parent(
=C2=A0 =C2=A0p_pa= rent_table :=3D 'schema1.test_part_drop_parent',
=C2=A0 =C2=A0p_= control :=3D 'c2_part_date',
=C2=A0 =C2=A0p_type :=3D 'nativ= e',
=C2=A0 =C2=A0p_interval :=3D '1 day',
=C2=A0 =C2=A0p_= premake :=3D 5,
=C2=A0 =C2=A0p_start_partition =3D> '2024-02-01 0= 0:00:00'
);

update partman.part_config set infinite_time_part= itions =3D 'true' , retention =3D '1 months', retention_kee= p_table=3D'false', retention_keep_index=3D'false'
where = parent_table =3D 'schema1.test_part_drop_parent';


select= partman.run_maintenance('schema1.test_part_drop_child');

se= lect partman.run_maintenance('schema1.test_part_drop_parent');
<= br>
SQL Error [P0001]: ERROR: cannot drop table schema1.test_part_drop_p= arent_p2024_02_01 because other objects depend on it
CONTEXT: SQL statem= ent "DROP TABLE schema1.test_part_drop_parent_p2024_02_01"
PL/= pgSQL function drop_partition_time(text,interval,boolean,boolean,text,times= tamp with time zone) line 213 at EXECUTE
PL/pgSQL function partman.run_m= aintenance(text,boolean,boolean) line 195 at assignment
DETAIL: constrai= nt test_part_drop_child_fk1 on table schema1.test_part_drop_child depends o= n table schema1.test_part_drop_parent_p2024_02_01
HINT: Use DROP ... CAS= CADE 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,boolea= n,boolean) line 195 at assignment
DETAIL:
HINT:
Where: PL/pgSQL fu= nction partman.run_maintenance(text,boolean,boolean) line 413 at RAISE
--0000000000002d28eb06142aaf31--