Isaac Morland <isaac.morland@gmail.com> writes: > This sounds like CREATE OR REPLACE to me, even if there is something to > explain in the documentation about what happens to the existing data > (assuming it is currently populated). I don't think of CREATE OR REPLACE as > completely re-creating an object anyhow. Certainly it can't arbitrarily > replace a function (can't change return type) nor a view (can't remove or > change type of a column). It's OK if not all possible changes are supported > by the "OR REPLACE" part of the command.
To my mind, the formal requirement for CREATE OR REPLACE should be "if the command succeeds, the resulting object properties are identical to what they'd be if we were creating it fresh" -- basically a form of idempotency. It's okay to fail when there are reasons why we can't or shouldn't make that so.
Yes, I can see the logic of this.
On the other hand, what do we mean by "properties of the object"? If we mean "what pg_dump would dump", I don't believe the data stored in a materialized view affect what gets dumped.
Perhaps there could be an option — "WITH EXISTING DATA". I'm actually a bit skeptical of how often this would be wanted. If I’ve updated the query that defines the contents of the view, why would I still want the old data that were computed used the old query? I can think of lots of scenarios, actually, but conceptually it doesn't feel like the safe default as after updating the query the naive expectation is that the contents have been updated.
In particular, ISTM that if we invent CREATE OR REPLACE MATERIALIZED VIEW, then the view content should be either computed afresh or left empty (depending on WITH NO DATA); it should never leave stale data. But I think Robert is correct that an ALTER command that does keep the old data is often going to be what's wanted.
In any case, I'm not too hung up on what the syntax looks like or the exact behaviour. But I do think if we're going to do something like this that there should be a command which will create the view if it doesn't exist, and update the query if it does. Otherwise it doesn't help at all with writing idempotent schema definitions, which is my main reason for using CREATE OR REPLACE.
