Skip to content

Commit b6fb534

Browse files
adunstanadunstan
committed
Add IF NOT EXISTS for CREATE SERVER and CREATE USER MAPPING
There is still some inconsistency with the error messages surrounding foreign servers. Some use the word "foreign" and some don't. My inclination is to remove all such uses of "foreign" on the basis that the CREATE/ALTER/DROP SERVER commands don't use the word. However, that is left for another day. In this patch I have kept to the existing usage in the affected commands, which omits "foreign". Anastasia Lubennikova, reviewed by Arthur Zakirov and Ashtosh Bapat. Discussion: http://postgr.es/m/7c2ab9b8-388a-1ce0-23a3-7acf2a0ed3c6@postgrespro.ru
1 parent 839cb06 commit b6fb534

File tree

7 files changed

+93
-7
lines changed

7 files changed

+93
-7
lines changed

‎doc/src/sgml/ref/create_server.sgml

Lines changed: 13 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -21,7 +21,7 @@ PostgreSQL documentation
2121

2222
<refsynopsisdiv>
2323
<synopsis>
24-
CREATE SERVER <replaceable class="parameter">server_name</replaceable> [ TYPE '<replaceable class="parameter">server_type</replaceable>' ] [ VERSION '<replaceable class="parameter">server_version</replaceable>' ]
24+
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>' ]
2525
FOREIGN DATA WRAPPER <replaceable class="parameter">fdw_name</replaceable>
2626
[ OPTIONS ( <replaceable class="PARAMETER">option</replaceable> '<replaceable class="PARAMETER">value</replaceable>' [, ... ] ) ]
2727
</synopsis>
@@ -56,6 +56,18 @@ CREATE SERVER <replaceable class="parameter">server_name</replaceable> [ TYPE '<
5656
<title>Parameters</title>
5757

5858
<variablelist>
59+
<varlistentry>
60+
<term><literal>IF NOT EXISTS</></term>
61+
<listitem>
62+
<para>
63+
Do not throw an error if a server with the same name already exists.
64+
A notice is issued in this case. Note that there is no guarantee that
65+
the existing server is anything like the one that would have been
66+
created.
67+
</para>
68+
</listitem>
69+
</varlistentry>
70+
5971
<varlistentry>
6072
<term><replaceable class="parameter">server_name</replaceable></term>
6173
<listitem>

‎doc/src/sgml/ref/create_user_mapping.sgml

Lines changed: 13 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -21,7 +21,7 @@ PostgreSQL documentation
2121

2222
<refsynopsisdiv>
2323
<synopsis>
24-
CREATE USER MAPPING FOR { <replaceable class="parameter">user_name</replaceable> | USER | CURRENT_USER | PUBLIC }
24+
CREATE USER MAPPING [IF NOT EXISTS] FOR { <replaceable class="parameter">user_name</replaceable> | USER | CURRENT_USER | PUBLIC }
2525
SERVER <replaceable class="parameter">server_name</replaceable>
2626
[ OPTIONS ( <replaceable class="PARAMETER">option</replaceable> '<replaceable class="PARAMETER">value</replaceable>' [ , ... ] ) ]
2727
</synopsis>
@@ -50,6 +50,18 @@ CREATE USER MAPPING FOR { <replaceable class="parameter">user_name</replaceable>
5050
<title>Parameters</title>
5151

5252
<variablelist>
53+
<varlistentry>
54+
<term><literal>IF NOT EXISTS</></term>
55+
<listitem>
56+
<para>
57+
Do not throw an error if a mapping of the given user to the given foreign
58+
server already exists. A notice is issued in this case. Note that there
59+
is no guarantee that the existing user mapping is anything like the one
60+
that would have been created.
61+
</para>
62+
</listitem>
63+
</varlistentry>
64+
5365
<varlistentry>
5466
<term><replaceable class="parameter">user_name</replaceable></term>
5567
<listitem>

‎src/backend/commands/foreigncmds.c

Lines changed: 33 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -879,12 +879,25 @@ CreateForeignServer(CreateForeignServerStmt *stmt)
879879

880880
/*
881881
* Check that there is no other foreign server by this name.
882+
* Do nothing if IF NOT EXISTS was enforced.
882883
*/
883884
if (GetForeignServerByName(stmt->servername, true) != NULL)
884-
ereport(ERROR,
885-
(errcode(ERRCODE_DUPLICATE_OBJECT),
886-
errmsg("server \"%s\" already exists",
887-
stmt->servername)));
885+
{
886+
if (stmt->if_not_exists)
887+
{
888+
ereport(NOTICE,
889+
(errcode(ERRCODE_DUPLICATE_OBJECT),
890+
errmsg("server \"%s\" already exists, skipping",
891+
stmt->servername)));
892+
heap_close(rel, RowExclusiveLock);
893+
return InvalidObjectAddress;
894+
}
895+
else
896+
ereport(ERROR,
897+
(errcode(ERRCODE_DUPLICATE_OBJECT),
898+
errmsg("server \"%s\" already exists",
899+
stmt->servername)));
900+
}
888901

889902
/*
890903
* Check that the FDW exists and that we have USAGE on it. Also get the
@@ -1152,12 +1165,27 @@ CreateUserMapping(CreateUserMappingStmt *stmt)
11521165
umId = GetSysCacheOid2(USERMAPPINGUSERSERVER,
11531166
ObjectIdGetDatum(useId),
11541167
ObjectIdGetDatum(srv->serverid));
1168+
11551169
if (OidIsValid(umId))
1156-
ereport(ERROR,
1170+
{
1171+
if (stmt->if_not_exists)
1172+
{
1173+
ereport(NOTICE,
1174+
(errcode(ERRCODE_DUPLICATE_OBJECT),
1175+
errmsg("user mapping for \"%s\" already exists for server %s, skipping",
1176+
MappingUserName(useId),
1177+
stmt->servername)));
1178+
1179+
heap_close(rel, RowExclusiveLock);
1180+
return InvalidObjectAddress;
1181+
}
1182+
else
1183+
ereport(ERROR,
11571184
(errcode(ERRCODE_DUPLICATE_OBJECT),
11581185
errmsg("user mapping for \"%s\" already exists for server %s",
11591186
MappingUserName(useId),
11601187
stmt->servername)));
1188+
}
11611189

11621190
fdw = GetForeignDataWrapper(srv->fdwid);
11631191

‎src/backend/parser/gram.y

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4621,6 +4621,19 @@ CreateForeignServerStmt: CREATE SERVER name opt_type opt_foreign_server_version
46214621
n->version = $5;
46224622
n->fdwname = $9;
46234623
n->options = $10;
4624+
n->if_not_exists = false;
4625+
$$ = (Node *) n;
4626+
}
4627+
| CREATE SERVER IF_P NOT EXISTS name opt_type opt_foreign_server_version
4628+
FOREIGN DATA_P WRAPPER name create_generic_options
4629+
{
4630+
CreateForeignServerStmt *n = makeNode(CreateForeignServerStmt);
4631+
n->servername = $6;
4632+
n->servertype = $7;
4633+
n->version = $8;
4634+
n->fdwname = $12;
4635+
n->options = $13;
4636+
n->if_not_exists = true;
46244637
$$ = (Node *) n;
46254638
}
46264639
;
@@ -4853,6 +4866,16 @@ CreateUserMappingStmt: CREATE USER MAPPING FOR auth_ident SERVER name create_gen
48534866
n->user = $5;
48544867
n->servername = $7;
48554868
n->options = $8;
4869+
n->if_not_exists = false;
4870+
$$ = (Node *) n;
4871+
}
4872+
| CREATE USER MAPPING IF_P NOT EXISTS FOR auth_ident SERVER name create_generic_options
4873+
{
4874+
CreateUserMappingStmt *n = makeNode(CreateUserMappingStmt);
4875+
n->user = $8;
4876+
n->servername = $10;
4877+
n->options = $11;
4878+
n->if_not_exists = true;
48564879
$$ = (Node *) n;
48574880
}
48584881
;

‎src/include/nodes/parsenodes.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2154,6 +2154,7 @@ typedef struct CreateForeignServerStmt
21542154
char *servertype; /* optional server type */
21552155
char *version; /* optional server version */
21562156
char *fdwname; /* FDW name */
2157+
bool if_not_exists; /* just do nothing if it already exists? */
21572158
List *options; /* generic options to server */
21582159
} CreateForeignServerStmt;
21592160

@@ -2188,6 +2189,7 @@ typedef struct CreateUserMappingStmt
21882189
NodeTag type;
21892190
RoleSpec *user; /* user role */
21902191
char *servername; /* server name */
2192+
bool if_not_exists; /* just do nothing if it already exists? */
21912193
List *options; /* generic options to server */
21922194
} CreateUserMappingStmt;
21932195

