Fix slot synchronization with two_phase decoding enabled
Zhijie Hou (Fujitsu) <houzj.fnst@fujitsu.com>
From: "Zhijie Hou (Fujitsu)" <houzj.fnst@fujitsu.com>
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2025-03-25T05:35:32Z
Lists: pgsql-hackers
Attachments
- v1-0001-Tap-test-to-reproduce-the-issue.patch (application/octet-stream) patch v1-0001
- v1-0003-Sync-the-two_phase_at-field-of-a-replication-slot.patch (application/octet-stream) patch v1-0003
- v1-0002-Display-two_phase_at-in-the-pg_replication_slots-.patch (application/octet-stream) patch v1-0002
Hi, When testing the slot synchronization with logical replication slots that enabled two_phase decoding, I found that transactions prepared before two-phase decoding is enabled may fail to replicate to the subscriber after being committed on a promoted standby following a failover. To reproduce this issue, please follow these steps (also detailed in the attached TAP test, v1-0001): 1. sub: create a subscription with (two_phase = false) 2. primary (pub): prepare a txn A. 3. sub: alter subscription set (two_phase = true) and wait for the logical slot to be synced to standby. 4. primary (pub): stop primary, promote the standby and let the subscriber use the promoted standby as publisher. 5. promoted standby (pub): COMMIT PREPARED A; 6. sub: the apply worker will report the following ERROR because it didn't receive the PREPARE. ERROR: prepared transaction with identifier "pg_gid_16387_752" does not exist I think the root cause of this issue is that the two_phase_at field of the slot, which indicates the LSN from which two-phase decoding is enabled (used to prevent duplicate data transmission for prepared transactions), is not synchronized to the standby server. In step 3, transaction A is not immediately replicated because it occurred before enabling two-phase decoding. Thus, the prepared transaction should only be replicated after decoding the final COMMIT PREPARED, as referenced in ReorderBufferFinishPrepared(). However, due to the invalid two_phase_at on the standby, the prepared transaction fails to send at that time. This problem arises after the support for altering the two-phase option (1462aad). Previously, two-phase was only enabled during slot creation, which wait for all prepared transactions to finish (via ... -> SnapBuildWaitSnapshot) before reaching a consistent state, so the bug didn't exist. To address the issue, I propose synchronizing the two_phase_at field to the standby server, as implemented in the attached patches. As mentioned earlier,this bug exists only for PG18 so we do not need to back patch. v1-0001: Tap test to reproduce the issue I place this patch as the first one so that reviewers can run it independently to reproduce the issue. Once the problem is thoroughly understood and the fix proves stable, the patches can be integrated. v1-0002: Display two_phase_at in the pg_replication_slots view v1-0003: Sync the two_phase_at field of a replication slot to the standby An alternative approach might be modifying ALTER_REPLICATION_SLOT to wait for all prepared transactions to commit when enabling two-phase. However, this appears inelegant and less user friendly. Best Regards, Hou zj
Commits
-
Don't retreat slot's confirmed_flush LSN.
- ad5eaf390c58 18.0 landed
- 7318f241d29c 17.6 landed
- c0f51fde534d 16.10 landed
- 9d1a62359606 15.14 landed
- e68459489c20 14.19 landed
- e323d9df002d 13.22 landed
-
Fix assertion failure during decoding from synced slots.
- 3ff2a1f0c9e8 18.0 landed
-
Fix xmin advancement during fast_forward decoding.
- d65485b02b1f 13.21 landed
- aaf9e95e8764 18.0 landed
- 36148b22ee09 17.5 landed
- 21a7caeeb948 16.9 landed
- f6429bd7db5e 15.13 landed
- 1f63b3626a52 14.18 landed
-
Fix slot synchronization for two_phase enabled slots.
- 4868c96bc8c6 18.0 landed
-
Again match pg_user_mappings to information_schema.user_mapping_options.
- b6e39ca92eee 9.4.13 cited