Re: [GENERAL] Updating column on row update

On Sun, Nov 22, 2009 at 11:38 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> On Sun, Nov 22, 2009 at 6:51 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> CREATE IF NOT EXISTS has been proposed and rejected before, more than
>>> once.  Please see the archives.
>
>> Search for CINE to find the discussions.  This is a good place to start:
>> http://archives.postgresql.org/pgsql-hackers/2009-05/msg00252.php
>
>> Despite Tom's assertions to the contrary, I am unable to find a clear
>> consensus against this feature in the archives,
>
> I think you didn't look back far enough --- that issue was settled years
> ago.  IIRC the killer argument is that after CINE you do not know the
> state of the object: it exists, yes, but what properties has it got?
> If it already existed then it's still got its old definition, which
> might or might not be what you're expecting.
>
> CREATE OR REPLACE has got far safer semantics from the viewpoint of a
> script that wants to bull through without having any actual error
> handling (which is more or less the scenario we are arguing here, no?)
> After successful execution of the command you know exactly what
> properties the object has got.

Sure.  I think that CINE only makes sense for objects for which COR
can't be implemented - things that have internal substructure, like
tables or tablespaces.  I agree that there are pitfalls for the unwary
but I still think it's better than nothing.  I understand that you
disagree.

> Whether it would be sensible to have CREATE OR REPLACE semantics for a
> language is something I'm not very clear on.  It seems like changing any
> of the properties of a pg_language entry could be rather fatal from the
> viewpoint of an existing function using the language.
>
> [ thinks for awhile... ]  Actually, CREATE LANGUAGE is unique among
> creation commands in that the common cases have no parameters, at least
> not since we added pg_pltemplate.  So you could imagine defining CINE
> for a language as disallowing any parameters and having these semantics:
>        * language not present -> create from template
>        * language present, matches template -> OK, do nothing
>        * language present, does not match template -> report error
> This would meet the objection of not being sure what the state is
> after successful execution of the command.  It doesn't scale to any
> other object type, but is it worth doing for this one type?

CREATE OR REPLACE seems like a better fit in this case.  For example,
it seems plausible that someone might want to add an inline handler to
a procedural language that didn't have one without dropping and
recreating the language.  Even changing the call handler seems like it
could be potentially useful in an upgrade scenario.

...Robert