‎src/test/regress/expected/foreign_data.out

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -221,6 +221,10 @@ CREATE FOREIGN DATA WRAPPER foo;
221221
CREATE SERVER s1 FOREIGN DATA WRAPPER foo;
222222
COMMENT ON SERVER s1 IS 'foreign server';
223223
CREATE USER MAPPING FOR current_user SERVER s1;
224+
CREATE USER MAPPING FOR current_user SERVER s1; -- ERROR
225+
ERROR: user mapping for "regress_foreign_data_user" already exists for server s1
226+
CREATE USER MAPPING IF NOT EXISTS FOR current_user SERVER s1; -- NOTICE
227+
NOTICE: user mapping for "regress_foreign_data_user" already exists for server s1, skipping
224228
\dew+
225229
List of foreign-data wrappers
226230
Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
@@ -284,6 +288,8 @@ CREATE FOREIGN DATA WRAPPER foo OPTIONS ("test wrapper" 'true');
284288
CREATE SERVER s1 FOREIGN DATA WRAPPER foo;
285289
CREATE SERVER s1 FOREIGN DATA WRAPPER foo; -- ERROR
286290
ERROR: server "s1" already exists
291+
CREATE SERVER IF NOT EXISTS s1 FOREIGN DATA WRAPPER foo; -- No ERROR, just NOTICE
292+
NOTICE: server "s1" already exists, skipping
287293
CREATE SERVER s2 FOREIGN DATA WRAPPER foo OPTIONS (host 'a', dbname 'b');
288294
CREATE SERVER s3 TYPE 'oracle' FOREIGN DATA WRAPPER foo;
289295
CREATE SERVER s4 TYPE 'oracle' FOREIGN DATA WRAPPER foo OPTIONS (host 'a', dbname 'b');

