Avoid resetting subscription two_phase state to pending when already enabled

Started by Zhijie Hou24 days ago18 messageshackers
Jump to latest
#1Zhijie Hou
houzhijie22@gmail.com

Hi,

While reviewing another logical replication thread, I noticed an issue
with altering a subscription's two_phase option.

In AlterSubscription(), executing ALTER SUBSCRIPTION ... SET (two_phase
= true) unconditionally set subtwophasestate to
LOGICALREP_TWOPHASE_STATE_PENDING, even when two_phase was already in
LOGICALREP_TWOPHASE_STATE_ENABLED state.

Because two_phase remains enabled on the publisher's replication slot
and prepared transactions may already exist on the subscriber,
overwriting the state to PENDING broke checks that rely on
subtwophasestate being ENABLED:

1. A subsequent ALTER SUBSCRIPTION ... SET (two_phase = false) skipped
the LookupGXactBySubid() check and disabled two_phase while prepared
transactions still existed on the subscriber. When those transactions
were later committed on the publisher, the publisher re-sent all of
their changes as regular transactions instead of COMMIT PREPARED,
leading to self-deadlocks, duplicate data, or orphaned prepared
transactions.

2. ALTER SUBSCRIPTION ... REFRESH PUBLICATION (and SET/ADD PUBLICATION)
with copy_data = true was allowed even though the publisher's slot still
had two_phase enabled, allowing prepared transactions on newly added
tables to be streamed during initial table synchronization and applied
as empty prepares.

I think we can fix this by not resetting subtwophasestate to PENDING
when two_phase is already in the ENABLED state, as shown in the attached
patch.

This issue has existed since PG 18, when altering the two_phase option
was introduced, so it should be back-patched down to that branch.

--
Best Regards,
Zhijie Hou

Attachments:

t254436_1
v1-0001-Avoid-resetting-subscription-two_phase-state-to-p.patchtext/plain; charset=UTF-8; name=v1-0001-Avoid-resetting-subscription-two_phase-state-to-p.patchDownload+23-7
#2shveta malik
shveta.malik@gmail.com
In reply to: Zhijie Hou (#1)
Re: Avoid resetting subscription two_phase state to pending when already enabled

On Thu, Oct 29, 2026 at 11:27 AM Zhijie Hou <houzhijie22@gmail.com> wrote:

Hi,

While reviewing another logical replication thread, I noticed an issue
with altering a subscription's two_phase option.

In AlterSubscription(), executing ALTER SUBSCRIPTION ... SET (two_phase
= true) unconditionally set subtwophasestate to
LOGICALREP_TWOPHASE_STATE_PENDING, even when two_phase was already in
LOGICALREP_TWOPHASE_STATE_ENABLED state.

Because two_phase remains enabled on the publisher's replication slot
and prepared transactions may already exist on the subscriber,
overwriting the state to PENDING broke checks that rely on
subtwophasestate being ENABLED:

1. A subsequent ALTER SUBSCRIPTION ... SET (two_phase = false) skipped
the LookupGXactBySubid() check and disabled two_phase while prepared
transactions still existed on the subscriber. When those transactions
were later committed on the publisher, the publisher re-sent all of
their changes as regular transactions instead of COMMIT PREPARED,
leading to self-deadlocks, duplicate data, or orphaned prepared
transactions.

2. ALTER SUBSCRIPTION ... REFRESH PUBLICATION (and SET/ADD PUBLICATION)
with copy_data = true was allowed even though the publisher's slot still
had two_phase enabled, allowing prepared transactions on newly added
tables to be streamed during initial table synchronization and applied
as empty prepares.

I think we can fix this by not resetting subtwophasestate to PENDING
when two_phase is already in the ENABLED state, as shown in the attached
patch.

This issue has existed since PG 18, when altering the two_phase option
was introduced, so it should be back-patched down to that branch.

The fix looks good. A few trivial things:

1)
+ if (!opts.twophase ||
+ sub->twophasestate != LOGICALREP_TWOPHASE_STATE_ENABLED)

I find this condition slightly difficult to follow. Would below be
more readable?

