From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
---|---|
To: | "James Pang (chaolpan)" <chaolpan(at)cisco(dot)com>, "pgsql-admin(at)lists(dot)postgresql(dot)org" <pgsql-admin(at)lists(dot)postgresql(dot)org> |
Subject: | Re: attach partition to parent table with default partition accessexclusive |
Date: | 2023-04-12 06:15:19 |
Message-ID: | 774f65350020e246daee7ffa726a9709c3122044.camel@cybertec.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
On Wed, 2023-04-12 at 03:03 +0000, James Pang (chaolpan) wrote:
> 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.
Yes, that is expected.
I recommend not to use a default partition if you want to attach more partitions later.
Yours,
Laurenz Albe
From | Date | Subject | |
---|---|---|---|
Next Message | Andres Perez | 2023-04-16 21:45:10 | recibido |
Previous Message | James Pang (chaolpan) | 2023-04-12 03:03:12 | attach partition to parent table with default partition accessexclusive |