From: | "James Pang (chaolpan)" <chaolpan(at)cisco(dot)com> |
---|---|
To: | "pgsql-admin(at)lists(dot)postgresql(dot)org" <pgsql-admin(at)lists(dot)postgresql(dot)org> |
Subject: | attach partition to parent table with default partition accessexclusive |
Date: | 2023-04-12 03:03:12 |
Message-ID: | PH0PR11MB51914C91CFF5B6CBD959AA1AD69B9@PH0PR11MB5191.namprd11.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Hi ,
We have a table wbxdata (xxxx...) partition by range(starttime) , and have a default partition, now we want to add a new partition and move the data from default partition to new added partition.
Begin;
Alter table only wbxdata ... detach partition wbxdata_pdefault; ---from here, accessexclusive lock on wbxdata_pdefault default partition, and it block all query on parent table wbxdata.
Insert into wbxdata_p2305 select * from wbxdata_pdefault where starttime >= '2023-05-01 00:00:00'::date and starttime < '2023-06-01 00:00:00';
Delete from wbxdata_pdefault where starttime >= '2023-05-01 00:00:00'::date and starttime < '2023-06-01 00:00:00';
Alter table only wbxdata attach partition wbxdata_p2305 for values from ''2023-05-01 00:00:00' TO '2023-06-01 00:00:00';
Alter table attach default partition;
End;
Is it expected to see all query even select on parent table wbxdata got blocked , when detach default partition and attach default partition ? Postgresql 14.7 version.
Thanks,
James
From | Date | Subject | |
---|---|---|---|
Next Message | Laurenz Albe | 2023-04-12 06:15:19 | Re: attach partition to parent table with default partition accessexclusive |
Previous Message | Dawn Yu | 2023-04-11 05:53:26 | Re: A Question about FPW and WAL |