if (!(opts.twophase &&
sub->twophasestate == LOGICALREP_TWOPHASE_STATE_ENABLED))

This simply says: skip the write if the user asked for true and it's already 'e'

2)
+# Verify that setting two_phase to true when it is already enabled does not
+# overwrite subtwophasestate to 'p' (pending).
+$node_subscriber->safe_psql('postgres',
+ "ALTER SUBSCRIPTION tap_sub_copy SET (two_phase = true);");
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT subtwophasestate FROM pg_subscription WHERE subname = 'tap_sub_copy';"
+);
+is($result, qq(e), 'two-phase subscription option remains enabled');
+

This new test is added between the two steps of a test that verifies
disabling two-phase. The comment in previous test '# Alter
subscription two_phase to false' now seems misplaced and thus should
be changed/shifted.

thanks
Shveta

#3Zhijie Hou
houzhijie22@gmail.com
In reply to: shveta malik (#2)
Re: Avoid resetting subscription two_phase state to pending when already enabled

Hi,

On Thu, Oct 29, 2026 at 02:33 PM shveta malik wrote:

On Thu, Oct 29, 2026 at 11:27 AM Zhijie Hou <houzhijie22@gmail.com> wrote:

While reviewing another logical replication thread, I noticed an issue
with altering a subscription's two_phase option.

In AlterSubscription(), executing ALTER SUBSCRIPTION ... SET (two_phase
= true) unconditionally set subtwophasestate to
LOGICALREP_TWOPHASE_STATE_PENDING, even when two_phase was already in
LOGICALREP_TWOPHASE_STATE_ENABLED state.

Because two_phase remains enabled on the publisher's replication slot
and prepared transactions may already exist on the subscriber,
overwriting the state to PENDING broke checks that rely on
subtwophasestate being ENABLED:

1. A subsequent ALTER SUBSCRIPTION ... SET (two_phase = false) skipped
the LookupGXactBySubid() check and disabled two_phase while prepared
transactions still existed on the subscriber. When those transactions
were later committed on the publisher, the publisher re-sent all of
their changes as regular transactions instead of COMMIT PREPARED,
leading to self-deadlocks, duplicate data, or orphaned prepared
transactions.

2. ALTER SUBSCRIPTION ... REFRESH PUBLICATION (and SET/ADD PUBLICATION)
with copy_data = true was allowed even though the publisher's slot still
had two_phase enabled, allowing prepared transactions on newly added
tables to be streamed during initial table synchronization and applied
as empty prepares.

I think we can fix this by not resetting subtwophasestate to PENDING
when two_phase is already in the ENABLED state, as shown in the attached
patch.

This issue has existed since PG 18, when altering the two_phase option
was introduced, so it should be back-patched down to that branch.

The fix looks good. A few trivial things:

Thanks for reviewing.

1)
+ if (!opts.twophase ||
+ sub->twophasestate != LOGICALREP_TWOPHASE_STATE_ENABLED)

I find this condition slightly difficult to follow. Would below be
more readable?

if (!(opts.twophase &&
sub->twophasestate == LOGICALREP_TWOPHASE_STATE_ENABLED))

Changed as suggested.

This simply says: skip the write if the user asked for true and it's already 'e'

I did not change the comment as I perfer the current version.

2)
+# Verify that setting two_phase to true when it is already enabled does not
+# overwrite subtwophasestate to 'p' (pending).
+$node_subscriber->safe_psql('postgres',
+ "ALTER SUBSCRIPTION tap_sub_copy SET (two_phase = true);");
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT subtwophasestate FROM pg_subscription WHERE subname = 'tap_sub_copy';"
+);
+is($result, qq(e), 'two-phase subscription option remains enabled');
+

This new test is added between the two steps of a test that verifies
disabling two-phase. The comment in previous test '# Alter
subscription two_phase to false' now seems misplaced and thus should
be changed/shifted.

Agreed. I adjusted the test order to make it clear.

Here is the V2 patch.

--
Best Regards,
Zhijie Hou

Attachments:

