Detect mismatched subscription and replication slot properties

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.

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?

[1]: https://postgr.es/m/CAA4eK1JuaorGxKuRiF%2BLs7MJyJKGid5b%3Dgz95KMm-KGFgo1%3D%2Bg%40mail.gmail.com
[2]: https://www.postgresql.org/docs/devel/sql-altersubscription.html
[3]: https://postgr.es/m/CAA4eK1Jd9dk=5POTKM9p4EyYqYzLXe-AnLzHrUELjzZScLz7mw@mail.gmail.com

Regards,

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