public inbox for [email protected]  

help / color / mirror / Atom feed

From: Shaheed Haque <[email protected]>
To: pgsql-general list <[email protected]>
Subject: Idempotent CREATE SUBSCRIPTION and DROP SUBSCRIPTION pair
Date: Sun, 2 Feb 2025 13:58:18 +0000
Message-ID: <CAHAc2jfWChbmL5m3f5U-WGD_kRSUtBEu6H_snoPFfx6ZythCHw@mail.gmail.com> (raw)

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-SU...),
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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Idempotent CREATE SUBSCRIPTION and DROP SUBSCRIPTION pair
  In-Reply-To: <CAHAc2jfWChbmL5m3f5U-WGD_kRSUtBEu6H_snoPFfx6ZythCHw@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox