Subject: Implement IF NOT EXISTS for CREATE PUBLICATION AND CREATE SUBSCRIPTION

From: vignesh C <[email-masked]>
Date: Sun, 12 Mar 2023 20:08:23 +0530

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 <[email-masked]>
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