‎src/test/regress/sql/foreign_data.sql

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -104,6 +104,8 @@ CREATE FOREIGN DATA WRAPPER foo;
104104
CREATE SERVER s1 FOREIGN DATA WRAPPER foo;
105105
COMMENT ON SERVER s1 IS 'foreign server';
106106
CREATE USER MAPPING FOR current_user SERVER s1;
107+
CREATE USER MAPPING FOR current_user SERVER s1; -- ERROR
108+
CREATE USER MAPPING IF NOT EXISTS FOR current_user SERVER s1; -- NOTICE
107109
\dew+
108110
\des+
109111
\deu+
@@ -121,6 +123,7 @@ CREATE SERVER s1 FOREIGN DATA WRAPPER foo; -- ERROR
121123
CREATE FOREIGN DATA WRAPPER foo OPTIONS ("test wrapper" 'true');
122124
CREATE SERVER s1 FOREIGN DATA WRAPPER foo;
123125
CREATE SERVER s1 FOREIGN DATA WRAPPER foo; -- ERROR
126+
CREATE SERVER IF NOT EXISTS s1 FOREIGN DATA WRAPPER foo; -- No ERROR, just NOTICE
124127
CREATE SERVER s2 FOREIGN DATA WRAPPER foo OPTIONS (host 'a', dbname 'b');
125128
CREATE SERVER s3 TYPE 'oracle' FOREIGN DATA WRAPPER foo;
126129
CREATE SERVER s4 TYPE 'oracle' FOREIGN DATA WRAPPER foo OPTIONS (host 'a', dbname 'b');

0 commit comments

Comments
 (0)