t254436_3
v2-0001-Avoid-resetting-subscription-two_phase-state-to-p.patchtext/plain; charset=UTF-8; name=v2-0001-Avoid-resetting-subscription-two_phase-state-to-p.patchDownload+26-10
#4Hayato Kuroda (Fujitsu)
kuroda.hayato@fujitsu.com
In reply to: Zhijie Hou (#1)
RE: Avoid resetting subscription two_phase state to pending when already enabled

Hi,

While reviewing another logical replication thread, I noticed an issue
with altering a subscription's two_phase option.

Good catch.

I think we can fix this by not resetting subtwophasestate to PENDING
when two_phase is already in the ENABLED state, as shown in the attached
patch.

+1 the fix, but I have a quick confirmation.

Assuming that twophasestate is 'e' and the user runs ALTER SUBSCRIPTION SET
(two_phase = on). In this case all attributes are retained as-is but update_tuple
is set to true anyway - CatalogTupleUpdate() would update the tuple with the
completely the same tuple.

But I feel it's acceptable because 1) it rarely happens,
2) conflict_log_destination option has the same issue, and 3) fixing it may
need wider changes, like setting update_tuple in the if-statement, thought?

Best regards,
Hayato Kuroda
FUJITSU LIMITED

#5Zhijie Hou
houzhijie22@gmail.com
In reply to: Hayato Kuroda (Fujitsu) (#4)
Re: Avoid resetting subscription two_phase state to pending when already enabled

Hi,

On Thu, Oct 29, 2026 at 03:29 PM "Hayato Kuroda (Fujitsu)" wrote:

While reviewing another logical replication thread, I noticed an issue
with altering a subscription's two_phase option.

Good catch.

I think we can fix this by not resetting subtwophasestate to PENDING
when two_phase is already in the ENABLED state, as shown in the attached
patch.

+1 the fix, but I have a quick confirmation.

Assuming that twophasestate is 'e' and the user runs ALTER SUBSCRIPTION SET
(two_phase = on). In this case all attributes are retained as-is but update_tuple
is set to true anyway - CatalogTupleUpdate() would update the tuple with the
completely the same tuple.

But I feel it's acceptable because 1) it rarely happens,
2) conflict_log_destination option has the same issue, and 3) fixing it may
need wider changes, like setting update_tuple in the if-statement, thought?

Right. This is the same as all (or most) subscription options and even
other object options like publication options have the same behavior,
since we don't compare the new value with the old one in most cases. So
I think that's an independent topic.

--
Best Regards,
Zhijie Hou

