From: Anastasia Lubennikova <a.lubennikova@postgrespro.ru>
To: Artur Zakirov <a.zakirov@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: Mon, 13 Mar 2017 13:01:08 +0300
Message-ID: <b462a9ce-cd47-42fb-5b62-75fb0c284517@postgrespro.ru> (raw)
In-Reply-To: <d6de307d-47ff-0c1d-4eae-d8200c5d3f45@postgrespro.ru>
References: <7c2ab9b8-388a-1ce0-23a3-7acf2a0ed3c6@postgrespro.ru>
<4c7d935a-397d-16fe-c604-5e398cc1fc03@2ndQuadrant.com>
<d5993f84-cefb-55a4-f052-6fc090183fd6@postgrespro.ru>
<d6de307d-47ff-0c1d-4eae-d8200c5d3f45@postgrespro.ru>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
13.03.2017 11:53, Artur Zakirov:
> On 15.02.2017 20:54, Anastasia Lubennikova wrote:
>>
>> Done.
>>
>
> I have gotten the error that AlterUserMappingStmt doesn't have
> if_not_exists (in Russian):
>
>> gram.y: В функции «base_yyparse»:
>> gram.y:4918:7: ошибка: «AlterUserMappingStmt {aka struct
>> AlterUserMappingStmt}» не содержит элемента с именем «if_not_exists»
>> n->if_not_exists = false;
>> ^~
>
> After applying the CREATE USER patch in gram.y I have:
>
>>
>> AlterUserMappingStmt: ALTER USER MAPPING FOR auth_ident SERVER name
>> alter_generic_options
>> {
>> AlterUserMappingStmt *n =
>> makeNode(AlterUserMappingStmt);
>> 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;
>> }
>> ;
>
> Here ALTER USER MAPPING and CREATE USER MAPPING commands were mixed.
>
Thanks for catching that.
It was caused by a conflict on applying of the patch.
Updated versions of both patches are attached.
--
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_2.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 d5d40e6..41b2c01 100644--- a/src/backend/commands/foreigncmds.c+++ b/src/backend/commands/foreigncmds.c@@ -879,12 +879,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 e7acc2d..da67b51 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 a44d217..804436b 100644--- a/src/include/nodes/parsenodes.h+++ b/src/include/nodes/parsenodes.h@@ -2151,6 +2151,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 a0f969f..4d39e37 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 c13d5ff..c1d688f 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_2.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 d5d40e6..8bd6ac4 100644--- a/src/backend/commands/foreigncmds.c+++ b/src/backend/commands/foreigncmds.c@@ -1152,12 +1152,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 e7acc2d..c7cf5d7 100644--- a/src/backend/parser/gram.y+++ b/src/backend/parser/gram.y@@ -4853,6 +4853,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 a44d217..7954bc8 100644--- a/src/include/nodes/parsenodes.h+++ b/src/include/nodes/parsenodes.h@@ -2185,6 +2185,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 a0f969f..29fec07 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 c13d5ff..abea0f9 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, a.zakirov@postgrespro.ru, andrew.dunstan@2ndquadrant.com
Subject: Re: IF NOT EXISTS option for CREATE SERVER and CREATE USER MAPPING statements
In-Reply-To: <b462a9ce-cd47-42fb-5b62-75fb0c284517@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