Thread: Materialized views don't show up in information_schema
Hi, I've been testing out some of the new materialized view functionality in 9.4 and noticed that they don't show up in the information_schema data dictionary views, specifically information_schema.tables or information_schema.views (sample output is below and it's the same for 9.3 as well). Is this on purpose or just an oversight? I think they should at least be in the information_schema.views view, though the case could be made that they should be in information_schema.tables as well (as they can have additional indexes). If they're added to the tables view then they should have a separate table_type (say, "MATERIALIZED VIEW"), similar to how foreign tables show up. Looking at the view definitions, it looks like it's just a matter of adding 'm' values to the IN clauses that filter pg_class.relkind. It doesn't look like they're used internally by anything other than tests. Only client apps that query the data dictionary views (ex: GUI clients or dynamic SQL generators) would be impacted. I'd argue it's for the better as they can they can now see those objects, even if they think they're regular tables or views. If this sound fine I can put together a patch for this. Regards, -- Sehrope Sarkuni postgres@vagrant-ubuntu-trusty-64:~$ psql test psql (9.4beta3) Type "help" for help. test=# CREATE TABLE some_table (x text); CREATE TABLE test=# CREATE MATERIALIZED VIEW some_mview AS SELECT * FROM some_table; SELECT 0 test=# CREATE VIEW some_view AS SELECT * FROM some_table; CREATE VIEW test=# \d some_mview Materialized view "public.some_mview"Column | Type | Modifiers --------+------+-----------x | text | test=# SELECT table_name, table_type FROM information_schema.tables WHERE table_schema = 'public';table_name | table_type ------------+------------some_table | BASE TABLEsome_view | VIEW (2 rows) test=# SELECT table_name, view_definition FROM information_schema.views WHERE table_schema = 'public';table_name | view_definition ------------+----------------------some_view | SELECT some_table.x+ | FROM some_table; (1 row)
Sehrope Sarkuni <sehrope@jackdb.com> writes: > I've been testing out some of the new materialized view functionality > in 9.4 and noticed that they don't show up in the information_schema > data dictionary views, specifically information_schema.tables or > information_schema.views (sample output is below and it's the same for > 9.3 as well). > Is this on purpose or just an oversight? It's on purpose. The information_schema can only show objects that exist in the SQL standard. regards, tom lane
* Tom Lane (tgl@sss.pgh.pa.us) wrote: > Sehrope Sarkuni <sehrope@jackdb.com> writes: > > I've been testing out some of the new materialized view functionality > > in 9.4 and noticed that they don't show up in the information_schema > > data dictionary views, specifically information_schema.tables or > > information_schema.views (sample output is below and it's the same for > > 9.3 as well). > > > Is this on purpose or just an oversight? > > It's on purpose. The information_schema can only show objects that > exist in the SQL standard. I'm not particularly thrilled with this answer. I'd aruge that the 'materialized' part of mat views isn't relevant to the standard, which does not concern itself with such performance-oriented considerations, and therefore, to the standard's view (pun rather intended), they're views. Thanks, Stephen
On 10/10/14 6:53 PM, Stephen Frost wrote: > I'm not particularly thrilled with this answer. I'd aruge that the > 'materialized' part of mat views isn't relevant to the standard, which > does not concern itself with such performance-oriented considerations, > and therefore, to the standard's view (pun rather intended), they're > views. For example, you can't drop a materialized view with DROP VIEW. So any tool that offers a list of views to manipulate based on the information schema would be confused. This is different from temporary views, for example.
* Peter Eisentraut (peter_e@gmx.net) wrote: > On 10/10/14 6:53 PM, Stephen Frost wrote: > > I'm not particularly thrilled with this answer. I'd aruge that the > > 'materialized' part of mat views isn't relevant to the standard, which > > does not concern itself with such performance-oriented considerations, > > and therefore, to the standard's view (pun rather intended), they're > > views. > > For example, you can't drop a materialized view with DROP VIEW. So any > tool that offers a list of views to manipulate based on the information > schema would be confused. This is different from temporary views, for > example. And users will be confused when using a tool which doesn't see mat views, which is what started this thread. Overall, I'm inclined to view materialized views as a subset of views, which would mean that we'd perhaps add the ability to drop them with 'drop view'. As a comparison, what about unlogged tables? They're not normal tables and they aren't defined by the SQL standard either. Thanks, Stephen
Another example is an ETL tool or ORM that queries the data dictionary to generate SQL or object models. If mviews show up in the regular information_schema views (specifically the .columns view) then it'll just work as if it'sa regular table or view (ex: like foreign tables). If not, it's possible to use the internal pg_ catalog tables to get the column details but it'd require PG specific coding. In our case I already plan on using the internal catalog tables to display mview metadata. I brought this up thinking aboutexisting apps and tools being able to work with mviews without additional code changes. Regards, -- Sehrope Sarkuni
On 10/10/14 8:44 PM, Stephen Frost wrote: > As a comparison, what about unlogged tables? They're not normal tables > and they aren't defined by the SQL standard either. They are normal tables when considered within the scope of the SQL standard. The only difference to normal tables is their crash recovery behavior, which is outside the scope of the SQL standard.
* Peter Eisentraut (peter_e@gmx.net) wrote: > On 10/10/14 8:44 PM, Stephen Frost wrote: > > As a comparison, what about unlogged tables? They're not normal tables > > and they aren't defined by the SQL standard either. > > They are normal tables when considered within the scope of the SQL > standard. The only difference to normal tables is their crash recovery > behavior, which is outside the scope of the SQL standard. Alright, coming back to this, I have to ask- how are matviews different from views from the SQL standard's perspective? I tried looking through the standard to figure it out (and I admit that I probably missed something), but the only thing appears to be a statement in the standard that (paraphrased) "functions are run with the view is queried" and that strikes me as a relatively minor point.. I'm also curious how other databases address this.. Do none of them put matviews into information_schema? Thanks! Stephen
2014-10-16 Stephen Frost <sfrost@snowman.net>: > Alright, coming back to this, I have to ask- how are matviews different > from views from the SQL standard's perspective? Matviews that are always up to date when you access them are semantically exactly the same as normal views. Matviews that can get out of date, however, are not. Nicolas -- A. Because it breaks the logical sequence of discussion. Q. Why is top posting bad?
Nicolas Barbier wrote > 2014-10-16 Stephen Frost < > sfrost@ > >: > >> Alright, coming back to this, I have to ask- how are matviews different >> from views from the SQL standard's perspective? > > Matviews that are always up to date when you access them are > semantically exactly the same as normal views. Matviews that can get > out of date, however, are not. Materialized Views share features and properties of both tables and views - and omit capabilities available to both as well. The performance optimization spoken of is basically the table aspect of the feature while the backing query makes it look like a view. But all the while it is a distinct feature and one not described in the SQL standard. From a read-only perspective I can see the value of having this particular row-source available in the standard information schema but anything trying to manipulate a matview as either a view or a table will be surprised. Since the standard doesn't distinguish between read and write aspects of the object types there isn't a safe way to add matviews to the information schema that doesn't violate the intent of the provided view. If the application/users wants to support/use PostgreSQL specific features it/they have to be ready and able to use the catalog. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Materialized-views-don-t-show-up-in-information-schema-tp5822643p5823379.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
On 10/16/14 9:45 AM, Stephen Frost wrote: > Alright, coming back to this, I have to ask- how are matviews different > from views from the SQL standard's perspective? I tried looking through > the standard to figure it out (and I admit that I probably missed > something), but the only thing appears to be a statement in the standard > that (paraphrased) "functions are run with the view is queried" and that > strikes me as a relatively minor point.. To me, the main criterion is that you cannot DROP VIEW a materialized view. Generally, if the information schema claims that a view/table/function/etc. named "foo" exists, then I should be able to operate on "foo" using the basic operations for a view/table/function/etc. of that name. I think think DROP VIEW is a basic operation for a view. Others might disagree. 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. All of this is a judgement call in corner cases. But I don't think this is a corner case at all.
On 10/17/14, 4:31 AM, David G Johnston wrote: > Since the standard doesn't distinguish between read and write aspects of the > object types there isn't a safe way to add matviews to the information > schema that doesn't violate the intent of the provided view. If the > application/users wants to support/use PostgreSQL specific features it/they > have to be ready and able to use the catalog. +1. If we add matviews to information_schema while they're not part of that standard then we're going to regret it at somepoint. Perhaps the answer to this problem is to restart the old pg_newsysviews project. -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.com
* Nicolas Barbier (nicolas.barbier@gmail.com) wrote: > 2014-10-16 Stephen Frost <sfrost@snowman.net>: > > > Alright, coming back to this, I have to ask- how are matviews different > > from views from the SQL standard's perspective? > > Matviews that are always up to date when you access them are > semantically exactly the same as normal views. Matviews that can get > out of date, however, are not. And when we have matviews which can be kept up to date..? Thanks, Stephen
* Peter Eisentraut (peter_e@gmx.net) wrote: > On 10/16/14 9:45 AM, Stephen Frost wrote: > > Alright, coming back to this, I have to ask- how are matviews different > > from views from the SQL standard's perspective? I tried looking through > > the standard to figure it out (and I admit that I probably missed > > something), but the only thing appears to be a statement in the standard > > that (paraphrased) "functions are run with the view is queried" and that > > strikes me as a relatively minor point.. > > To me, the main criterion is that you cannot DROP VIEW a materialized view. That is an entirely correctable situation. We don't require 'DROP UNLOGGED TABLE'. > Generally, if the information schema claims that a > view/table/function/etc. named "foo" exists, then I should be able to > operate on "foo" using the basic operations for a > view/table/function/etc. of that name. I think think DROP VIEW is a > basic operation for a view. Others might disagree. This strikes me as a reason to allow DROP VIEW and perhaps other operations against a matview, not as a reason why matviews aren't views. > 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. Thanks, Stephen
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?
> 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? I can only speak for Oracle. Oracle doesn't have INFORMATION_SCHEMA but their JDBC driver treats mviews as tables. If you ask the driver for a list of tables (explicitly passing "TABLE" as the only type to return), it will include mviews as well, returning "MATERIALIZED VIEW" as the type of the object. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Materialized-views-don-t-show-up-in-information-schema-tp5822643p5823559.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
On Fri, Oct 17, 2014 at 8:10 PM, Stephen Frost <sfrost@snowman.net> wrote: > * Peter Eisentraut (peter_e@gmx.net) wrote: >> On 10/16/14 9:45 AM, Stephen Frost wrote: >> > Alright, coming back to this, I have to ask- how are matviews different >> > from views from the SQL standard's perspective? I tried looking through >> > the standard to figure it out (and I admit that I probably missed >> > something), but the only thing appears to be a statement in the standard >> > that (paraphrased) "functions are run with the view is queried" and that >> > strikes me as a relatively minor point.. >> >> To me, the main criterion is that you cannot DROP VIEW a materialized view. > > That is an entirely correctable situation. We don't require 'DROP > UNLOGGED TABLE'. I think that's an inapposite comparison. The fact that a table is unlogged is merely a property of the table; it does not change the fact that it is a table. A materialized view, on the other hand, is different kind of object from a view. This manifests itself the fact that it's represented by a different relkind; and that different syntax is used not only for DROP but also for COMMENT, ALTER VIEW, SECURITY LABEL, and ALTER EXTENSION .. ADD/DROP; and that the set of supported operations on a materialized view is different from a regular view (and will probably be more different in the future). If we really want to change this, we can't just change DROP VIEW; we need to change all of the places in a consistent fashion, and we probably have to continue to support the old syntax so that we don't break existing dumps. But I think it's the wrong thing anyway, because it presumes that, when Kevin chose to make materialized views a different relkind and a different object type, rather than just a property of an object, he made the wrong call, and I don't agree with that. I think he got it exactly right. A materialized view is really much more like a table than a view: it has storage and can be vacuumed, clustered, analyzed, and so on. That's far more significant IMV than the difference between a table and unlogged table. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
* Robert Haas (robertmhaas@gmail.com) wrote: > On Fri, Oct 17, 2014 at 8:10 PM, Stephen Frost <sfrost@snowman.net> wrote: > > * Peter Eisentraut (peter_e@gmx.net) wrote: > >> On 10/16/14 9:45 AM, Stephen Frost wrote: > >> > Alright, coming back to this, I have to ask- how are matviews different > >> > from views from the SQL standard's perspective? I tried looking through > >> > the standard to figure it out (and I admit that I probably missed > >> > something), but the only thing appears to be a statement in the standard > >> > that (paraphrased) "functions are run with the view is queried" and that > >> > strikes me as a relatively minor point.. > >> > >> To me, the main criterion is that you cannot DROP VIEW a materialized view. > > > > That is an entirely correctable situation. We don't require 'DROP > > UNLOGGED TABLE'. > > I think that's an inapposite comparison. The fact that a table is > unlogged is merely a property of the table; it does not change the > fact that it is a table. A materialized view, on the other hand, is > different kind of object from a view. This manifests itself the fact > that it's represented by a different relkind; and that different > syntax is used not only for DROP but also for COMMENT, ALTER VIEW, > SECURITY LABEL, and ALTER EXTENSION .. ADD/DROP; and that the set of > supported operations on a materialized view is different from a > regular view (and will probably be more different in the future). If > we really want to change this, we can't just change DROP VIEW; we need > to change all of the places in a consistent fashion, and we probably > have to continue to support the old syntax so that we don't break > existing dumps. Yes, clearly we'd have to adjust the syntax for all of the commands to support both with MATERIALIZED and without. I don't have an issue with that. The argument makes more sense if there are operations which ONLY work against a regular view and do *not* work against a matview. Operations which operate only against a matview simply must have MATERIALIZED used for them. > But I think it's the wrong thing anyway, because it presumes that, > when Kevin chose to make materialized views a different relkind and a > different object type, rather than just a property of an object, he > made the wrong call, and I don't agree with that. I think he got it > exactly right. A materialized view is really much more like a table > than a view: it has storage and can be vacuumed, clustered, analyzed, > and so on. That's far more significant IMV than the difference > between a table and unlogged table. I don't think Kevin was wrong to use a different relkind, but I don't buy into the argument that a different relkind means it's not a view. As for the other comments, I agree that a matview is *more* than a view, but at its base, in my view (pun intended), it's still a view. Why not call it a materialized query? Thanks, Stephen
On Mon, Oct 27, 2014 at 11:45 AM, Stephen Frost <sfrost@snowman.net> wrote: >> But I think it's the wrong thing anyway, because it presumes that, >> when Kevin chose to make materialized views a different relkind and a >> different object type, rather than just a property of an object, he >> made the wrong call, and I don't agree with that. I think he got it >> exactly right. A materialized view is really much more like a table >> than a view: it has storage and can be vacuumed, clustered, analyzed, >> and so on. That's far more significant IMV than the difference >> between a table and unlogged table. > > I don't think Kevin was wrong to use a different relkind, but I don't > buy into the argument that a different relkind means it's not a view. > As for the other comments, I agree that a matview is *more* than a view, > but at its base, in my view (pun intended), it's still a view. Why not > call it a materialized query? Your view seems very odd to me. The access characteristics of a materialized view are completely unlike those of a view, and completely like those of a table. It has storage, and maybe indexes. Trying to say that it's the same kind of an object as something that has neither seems really odd. The overlap between the operations you can do on a materialized view and those you can do on a view is really pretty small. It might have been better if the database industry had settled on some name for this kind of object that didn't reuse the word "view", but at this point I think we're stuck with the fact that adding the word "materialized" makes it into a completely different kind of object. You wouldn't expect to find "butter" and "peanut butter" in the same aisle at the supermarket.... -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
* Robert Haas (robertmhaas@gmail.com) wrote: > On Mon, Oct 27, 2014 at 11:45 AM, Stephen Frost <sfrost@snowman.net> wrote: > > I don't think Kevin was wrong to use a different relkind, but I don't > > buy into the argument that a different relkind means it's not a view. > > As for the other comments, I agree that a matview is *more* than a view, > > but at its base, in my view (pun intended), it's still a view. Why not > > call it a materialized query? > > Your view seems very odd to me. The access characteristics of a > materialized view are completely unlike those of a view, and > completely like those of a table. It has storage, and maybe indexes. I agree with this, certainly, but these are not considerations that the SQL spec takes into account. I've always found it odd of the spec to avoid these considerations and concerns, but it is the spec and it's viewpoint that we're discussing. > Trying to say that it's the same kind of an object as something that > has neither seems really odd. The overlap between the operations you > can do on a materialized view and those you can do on a view is really > pretty small. ... That overlap is exactly the set that you can do on *just* a view, no? That's what I was driving towards anyway. > It might have been better if the database industry had settled on some > name for this kind of object that didn't reuse the word "view", but at > this point I think we're stuck with the fact that adding the word > "materialized" makes it into a completely different kind of object. What would have been great is if the spec had decided to address these issues, but I don't expect that to change. > You wouldn't expect to find "butter" and "peanut butter" in the same > aisle at the supermarket.... No, though they are both spreadable and tasty. :) Thanks! Stephen
On Wed, Oct 29, 2014 at 1:26 PM, Stephen Frost <sfrost@snowman.net> wrote: > I agree with this, certainly, but these are not considerations that the > SQL spec takes into account. I've always found it odd of the spec to > avoid these considerations and concerns, but it is the spec and it's > viewpoint that we're discussing. I don't think you can fairly infer anything about how an object not covered by the spec should be displayed in a spec-compliant view. >> Trying to say that it's the same kind of an object as something that >> has neither seems really odd. The overlap between the operations you >> can do on a materialized view and those you can do on a view is really >> pretty small. > > ... That overlap is exactly the set that you can do on *just* a view, > no? That's what I was driving towards anyway. No. Materialized views don't have column defaults, and marking them security_barrier does nothing. >> You wouldn't expect to find "butter" and "peanut butter" in the same >> aisle at the supermarket.... > > No, though they are both spreadable and tasty. :) Sir, you are a gentleman and a scholar. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
* Robert Haas (robertmhaas@gmail.com) wrote: > On Wed, Oct 29, 2014 at 1:26 PM, Stephen Frost <sfrost@snowman.net> wrote: > > I agree with this, certainly, but these are not considerations that the > > SQL spec takes into account. I've always found it odd of the spec to > > avoid these considerations and concerns, but it is the spec and it's > > viewpoint that we're discussing. > > I don't think you can fairly infer anything about how an object not > covered by the spec should be displayed in a spec-compliant view. The characteristics which make mat views different from views are specifically those that the spec actively avoids discussing at all. > >> Trying to say that it's the same kind of an object as something that > >> has neither seems really odd. The overlap between the operations you > >> can do on a materialized view and those you can do on a view is really > >> pretty small. > > > > ... That overlap is exactly the set that you can do on *just* a view, > > no? That's what I was driving towards anyway. > > No. Materialized views don't have column defaults, and marking them > security_barrier does nothing. I'm a bit confused by this- views have column defaults? I agree that marking them security_barrier does nothing and that bit is interesting but it also strikes me as a relatively minor point (nothing which is trying to go by what's in information_schema is going to be looking at if it's a security_barrier view or not..). In any case, I don't feel this is really urgent and we don't seem to be making much progress, so perhaps it'd be best to just chat about it sometime in Ottawa or NYC and find common ground over a pint. :) > >> You wouldn't expect to find "butter" and "peanut butter" in the same > >> aisle at the supermarket.... > > > > No, though they are both spreadable and tasty. :) > > Sir, you are a gentleman and a scholar. hahaha. This made me laugh. Many thanks. :) Stephen
On Wed, Oct 29, 2014 at 1:57 PM, Stephen Frost <sfrost@snowman.net> wrote: >> No. Materialized views don't have column defaults, and marking them >> security_barrier does nothing. > > I'm a bit confused by this- views have column defaults? Yep. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company