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-anlzhrueljzzsclz...@mail.gmail.com Regards, -- Bertrand Drouvot PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com
