Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1ce3oS-0000eq-3k for pgsql-hackers@arkaria.postgresql.org; Wed, 15 Feb 2017 17:56:20 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1ce3oR-0007CW-1p for pgsql-hackers@arkaria.postgresql.org; Wed, 15 Feb 2017 17:56:19 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1ce3n0-0004xp-7e for pgsql-hackers@postgresql.org; Wed, 15 Feb 2017 17:54:50 +0000 Received: from mail.postgrespro.ru ([93.174.131.138]) by makus.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1ce3mw-00034R-OB for pgsql-hackers@postgresql.org; Wed, 15 Feb 2017 17:54:49 +0000 Received: from localhost (localhost [127.0.0.1]) by mail.postgrespro.ru (Postfix) with ESMTP id 033D121C2A29; Wed, 15 Feb 2017 20:54:44 +0300 (MSK) X-Virus-Scanned: Debian amavisd-new at postgrespro.ru Subject: Re: IF NOT EXISTS option for CREATE SERVER and CREATE USER MAPPING statements DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mail; t=1487181283; bh=wj5IYPHx2jmQoD2OltR0VPlSrOQMq2bcprPBCEesRCc=; h=To:References:From:Date:In-Reply-To; b=gxVQGRzrcpggkT6UvpGYTZwTWFa5Yi2Ms6ul3sF6cQWmSFKoKeCyN1Wby2DAbqzbL FWjIDL71HbK2rS/brQEbILsG7RSRJV+TsvdBuEXOWFDrA4t8tjhtRslc8UyCrFwae5 0mMsJIH5PUtCwYfey3LjBUrgXDwYvCDnTZ85r0e4= To: Andrew Dunstan , pgsql-hackers References: <7c2ab9b8-388a-1ce0-23a3-7acf2a0ed3c6@postgrespro.ru> <4c7d935a-397d-16fe-c604-5e398cc1fc03@2ndQuadrant.com> From: Anastasia Lubennikova Message-ID: Date: Wed, 15 Feb 2017 20:54:43 +0300 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101 Thunderbird/45.5.1 MIME-Version: 1.0 In-Reply-To: <4c7d935a-397d-16fe-c604-5e398cc1fc03@2ndQuadrant.com> Content-Type: multipart/mixed; boundary="------------8E33127C15F843E0E8743769" X-Pg-Spam-Score: -2.0 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-hackers Precedence: bulk Sender: pgsql-hackers-owner@postgresql.org This is a multi-part message in MIME format. --------------8E33127C15F843E0E8743769 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit 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 --------------8E33127C15F843E0E8743769 Content-Type: text/x-patch; name="create_foreign_server_if_not_exists_1.0.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="create_foreign_server_if_not_exists_1.0.patch" 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 -CREATE SERVER server_name [ TYPE 'server_type' ] [ VERSION 'server_version' ] +CREATE SERVER [IF NOT EXISTS] server_name [ TYPE 'server_type' ] [ VERSION 'server_version' ] FOREIGN DATA WRAPPER fdw_name [ OPTIONS ( option 'value' [, ... ] ) ] @@ -56,6 +56,19 @@ CREATE SERVER server_name [ TYPE '< Parameters + + + IF NOT EXISTS + + + 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. + + + + server_name 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'); --------------8E33127C15F843E0E8743769 Content-Type: text/x-patch; name="create_user_mapping_if_not_exists_1.0.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="create_user_mapping_if_not_exists_1.0.patch" 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 -CREATE USER MAPPING FOR { user_name | USER | CURRENT_USER | PUBLIC } +CREATE USER MAPPING [IF NOT EXISTS] FOR { user_name | USER | CURRENT_USER | PUBLIC } SERVER server_name [ OPTIONS ( option 'value' [ , ... ] ) ] @@ -49,6 +49,18 @@ CREATE USER MAPPING FOR { user_name Parameters + + IF NOT EXISTS + + + 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. + + + + user_name 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+ --------------8E33127C15F843E0E8743769 Content-Type: text/plain Content-Disposition: inline Content-Transfer-Encoding: 8bit MIME-Version: 1.0 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers --------------8E33127C15F843E0E8743769--