Message-ID: <774f65350020e246daee7ffa726a9709c3122044.camel@cybertec.at>
Subject: Re: attach partition to parent table with default partition
 accessexclusive
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: "James Pang (chaolpan)" <chaolpan@cisco.com>,
	"pgsql-admin@lists.postgresql.org"
	 <pgsql-admin@lists.postgresql.org>
Date: Wed, 12 Apr 2023 08:15:19 +0200
In-Reply-To: 
 <PH0PR11MB51914C91CFF5B6CBD959AA1AD69B9@PH0PR11MB5191.namprd11.prod.outlook.com>
References: 
	<PH0PR11MB51914C91CFF5B6CBD959AA1AD69B9@PH0PR11MB5191.namprd11.prod.outlook.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.46.4 (3.46.4-1.fc37) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/774f65350020e246daee7ffa726a9709c3122044.camel%40cybertec.at>
Precedence: bulk

On Wed, 2023-04-12 at 03:03 +0000, James Pang (chaolpan) wrote:
> We have a table=C2=A0=C2=A0=C2=A0 wbxdata (xxxx=E2=80=A6)=C2=A0 partition=
 by range(starttime) , and have a
> default partition, now we want to add a new partition and move the data f=
rom
> default partition to new added partition.
> =C2=A0
> Begin;=20
> =C2=A0=C2=A0Alter table only=C2=A0 wbxdata =E2=80=A6 detach partition=C2=
=A0 wbxdata_pdefault; =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0---from here, accessexclusive lock on wbxdata_pdefault default partition=
, and it block all query on parent table wbxdata.
> =C2=A0Insert into wbxdata_p2305 select * from wbxdata_pdefault where star=
ttime >=3D =E2=80=982023-05-01 00:00:00=E2=80=99::date and starttime < =E2=
=80=982023-06-01 00:00:00=E2=80=99;
> Delete from wbxdata_pdefault where starttime >=3D =E2=80=982023-05-01 00:=
00:00=E2=80=99::date and starttime < =E2=80=982023-06-01 00:00:00=E2=80=99;
> Alter table only wbxdata attach partition wbxdata_p2305 for values from =
=E2=80=98=E2=80=982023-05-01 00:00:00=E2=80=99 TO =C2=A0=E2=80=982023-06-01=
 00:00:00=E2=80=99;
> Alter table attach default partition;
> End;
> Is it expected to see all query even select on=C2=A0 parent table=C2=A0 w=
bxdata got blocked ,
> when=C2=A0 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 parti=
tions later.

Yours,
Laurenz Albe