From: Anastasia Lubennikova <a.lubennikova@postgrespro.ru>
To: Andrew Dunstan <andrew.dunstan@2ndquadrant.com>
To: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: IF NOT EXISTS option for CREATE SERVER and CREATE USER MAPPING statements
Date: Wed, 15 Feb 2017 20:54:43 +0300
Message-ID: <d5993f84-cefb-55a4-f052-6fc090183fd6@postgrespro.ru> (raw)
In-Reply-To: <4c7d935a-397d-16fe-c604-5e398cc1fc03@2ndQuadrant.com>
References: <7c2ab9b8-388a-1ce0-23a3-7acf2a0ed3c6@postgrespro.ru>
<4c7d935a-397d-16fe-c604-5e398cc1fc03@2ndQuadrant.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackersAttachments:download all (2 files, .tar.gz)create_foreign_server_if_not_exists_1.0.patch (text/x-patch, 5.6K) downloaddiff --git a/doc/src/sgml/ref/create_server.sgml b/doc/src/sgml/ref/create_server.sgmlindex 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.cindex 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.yindex 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.hindex 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.outindex 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.sqlindex 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');
create_user_mapping_if_not_exists_1.0.patch (text/x-patch, 5.0K) downloaddiff --git a/doc/src/sgml/ref/create_user_mapping.sgml b/doc/src/sgml/ref/create_user_mapping.sgmlindex 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.cindex 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.yindex 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.hindex 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.outindex 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.sqlindex 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+
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: a.lubennikova@postgrespro.ru, andrew.dunstan@2ndquadrant.com
Subject: Re: IF NOT EXISTS option for CREATE SERVER and CREATE USER MAPPING statements
In-Reply-To: <d5993f84-cefb-55a4-f052-6fc090183fd6@postgrespro.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox