Re: 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(at)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 TABLE
some_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(at)jackdb(dot)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(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> Sehrope Sarkuni <sehrope(at)jackdb(dot)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(at)gmx(dot)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's a 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 about existing 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(at)gmx(dot)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(at)snowman(dot)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 &lt;

> sfrost@

> &gt;:
>
>> 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 some point.

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(dot)barbier(at)gmail(dot)com) wrote:
> 2014-10-16 Stephen Frost <sfrost(at)snowman(dot)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(at)gmx(dot)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(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?

> 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(at)snowman(dot)net> wrote:
> * Peter Eisentraut (peter_e(at)gmx(dot)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(at)gmail(dot)com) wrote:
> On Fri, Oct 17, 2014 at 8:10 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> > * Peter Eisentraut (peter_e(at)gmx(dot)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(at)snowman(dot)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(at)gmail(dot)com) wrote:
> On Mon, Oct 27, 2014 at 11:45 AM, Stephen Frost <sfrost(at)snowman(dot)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(at)snowman(dot)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(at)gmail(dot)com) wrote:
> On Wed, Oct 29, 2014 at 1:26 PM, Stephen Frost <sfrost(at)snowman(dot)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(at)snowman(dot)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