MIME-Version: 1.0
From: Shaheed Haque <shaheedhaque@gmail.com>
Date: Sun, 2 Feb 2025 13:58:18 +0000
Message-ID: <CAHAc2jfWChbmL5m3f5U-WGD_kRSUtBEu6H_snoPFfx6ZythCHw@mail.gmail.com>
Subject: Idempotent CREATE SUBSCRIPTION and DROP SUBSCRIPTION pair
To: pgsql-general list <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000ac1e8f062d292bf3"
Archived-At: <https://www.postgresql.org/message-id/CAHAc2jfWChbmL5m3f5U-WGD_kRSUtBEu6H_snoPFfx6ZythCHw%40mail.gmail.com>
Precedence: bulk

--000000000000ac1e8f062d292bf3
Content-Type: text/plain; charset="UTF-8"

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

--000000000000ac1e8f062d292bf3
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hi,</div><div>For automation purposes, I&#39;d like t=
o identify an idempotent pair of command sequences such that I can CREATE S=
UBSCRIPTION and DROP SUBSCRIPTION without knowing whether a previous attemp=
t to do either operation partly succeeded or not. Specifically, as per Goog=
le and the notes in the docs (<a href=3D"https://www.postgresql.org/docs/cu=
rrent/logical-replication-subscription.html#LOGICAL-REPLICATION-SUBSCRIPTIO=
N-SLOT">https://www.postgresql.org/docs/current/logical-replication-subscri=
ption.html#LOGICAL-REPLICATION-SUBSCRIPTION-SLOT</a>), sometimes, a simple =
&quot;DROP REPLICATION&quot; is not enough, and one must do something like =
this:</div><div><br></div><div>=C2=A0=C2=A0 =C2=A0 =C2=A0=C2=A0 ALTER SUBSC=
RIPTION $SUBSCRIPTION DISABLE;<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 ALTER SUBSCRI=
PTION $SUBSCRIPTION SET (slot_name =3D none);<br>=C2=A0 =C2=A0 =C2=A0 =C2=
=A0 DROP SUBSCRIPTION IF EXISTS $SUBSCRIPTION CASCADE;</div><div><br></div>=
<div>which of course leaves the slot as the other end, and so when it is to=
 be recreated, &quot;CREATE SUBSCRIPTION&quot; would have to be augmented b=
y &quot;WITH (create_slot=3Dfalse)&quot;.</div><div><br></div><div><div>Let=
&#39;s take it as read that network connectivity between the subscribing en=
d and the publication end is OK. Let&#39;s say the DROP sequence looked lik=
e this:</div><div><br></div><div>=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0 try:</div><div>=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0 DROP SUBSCRIPTION IF EXISTS $SUBSCRIPTION CASCA=
DE;</div><div>=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 except=
 e:</div><div>=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0 # Optionally, check if the exception e relates to a spec=
ific set of errors to do with the slot?</div><div>=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ALTER SUBSCRIPTION $=
SUBSCRIPTION DISABLE;<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0 ALTER SUBSCRIPTION $SUBSCRIPTION SET (slot_name =3D none);<=
br>=C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 DROP SUB=
SCRIPTION IF EXISTS $SUBSCRIPTION CASCADE;</div><div>=C2=A0<br></div><div>I=
f the exception path were to be taken, then the next CREATE side would have=
 to look something like this<br></div><div><br></div><div>=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 try:</div><div><div>=C2=A0 =C2=A0=C2=A0 =
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 CREATE SUBSCRIPTION ...</div>=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 except e:</div><div>=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 # Optionall=
y, check if e relates to a pre-existing slot.<br></div><div><div>=C2=A0 =C2=
=A0=C2=A0 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 CREATE SUBSCRIPTION ..=
. WITH (create_flot=3Dfalse);<br></div></div></div><div><br></div><div>Is t=
hat the best that can be done? Is there a better way? I&#39;m happy to use =
SQL, or PL/SQL as needed.</div><div><br></div><div>Thanks, Shaheed<br></div=
><div><br></div></div>

--000000000000ac1e8f062d292bf3--