Re: create or replace language

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

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

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

Andrew Dunstan <andrew(at)dunslane(dot)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

Heikki Linnakangas <heikki(at)enterprisedb(dot)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

Tom Lane wrote:
> Heikki Linnakangas <heikki(at)enterprisedb(dot)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

Heikki Linnakangas wrote:
> Tom Lane wrote:
> > Heikki Linnakangas <heikki(at)enterprisedb(dot)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

Magnus Hagander <magnus(at)hagander(dot)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

Tom Lane wrote:
> Magnus Hagander <magnus(at)hagander(dot)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

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. 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

Hello Heikki,

On Sat, 29 Mar 2008 11:49:56 +0000 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

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

On Sat, 29 Mar 2008 22:35:21 -0400 Tom Lane wrote:

> 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.

Attached is a first version for the "CREATE OR REPLACE LANGUAGE" patch.
It's still missing some functionality (especially the update part is
far away from being complete) and it's also missing documentation.

I just want to know if i'm heading in the right direction or if
something is totally broken in my basic approach:

In case a language is already in pg_pltemplate, the (possibly changed)
values from this table are used to update the pg_languages entry. This
gives the ability to change the owner, trust status, the language or
validator handler.

In case the language is not in pg_pltemplate, the values from the
commandline are used, just like "create language".

Thanks & kind regards

--
Andreas 'ads' Scherbaum
German PostgreSQL User Group

"Andreas 'ads' Scherbaum" <adsmail(at)wars-nicht(dot)de> writes:
> Attached is a first version for the "CREATE OR REPLACE LANGUAGE" patch.
> It's still missing some functionality (especially the update part is
> far away from being complete) and it's also missing documentation.

It strikes me that if there are any existing functions in the language,
we might want to restrict what can be changed by CREATE OR REPLACE.
For instance switching to a completely different language handler
doesn't seem like a great idea.

The equivalent problem for views and functions is handled by restricting
CREATE OR REPLACE to not change the output column set of a view or the
type signature of a function, independently of whether there are any
actual references to the object. So maybe the right thing is that
CREATE OR REPLACE LANGUAGE can change "inessential" properties of an
existing language, but not the core properties --- which might only be
the handler function, though you could make a case for the validator and
the trusted flag as well.

regards, tom lane

Hello,

On Sat, 03 May 2008 13:34:05 -0400 Tom Lane wrote:

> So maybe the right thing is that CREATE OR REPLACE LANGUAGE can change
> "inessential" properties of an existing language, but not the core
> properties --- which might only be the handler function, though you
> could make a case for the validator and the trusted flag as well.

Already thought about that: exchanging the handler function or the
libbrary might only be useful in a developing environment, i don't see
other use cases here. The same is true for the validator (but a missing
validator could be added afterwards) and in my opinion i would prefer
not to change the trust flag - some functions may depend on this.

The name cannot be changed at all so only the owner and maybe the
validator is left ...

Did i miss something?

Kind regards

--
Andreas 'ads' Scherbaum
German PostgreSQL User Group

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> The equivalent problem for views and functions is handled by restricting
> CREATE OR REPLACE to not change the output column set of a view or the
> type signature of a function, independently of whether there are any
> actual references to the object. So maybe the right thing is that
> CREATE OR REPLACE LANGUAGE can change "inessential" properties of an
> existing language, but not the core properties --- which might only be
> the handler function, though you could make a case for the validator and
> the trusted flag as well.

I'm not so sure. What about if a PL language wants to include a version number
in the language handler? Or if a new version has to change the name for some
reason -- perhaps they discover that the old name doesn't work on some linkers
for some reason.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!

Gregory Stark <stark(at)enterprisedb(dot)com> writes:
> "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>> ... So maybe the right thing is that
>> CREATE OR REPLACE LANGUAGE can change "inessential" properties of an
>> existing language, but not the core properties --- which might only be
>> the handler function, though you could make a case for the validator and
>> the trusted flag as well.

> I'm not so sure. What about if a PL language wants to include a version number
> in the language handler? Or if a new version has to change the name for some
> reason -- perhaps they discover that the old name doesn't work on some linkers
> for some reason.

Not sure that I find those cases convincing. Remember that what CREATE
OR REPLACE LANGUAGE is going to be referring to is the handler
function's SQL-level name; there's already a layer of indirection
between it and link-level issues.

regards, tom lane