#6Hayato Kuroda (Fujitsu)
kuroda.hayato@fujitsu.com
In reply to: Zhijie Hou (#5)
RE: Avoid resetting subscription two_phase state to pending when already enabled

Hi,

Right. This is the same as all (or most) subscription options and even
other object options like publication options have the same behavior,
since we don't compare the new value with the old one in most cases. So
I think that's an independent topic.

Make sense. I checked the v2 patch and LGTM.

Best regards,
Hayato Kuroda
FUJITSU LIMITED

#7shveta malik
shveta.malik@gmail.com
In reply to: Zhijie Hou (#3)
Re: Avoid resetting subscription two_phase state to pending when already enabled

On Thu, Oct 29, 2026 at 12:34 PM Zhijie Hou <houzhijie22@gmail.com> wrote:

Hi,

On Thu, Oct 29, 2026 at 02:33 PM shveta malik wrote:

On Thu, Oct 29, 2026 at 11:27 AM Zhijie Hou <houzhijie22@gmail.com> wrote:

While reviewing another logical replication thread, I noticed an issue
with altering a subscription's two_phase option.

In AlterSubscription(), executing ALTER SUBSCRIPTION ... SET (two_phase
= true) unconditionally set subtwophasestate to
LOGICALREP_TWOPHASE_STATE_PENDING, even when two_phase was already in
LOGICALREP_TWOPHASE_STATE_ENABLED state.

Because two_phase remains enabled on the publisher's replication slot
and prepared transactions may already exist on the subscriber,
overwriting the state to PENDING broke checks that rely on
subtwophasestate being ENABLED:

1. A subsequent ALTER SUBSCRIPTION ... SET (two_phase = false) skipped
the LookupGXactBySubid() check and disabled two_phase while prepared
transactions still existed on the subscriber. When those transactions
were later committed on the publisher, the publisher re-sent all of
their changes as regular transactions instead of COMMIT PREPARED,
leading to self-deadlocks, duplicate data, or orphaned prepared
transactions.

2. ALTER SUBSCRIPTION ... REFRESH PUBLICATION (and SET/ADD PUBLICATION)
with copy_data = true was allowed even though the publisher's slot still
had two_phase enabled, allowing prepared transactions on newly added
tables to be streamed during initial table synchronization and applied
as empty prepares.

I think we can fix this by not resetting subtwophasestate to PENDING
when two_phase is already in the ENABLED state, as shown in the attached
patch.

This issue has existed since PG 18, when altering the two_phase option
was introduced, so it should be back-patched down to that branch.

The fix looks good. A few trivial things:

Thanks for reviewing.

1)
+ if (!opts.twophase ||
+ sub->twophasestate != LOGICALREP_TWOPHASE_STATE_ENABLED)

I find this condition slightly difficult to follow. Would below be
more readable?

if (!(opts.twophase &&
sub->twophasestate == LOGICALREP_TWOPHASE_STATE_ENABLED))

Changed as suggested.

This simply says: skip the write if the user asked for true and it's already 'e'

I did not change the comment as I perfer the current version.

Sure. I did not suggest changing the code-comment, I was just
describing the simplicity of the change.

2)
+# Verify that setting two_phase to true when it is already enabled does not
+# overwrite subtwophasestate to 'p' (pending).
+$node_subscriber->safe_psql('postgres',
+ "ALTER SUBSCRIPTION tap_sub_copy SET (two_phase = true);");
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT subtwophasestate FROM pg_subscription WHERE subname = 'tap_sub_copy';"
+);
+is($result, qq(e), 'two-phase subscription option remains enabled');
+

This new test is added between the two steps of a test that verifies
disabling two-phase. The comment in previous test '# Alter
subscription two_phase to false' now seems misplaced and thus should
be changed/shifted.

Agreed. I adjusted the test order to make it clear.

Here is the V2 patch.

thanks, v2 LGTM.

