Hi Hayato, > > Dear Hüseyin, > > > > > I encountered an issue after a pg_upgrade of a subscriber instance. > > > pg_subscription_rel came back empty, and the apply worker then discarded > > > every incoming change while the replication origin and the publisher > > > slot's confirmed_flush_lsn advanced past the discarded rows. Both ends > > > durably recording the transactions as applied, with nothing in either log > > > to say otherwise. > > > > To confirm, what was the datasabse version of old and new cluster? 9a17be1e2 > > allowed to migrate the content of pg_subscription_rel catalog[1]. It means > > it's > > unexpected behavior and must be fixed if old one is PG17+. > > > > [1]: > > https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=9a17be1e2 > > > > Best regards, > > Hayato Kuroda > > FUJITSU LIMITED
Created a v2 patch and let me summarize the problem. If a table is added to a publication (ALTER PUBLICATION ... ADD TABLE) and ALTER SUBSCRIPTION ... REFRESH PUBLICATION is not run on the subscriber, the table has no pg_subscription_rel row. The apply worker drops every change for it in should_apply_changes_for_rel(), with no log message, no conflict and no statistics counter. The publisher is told the changes were applied, so the origin and the slot move past them. Rows only reappear if the table is later added and copied again. Report the skip from should_apply_changes_for_rel(): - relation not in the subscription: LOG, with a hint to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION - relation not yet synchronized: DEBUG2 Each relation is reported once per sync state for the lifetime of the apply worker, tracked in LogicalRepRelMapEntry, since this gate is reached once per change. A restarted worker reports again. Example: LOG: skipping changes for relation "public.tab_4" in subscription "tap_sub" DETAIL: The relation is not part of the subscription. HINT: Use ALTER SUBSCRIPTION ... REFRESH PUBLICATION to add it. No behavior change beyond the new messages. The patch includes TAP tests for the apply worker (024_add_drop_pub.pl) and for a parallel apply worker (015_stream.pl). I intend this for master only. Regards, Demir.
v2-0001-log-unapplied-changes-for-non-ready-relations.patch
Description: Binary data
