Re: undetected deadlock in ALTER SUBSCRIPTION ... REFRESH PUBLICATION - Mailing list pgsql-hackers

From Amit Kapila
Subject Re: undetected deadlock in ALTER SUBSCRIPTION ... REFRESH PUBLICATION
Date
Msg-id CAA4eK1KKgA+j4z2XYkAZh5cEzAUEdKw0-u96bu5a9PBbZumS7Q@mail.gmail.com
Whole thread Raw
In response to Re: undetected deadlock in ALTER SUBSCRIPTION ... REFRESH PUBLICATION  (Shlok Kyal <shlok.kyal.oss@gmail.com>)
Responses Re: undetected deadlock in ALTER SUBSCRIPTION ... REFRESH PUBLICATION
List pgsql-hackers
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.



pgsql-hackers by date:

Previous
From: Alvaro Herrera
Date:
Subject: Re: Failure with pgbench and --disable-thread-safety in ~v16
Next
From: Tatsuo Ishii
Date:
Subject: Re: Row pattern recognition