SEO

Hello all,

yesterday i ran into a small problem:
http://andreas.scherbaum.la/blog/arc...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


--
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches

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

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

Andrew Dunstan <andrew@dunslane.net> 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

Heikki Linnakangas <heikki@enterprisedb.com> 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

Tom Lane wrote:
> Heikki Linnakangas <heikki@enterprisedb.com> 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

Heikki Linnakangas wrote:
> Tom Lane wrote:
> > Heikki Linnakangas <heikki@enterprisedb.com> 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

Magnus Hagander <magnus@hagander.net> 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

Tom Lane wrote:
> Magnus Hagander <magnus@hagander.net> 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

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...0/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. Perhaps there is a use-case for the alternate
behavior where the pre-existing object doesn't get modified, but I'm
not too sure what it would be.

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