From: Anastasia Lubennikova <a.lubennikova@postgrespro.ru>
To: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: IF NOT EXISTS option for CREATE SERVER and CREATE USER MAPPING statements
Date: Fri, 13 Jan 2017 16:36:37 +0300
Message-ID: <7c2ab9b8-388a-1ce0-23a3-7acf2a0ed3c6@postgrespro.ru> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
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.
--
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:create_foreign_server_if_not_exists.patch (text/x-patch, 4.1K) download
commit cf20fa8c9f29310d8f67e5b198a9eb908d903431
Author: Anastasia <a.lubennikova@postgrespro.ru>
Date: Fri Jan 13 16:22:01 2017 +0300
Add [IF NOT EXISTS] option to CREATE SERVER statement
diff --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 06b4bc3..0b0114c 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 9eef550..ab4b3b1 100644--- a/src/backend/parser/gram.y+++ b/src/backend/parser/gram.y@@ -4621,6 +4621,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 7ceaa22..8a79ec0 100644--- a/src/include/nodes/parsenodes.h+++ b/src/include/nodes/parsenodes.h@@ -2075,6 +2075,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;
create_user_mapping_if_not_exists.patch (text/x-patch, 3.7K) download
commit 4237a23d6674da774b0772ffc953e5f499c04803
Author: Anastasia <a.lubennikova@postgrespro.ru>
Date: Fri Jan 13 16:23:53 2017 +0300
Add [IF NOT EXISTS] option to CREATE USER MAPPING statement
diff --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 0b0114c..32fa6a5 100644--- a/src/backend/commands/foreigncmds.c+++ b/src/backend/commands/foreigncmds.c@@ -1172,12 +1172,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 ab4b3b1..2fb713f 100644--- a/src/backend/parser/gram.y+++ b/src/backend/parser/gram.y@@ -4897,6 +4897,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 8a79ec0..bb7c02f 100644--- a/src/include/nodes/parsenodes.h+++ b/src/include/nodes/parsenodes.h@@ -2110,6 +2110,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;
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
Subject: Re: IF NOT EXISTS option for CREATE SERVER and CREATE USER MAPPING statements
In-Reply-To: <7c2ab9b8-388a-1ce0-23a3-7acf2a0ed3c6@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