#8Bertrand Drouvot
bertranddrouvot.pg@gmail.com
In reply to: Zhijie Hou (#3)
Re: Avoid resetting subscription two_phase state to pending when already enabled

Hi,

On Thu, Oct 29, 2026 at 03:04:12PM +0800, Zhijie Hou wrote:

Agreed. I adjusted the test order to make it clear.

Here is the V2 patch.

Thanks for the patch!

+         /*
+          * Change system catalog accordingly, but do not reset the
+          * state to PENDING if two_phase is already enabled.
+          */
+          if (!(opts.twophase &&
+                sub->twophasestate == LOGICALREP_TWOPHASE_STATE_ENABLED))

The new condition also preserves 'e' when slot_name changes. In that case, a
replacement slot with two_phase=false remains false because the worker does not
go through the PENDING state.

I reproduced this with:

ALTER SUBSCRIPTION sub SET (slot_name = 'replacement', two_phase = true);

Should we preserve 'e' only when the slot name is unchanged?

Regards,

--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

#9vignesh C
vignesh21@gmail.com
In reply to: Bertrand Drouvot (#8)
Re: Avoid resetting subscription two_phase state to pending when already enabled

On Thu, 29 Oct 2026 at 14:41, Bertrand Drouvot
<bertranddrouvot.pg@gmail.com> wrote:

Hi,

On Thu, Oct 29, 2026 at 03:04:12PM +0800, Zhijie Hou wrote:

Agreed. I adjusted the test order to make it clear.

Here is the V2 patch.

Thanks for the patch!

+         /*
+          * Change system catalog accordingly, but do not reset the
+          * state to PENDING if two_phase is already enabled.
+          */
+          if (!(opts.twophase &&
+                sub->twophasestate == LOGICALREP_TWOPHASE_STATE_ENABLED))

The new condition also preserves 'e' when slot_name changes. In that case, a
replacement slot with two_phase=false remains false because the worker does not
go through the PENDING state.

I reproduced this with:

ALTER SUBSCRIPTION sub SET (slot_name = 'replacement', two_phase = true);

Should we preserve 'e' only when the slot name is unchanged?

There is the following documentation mentioning that slots must be
created with the same parameters of the subscription at [1]https://www.postgresql.org/docs/devel/sql-altersubscription.html.
When altering the slot_name, the failover and two_phase property
values of the named slot may differ from the counterpart failover and
two_phase parameters specified in the subscription. When creating the
slot, ensure the slot properties failover and two_phase match their
counterpart parameters of the subscription. Otherwise, the slot on the
publisher may behave differently from what these subscription options
say: for example, the slot on the publisher could either be synced to
the standbys even when the subscription's failover option is disabled
or could be disabled for sync even when the subscription's failover
option is enabled.

A similar thing can happen with SET (slot_name = 'other_slot') i.e,
without specifying two_phase option.

I'm not sure if this should be handled here as it is documented already.

[1]: https://www.postgresql.org/docs/devel/sql-altersubscription.html

Regards,
Vignesh

#10Bertrand Drouvot
bertranddrouvot.pg@gmail.com
In reply to: vignesh C (#9)
Re: Avoid resetting subscription two_phase state to pending when already enabled

Hi,

On Thu, Oct 29, 2026 at 03:52:46PM +0530, vignesh C wrote:

There is the following documentation mentioning that slots must be
created with the same parameters of the subscription at [1].
When altering the slot_name, the failover and two_phase property
values of the named slot may differ from the counterpart failover and
two_phase parameters specified in the subscription. When creating the
slot, ensure the slot properties failover and two_phase match their
counterpart parameters of the subscription. Otherwise, the slot on the
publisher may behave differently from what these subscription options
say: for example, the slot on the publisher could either be synced to
the standbys even when the subscription's failover option is disabled
or could be disabled for sync even when the subscription's failover
option is enabled.

I'm not sure if this should be handled here as it is documented already.

I agree about the documentation, but the patch introduces a regression: without
the patch, two_phase is enabled on the replacement slot. With the patch, it remains
disabled.

It might be worth fixing, as preserving 'e' only when slot_name is unchanged
should be simple enough.

Regards,

--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

#11Zhijie Hou
houzhijie22@gmail.com
In reply to: Bertrand Drouvot (#10)
Re: Avoid resetting subscription two_phase state to pending when already enabled

Hi,

On Thu, Oct 29, 2026 at 06:41 PM Bertrand Drouvot wrote:

On Thu, Oct 29, 2026 at 03:52:46PM +0530, vignesh C wrote:

There is the following documentation mentioning that slots must be
created with the same parameters of the subscription at [1].
When altering the slot_name, the failover and two_phase property
values of the named slot may differ from the counterpart failover and
two_phase parameters specified in the subscription. When creating the
slot, ensure the slot properties failover and two_phase match their
counterpart parameters of the subscription. Otherwise, the slot on the
publisher may behave differently from what these subscription options
say: for example, the slot on the publisher could either be synced to
the standbys even when the subscription's failover option is disabled
or could be disabled for sync even when the subscription's failover
option is enabled.

I'm not sure if this should be handled here as it is documented already.

I agree about the documentation, but the patch introduces a regression: without
the patch, two_phase is enabled on the replacement slot. With the patch, it remains
disabled.

Like Vignesh, I think the documentation already requires users to create
the replacement slot with matching properties when altering slot_name,
so we don't promise that setting two_phase = true will update the
replacement slot.

It might be worth fixing, as preserving 'e' only when slot_name is unchanged
should be simple enough.

I don't think handling that here would be simple. Resetting ENABLED to
PENDING when slot_name is changed would re-introduce the same bugs that
this patch aims to fix: prepared transactions from the previous slot may
still exist on the subscriber, and if the replacement slot already has
two_phase enabled on the publisher, prepared transactions could again be
streamed concurrently with table synchronization. Avoiding that would
require checking prepared transactions and the remote slot's status
during the change, which would complicate the fix.

--
Best Regards,
Zhijie Hou

#12vignesh C
vignesh21@gmail.com
In reply to: Zhijie Hou (#3)
Re: Avoid resetting subscription two_phase state to pending when already enabled

On Thu, 29 Oct 2026 at 12:34, Zhijie Hou <houzhijie22@gmail.com> wrote:

Here is the V2 patch.

I had a look at the patch, the patch looks good to me.

Regards,
Vignesh

#13Peter Smith
smithpb2250@gmail.com
In reply to: Zhijie Hou (#3)
Re: Avoid resetting subscription two_phase state to pending when already enabled

IMO, the nested checking of opts.twophase in the ternary makes the
patch code unnecessarily hard to follow across combinations.

My suggestion is to implement it using more verbose, but easier-to-read code:

/* Change system catalog accordingly */
if (sub->twophasestate == LOGICALREP_TWOPHASE_STATE_DISABLED)
{
if (opts.twophase)
{
/* enable two_phase */
values[Anum_pg_subscription_subtwophasestate - 1] =
CharGetDatum(LOGICALREP_TWOPHASE_STATE_PENDING);
replaces[Anum_pg_subscription_subtwophasestate - 1] = true;
}
}
else if (!opts.twophase)
{
/* disable two_phase */
values[Anum_pg_subscription_subtwophasestate - 1] =
CharGetDatum(LOGICALREP_TWOPHASE_STATE_DISABLED);
replaces[Anum_pg_subscription_subtwophasestate - 1] = true;
}

~

This way also avoids redundant replacements like 'd' to 'd', 'p' to 'p', etc.

======
Kind Regards,
Peter Smith.
Fujitsu Australia

#14Zhijie Hou
houzhijie22@gmail.com
In reply to: Peter Smith (#13)
Re: Avoid resetting subscription two_phase state to pending when already enabled

Hi,

On Mon, Nov 2, 2026 at 10:04 AM Peter Smith wrote:

IMO, the nested checking of opts.twophase in the ternary makes the
patch code unnecessarily hard to follow across combinations.

My suggestion is to implement it using more verbose, but easier-to-read code:

/* Change system catalog accordingly */
if (sub->twophasestate == LOGICALREP_TWOPHASE_STATE_DISABLED)
{
if (opts.twophase)
{
/* enable two_phase */
values[Anum_pg_subscription_subtwophasestate - 1] =
CharGetDatum(LOGICALREP_TWOPHASE_STATE_PENDING);
replaces[Anum_pg_subscription_subtwophasestate - 1] = true;
}
}
else if (!opts.twophase)
{
/* disable two_phase */
values[Anum_pg_subscription_subtwophasestate - 1] =
CharGetDatum(LOGICALREP_TWOPHASE_STATE_DISABLED);
replaces[Anum_pg_subscription_subtwophasestate - 1] = true;
}

~

Thank for the suggestion, but I personally don't prefer the longer,
nested style, as I don't find it clearly easier to read. So I would keep
the current version for now and see what the committer thinks.

--
Best Regards,
Zhijie Hou

#15Amit Kapila
amit.kapila16@gmail.com
In reply to: Zhijie Hou (#11)
Re: Avoid resetting subscription two_phase state to pending when already enabled

On Thu, Oct 29, 2026 at 8:44 PM Zhijie Hou <houzhijie22@gmail.com> wrote:

On Thu, Oct 29, 2026 at 06:41 PM Bertrand Drouvot wrote:

On Thu, Oct 29, 2026 at 03:52:46PM +0530, vignesh C wrote:

There is the following documentation mentioning that slots must be
created with the same parameters of the subscription at [1].
When altering the slot_name, the failover and two_phase property
values of the named slot may differ from the counterpart failover and
two_phase parameters specified in the subscription. When creating the
slot, ensure the slot properties failover and two_phase match their
counterpart parameters of the subscription. Otherwise, the slot on the
publisher may behave differently from what these subscription options
say: for example, the slot on the publisher could either be synced to
the standbys even when the subscription's failover option is disabled
or could be disabled for sync even when the subscription's failover
option is enabled.

I'm not sure if this should be handled here as it is documented already.

I agree about the documentation, but the patch introduces a regression: without
the patch, two_phase is enabled on the replacement slot. With the patch, it remains
disabled.

Like Vignesh, I think the documentation already requires users to create
the replacement slot with matching properties when altering slot_name,
so we don't promise that setting two_phase = true will update the
replacement slot.

It might be worth fixing, as preserving 'e' only when slot_name is unchanged
should be simple enough.

I don't think handling that here would be simple. Resetting ENABLED to
PENDING when slot_name is changed would re-introduce the same bugs that
this patch aims to fix: prepared transactions from the previous slot may
still exist on the subscriber, and if the replacement slot already has
two_phase enabled on the publisher, prepared transactions could again be
streamed concurrently with table synchronization. Avoiding that would
require checking prepared transactions and the remote slot's status
during the change, which would complicate the fix.

I agree that we don't need to preserve the old behavior as it will
open up other bugs and we already document that such slot's properties
should match subscription's options. But I feel we should add that
change in behavior for new slot's in the commit message. Other than
that the latest patch in this thread looks good to me.

--
With Regards,
Amit Kapila.

#16Zhijie Hou
houzhijie22@gmail.com
In reply to: Amit Kapila (#15)
Re: Avoid resetting subscription two_phase state to pending when already enabled

Hi,

On Wed, Nov 4, 2026 at 6:01 PM Amit Kapila <amit.kapila16@gmail.com> wrote:

On Thu, Oct 29, 2026 at 8:44 PM Zhijie Hou <houzhijie22@gmail.com> wrote:

On Thu, Oct 29, 2026 at 06:41 PM Bertrand Drouvot wrote:

It might be worth fixing, as preserving 'e' only when slot_name is unchanged
should be simple enough.

I don't think handling that here would be simple. Resetting ENABLED to
PENDING when slot_name is changed would re-introduce the same bugs that
this patch aims to fix: prepared transactions from the previous slot may
still exist on the subscriber, and if the replacement slot already has
two_phase enabled on the publisher, prepared transactions could again be
streamed concurrently with table synchronization. Avoiding that would
require checking prepared transactions and the remote slot's status
during the change, which would complicate the fix.

I agree that we don't need to preserve the old behavior as it will
open up other bugs and we already document that such slot's properties
should match subscription's options. But I feel we should add that
change in behavior for new slot's in the commit message. Other than
that the latest patch in this thread looks good to me.

I added a section describing this behavior change, please see the
attached V3 patch.

I confirmed the patch applies cleanly to all affected branches (PG 18
through HEAD),
and the tests pass on all of them.

--
Best Regards,
Zhijie Hou

Attachments:

t254436_16
v3-0001-Avoid-resetting-subscription-two_phase-state-to-p.patchapplication/octet-stream; name=v3-0001-Avoid-resetting-subscription-two_phase-state-to-p.patchDownload+26-10
#17Bertrand Drouvot
bertranddrouvot.pg@gmail.com
In reply to: Amit Kapila (#15)
Re: Avoid resetting subscription two_phase state to pending when already enabled

Hi,

On Wed, Nov 04, 2026 at 03:31:19PM +0530, Amit Kapila wrote:

But I feel we should add that
change in behavior for new slot's in the commit message.

+1. I think this behavior change should also be mentioned in the release notes
for the affected branches. Is that also the intention behind adding it to the
commit message?

Regards,

--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

#18Amit Kapila
amit.kapila16@gmail.com
In reply to: Bertrand Drouvot (#17)
Re: Avoid resetting subscription two_phase state to pending when already enabled

On Wed, Nov 4, 2026 at 6:54 PM Bertrand Drouvot
<bertranddrouvot.pg@gmail.com> wrote:

On Wed, Nov 04, 2026 at 03:31:19PM +0530, Amit Kapila wrote:

But I feel we should add that
change in behavior for new slot's in the commit message.

+1. I think this behavior change should also be mentioned in the release notes
for the affected branches. Is that also the intention behind adding it to the
commit message?

Yes, I have pushed the patch. Thanks.

--
With Regards,
Amit Kapila.