pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  

help / color / mirror / Atom feed

attach partition to parent table with default partition accessexclusive
2+ messages / 2 participants
[nested] [flat]

* attach partition to parent table with default partition accessexclusive
@ 2023-04-12 03:03  James Pang (chaolpan) <chaolpan@cisco.com>
  0 siblings, 1 reply; 2+ messages in thread

From: James Pang (chaolpan) @ 2023-04-12 03:03 UTC (permalink / raw)
  To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>

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

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: attach partition to parent table with default partition accessexclusive
@ 2023-04-12 06:15  Laurenz Albe <laurenz.albe@cybertec.at>
  parent: James Pang (chaolpan) <chaolpan@cisco.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Laurenz Albe @ 2023-04-12 06:15 UTC (permalink / raw)
  To: James Pang (chaolpan) <chaolpan@cisco.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>

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





^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2023-04-12 06:15 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-04-12 03:03 attach partition to parent table with default partition accessexclusive James Pang (chaolpan) <chaolpan@cisco.com>
2023-04-12 06:15 ` Laurenz Albe <laurenz.albe@cybertec.at>

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