Re: Materialized views don't show up in information_schema

2014-10-18 Stephen Frost <sfrost(at)snowman(dot)net>:

> * Peter Eisentraut (peter_e(at)gmx(dot)net) wrote:
>
>> More subtly, if we claim that a materialized view is a view, then we
>> cannot have asynchronously updated materialized views, because then we
>> have different semantics.
>
> This is, at least, a reason I can understand, though I'm not sure I see
> it as sufficient to say matviews are so different from views that they
> shouldn't be listed as such.

Maybe it's useful to try to imagine who the actual consumers of
INFORMATION_SCHEMA are? It's probably mostly generic SQL tools that
try to provide a list of objects with corresponding operations (drop,
alter, dump to a file, show contents, etc) or tools that provide
syntax completion.

I can only imagine two practical (though rather hypothetical) problems
caused by the semantical mismatch between normal views and
possibly-outdated matviews:

(1) Accidentally seeing outdated data: Something tries to replace part
of a query with a reference to a matview, because INFORMATION_SCHEMA
says that the definition of the view is such-and-so. This causes the
resulting query to possibly see outdated data.

(2) Accidentally seeing data that is "too new": Something replaces a
reference to a matview in a query with the defining query of the
matview. This causes the resulting query to possibly see data that is
"too new", assuming that the original query author is trying to rely
on the outdatedness of the matview in the vein of "I want to see
yesterday's data". I personally consider "relying on the outdatedness
of a matview" to be bad design; Maybe that should be mentioned in the
documentation if I'm not the only one thinking that way.

(Note that (2) also happens when a generic SQL tool regenerates a
schema by recreating a matview as a normal view. The resulting normal
view seems to contain data that is "too new".)

Those problems sound so far-fetched, that I suggest putting matviews
(even though they may be out-of-date) in INFORMATION_SCHEMA.VIEWS as
if they were normal views, so that in all other use cases (i.e., the
abovementioned generic SQL tools), the right thing happens. It is
probably useful to put them in INFORMATION_SCHEMA.TABLES with a
specialized type MATERIALIZED VIEW (or somesuch), so that tools that
know about the existence of matviews know how to make the difference.

Does someone know what other DBMSs do in this regard? I.e., do they
put anything in INFORMATION_SCHEMA.VIEWS for matviews? What TABLE_TYPE
do they use in INFORMATION_SCHEMA.TABLES?

Nicolas

--
A. Because it breaks the logical sequence of discussion.
Q. Why is top posting bad?