Re: [PATCHES] create language ... if not exists
Hello Heikki, On Sat, 29 Mar 2008 11:49:56 + Heikki Linnakangas wrote: Regarding the patch itself: You define rule opt_if_not_exists, but never use it. And you add a new rule for CREATE LANGUAGE ... HANDLER ..., but forgot IF_P NOT EXISTS from the end of that. Looks like you couldn't decide which approach to take, and ended up doing a little bit of both ;-). Now that you say it: yes, i tested a bit around, how to implement this feature. But since my current approach is wrong, i have to change this anyway. Thank you for pointing this out. -- Andreas 'ads' Scherbaum German PostgreSQL User Group -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] create language ... if not exists
Hello all, sorry, was off yesterday and i'm just reading all your answers. On Sat, 29 Mar 2008 22:35:21 -0400 Tom Lane wrote: I wrote: ... However, I seem to recall that in the discussions leading up to implementing DROP IF EXISTS, we considered and specifically rejected CREATE IF NOT EXISTS. Don't have time right now to troll the archives for the reasoning. [ back from dinner party... ] Here's the thread I was remembering: http://archives.postgresql.org/pgsql-hackers/2005-10/msg00632.php The key argument seems to be that it's quite unclear what the state following CREATE IF NOT EXISTS (CINE) should be, if the object does exist but not with the same properties specified in the CINE command. CREATE OR REPLACE resolves that by making it clear that it's gonna be what the command says. Tom: this answers my other question: if someone executes a REPLACE LANGUAGE and as example is using another handler, the new handler should replace the old one. Correct? So i will change my small patch and reimplement this extension with CREATE OR REPLACE. Thanks all for your useful answers. -- Andreas 'ads' Scherbaum German PostgreSQL User Group -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] create language ... if not exists
Andreas 'ads' Scherbaum wrote: The attached patch for HEAD extends the CREATE LANGUAGE statement by an IF NOT EXISTS option which in effect changes the raised error into a notice. Before i continue working on this patch i would like to know if this extension has a chance to go into PG and what other changes i should apply (beside the missing documentation). The way we've solved this problem for other CREATE commands is to add OR REPLACE option, instead of IF NOT EXISTS. We should do the same here. Regarding the patch itself: You define rule opt_if_not_exists, but never use it. And you add a new rule for CREATE LANGUAGE ... HANDLER ..., but forgot IF_P NOT EXISTS from the end of that. Looks like you couldn't decide which approach to take, and ended up doing a little bit of both ;-). -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] create language ... if not exists
Heikki Linnakangas wrote: Andreas 'ads' Scherbaum wrote: The attached patch for HEAD extends the CREATE LANGUAGE statement by an IF NOT EXISTS option which in effect changes the raised error into a notice. Before i continue working on this patch i would like to know if this extension has a chance to go into PG and what other changes i should apply (beside the missing documentation). The way we've solved this problem for other CREATE commands is to add OR REPLACE option, instead of IF NOT EXISTS. We should do the same here. My recollection is that we only do that where we need to for reasons of dependency. Not sure that applies here. cheers andrew -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] create language ... if not exists
Andrew Dunstan [EMAIL PROTECTED] writes: Heikki Linnakangas wrote: The way we've solved this problem for other CREATE commands is to add OR REPLACE option, instead of IF NOT EXISTS. We should do the same here. My recollection is that we only do that where we need to for reasons of dependency. Not sure that applies here. I was about to make the same complaint as Heikki. We currently have two different ways of dealing with this type of scenario: DROP IF EXISTS (for most object types) CREATE OR REPLACE (for functions, rules, views) The OP wants to introduce yet a third variant, implemented for only one kind of object. That's not a feature, it's a wart. Clearly DROP IF EXISTS isn't helpful for the proposed use-case (since you'd lose any pre-existing functions in the language) but I don't see why CREATE OR REPLACE wouldn't serve. regards, tom lane -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] create language ... if not exists
Heikki Linnakangas [EMAIL PROTECTED] writes: The way we've solved this problem for other CREATE commands is to add OR REPLACE option, instead of IF NOT EXISTS. We should do the same here. If we're willing to consider a solution that is specific to CREATE LANGUAGE (as opposed to implementing IF NOT EXISTS across-the-board, which might happen someday) what I'd suggest is just incorporating the behavior directly into the abbreviated (no parameters) form of CREATE LANGUAGE. If the language already exists and has the same properties specified in pg_pltemplate, don't raise an error. Give a notice maybe. One thing that's not too clear is whether that should happen before or after the privilege check: if a user who doesn't have the rights to create a language issues a CREATE, and the language already exists, should he get a no privilege error or an it already exists notice? regards, tom lane -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] create language ... if not exists
Tom Lane wrote: Heikki Linnakangas [EMAIL PROTECTED] writes: The way we've solved this problem for other CREATE commands is to add OR REPLACE option, instead of IF NOT EXISTS. We should do the same here. If we're willing to consider a solution that is specific to CREATE LANGUAGE (as opposed to implementing IF NOT EXISTS across-the-board, which might happen someday) what I'd suggest is just incorporating the behavior directly into the abbreviated (no parameters) form of CREATE LANGUAGE. If the language already exists and has the same properties specified in pg_pltemplate, don't raise an error. Give a notice maybe. Why not implement OR REPLACE like for other things? Still seems the most consistent behavior to me. You might want to get the error if the language already exists, which your proposal wouldn't allow. And it wouldn't help with languages without a pg_pltemplate entry. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] create language ... if not exists
Heikki Linnakangas wrote: Tom Lane wrote: Heikki Linnakangas [EMAIL PROTECTED] writes: The way we've solved this problem for other CREATE commands is to add OR REPLACE option, instead of IF NOT EXISTS. We should do the same here. If we're willing to consider a solution that is specific to CREATE LANGUAGE (as opposed to implementing IF NOT EXISTS across-the-board, which might happen someday) what I'd suggest is just incorporating the behavior directly into the abbreviated (no parameters) form of CREATE LANGUAGE. If the language already exists and has the same properties specified in pg_pltemplate, don't raise an error. Give a notice maybe. Why not implement OR REPLACE like for other things? Still seems the most consistent behavior to me. You might want to get the error if the language already exists, which your proposal wouldn't allow. And it wouldn't help with languages without a pg_pltemplate entry. Even though I was the guy originally suggesting that Andreas put forward a patch for IF NOT EXISTS, now that it's being mention I agree with Heikki - it's more consistent. And I see the primary use as being in installation scripts for software that requires pl/pgsql (or any other PL), for which the exact syntax really doesn't matter - it's better to be consistent. If we're implementing IF NOT EXISTS across the board, let's do that for languages at the same time as for others. //Magnus -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] create language ... if not exists
Magnus Hagander [EMAIL PROTECTED] writes: If we're implementing IF NOT EXISTS across the board, let's do that for languages at the same time as for others. Yeah, if we were going to do it at all it should be handled across-the-board, the way DROP IF EXISTS was. However, I seem to recall that in the discussions leading up to implementing DROP IF EXISTS, we considered and specifically rejected CREATE IF NOT EXISTS. Don't have time right now to troll the archives for the reasoning. regards, tom lane -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] create language ... if not exists
Tom Lane wrote: Magnus Hagander [EMAIL PROTECTED] writes: If we're implementing IF NOT EXISTS across the board, let's do that for languages at the same time as for others. Yeah, if we were going to do it at all it should be handled across-the-board, the way DROP IF EXISTS was. However, I seem to recall that in the discussions leading up to implementing DROP IF EXISTS, we considered and specifically rejected CREATE IF NOT EXISTS. Don't have time right now to troll the archives for the reasoning. Right. Which is one of the reasons why I'm suggesting we stick with the CREATE OR REPLACE for now. //Magnus -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
[PATCHES] create language ... if not exists
Hello all,
yesterday i ran into a small problem:
http://andreas.scherbaum.la/blog/archives/346-create-language-if-not-exist.html
and was bugged to create a patch for PostgreSQL. So here is a first
version, still missing some things like documentation.
The attached patch for HEAD extends the CREATE LANGUAGE statement by an
IF NOT EXISTS option which in effect changes the raised error into a
notice.
Before i continue working on this patch i would like to know if this
extension has a chance to go into PG and what other changes i should
apply (beside the missing documentation).
Thank you
--
Andreas 'ads' Scherbaum
German PostgreSQL User Group
Index: src/backend/commands/proclang.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/commands/proclang.c,v
retrieving revision 1.77
diff -u -3 -p -r1.77 proclang.c
--- src/backend/commands/proclang.c 27 Mar 2008 03:57:33 - 1.77
+++ src/backend/commands/proclang.c 29 Mar 2008 01:18:59 -
@@ -75,9 +75,20 @@ CreateProceduralLanguage(CreatePLangStmt
if (SearchSysCacheExists(LANGNAME,
PointerGetDatum(languageName),
0, 0, 0))
- ereport(ERROR,
-(errcode(ERRCODE_DUPLICATE_OBJECT),
- errmsg(language \%s\ already exists, languageName)));
+ {
+ if (!stmt-existing_ok)
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg(language \%s\ already exists, languageName)));
+ }
+ else
+ {
+ ereport(NOTICE,
+ (errmsg(language \%s\ already exists, skipping, languageName)));
+ return;
+ }
+ }
/*
* If we have template information for the language, ignore the supplied
Index: src/backend/parser/gram.y
===
RCS file: /projects/cvsroot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.611
diff -u -3 -p -r2.611 gram.y
--- src/backend/parser/gram.y 28 Mar 2008 00:21:55 - 2.611
+++ src/backend/parser/gram.y 29 Mar 2008 01:19:03 -
@@ -195,7 +195,7 @@ static Node *makeXmlExpr(XmlExprOp op, c
%type ival opt_lock lock_type cast_context
%type boolean opt_force opt_or_replace
opt_grant_grant_option opt_grant_admin_option
-opt_nowait opt_if_exists
+opt_nowait opt_if_exists opt_if_not_exists
%type list OptRoleList
%type defelt OptRoleElem
@@ -2529,6 +2529,7 @@ CreatePLangStmt:
n-plhandler = NIL;
n-plvalidator = NIL;
n-pltrusted = false;
+n-existing_ok = false;
$$ = (Node *)n;
}
| CREATE opt_trusted opt_procedural LANGUAGE ColId_or_Sconst
@@ -2540,6 +2541,30 @@ CreatePLangStmt:
n-plvalidator = $8;
n-pltrusted = $2;
/* LANCOMPILER is now ignored entirely */
+n-existing_ok = false;
+$$ = (Node *)n;
+ }
+ | CREATE opt_trusted opt_procedural LANGUAGE ColId_or_Sconst IF_P NOT EXISTS
+ {
+CreatePLangStmt *n = makeNode(CreatePLangStmt);
+n-plname = $5;
+/* parameters are all to be supplied by system */
+n-plhandler = NIL;
+n-plvalidator = NIL;
+n-pltrusted = false;
+n-existing_ok = true;
+$$ = (Node *)n;
+ }
+ | CREATE opt_trusted opt_procedural LANGUAGE ColId_or_Sconst
+ HANDLER handler_name opt_validator opt_lancompiler
+ {
+CreatePLangStmt *n = makeNode(CreatePLangStmt);
+n-plname = $5;
+n-plhandler = $7;
+n-plvalidator = $8;
+n-pltrusted = $2;
+/* LANCOMPILER is now ignored entirely */
+n-existing_ok = true;
$$ = (Node *)n;
}
;
@@ -4493,6 +4518,10 @@ opt_if_exists: IF_P EXISTS { $$ = T
| /*EMPTY*/{ $$ = FALSE; }
;
+opt_if_not_exists: IF_P NOT EXISTS { $$ = TRUE; }
+ | /*EMPTY*/{ $$ = FALSE; }
+ ;
+
/*
*
Index: src/include/nodes/parsenodes.h
===
RCS file: /projects/cvsroot/pgsql/src/include/nodes/parsenodes.h,v
retrieving revision 1.361
diff -u -3 -p -r1.361 parsenodes.h
--- src/include/nodes/parsenodes.h 21 Mar 2008 22:41:48 - 1.361
+++ src/include/nodes/parsenodes.h 29 Mar 2008 01:19:05 -
@@ -1262,6 +1262,7 @@ typedef struct CreatePLangStmt
List *plhandler; /* PL call handler function (qual. name) */
List *plvalidator; /* optional validator function (qual. name) */
bool pltrusted; /* PL is trusted */
+ bool existing_ok; /* skip error if already there? */
} CreatePLangStmt;
typedef struct DropPLangStmt
Index: src/interfaces/ecpg/preproc/preproc.y
===
RCS file: /projects/cvsroot/pgsql/src/interfaces/ecpg/preproc/preproc.y,v
retrieving revision 1.363
diff -u -3 -p -r1.363 preproc.y
--- src/interfaces/ecpg/preproc/preproc.y 27 Mar 2008 07:56:00 - 1.363
+++ src/interfaces/ecpg/preproc/preproc.y 29 Mar 2008 01:19:09 -
@@ -593,7 +593,7 @@ add_typedef(char *name, char * dimension
%type str CreatePLangStmt
