Re: undetected deadlock in ALTER SUBSCRIPTION ... REFRESH PUBLICATION

On Sat, Dec 2, 2023 at 9:52 PM Shlok Kyal <shlok.kyal.oss@gmail.com> wrote:
>
> > thread. I think you can compare the timing of regression tests in
> > subscription, with and without the patch to show there is no
> > regression. And probably some tests with a large number of tables for
> > sync with very little data.
>
> I have tested the regression test timings for subscription with and
> without patch. I also did the timing test for sync of subscription
> with the publisher for 100 and 1000 tables respectively.
> I have attached the test script and results of the timing test are as follows:
>
> Time taken for test to run in Linux VM
> Summary                                |  Subscription Test (sec)
> |    100 tables in pub and Sub (sec)    |  1000 tables in pub and Sub
> (sec)
> Without patch Release           |  95.564
>  |     7.877                                             |   58.919
> With patch Release                |  96.513
>    |     6.533                                             |   45.807
>
> Time Taken for test to run in another Linux VM
> Summary                                |  Subscription Test (sec)
> |    100 tables in pub and Sub (sec)    |  1000 tables in pub and Sub
> (sec)
> Without patch Release           |  109.8145
> |    6.4675                                           |    83.001
> With patch Release                |  113.162
>    |    7.947                                              |   87.113
>

So, on some machines, it may increase the test timing although not too
much. I think the reason is probably doing the work in multiple
transactions for multiple relations. I am wondering that instead of
committing and starting a new transaction before
wait_for_relation_state_change(), what if we do it inside that
function just before we decide to wait? It is quite possible that in
many cases we don't need any wait at all.

-- 
With Regards,
Amit Kapila.