Re: Materialized views don't show up in information_schema
От | Nicolas Barbier |
---|---|
Тема | Re: Materialized views don't show up in information_schema |
Дата | |
Msg-id | CAP-rdTba3y7eNiq3NFVh_PizuFGZGLWCcokVMHPWJ9ScQLzhXA@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: Materialized views don't show up in information_schema (Stephen Frost <sfrost@snowman.net>) |
Ответы |
Re: Materialized views don't show up in information_schema
|
Список | pgsql-hackers |
2014-10-18 Stephen Frost <sfrost@snowman.net>: > * Peter Eisentraut (peter_e@gmx.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?
В списке pgsql-hackers по дате отправления:
Следующее
От: Bruce MomjianДата:
Сообщение: Re: get_actual_variable_range vs idx_scan/idx_tup_fetch