Re: BUG #18644: ALTER PUBLICATION ... SET (publish_via_partition_root) wrong/undocumented behavior.

On Tue, Oct 8, 2024 at 12:22 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Fri, Oct 4, 2024 at 7:38 PM Maxim Boguk <maxim(dot)boguk(at)gmail(dot)com> wrote:
> >
> > >>It would be useful if you can create a small test case to show the exact problem and what is your usecase for the same?
> > Usecase - after initial load of logical replica I decided that on the
> > replica I better split future data into weekly partitions due huge
> > size (instead of monthly partitions on the master/publisher)
> > exactly the case for "alter publication set (publish_via_partition_root)".
> >
> > My main issues with this case - there is no way to fix this problem if
> > it happened less than reloading whole logical replication from blank.
> >
>
> You can prevent the problem by avoiding writes to the partitioned
> tables between the Alter Pub and Alter Sub steps. One idea could be
> that in a parallel session on publisher lock the parent table in
> Access Exclusive mode till the Alter Sub command (with
> copy_data=false) is finished.
>
> --
> With Regards,
> Amit Kapila.

Thank you, it should be work. Unfortunately my English writing now is
not good enough to suggest correct and easy to understand warnings in
the documentation about this issue.

--
Maxim Boguk
Senior Postgresql DBA

Phone UA: +380 99 143 0000
Phone AU: +61 45 218 5678