Re: Per-table resync for logical replication subscriptions

At 2026-11-02 04:05:10, "Cagri Biroglu" <cagri(dot)biroglu(at)adyen(dot)com> wrote:
>Hi Shlok ,
>
>thank you , fixed in v13, attached.
>
>REFRESH TABLE held pg_subscription_rel while it waited for a lock on a user
>relation. It now takes every relation lock first and opens the catalogue
>afterwards, which is the order AlterSubscription_refresh() already uses, so
>the cycle cannot form. The lock level was not the problem: RowExclusiveLock
>conflicts with the AccessExclusiveLock that command wants there just as
>surely.

Hi,
While reviewing the v13 patch, I found that the 039_refresh_table.pl test fails on my local machine.
It appears that case #79 behaves differently from your test results.
I believe the expected output of #79 is not guaranteed by the code logic,
but depends on the completion timing of two concurrent commands.
This is a test race condition.

I tried the following change:
move `$bg_pub->query('SELECT 1')` before `COMMIT PREPARED`

-$node_subscriber->safe_psql('postgres', "COMMIT PREPARED 'refresh_lk'");
-$bg_pub->query('SELECT 1');
-$bg_lk->query('SELECT 1');
+$bg_pub->query('SELECT 1');
+my $row_after = $node_subscriber->safe_psql('postgres',
+ "SELECT count(*) FROM pg_subscription_rel sr
+ JOIN pg_subscription s ON s.oid = sr.srsubid
+ WHERE s.subname = 'tap_sub_lk2'
+ AND sr.srrelid = 'tab_lk1'::regclass");
+diag("B1 after sync: row=$row_after");
+$node_subscriber->safe_psql('postgres', "COMMIT PREPARED 'refresh_lk'");
+$bg_lk->query('SELECT 1');

This ensures that bg_lk is only allowed to proceed after the REFRESH PUBLICATION on bg_pub has committed.
The 039_refresh_table.pl test now passes as expected.