Hi,
Currently we don't support "IF NOT EXISTS" for Create publication and
Create subscription, I felt it would be useful to add this "IF NOT
EXISTS" which will create publication/subscription only if the object
does not exist.
Attached patch for handling the same.
Thoughts?
Regards,
Vignesh
From 63b53a87253af6e5d08f473cb0d568c2321a2bd3 Mon Sep 17 00:00:00 2001
From: Vignesh C <vignes...@gmail.com>
Date: Fri, 3 Mar 2023 15:54:00 +0530
Subject: [PATCH v1] Implement IF NOT EXISTS for CREATE PUBLICATION AND CREATE
SUBSCRIPTION
Implement IF NOT EXISTS for CREATE PUBLICATION AND CREATE SUBSCRIPTION
---
doc/src/sgml/ref/create_publication.sgml | 14 ++++++-
doc/src/sgml/ref/create_subscription.sgml | 14 ++++++-
src/backend/commands/publicationcmds.c | 20 ++++++++--
src/backend/commands/subscriptioncmds.c | 18 +++++++--
src/backend/parser/gram.y | 45 ++++++++++++++++++++++
src/include/nodes/parsenodes.h | 2 +
src/test/regress/expected/publication.out | 2 +
src/test/regress/expected/subscription.out | 2 +
src/test/regress/sql/publication.sql | 1 +
src/test/regress/sql/subscription.sql | 1 +
10 files changed, 109 insertions(+), 10 deletions(-)
diff --git a/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml
index a2946feaa3..b2f17bd7d8 100644
--- a/doc/src/sgml/ref/create_publication.sgml
+++ b/doc/src/sgml/ref/create_publication.sgml
@@ -21,7 +21,7 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
-CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
+CREATE PUBLICATION [ IF NOT EXISTS ] <replaceable class="parameter">name</replaceable>
[ FOR ALL TABLES
| FOR <replaceable class="parameter">publication_object</replaceable> [, ... ] ]
[ WITH ( <replaceable class="parameter">publication_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) ]
@@ -54,6 +54,18 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
<title>Parameters</title>
<variablelist>
+ <varlistentry>
+ <term><literal>IF NOT EXISTS</literal></term>
+ <listitem>
+ <para>
+ Do not throw an error if a publication with the same name already exists.
+ A notice is issued in this case. Note that there is no guarantee that
+ the existing publication is anything like the one that would have been
+ created.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><replaceable class="parameter">name</replaceable></term>
<listitem>
diff --git a/doc/src/sgml/ref/create_subscription.sgml b/doc/src/sgml/ref/create_subscription.sgml
index 51c45f17c7..4c7fe5dccd 100644
--- a/doc/src/sgml/ref/create_subscription.sgml
+++ b/doc/src/sgml/ref/create_subscription.sgml
@@ -21,7 +21,7 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
-CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceable>
+CREATE SUBSCRIPTION [ IF NOT EXISTS ] <replaceable class="parameter">subscription_name</replaceable>
CONNECTION '<replaceable class="parameter">conninfo</replaceable>'
PUBLICATION <replaceable class="parameter">publication_name</replaceable> [, ...]
[ WITH ( <replaceable class="parameter">subscription_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) ]
@@ -61,6 +61,18 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl
<title>Parameters</title>
<variablelist>
+ <varlistentry>
+ <term><literal>IF NOT EXISTS</literal></term>
+ <listitem>
+ <para>
+ Do not throw an error if a subscription with the same name already
+ exists. A notice is issued in this case. Note that there is no guarantee
+ that the existing subscription is anything like the one that would have
+ been created.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><replaceable class="parameter">subscription_name</replaceable></term>
<listitem>
diff --git a/src/backend/commands/publicationcmds.c b/src/backend/commands/publicationcmds.c
index f4ba572697..41e5f2c3e1 100644
--- a/src/backend/commands/publicationcmds.c
+++ b/src/backend/commands/publicationcmds.c
@@ -766,10 +766,22 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt)
puboid = GetSysCacheOid1(PUBLICATIONNAME, Anum_pg_publication_oid,
CStringGetDatum(stmt->pubname));
if (OidIsValid(puboid))
- ereport(ERROR,
- (errcode(ERRCODE_DUPLICATE_OBJECT),
- errmsg("publication \"%s\" already exists",
- stmt->pubname)));
+ {
+ if (stmt->if_not_exists)
+ {
+ ereport(NOTICE,
+ errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("publication \"%s\" already exists, skipping",
+ stmt->pubname));
+ table_close(rel, RowExclusiveLock);
+ return InvalidObjectAddress;
+ }
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("publication \"%s\" already exists",
+ stmt->pubname)));
+ }
/* Form a tuple. */
memset(values, 0, sizeof(values));
diff --git a/src/backend/commands/subscriptioncmds.c b/src/backend/commands/subscriptioncmds.c
index 464db6d247..f2dd59244b 100644
--- a/src/backend/commands/subscriptioncmds.c
+++ b/src/backend/commands/subscriptioncmds.c
@@ -593,10 +593,20 @@ CreateSubscription(ParseState *pstate, CreateSubscriptionStmt *stmt,
MyDatabaseId, CStringGetDatum(stmt->subname));
if (OidIsValid(subid))
{
- ereport(ERROR,
- (errcode(ERRCODE_DUPLICATE_OBJECT),
- errmsg("subscription \"%s\" already exists",
- stmt->subname)));
+ if (stmt->if_not_exists)
+ {
+ ereport(NOTICE,
+ errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("subscription \"%s\" already exists, skipping",
+ stmt->subname));
+ table_close(rel, RowExclusiveLock);
+ return InvalidObjectAddress;
+ }
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("subscription \"%s\" already exists",
+ stmt->subname)));
}
if (!IsSet(opts.specified_opts, SUBOPT_SLOT_NAME) &&
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index a0138382a1..abfd33914c 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -10371,6 +10371,16 @@ CreatePublicationStmt:
n->pubname = $3;
n->options = $4;
+ n->if_not_exists = false;
+ $$ = (Node *) n;
+ }
+ | CREATE PUBLICATION IF_P NOT EXISTS name opt_definition
+ {
+ CreatePublicationStmt *n = makeNode(CreatePublicationStmt);
+
+ n->pubname = $6;
+ n->options = $7;
+ n->if_not_exists = true;
$$ = (Node *) n;
}
| CREATE PUBLICATION name FOR ALL TABLES opt_definition
@@ -10380,6 +10390,17 @@ CreatePublicationStmt:
n->pubname = $3;
n->options = $7;
n->for_all_tables = true;
+ n->if_not_exists = false;
+ $$ = (Node *) n;
+ }
+ | CREATE PUBLICATION IF_P NOT EXISTS name FOR ALL TABLES opt_definition
+ {
+ CreatePublicationStmt *n = makeNode(CreatePublicationStmt);
+
+ n->pubname = $6;
+ n->options = $10;
+ n->for_all_tables = true;
+ n->if_not_exists = true;
$$ = (Node *) n;
}
| CREATE PUBLICATION name FOR pub_obj_list opt_definition
@@ -10389,6 +10410,18 @@ CreatePublicationStmt:
n->pubname = $3;
n->options = $6;
n->pubobjects = (List *) $5;
+ n->if_not_exists = false;
+ preprocess_pubobj_list(n->pubobjects, yyscanner);
+ $$ = (Node *) n;
+ }
+ | CREATE PUBLICATION IF_P NOT EXISTS name FOR pub_obj_list opt_definition
+ {
+ CreatePublicationStmt *n = makeNode(CreatePublicationStmt);
+
+ n->pubname = $6;
+ n->options = $9;
+ n->pubobjects = (List *) $8;
+ n->if_not_exists = true;
preprocess_pubobj_list(n->pubobjects, yyscanner);
$$ = (Node *) n;
}
@@ -10564,6 +10597,18 @@ CreateSubscriptionStmt:
n->conninfo = $5;
n->publication = $7;
n->options = $8;
+ n->if_not_exists = false;
+ $$ = (Node *) n;
+ }
+ | CREATE SUBSCRIPTION IF_P NOT EXISTS name CONNECTION Sconst PUBLICATION name_list opt_definition
+ {
+ CreateSubscriptionStmt *n =
+ makeNode(CreateSubscriptionStmt);
+ n->subname = $6;
+ n->conninfo = $8;
+ n->publication = $10;
+ n->options = $11;
+ n->if_not_exists = true;
$$ = (Node *) n;
}
;
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 371aa0ffc5..c47a8797ef 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -3875,6 +3875,7 @@ typedef struct CreatePublicationStmt
List *options; /* List of DefElem nodes */
List *pubobjects; /* Optional list of publication objects */
bool for_all_tables; /* Special publication for all tables in db */
+ bool if_not_exists; /* Just do nothing if it already exists? */
} CreatePublicationStmt;
typedef enum AlterPublicationAction
@@ -3909,6 +3910,7 @@ typedef struct CreateSubscriptionStmt
char *conninfo; /* Connection string to publisher */
List *publication; /* One or more publication to subscribe to */
List *options; /* List of DefElem nodes */
+ bool if_not_exists; /* Just do nothing if it already exists? */
} CreateSubscriptionStmt;
typedef enum AlterSubscriptionType
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index 427f87ea07..0475dffdcf 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -9,6 +9,8 @@ SET SESSION AUTHORIZATION 'regress_publication_user';
SET client_min_messages = 'ERROR';
CREATE PUBLICATION testpub_default;
RESET client_min_messages;
+CREATE PUBLICATION IF NOT EXISTS testpub_default;
+NOTICE: publication "testpub_default" already exists, skipping
COMMENT ON PUBLICATION testpub_default IS 'test publication';
SELECT obj_description(p.oid, 'pg_publication') FROM pg_publication p;
obj_description
diff --git a/src/test/regress/expected/subscription.out b/src/test/regress/expected/subscription.out
index 3f99b14394..77d2c41c43 100644
--- a/src/test/regress/expected/subscription.out
+++ b/src/test/regress/expected/subscription.out
@@ -31,6 +31,8 @@ ERROR: publication name "foo" used more than once
CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false);
WARNING: subscription was created, but is not connected
HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription.
+CREATE SUBSCRIPTION IF NOT EXISTS regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false);
+NOTICE: subscription "regress_testsub" already exists, skipping
COMMENT ON SUBSCRIPTION regress_testsub IS 'test subscription';
SELECT obj_description(s.oid, 'pg_subscription') FROM pg_subscription s;
obj_description
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index a47c5939d5..14442ce8b1 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -10,6 +10,7 @@ SET SESSION AUTHORIZATION 'regress_publication_user';
SET client_min_messages = 'ERROR';
CREATE PUBLICATION testpub_default;
RESET client_min_messages;
+CREATE PUBLICATION IF NOT EXISTS testpub_default;
COMMENT ON PUBLICATION testpub_default IS 'test publication';
SELECT obj_description(p.oid, 'pg_publication') FROM pg_publication p;
diff --git a/src/test/regress/sql/subscription.sql b/src/test/regress/sql/subscription.sql
index 7281f5fee2..e9050f09d5 100644
--- a/src/test/regress/sql/subscription.sql
+++ b/src/test/regress/sql/subscription.sql
@@ -26,6 +26,7 @@ CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUB
-- ok
CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false);
+CREATE SUBSCRIPTION IF NOT EXISTS regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false);
COMMENT ON SUBSCRIPTION regress_testsub IS 'test subscription';
SELECT obj_description(s.oid, 'pg_subscription') FROM pg_subscription s;
--
2.34.1
