RE: Subscription statistics are not dropped at DROP SUBSCRIPTION in some cases

On Wednesday, July 5, 2023 2:53 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:

Hi,

> Thanks for reviewing the patch. Pushed.

My colleague Vignesh noticed that the newly added test cases were failing in BF animal sungazer[1].

The test failed to drop the slot which is active on publisher.

--error-log--
This failure is because pg_drop_replication_slot fails with "replication slot "test_tab2_sub" is active for PID 55771638":
2023-09-02 09:00:04.806 UTC [12910732:4] 026_stats.pl LOG:  statement: SELECT pg_drop_replication_slot('test_tab2_sub')
2023-09-02 09:00:04.807 UTC [12910732:5] 026_stats.pl ERROR:  replication slot "test_tab2_sub" is active for PID 55771638
2023-09-02 09:00:04.807 UTC [12910732:6] 026_stats.pl STATEMENT:  SELECT pg_drop_replication_slot('test_tab2_sub')
-------------

I the reason is that the test DISABLEd the subscription before dropping the
slot, while "ALTER SUBSCRIPTION DISABLE" doesn't wait for the walsender to
release the slot, so it's possible that the walsender is still alive when calling
pg_drop_replication_slot() to drop the slot on publisher(pg_drop_xxxslot() 
doesn't wait for slot to be released).

I think we can wait for the slot to become inactive before dropping like:
	$node_primary->poll_query_until('otherdb',
		"SELECT NOT EXISTS (SELECT 1 FROM pg_replication_slots WHERE active_pid IS NOT NULL)"
	)

Or we can just don't drop the slot as it’s the last testcase.

Another thing might be worth considering is we can add one parameter in
pg_drop_replication_slot() to let user control whether to wait or not, and the
test can be fixed as well by passing nowait=false to the func.

[1] https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=sungazer&dt=2023-09-02%2002%3A17%3A01

Best Regards,
Hou zj