From: Laurenz Albe <laurenz.albe@cybertec.at>
To: James Pang (chaolpan) <chaolpan@cisco.com>
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: attach partition to parent table with default partition accessexclusive
Date: Wed, 12 Apr 2023 08:15:19 +0200
Message-ID: <774f65350020e246daee7ffa726a9709c3122044.camel@cybertec.at> (raw)
In-Reply-To: <PH0PR11MB51914C91CFF5B6CBD959AA1AD69B9@PH0PR11MB5191.namprd11.prod.outlook.com>
References: <PH0PR11MB51914C91CFF5B6CBD959AA1AD69B9@PH0PR11MB5191.namprd11.prod.outlook.com>
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
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-admin@postgresql.org
Cc: laurenz.albe@cybertec.at, chaolpan@cisco.com, pgsql-admin@lists.postgresql.org
Subject: Re: attach partition to parent table with default partition accessexclusive
In-Reply-To: <774f65350020e246daee7ffa726a9709c3122044.camel@cybertec.at>
* 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