Detect mismatched subscription and replication slot properties
Hi hackers,
This is a follow-up to the discussion at [1]/messages/by-id/CAA4eK1JuaorGxKuRiF+Ls7MJyJKGid5b=gz95KMm-KGFgo1=+g@mail.gmail.com. The issue raised was that, when the
subscription's two phase state is already ENABLED, changing slot_name together
with two_phase = true can succeed even though the replacement slot has two_phase
= false.
Although, the possibility of such a mismatch is documented [2]https://www.postgresql.org/docs/devel/sql-altersubscription.html, it does not sound
ideal.
The same issue exists for both two_phase and failover. The consequences are real:
a mismatch can prevent replication from resuming after promotion or cause incorrect
two phase transaction handling, potentially resulting in duplicate data.
I initially thought about validating the properties during ALTER SUBSCRIPTION,
but the slot selected by the apply worker, or its properties, can change
independently of that command. Such a check could therefore already be stale when
the slot is used.
I wonder if it wouldn't make more sense to validate both properties when the main
apply worker starts replication? At that point the actual publisher and slot are
known. Ideally, the publisher would perform the validation after acquiring the
slot and before decoding starts. This would only validate the properties, not
modify them.
For failover, the value should match exactly.
For two_phase, the expected value depends on subtwophasestate:
• DISABLED: the slot must have two_phase = false.
• PENDING, before the tables are ready: it must remain false.
• PENDING, once the tables are ready: either value can be accepted before enabling
it and recording the state as ENABLED. This also makes the transition safe if the
worker previously exited between those two operations.
• ENABLED: the slot must have two_phase = true.
A persistent mismatch would cause the apply worker to fail each time it is restarted,
so we would also need to consider disable_on_error, apply_error_count, and the
resulting log messages. We would also need to consider how to handle a subscriber
connecting to an older publisher.
A related approach was discussed in [3]/messages/by-id/CAA4eK1Jd9dk=5POTKM9p4EyYqYzLXe-AnLzHrUELjzZScLz7mw@mail.gmail.com, where Hou-san proposed updating the
slot's failover property at each apply worker startup. What I have in mind here
is different: only report an error, without modifying the slot properties.
I'm not sharing any patch at this stage, just checking if this approach makes
sense?
[1]: /messages/by-id/CAA4eK1JuaorGxKuRiF+Ls7MJyJKGid5b=gz95KMm-KGFgo1=+g@mail.gmail.com
[2]: https://www.postgresql.org/docs/devel/sql-altersubscription.html
[3]: /messages/by-id/CAA4eK1Jd9dk=5POTKM9p4EyYqYzLXe-AnLzHrUELjzZScLz7mw@mail.gmail.com
Regards,
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
On Tue, Nov 10, 2026 at 7:44 PM Bertrand Drouvot
<bertranddrouvot.pg@gmail.com> wrote:
Hi hackers,
This is a follow-up to the discussion at [1]. The issue raised was that, when the
subscription's two phase state is already ENABLED, changing slot_name together
with two_phase = true can succeed even though the replacement slot has two_phase
= false.Although, the possibility of such a mismatch is documented [2], it does not sound
ideal.The same issue exists for both two_phase and failover. The consequences are real:
a mismatch can prevent replication from resuming after promotion or cause incorrect
two phase transaction handling, potentially resulting in duplicate data.I initially thought about validating the properties during ALTER SUBSCRIPTION,
but the slot selected by the apply worker, or its properties, can change
independently of that command. Such a check could therefore already be stale when
the slot is used.I wonder if it wouldn't make more sense to validate both properties when the main
apply worker starts replication? At that point the actual publisher and slot are
known. Ideally, the publisher would perform the validation after acquiring the
slot and before decoding starts. This would only validate the properties, not
modify them.For failover, the value should match exactly.
For two_phase, the expected value depends on subtwophasestate:
• DISABLED: the slot must have two_phase = false.
• PENDING, before the tables are ready: it must remain false.
• PENDING, once the tables are ready: either value can be accepted before enabling
it and recording the state as ENABLED. This also makes the transition safe if the
worker previously exited between those two operations.
• ENABLED: the slot must have two_phase = true.
Instead of PENDING state checks, how about checking mismatch in
begin_prepared kind of functions whether the subscription has reached
ENABLED state? I am asking this because if the slot is initially
created with two_phase = true and the subscription is in pending state
then the apply worker will start receiving prepared transactions and
will lead to problems described under "TWO_PHASE TRANSACTIONS" in
worker.c.
A persistent mismatch would cause the apply worker to fail each time it is restarted,
so we would also need to consider disable_on_error, apply_error_count, and the
resulting log messages. We would also need to consider how to handle a subscriber
connecting to an older publisher.A related approach was discussed in [3], where Hou-san proposed updating the
slot's failover property at each apply worker startup. What I have in mind here
is different: only report an error, without modifying the slot properties.I'm not sharing any patch at this stage, just checking if this approach makes
sense?
I think this makes sense to me. In general, we rely on users to set up
a logical-replication as desired, otherwise, it could easily lead to
non-recoverable situations (the data won't be replicated as expected
or could lead to ERRORs). For example, source and target schema must
match, appropriate privileges on the target table, the publications
should exist, etc. Having said that, it is good to tighten the
mismatch between pub-sub where possible without adding too much
complexity in the code.
If we want to proceed with this, then we should consider whether the
mismatch in 'failover' property should be an ERROR or a simple
'WARNING' as that won't immediately lead to data inconsistency or such
issues.
--
With Regards,
Amit Kapila.
Hi,
On Mon, Nov 16, 2026 at 03:46:25PM +0530, Amit Kapila wrote:
On Tue, Nov 10, 2026 at 7:44 PM Bertrand Drouvot
For failover, the value should match exactly.
For two_phase, the expected value depends on subtwophasestate:
• DISABLED: the slot must have two_phase = false.
• PENDING, before the tables are ready: it must remain false.
• PENDING, once the tables are ready: either value can be accepted before enabling
it and recording the state as ENABLED. This also makes the transition safe if the
worker previously exited between those two operations.
• ENABLED: the slot must have two_phase = true.Instead of PENDING state checks, how about checking mismatch in
begin_prepared kind of functions whether the subscription has reached
ENABLED state? I am asking this because if the slot is initially
created with two_phase = true and the subscription is in pending state
then the apply worker will start receiving prepared transactions and
will lead to problems described under "TWO_PHASE TRANSACTIONS" in
worker.c.
Thanks for looking at it!
I think that could be too late. With parallel streaming, a parallel apply worker
may already have executed the transaction's changes.
So, in the attached, I went with checking the slot property after acquiring the
slot, before decoding starts. A PENDING subscription requires two_phase to remain
disabled until table synchronization is complete. Once ready, START_REPLICATION
can enable it.
The patches therefore add slot_failover and slot_two_phase pgoutput options to
validate the current slot properties without changing them.
For older publishers, the patches preserve the existing behavior and leave the
user responsible for ensuring that the values match. I also considered querying
pg_replication_slots before START_REPLICATION. That would provide useful best
effort validation, but the slot could be changed or recreated between the query
and its acquisition. I decided not to include it. What do you think?
If we want to proceed with this, then we should consider whether the
mismatch in 'failover' property should be an ERROR or a simple
'WARNING' as that won't immediately lead to data inconsistency or such
issues.
Yeah, a failover mismatch does not have the same immediate consequences as a
two_phase mismatch, but I think an ERROR is still preferable. For example, it can
prevent replication from resuming after promotion.
Also, since the documentation requires the values to match, I don't think we
should let replication continue with a mismatched configuration.
Regards,
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
Hi,
On Wed, Nov18, 2026 at 10:15 PM Bertrand Drouvot wrote:
On Mon, Nov 16, 2026 at 03:46:25PM +0530, Amit Kapila wrote:
On Tue, Nov 10, 2026 at 7:44 PM Bertrand Drouvot
For failover, the value should match exactly.
For two_phase, the expected value depends on subtwophasestate:
• DISABLED: the slot must have two_phase = false.
• PENDING, before the tables are ready: it must remain false.
• PENDING, once the tables are ready: either value can be accepted before enabling
it and recording the state as ENABLED. This also makes the
transition safe if the worker previously exited between those two
operations.
• ENABLED: the slot must have two_phase = true.Instead of PENDING state checks, how about checking mismatch in
begin_prepared kind of functions whether the subscription has
reached ENABLED state? I am asking this because if the slot is
initially created with two_phase = true and the subscription is in
pending state then the apply worker will start receiving prepared
transactions and will lead to problems described under "TWO_PHASE
TRANSACTIONS" in worker.c.Thanks for looking at it!
I think that could be too late. With parallel streaming, a parallel
apply worker may already have executed the transaction's changes.
IIUC, it wouldn't be too late. Even if the changes are already being
executed, the transaction won't be prepared if we catch the mismatch in
apply_handle_prepare (and related functions). The user can still recover
by disabling two_phase on the slot, and the transaction will then be
replicated again as a normal transaction once COMMIT PREPARED is
executed on the publisher.
Also, catching this in the apply_handle_* functions still protects cases
where the publisher is on an older version that does not support the new
protocol option. It can also serve as a useful safeguard against
non-standard servers that might accidentally send unexpected prepared
transactions (similar checks were added in fe6a20c), and we already have
related checks that disallow prepare messages in the tablesync worker.
So adding a runtime check seems like an independent win to me.
Or do you have scenarios in mind that the runtime check cannot handle
that I might be missing (for cases when two_phase is disabled on sub but
enabled on slot) ?
I am attaching a draft patch that adds runtime check with a test to show
that reporting an ERROR at that point is recoverable, just for reference
and discussion.
--
Best Regards,
Zhijie Hou
Attachments:
nocfbot-0001-Reject-two-phase-transactions-when-two_phase-is-n.patchtext/plain; charset=UTF-8; name=nocfbot-0001-Reject-two-phase-transactions-when-two_phase-is-n.patchDownload+94-12
On Thu, Nov 19, 2026 at 12:02 PM Zhijie Hou <houzhijie22@gmail.com> wrote:
On Wed, Nov18, 2026 at 10:15 PM Bertrand Drouvot wrote:
On Mon, Nov 16, 2026 at 03:46:25PM +0530, Amit Kapila wrote:
On Tue, Nov 10, 2026 at 7:44 PM Bertrand Drouvot
For failover, the value should match exactly.
For two_phase, the expected value depends on subtwophasestate:
• DISABLED: the slot must have two_phase = false.
• PENDING, before the tables are ready: it must remain false.
• PENDING, once the tables are ready: either value can be accepted before enabling
it and recording the state as ENABLED. This also makes the
transition safe if the worker previously exited between those two
operations.
• ENABLED: the slot must have two_phase = true.Instead of PENDING state checks, how about checking mismatch in
begin_prepared kind of functions whether the subscription has
reached ENABLED state? I am asking this because if the slot is
initially created with two_phase = true and the subscription is in
pending state then the apply worker will start receiving prepared
transactions and will lead to problems described under "TWO_PHASE
TRANSACTIONS" in worker.c.Thanks for looking at it!
I think that could be too late. With parallel streaming, a parallel
apply worker may already have executed the transaction's changes.IIUC, it wouldn't be too late. Even if the changes are already being
executed, the transaction won't be prepared if we catch the mismatch in
apply_handle_prepare (and related functions).
Right, I also thought so. Also, I don't like adding new options for
detecting these ERROR cases, the main idea is that such differences
should not lead to inconsistencies. BTW, I don't know what to do for a
failover property if we go with this idea? Do we need to perform a SQL
query to get the required slot properties and then compare it with
current subscription properties?
--
With Regards,
Amit Kapila.
Hi,
On Thu, Nov 19, 2026 at 01:09:42PM +0530, Amit Kapila wrote:
On Thu, Nov 19, 2026 at 12:02 PM Zhijie Hou <houzhijie22@gmail.com> wrote:
I think that could be too late. With parallel streaming, a parallel
apply worker may already have executed the transaction's changes.IIUC, it wouldn't be too late. Even if the changes are already being
executed, the transaction won't be prepared if we catch the mismatch in
apply_handle_prepare (and related functions).Right, I also thought so.
I agree that the transaction itself will not be prepared and its transactional
changes will be rolled back. My concern is that, with parallel streaming,
subscriber nontransactional effects may already have occurred before
STREAM PREPARE (for example, subscriber having a default column calling nextval()).
This can also happen with other apply errors, so it is not new. However, this
particular error is predictable before decoding starts, and checking there
avoids entering that path unnecessarily.
Maybe I'm being overly concerned about this though?
Also, I don't like adding new options for
detecting these ERROR cases,
Yeah, I see the concern. The reason for passing the expected values to pgoutput
is to let the publisher validate the actual slot after acquiring it and before
decoding starts.
BTW, I don't know what to do for a
failover property if we go with this idea? Do we need to perform a SQL
query to get the required slot properties and then compare it with
current subscription properties?
I considered that too, as mentioned up-thread. My concern is that the slot could
be changed or recreated between the query and its acquisition by START_REPLICATION.
Regards,
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
On Thu, Nov 19, 2026 at 1:35 PM Bertrand Drouvot
<bertranddrouvot.pg@gmail.com> wrote:
On Thu, Nov 19, 2026 at 01:09:42PM +0530, Amit Kapila wrote:
I agree that the transaction itself will not be prepared and its transactional
changes will be rolled back. My concern is that, with parallel streaming,
subscriber nontransactional effects may already have occurred before
STREAM PREPARE (for example, subscriber having a default column calling nextval()).This can also happen with other apply errors, so it is not new. However, this
particular error is predictable before decoding starts, and checking there
avoids entering that path unnecessarily.Maybe I'm being overly concerned about this though?
I feel so. I think it would be good to have such checks with no or
minimal cost in terms of additional infrastructure as we expect users
to set up replication appropriately, otherwise it can fail in many
ways.
BTW, I don't know what to do for a
failover property if we go with this idea? Do we need to perform a SQL
query to get the required slot properties and then compare it with
current subscription properties?I considered that too, as mentioned up-thread. My concern is that the slot could
be changed or recreated between the query and its acquisition by START_REPLICATION.
Possible, but again let's try to find some way if possible to catch
differences in publisher and subscriber without additional options or
a totally new infrastructure, otherwise, we can rely on the
documentation as we do now. OTOH, if we see user complaints in this or
related differences between pub-sub configuration misbehaving then we
can try to build something better to catch the differences.
--
With Regards,
Amit Kapila.