13.02.2017 19:34, Andrew Dunstan:
On 01/13/2017 08:36 AM, Anastasia Lubennikova wrote:
I implemented IF NOT EXISTS option for CREATE SERVER and CREATE USER
MAPPING statements
for one of our customers.
I think other users can also find it useful for scripting and
automated tasks.
The patches themselves are small and simple. Documentation is updated
as well.
This looks good and useful. Please add some regression tests.
Done.
--
Anastasia Lubennikova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
diff --git a/doc/src/sgml/ref/create_server.sgml b/doc/src/sgml/ref/create_server.sgml
index 734c6c9..6777679 100644
--- a/doc/src/sgml/ref/create_server.sgml
+++ b/doc/src/sgml/ref/create_server.sgml
@@ -21,7 +21,7 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
-CREATE SERVER <replaceable class="parameter">server_name</replaceable> [ TYPE '<replaceable class="parameter">server_type</replaceable>' ] [ VERSION '<replaceable class="parameter">server_version</replaceable>' ]
+CREATE SERVER [IF NOT EXISTS] <replaceable class="parameter">server_name</replaceable> [ TYPE '<replaceable class="parameter">server_type</replaceable>' ] [ VERSION '<replaceable class="parameter">server_version</replaceable>' ]
FOREIGN DATA WRAPPER <replaceable class="parameter">fdw_name</replaceable>
[ OPTIONS ( <replaceable class="PARAMETER">option</replaceable> '<replaceable class="PARAMETER">value</replaceable>' [, ... ] ) ]
</synopsis>
@@ -56,6 +56,19 @@ CREATE SERVER <replaceable class="parameter">server_name</replaceable> [ TYPE '<
<title>Parameters</title>
<variablelist>
+
+ <varlistentry>
+ <term><literal>IF NOT EXISTS</></term>
+ <listitem>
+ <para>
+ Do not throw an error if a server with the same name already exists.
+ A notice is issued in this case. Note that there is no guarantee that
+ the existing server is anything like the one that would have been
+ created.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><replaceable class="parameter">server_name</replaceable></term>
<listitem>
diff --git a/src/backend/commands/foreigncmds.c b/src/backend/commands/foreigncmds.c
index 6ff8b69..b4ae5de 100644
--- a/src/backend/commands/foreigncmds.c
+++ b/src/backend/commands/foreigncmds.c
@@ -883,12 +883,25 @@ CreateForeignServer(CreateForeignServerStmt *stmt)
/*
* Check that there is no other foreign server by this name.
+ * Do nothing if IF NOT EXISTS was enforced.
*/
if (GetForeignServerByName(stmt->servername, true) != NULL)
- ereport(ERROR,
- (errcode(ERRCODE_DUPLICATE_OBJECT),
- errmsg("server \"%s\" already exists",
- stmt->servername)));
+ {
+ if (stmt->if_not_exists)
+ {
+ ereport(NOTICE,
+ (errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("foreign server \"%s\" already exists, skipping",
+ stmt->servername)));
+ heap_close(rel, RowExclusiveLock);
+ return InvalidObjectAddress;
+ }
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("foreign server \"%s\" already exists",
+ stmt->servername)));
+ }
/*
* Check that the FDW exists and that we have USAGE on it. Also get the
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index a4edea0..d007468 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -4655,6 +4655,19 @@ CreateForeignServerStmt: CREATE SERVER name opt_type opt_foreign_server_version
n->version = $5;
n->fdwname = $9;
n->options = $10;
+ n->if_not_exists = false;
+ $$ = (Node *) n;
+ }
+ | CREATE SERVER IF_P NOT EXISTS name opt_type opt_foreign_server_version
+ FOREIGN DATA_P WRAPPER name create_generic_options
+ {
+ CreateForeignServerStmt *n = makeNode(CreateForeignServerStmt);
+ n->servername = $6;
+ n->servertype = $7;
+ n->version = $8;
+ n->fdwname = $12;
+ n->options = $13;
+ n->if_not_exists = true;
$$ = (Node *) n;
}
;
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 07a8436..704bc6b 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2113,6 +2113,7 @@ typedef struct CreateForeignServerStmt
char *servertype; /* optional server type */
char *version; /* optional server version */
char *fdwname; /* FDW name */
+ bool if_not_exists; /* just do nothing if it already exists? */
List *options; /* generic options to server */
} CreateForeignServerStmt;
diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out
index 3a9fb8f..17f9f40 100644
--- a/src/test/regress/expected/foreign_data.out
+++ b/src/test/regress/expected/foreign_data.out
@@ -283,7 +283,9 @@ ERROR: foreign-data wrapper "foo" does not exist
CREATE FOREIGN DATA WRAPPER foo OPTIONS ("test wrapper" 'true');
CREATE SERVER s1 FOREIGN DATA WRAPPER foo;
CREATE SERVER s1 FOREIGN DATA WRAPPER foo; -- ERROR
-ERROR: server "s1" already exists
+ERROR: foreign server "s1" already exists
+CREATE SERVER IF NOT EXISTS s1 FOREIGN DATA WRAPPER foo; -- No ERROR, just NOTICE
+NOTICE: foreign server "s1" already exists, skipping
CREATE SERVER s2 FOREIGN DATA WRAPPER foo OPTIONS (host 'a', dbname 'b');
CREATE SERVER s3 TYPE 'oracle' FOREIGN DATA WRAPPER foo;
CREATE SERVER s4 TYPE 'oracle' FOREIGN DATA WRAPPER foo OPTIONS (host 'a', dbname 'b');
diff --git a/src/test/regress/sql/foreign_data.sql b/src/test/regress/sql/foreign_data.sql
index 38e1d41..a1fc10f 100644
--- a/src/test/regress/sql/foreign_data.sql
+++ b/src/test/regress/sql/foreign_data.sql
@@ -121,6 +121,7 @@ CREATE SERVER s1 FOREIGN DATA WRAPPER foo; -- ERROR
CREATE FOREIGN DATA WRAPPER foo OPTIONS ("test wrapper" 'true');
CREATE SERVER s1 FOREIGN DATA WRAPPER foo;
CREATE SERVER s1 FOREIGN DATA WRAPPER foo; -- ERROR
+CREATE SERVER IF NOT EXISTS s1 FOREIGN DATA WRAPPER foo; -- No ERROR, just NOTICE
CREATE SERVER s2 FOREIGN DATA WRAPPER foo OPTIONS (host 'a', dbname 'b');
CREATE SERVER s3 TYPE 'oracle' FOREIGN DATA WRAPPER foo;
CREATE SERVER s4 TYPE 'oracle' FOREIGN DATA WRAPPER foo OPTIONS (host 'a', dbname 'b');
diff --git a/doc/src/sgml/ref/create_user_mapping.sgml b/doc/src/sgml/ref/create_user_mapping.sgml
index 44fe302..680906b 100644
--- a/doc/src/sgml/ref/create_user_mapping.sgml
+++ b/doc/src/sgml/ref/create_user_mapping.sgml
@@ -21,7 +21,7 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
-CREATE USER MAPPING FOR { <replaceable class="parameter">user_name</replaceable> | USER | CURRENT_USER | PUBLIC }
+CREATE USER MAPPING [IF NOT EXISTS] FOR { <replaceable class="parameter">user_name</replaceable> | USER | CURRENT_USER | PUBLIC }
SERVER <replaceable class="parameter">server_name</replaceable>
[ OPTIONS ( <replaceable class="PARAMETER">option</replaceable> '<replaceable class="PARAMETER">value</replaceable>' [ , ... ] ) ]
</synopsis>
@@ -49,6 +49,18 @@ CREATE USER MAPPING FOR { <replaceable class="parameter">user_name</replaceable>
<refsect1>
<title>Parameters</title>
+ <varlistentry>
+ <term><literal>IF NOT EXISTS</></term>
+ <listitem>
+ <para>
+ Do not throw an error if a user mapping with the same name already exists.
+ A notice is issued in this case. Note that there is no guarantee that
+ the existing user mapping is anything like the one that would have been
+ created.
+ </para>
+ </listitem>
+ </varlistentry>
+
<variablelist>
<varlistentry>
<term><replaceable class="parameter">user_name</replaceable></term>
diff --git a/src/backend/commands/foreigncmds.c b/src/backend/commands/foreigncmds.c
index 6ff8b69..33c47b0 100644
--- a/src/backend/commands/foreigncmds.c
+++ b/src/backend/commands/foreigncmds.c
@@ -1159,12 +1159,27 @@ CreateUserMapping(CreateUserMappingStmt *stmt)
umId = GetSysCacheOid2(USERMAPPINGUSERSERVER,
ObjectIdGetDatum(useId),
ObjectIdGetDatum(srv->serverid));
+
if (OidIsValid(umId))
- ereport(ERROR,
+ {
+ if (stmt->if_not_exists)
+ {
+ ereport(NOTICE,
+ (errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("user mapping \"%s\" already exists for server %s, skipping",
+ MappingUserName(useId),
+ stmt->servername)));
+
+ heap_close(rel, RowExclusiveLock);
+ return InvalidObjectAddress;
+ }
+ else
+ ereport(ERROR,
(errcode(ERRCODE_DUPLICATE_OBJECT),
errmsg("user mapping \"%s\" already exists for server %s",
MappingUserName(useId),
stmt->servername)));
+ }
fdw = GetForeignDataWrapper(srv->fdwid);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index a4edea0..cdd3625 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -4918,6 +4918,16 @@ CreateUserMappingStmt: CREATE USER MAPPING FOR auth_ident SERVER name create_gen
n->user = $5;
n->servername = $7;
n->options = $8;
+ n->if_not_exists = false;
+ $$ = (Node *) n;
+ }
+ | CREATE USER MAPPING IF_P NOT EXISTS FOR auth_ident SERVER name create_generic_options
+ {
+ CreateUserMappingStmt *n = makeNode(CreateUserMappingStmt);
+ n->user = $8;
+ n->servername = $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 07a8436..ffa74d7 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2147,6 +2147,7 @@ typedef struct CreateUserMappingStmt
NodeTag type;
RoleSpec *user; /* user role */
char *servername; /* server name */
+ bool if_not_exists; /* just do nothing if it already exists? */
List *options; /* generic options to server */
} CreateUserMappingStmt;
diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out
index 3a9fb8f..8356982 100644
--- a/src/test/regress/expected/foreign_data.out
+++ b/src/test/regress/expected/foreign_data.out
@@ -221,6 +221,10 @@ CREATE FOREIGN DATA WRAPPER foo;
CREATE SERVER s1 FOREIGN DATA WRAPPER foo;
COMMENT ON SERVER s1 IS 'foreign server';
CREATE USER MAPPING FOR current_user SERVER s1;
+CREATE USER MAPPING FOR current_user SERVER s1; -- ERROR
+ERROR: user mapping "regress_foreign_data_user" already exists for server s1
+CREATE USER MAPPING IF NOT EXISTS FOR current_user SERVER s1; -- NOTICE
+NOTICE: user mapping "regress_foreign_data_user" already exists for server s1, skipping
\dew+
List of foreign-data wrappers
Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
diff --git a/src/test/regress/sql/foreign_data.sql b/src/test/regress/sql/foreign_data.sql
index 38e1d41..7b54ea6 100644
--- a/src/test/regress/sql/foreign_data.sql
+++ b/src/test/regress/sql/foreign_data.sql
@@ -104,6 +104,8 @@ CREATE FOREIGN DATA WRAPPER foo;
CREATE SERVER s1 FOREIGN DATA WRAPPER foo;
COMMENT ON SERVER s1 IS 'foreign server';
CREATE USER MAPPING FOR current_user SERVER s1;
+CREATE USER MAPPING FOR current_user SERVER s1; -- ERROR
+CREATE USER MAPPING IF NOT EXISTS FOR current_user SERVER s1; -- NOTICE
\dew+
\des+
\deu+
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