On Sat, 3 May 2008 21:12:51 +0200 Andreas 'ads' Scherbaum wrote:
> On Sat, 03 May 2008 13:34:05 -0400 Tom Lane wrote:
>
> > So maybe the right thing is that CREATE OR REPLACE LANGUAGE can change
> > "inessential" properties of an existing language, but not the core
> > properties --- which might only be the handler function, though you
> > could make a case for the validator and the trusted flag as well.
>
> Already thought about that: exchanging the handler function or the
> libbrary might only be useful in a developing environment, i don't see
> other use cases here. The same is true for the validator (but a missing
> validator could be added afterwards) and in my opinion i would prefer
> not to change the trust flag - some functions may depend on this.
>
> The name cannot be changed at all so only the owner and maybe the
> validator is left ...

Even the owner does not make sense, because it seems it is not possible
that the owner will changed through the SQL interface. ALTER LANGUAGE
already exists for this purpose and CREATE LANGUAGE has no option for
the language owner.

So do we want to replace any data (in my opinion only the validator is
left) at all or just skip any error message?

Kind regards

--
Andreas 'ads' Scherbaum
German PostgreSQL User Group

On Sat, 10 May 2008 09:36:26 +0200 Andreas 'ads' Scherbaum wrote:

> On Sat, 3 May 2008 21:12:51 +0200 Andreas 'ads' Scherbaum wrote:
> > On Sat, 03 May 2008 13:34:05 -0400 Tom Lane wrote:
> >
> > > So maybe the right thing is that CREATE OR REPLACE LANGUAGE can change
> > > "inessential" properties of an existing language, but not the core
> > > properties --- which might only be the handler function, though you
> > > could make a case for the validator and the trusted flag as well.
> >
> > Already thought about that: exchanging the handler function or the
> > libbrary might only be useful in a developing environment, i don't see
> > other use cases here. The same is true for the validator (but a missing
> > validator could be added afterwards) and in my opinion i would prefer
> > not to change the trust flag - some functions may depend on this.
> >
> > The name cannot be changed at all so only the owner and maybe the
> > validator is left ...
>
> Even the owner does not make sense, because it seems it is not possible
> that the owner will changed through the SQL interface. ALTER LANGUAGE
> already exists for this purpose and CREATE LANGUAGE has no option for
> the language owner.

Attached is another version of the patch (still missing documentation),
which changes the language owner on update (the owner can still be
changed in pg_pltemplate).

> So do we want to replace any data (in my opinion only the validator is
> left) at all or just skip any error message?

Anyone has an opinion here?

Kind regards

--
Andreas 'ads' Scherbaum
German PostgreSQL User Group

Andreas 'ads' Scherbaum wrote:
> Attached is another version of the patch (still missing documentation),
> which changes the language owner on update (the owner can still be
> changed in pg_pltemplate).

The other CREATE OR REPLACE commands don't change the owner, so CREATE
OR REPLACE LANGUAGE shouldn't do that either.

>> So do we want to replace any data (in my opinion only the validator is
>> left) at all or just skip any error message?

I think you should be able to change handler and validator functions,
and the trusted flag. Or is there a reason to not allow that?

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Thu, 15 May 2008 12:29:11 +0100 Heikki Linnakangas wrote:

> Andreas 'ads' Scherbaum wrote:
> > Attached is another version of the patch (still missing documentation),
> > which changes the language owner on update (the owner can still be
> > changed in pg_pltemplate).
>
> The other CREATE OR REPLACE commands don't change the owner, so CREATE
> OR REPLACE LANGUAGE shouldn't do that either.

It's possible that the language owner is changed in the meantime (in
pg_pltemplate). Since the owner cannot be changed from the "CREATE OR
REPLACE" syntax, a modified owner in the template table is the only
possibility where a new owner can came from. If "CREATE LANGUAGE"
find's a language entry in pg_pltemplate, it drops any data from the
commandline and uses the data from the template - so a new owner is
something which should be distributed along with the REPLACE.

> >> So do we want to replace any data (in my opinion only the validator is
> >> left) at all or just skip any error message?
>
> I think you should be able to change handler and validator functions,
> and the trusted flag. Or is there a reason to not allow that?

Message-ID: <9942(dot)1209836045(at)sss(dot)pgh(dot)pa(dot)us>
No other answer yet.

Kind regards

--
Andreas 'ads' Scherbaum
German PostgreSQL User Group