RE: Failed transaction statistics to measure the logical replication progress

On Wednesday, December 22, 2021 9:38 PM Wang, Wei/王 威 <wangw(dot)fnst(at)fujitsu(dot)com> wrote:
> I have a question on the v19-0002 patch:
>
> When I tested for this patch, I found pg_stat_subscription_workers has some
> unexpected data.
> For example:
> [Publisher]
> create table replica_test1(a int, b text); create publication pub1 for table
> replica_test1; create table replica_test2(a int, b text); create publication pub2
> for table replica_test2;
>
> [Subscriber]
> create table replica_test1(a int, b text); create subscription sub1 CONNECTION
> 'dbname=postgres' publication pub1; create table replica_test2(a int, b text);
> create subscription sub2 CONNECTION 'dbname=postgres' publication pub2;
>
> [Publisher]
> insert into replica_test1 values(1,'1');
>
> [Subscriber]
> select * from pg_stat_subscription_workers;
>
> -[ RECORD 1 ]------+------
> Subid | 16389
> subname | sub1
> subrelid |
> commit_count | 1
> ...
> -[ RECORD 2 ]------+------
> subid | 16395
> subname | sub2
> subrelid |
> commit_count | 1
> ...
>
> I originally expected only one record for "sub1".
>
> I think the reason is apply_handle_commit() always invoke
> pgstat_report_subworker_xact_end().
> But when we insert data to replica_test1 in publish side:
> [In the publish]
> pub1's walsender1 will send three messages((LOGICAL_REP_MSG_BEGIN,
> LOGICAL_REP_MSG_INSERT and LOGICAL_REP_MSG_COMMIT))
> to sub1's apply worker1.
> pub2's walsender2 will also send two messages(LOGICAL_REP_MSG_BEGIN
> and LOGICAL_REP_MSG_COMMIT) to sub2's apply worker2. Because
> inserted table is not published by pub2.
>
> [In the subscription]
> sub1's apply worker1 receive LOGICAL_REP_MSG_COMMIT,
> so invoke pgstat_report_subworker_xact_end to increase
> commit_count of sub1's stats.
> sub2's apply worker2 receive LOGICAL_REP_MSG_COMMIT,
> it will do the same action to increase commit_count of sub2's stats.
>
> Do we expect these commit counts which come from empty transactions ?
Hi, thank you so much for your test !

This is another issue discussed in [1]
where the patch in the thread is a work in progress, I think.

The point you reported will bring a lot of confusion for the users,
to interpret the results of the subscription stats values,
if those patches including the subscription stats patch will not get committed
together (like in the same version).

I've confirmed that HEAD applied with v19-* and
v15-0001-Skip-empty-transactions-for-logical-replication.patch
on top of v19-* showed only one record, as you expected like below,
although all patches are not finished yet.

postgres=# select * from pg_stat_subscription_workers;
-[ RECORD 1 ]------+------
subid | 16389
subname | sub1
subrelid |
commit_count | 1
abort_count | 0
error_count | 0
....

IMHO, the conclusion is we are currently in the middle of fixing the behavior.

[1] - https://www.postgresql.org/message-id/CAFPTHDbVLWxpfnwMxJcXq703gLXciXHE83hwKQ_0OTCZ6oLCjg%40mail.gmail.com

Best Regards,
Takamichi Osumi