CREATE OR REPLACE MATERIALIZED VIEW

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

CREATE OR REPLACE MATERIALIZED VIEW

Aidan Samuel
I've been looking over the syntax for creating various database objects: tables, views, functions, etc., and I wondered why there seem to be some discrepancies.

For example, table creation syntax features [ IF NOT EXISTS ] but doesn't feature [ OR REPLACE ], function creation syntax is the inverse and has [ OR REPLACE ], but no [ IF NOT EXISTS ].

I also noticed that the syntax for creating VIEWS and creating MATERIALISED VIEWS is handled separately [1][2].

I was expecting to see one page of documentation with the syntax listed as something like: CREATE [ OR REPLACE ] [ MATERIALIZED ] VIEW [ IF NOT EXISTS ] view_name.

Is this because a materialized view is actually a table and thus inherits the table creation syntax instead of the view creation syntax?

How come table creation doesn't allow [ OR REPLACE ], and view creation doesn't allow [ IF NOT EXISTS ]? Is this just how the SQL spec defines things?

Thanks,
Aidan.


Reply | Threaded
Open this post in threaded view
|

Re: CREATE OR REPLACE MATERIALIZED VIEW

Tom Lane-2
Aidan Samuel <[hidden email]> writes:
> How come table creation doesn't allow [ OR REPLACE ], and view creation
> doesn't allow [ IF NOT EXISTS ]? Is this just how the SQL spec defines
> things?

I think all that stuff is our own fault rather than something you can
find in the SQL spec.

For indivisible database objects such as functions, IMV the "or replace"
semantics (COR) is better than the "if not exists" semantics (CINE).
With COR, if the command succeeds then you know what properties the
object has: the ones you just gave.  With CINE, you don't really know
anything at all except that there's an object by that name.  So we've
generally preferred to implement COR if practical.

For tables, though, the tradeoffs seem pretty different.  In particular,
COR semantics would more or less imply throwing away the table contents,
and probably dropping existing foreign-key connections too, since none of
that could be inferred from the given command.  It's a little scary to
do that implicitly.  So we insist that if you really want to lose data,
you say DROP TABLE explicitly, and then you can create a fresh empty
table.

(Personally I think CINE is seldom anything but a foot-gun, and would
rather we didn't have it at all; but some folks insisted.)

> I also noticed that the syntax for creating VIEWS and creating MATERIALISED
> VIEWS is handled separately [1][2].

Again, those aren't really the same thing: a matview is a lot more like a
table than it is like a view, so we don't treat it the same.  I think
if you compare the text for the CREATE VIEW and CREATE MATVIEW man pages,
you'll agree that a merged version would be pretty confusing.  All the
options are different.

Admittedly, this is all judgment calls and experience, but that's
why it's like that.

                        regards, tom lane

Reply | Threaded
Open this post in threaded view
|

Re: CREATE OR REPLACE MATERIALIZED VIEW

Aidan Samuel
On Thu, 10 Jan 2019 at 12:13, Tom Lane <[hidden email]> wrote:
> For indivisible database objects such as functions, IMV the "or replace"
> semantics (COR) is better than the "if not exists" semantics (CINE)...
>
> For tables, though, the tradeoffs seem pretty different...

Thanks Tom, your explanation makes a lot of sense.