attach partition to parent table with default partition accessexclusive

Поиск
Список
Период
Сортировка
От James Pang (chaolpan)
Тема attach partition to parent table with default partition accessexclusive
Дата
Msg-id PH0PR11MB51914C91CFF5B6CBD959AA1AD69B9@PH0PR11MB5191.namprd11.prod.outlook.com
обсуждение исходный текст
Ответы Re: attach partition to parent table with default partition accessexclusive  (Laurenz Albe <laurenz.albe@cybertec.at>)
Список 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

 

В списке pgsql-admin по дате отправления:

Предыдущее
От: Dawn Yu
Дата:
Сообщение: Re: A Question about FPW and WAL
Следующее
От: Laurenz Albe
Дата:
Сообщение: Re: attach partition to parent table with default partition accessexclusive