Idempotent CREATE SUBSCRIPTION and DROP SUBSCRIPTION pair

[Shaheed Haque <shaheedhaque@xxxxxxxxx>]

Hi,

For automation purposes, I'd like to identify an idempotent pair of command sequences such that I can CREATE SUBSCRIPTION and DROP SUBSCRIPTION without knowing whether a previous attempt to do either operation partly succeeded or not. Specifically, as per Google and the notes in the docs (https://www.postgresql.org/docs/current/logical-replication-subscription.html#LOGICAL-REPLICATION-SUBSCRIPTION-SLOT), sometimes, a simple "DROP REPLICATION" is not enough, and one must do something like this:

        ALTER SUBSCRIPTION $SUBSCRIPTION DISABLE;
        ALTER SUBSCRIPTION $SUBSCRIPTION SET (slot_name = none);
        DROP SUBSCRIPTION IF EXISTS $SUBSCRIPTION CASCADE;

which of course leaves the slot as the other end, and so when it is to be recreated, "CREATE SUBSCRIPTION" would have to be augmented by "WITH (create_slot=false)".

Let's take it as read that network connectivity between the subscribing end and the publication end is OK. Let's say the DROP sequence looked like this:

         try:

              DROP SUBSCRIPTION IF EXISTS $SUBSCRIPTION CASCADE;

          except e:

              # Optionally, check if the exception e relates to a specific set of errors to do with the slot?

              ALTER SUBSCRIPTION $SUBSCRIPTION DISABLE;
              ALTER SUBSCRIPTION $SUBSCRIPTION SET (slot_name = none);
              DROP SUBSCRIPTION IF EXISTS $SUBSCRIPTION CASCADE;

 

If the exception path were to be taken, then the next CREATE side would have to look something like this

         try:

             CREATE SUBSCRIPTION ...

         except e:

             # Optionally, check if e relates to a pre-existing slot.

             CREATE SUBSCRIPTION ... WITH (create_flot=false);

Is that the best that can be done? Is there a better way? I'm happy to use SQL, or PL/SQL as needed.

Thanks, Shaheed