On Thu, Jun 19, 2025 at 6:00 PM Bertrand Drouvot <bertranddrouvot...@gmail.com> wrote: > > Hi, > > On Wed, Jun 18, 2025 at 03:22:59PM +0530, shveta malik wrote: > > On Wed, Jun 18, 2025 at 2:39 PM Bertrand Drouvot > > <bertranddrouvot...@gmail.com> wrote: > > > > > > IIUC the idea is to "just" increase WAL level to 'logical' so that one > > > could then > > > be allowed to make use of logical decoding from the standby. The primary > > > goal > > > of logical decoding from standby is to move some load from the primay to > > > the standby i.e we don't expect/want the logical slot to be used on the > > > primary. > > > > > > So what about making sure that if a logical slot is created with > > > immediately_reserve > > > set to false then no one can use it? (That would ensure that WAL > > > reservation > > > will not happen). > > > > > > > Another concern is ensuring that users actually create this slot. If > > there is already an active subscription subscribed to the primary, the > > effective_wal_level will be set to logical already, allowing logical > > decoding on the standby to proceed without issue. In such a case, the > > user might not bother to create additional slots (same as problem > > described in [1])) and later may unintentionally end up making standby > > slots unusable. Any ideas on how to ensure it? > > > > That said, we might also want to create another parameter name (than > > > immediately_reserve) to better reflect this behavior (if we move that > > > way). > > > > > > Thoughts? > > > > Or we could avoid exposing control of immediately_reserve to the user > > altogether? Instead, we reserve a specific slot name and ensure that > > it never reserves WAL in the future by preventing it from being > > consumed under any circumstances (as you suggested). > > I wonder if a way to address the concerns that we shared above is to use a > mixed approach like: > > - Forget the immediately_reserve idea > - If a user creates a logical slot then we automatically switch to wal_level = > logical (if not already done): I think that's a nice user experience > - *and* provide a new API pg_activate_logical_decoding(), if the user has no > need to create a logical slot on the primary (wants to use the standby to > offload > all the logical decoding) > > So if the user also uses a logical slot on the primary (for real..) then there > is no need to launch pg_activate_logical_decoding(), until....: > > The user decides to drop the logical slot on the primary, and then: > > - If the slot is not the last logical slot, that's fine, drop it > - If the slot is the last logical one AND the user did not set a new flag > "wal_level_action" to "say preserve" or "force downgrade" (in the drop > command) > then the drop fails with an informative error message. > > That way: > > - pg_activate_logical_decoding() is needed only if there is not already a > logical > slot on the primary > - the drop requires the user to think twice if this is the last logical slot > - we don't ask the user to create a logical slot if he does not want to use it > on the primary > > Thoughts?
If there is no logical slot on the primary, how can the user disable logical decoding that has been enabled via pg_activate_logical_decoding()? Given the discussion so far, it seems we might want to have a safeguard to prevent the effective_wal_level from being dropped to 'replica' if the last logical slot is accidentally dropped. Another idea we can consider is that we automatically increase effective_wal_level to 'logical' upon the logical slot creation but don't automatically decrease it when dropping the last slot. To decrease the effective_wal_level to 'replica', users would need to do that explicitly for example using a SQL function, pg_disable_logical_decoding(). We might want to have a GUC parameter for users to turn on/off this automatic behavior. Regards, -- Masahiko Sawada Amazon Web Services: https://aws.amazon.com
