From: | Shaheed Haque <shaheedhaque(at)gmail(dot)com> |
---|---|
To: | pgsql-general list <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Idempotent CREATE SUBSCRIPTION and DROP SUBSCRIPTION pair |
Date: | 2025-02-02 13:58:18 |
Message-ID: | CAHAc2jfWChbmL5m3f5U-WGD_kRSUtBEu6H_snoPFfx6ZythCHw@mail.gmail.com |
Views: | Whole Thread | Raw Message | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2025-02-02 17:07:14 | Re: Log retention query |
Previous Message | Paul Brindusa | 2025-02-02 12:12:07 | Re: Log retention query |