Re: create or replace view

Tom Lane wrote:
> snpe <snpe(at)snpe(dot)co(dot)yu> writes:
>
>>On Thursday 14 November 2002 05:22 pm, Bruno Wolff III wrote:
>>
>>>Are you trying to save typing a few characters or what?
>>
>
>>Yes, it is 'create or replace view', not ?
>
>
> The statement was not invented to save a few characters of typing.
> It was invented to allow people to make internal changes to view
> definitions without breaking other objects that refer to the view.
>
> If we made it automatically drop and recreate the view then we'd
> be defeating the purpose.

It might just be me but it seems that this discussion is missing the
point if we believe this request is about saving some characters. I
don't think it is. I think it's about being able to write simple SQL
scripts that don't produce errors when you use the syntax below in an
adminstration or development script and the object doesn't exist:

drop...
create...

The accepted syntax in both PG and others for trying to avoiding this
issue is:

create or replace....

Using this syntax the database script will run without errors, quietly
adjusting the object definition as required. Perfect. That's what we want.

Now I'm only interpreting here and haven't run into this problem myself
in PG but it appears from some of the early posts on this subject that
PG isn't consistent in whether it will allow the change to occur, at
least with respect to views. Instead, PG apparently tries to "help" by
not updating the view if the views' result schema would be different,
hence the request (perhaps misguided by trying to specify "how" instead
of "what") to drop/create.

Assuming that's a correct assessment and summary of the problem then
reviewing the following use cases seems in order:

1. The view doesn't exist.

Action: create the new view

2. The view exists and the change can be determined to be benign,
presumably because the virtual table schema of the view retains the same
column specifications (names and types match original specification).

Action: replace the view "in situ" so that dependencies are ok

3. The view exists but the change isn't benign and it's clear that other
objects referencing the view are going to have issues since column
names, types, number, etc. are being changed.

Action 3: drop/create the view. Optionally we might consider doing a
NOTIFY "dependent object references" which might also work nicely in
other areas such as trigger functions etc.

Why drop/create? (or appropriate similar internal operation). A lot of
reasons actually.

First, this use case, by definition, says the new view's going to break
other objects -- and that this will be true regardless of whether I use
create-replace or drop/create. So not allowing create-replace to operate
as sugar changes nothing in terms of the resulting schema issues upon
statement completion. It has a big impact on my SQL though, since
drop/create may throw errors that create-replace won't. So we haven't
solved a problem by ignoring case #3. Instead we've continued to require
developers to use a syntax guaranteed to throw errors. Cool.

Second, if there are other objects depending on the view to look a
certain way, and I'm knowingly changing the view what can you infer? One
might choose to infer "The programmer's an idiot for wanting to break
his schema like this." I see far too much code written from this
attitude...it's what I hate about most M$ code. I prefer to infer that
"The programmer's a human being who might just be 10x smarter than
me...maybe I should let him do his job as he sees fit."

As an aside, this is the UNIX philosophy. Not only do we not try to
protect you from yourself by taking away all the guns (no command prompt
etc), we give you a fully loaded semi-automatic weapon (C, shell, etc)
with the safety off (root) and say "Be careful".

<soapbox>

So, instead of assuming that we know more about what's right than the
programmer, perhaps we should try assuming that the programmer's next
SQL script lines will adapt to the new view definition and make the
appropriate changes -- perhaps via a series of more create or replace
statements ;). A reasonable developer/DBA should know they're changing
the view in a way that isn't compatible with previously defined
dependents, just as they should realize dependencies may exist when they
alter schema in general. If not, then hey we told you to "Be careful".

The "create or replace" syntax, in my mind anyway, wasn't designed to
say "If you can create, do so. If you can replace, do so. If you have to
drop, tell the programmer to bite you" as implied by many of the posts
on this thread. It has a different goal, one of making the developer or
DBA's life easier (which occasionally means saving characters BTW. I
mean, if people weren't concerned about that how can you explain Unix or
Perl? ;) ).

If we're concerned with this change from a consistency perspective, look
at triggers. The programmer drops a function and the triggers relying on
that function go to hell. Sure, and if we said "you can't drop the
function because triggers might break" then it'd parallel what we're
saying here -- in effect "we know better than you do what you want". Or
to use M$ terminology "we know where you want to go today" ;).

Now, if I've misunderstood the problem here I just spent a lot of time
on a non-issue and wasted a lot of time, for which I apologize. But I
think the overall philosophy is reusable in any event. I bring it up
here because I've gotten a distinct sense of disrepect in some of the
replies on this thread and it disturbs me. If we have any goals for the
Postgres community they should include:

A. We want the programmer/DBA to have an easier time getting their job
done and anything we do to that end that is compatible with existing and
emerging standards is "a good thing". If PG is easier to use it'll get
used more.

B. We want to treat people who are interested in PostgreSQL with respect
at all times, keeping in mind that we communicate with them not only
through this forum, but through the code we write for them.

As a personal note, any time I see a response to my posts consisting of
"Why would you want to do that?" I automatically assume the author
simply left off the implied suffix of "you idiot". It's not a question
that I feel treats me with respect. I'm sure I'm not alone.

</soapbox>

ss

Scott Shattuck
Technical Pursuit Inc.