Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cnMmy-0007ZP-NJ for pgsql-hackers@arkaria.postgresql.org; Mon, 13 Mar 2017 10:01:17 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cnMmy-0006Ul-9g for pgsql-hackers@arkaria.postgresql.org; Mon, 13 Mar 2017 10:01:16 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cnMmx-0006Rr-2t for pgsql-hackers@postgresql.org; Mon, 13 Mar 2017 10:01:15 +0000 Received: from mail.postgrespro.ru ([93.174.131.138]) by magus.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cnMmt-0003wx-0F for pgsql-hackers@postgresql.org; Mon, 13 Mar 2017 10:01:14 +0000 Received: from localhost (localhost [127.0.0.1]) by mail.postgrespro.ru (Postfix) with ESMTP id 843B821C2511; Mon, 13 Mar 2017 13:01:09 +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=1489399268; bh=3eq4K7fnZl38yKdVQgLMSCJwkEd3voGFdgo4cvtEeHk=; h=To:References:From:Date:In-Reply-To; b=p80gaCsp+U+F8zfVkwE/WvgkyJ6NJv00UO/f9C1K8PRAcJIjrXLfVTpAJ2PQ+bKfR bge2SkRvjBLxNsiHNAaKvp/jbr1KDhLmw9PgiVXpJEyNmI7Vd+yaUExN0m3F6Mg15N oRAOlCH332BvlZmZFRNhmNfSYC8HA1iJCYwfbLVM= To: Artur Zakirov , 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: Mon, 13 Mar 2017 13:01:08 +0300 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101 Thunderbird/45.7.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/mixed; boundary="------------4E854B8E49EB3D9550AF88E7" X-Pg-Spam-Score: -2.3 (--) 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. --------------4E854B8E49EB3D9550AF88E7 Content-Type: multipart/alternative; boundary="------------228D6BA89E4D65B10BE6BA1C" --------------228D6BA89E4D65B10BE6BA1C Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit 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 --------------228D6BA89E4D65B10BE6BA1C Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit 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
--------------228D6BA89E4D65B10BE6BA1C-- --------------4E854B8E49EB3D9550AF88E7 Content-Type: text/x-patch; name="create_foreign_server_if_not_exists_2.0.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="create_foreign_server_if_not_exists_2.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 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.y index 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.h index 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.out index 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.sql index 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'); --------------4E854B8E49EB3D9550AF88E7 Content-Type: text/x-patch; name="create_user_mapping_if_not_exists_2.0.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="create_user_mapping_if_not_exists_2.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 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.y index 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.h index 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.out index 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.sql index 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+ --------------4E854B8E49EB3D9550AF88E7 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 --------------4E854B8E49EB3D9550AF88E7--