On Mon, Feb 18, 2013 at 4:48 PM, Kevin Grittner kgri...@ymail.com wrote:
This should allow me to simplify the code a little bit and move the
RMV step to the very end. That may have some advantages when users
want to start using the database while MVs are being populated.
In the department of
2013/2/19 Robert Haas robertmh...@gmail.com:
In the department of crazy ideas, what about having pg_dump NEVER
refresh ANY materialized views?
It's true that the job of pg_dump and pg_restore is to put the new
database in the same state that the old database was in, but I think
you could
On 2/19/13 8:54 AM, Robert Haas wrote:
In the department of crazy ideas, what about having pg_dump NEVER
refresh ANY materialized views?
It might be useful to have an option for this, but I don't think it
should be the default. The default should be that the new database is
ready to go.
Then
On Sat, February 16, 2013 02:01, Kevin Grittner wrote:
matview-v4.patch.gz
Hi,
I was wondering if material views should not go into information_schema. I was
thinking either
.views or .tables. Have you considered this?
I ask because as far as I can see querying for mv's has to go like this:
On 02/19/2013 02:09 PM, Erik Rijkers wrote:
On Sat, February 16, 2013 02:01, Kevin Grittner wrote:
matview-v4.patch.gz
Hi,
I was wondering if material views should not go into information_schema. I
was thinking either
.views or .tables. Have you considered this?
I ask because as
On Tue, Feb 19, 2013 at 11:09:13PM +0100, Erik Rijkers wrote:
On Sat, February 16, 2013 02:01, Kevin Grittner wrote:
matview-v4.patch.gz
Hi,
I was wondering if material views should not go into information_schema. I
was thinking either
.views or .tables. Have you considered this?
Erik Rijkers e...@xs4all.nl wrote:
I was wondering if material views should not go into
information_schema. I was thinking either .views or .tables.
Have you considered this?
I had not considered this to be a good idea because
information_schema is defined by the standard, and materialized
Josh Berkus j...@agliodbs.com wrote:
Well, I'm not sure about information_schema, but we'll definitely
want a pg_matviews system view.
That could wait until 9.4, though.
That I could probably do. Do you think they should have a separate
pg_stat_user_matviews table, etc., or do you think it
That I could probably do. Do you think they should have a separate
pg_stat_user_matviews table, etc., or do you think it would be
better to include them in with tables there?
Well, ideally pg_matviews would have matview definitions, and
pg_stat_matviews would have stats on matview usage and
Kevin Grittner kgri...@ymail.com
There was one minor syntax issue not addressed by Noah, nor much
discussed in general that I didn't want to just unilaterally
choose; but given that nobody seems to care that much I will put
forward a proposal and do it that way tomorrow if nobody objects.
Josh Berkus j...@agliodbs.com wrote:
That I could probably do. Do you think they should have a separate
pg_stat_user_matviews table, etc., or do you think it would be
better to include them in with tables there?
Well, ideally pg_matviews would have matview definitions, and
pg_stat_matviews
Kevin Grittner kgri...@ymail.com wrote:
I'm attaching the patch for just the system_views.sql file
for discussion before I go write docs for this part.
Meh. If I'm gonna have pg_matviews I might as well include an
isscannable column. v2 attached.
--
Kevin Grittner
EnterpriseDB:
On 02/19/2013 03:41 PM, Kevin Grittner wrote:
Kevin Grittner kgri...@ymail.com wrote:
I'm attaching the patch for just the system_views.sql file
for discussion before I go write docs for this part.
Meh. If I'm gonna have pg_matviews I might as well include an
isscannable column. v2
Kevin Grittner escribió:
I'm OK with that approach, and in the absence of anyone pushing for
another direction, will make that change to pg_dump. I'm thinking
I would only do this for materialized views which were not
scannable, but which cause REFRESH failures on other materialized
views
Alvaro Herrera alvhe...@2ndquadrant.com wrote:
Kevin Grittner escribió:
I'm OK with that approach, and in the absence of anyone pushing
for another direction, will make that change to pg_dump. I'm
thinking I would only do this for materialized views which were
not scannable, but which cause
Thom Brown t...@linux.com wrote:
On 16 February 2013 01:01, Kevin Grittner kgri...@ymail.com wrote:
Unless something else comes up in review or I get feedback to
the contrary I plan to deal with the above-mentioned issues and
commit this within a week or two.
At the moment it's not possible
On Mon, Feb 18, 2013 at 06:49:14AM -0800, Kevin Grittner wrote:
Alvaro Herrera alvhe...@2ndquadrant.com wrote:
Maybe it would be a good idea to try to put such commands at the
very end of the dump, if possible.
25, /* DO_POST_DATA_BOUNDARY */
26,
Noah Misch n...@leadboat.com wrote:
On Mon, Feb 18, 2013 at 06:49:14AM -0800, Kevin Grittner wrote:
Alvaro Herrera alvhe...@2ndquadrant.com wrote:
Maybe it would be a good idea to try to put such commands at
the very end of the dump, if possible.
25, /*
Noah Misch n...@leadboat.com wrote:
On Sat, Feb 16, 2013 at 09:53:14AM -0800, Kevin Grittner wrote:
I agree that making the dump fail on this account is bad.
I would argue that this is an overstatement of the issue except for
restores that use the single-transaction switch and pg_upgrade
On 16 February 2013 01:01, Kevin Grittner kgri...@ymail.com wrote:
Unless something else comes up in review or I get feedback to the
contrary I plan to deal with the above-mentioned issues and commit
this within a week or two.
At the moment it's not possible to rename a column without using
Robert Haas robertmh...@gmail.com wrote:
On Fri, Feb 15, 2013 at 8:01 PM, Kevin Grittner kgri...@ymail.com wrote:
There is one odd aspect to pg_dump, but I think the way it is
behaving is the best way to handle it, although I invite other
opinions. If you load from pg_dump output, it will
Bruce Momjian br...@momjian.us wrote:
On Fri, Feb 15, 2013 at 08:24:16PM -0500, Robert Haas wrote:
On Fri, Feb 15, 2013 at 8:01 PM, Kevin Grittner kgri...@ymail.com wrote:
There is one odd aspect to pg_dump, but I think the way it is
behaving is the best way to handle it, although I invite
On Sat, Feb 16, 2013 at 09:53:14AM -0800, Kevin Grittner wrote:
Robert Haas robertmh...@gmail.com wrote:
On Fri, Feb 15, 2013 at 8:01 PM, Kevin Grittner kgri...@ymail.com wrote:
There is one odd aspect to pg_dump, but I think the way it is
behaving is the best way to handle it, although I
On 1/25/13 1:00 AM, Kevin Grittner wrote:
New patch rebased, fixes issues raised by Thom Brown, and addresses
some of your points.
This patch doesn't apply anymore, so I just took a superficial look. I
think the intended functionality and the interfaces look pretty good.
Documentation looks
On Fri, Feb 15, 2013 at 8:01 PM, Kevin Grittner kgri...@ymail.com wrote:
There is one odd aspect to pg_dump, but I think the way it is
behaving is the best way to handle it, although I invite other
opinions. If you load from pg_dump output, it will try to
populated materialized views which
On Fri, Feb 15, 2013 at 08:24:16PM -0500, Robert Haas wrote:
On Fri, Feb 15, 2013 at 8:01 PM, Kevin Grittner kgri...@ymail.com wrote:
There is one odd aspect to pg_dump, but I think the way it is
behaving is the best way to handle it, although I invite other
opinions. If you load from
On Fri, Jan 25, 2013 at 01:00:59AM -0500, Kevin Grittner wrote:
Noah Misch wrote:
*** a/contrib/sepgsql/sepgsql.h
--- b/contrib/sepgsql/sepgsql.h
***
*** 32,37
--- 32,39
/*
* Internally used code of object classes
+ *
+ * NOTE: Materialized
On 25 January 2013 06:00, Kevin Grittner kgri...@mail.com wrote:
Noah Misch wrote:
The patch conflicts with git master; I tested against master@{2013-01-20}.
New patch rebased, fixes issues raised by Thom Brown, and addresses
some of your points.
Thanks for the new version. All previous
On Thu, Jan 17, 2013 at 07:54:55AM -0500, Robert Haas wrote:
On Wed, Jan 16, 2013 at 1:38 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Where I really need someone to hit me upside the head with a
clue-stick is the code I added to the bottom of RelationBuildDesc()
in relcache.c. The idea is that
Hi Kevin,
The patch conflicts with git master; I tested against master@{2013-01-20}.
On Wed, Jan 16, 2013 at 12:40:55AM -0500, Kevin Grittner wrote:
I've been struggling with two areas:
- pg_dump sorting for MVs which depend on other MVs
From your later messages, I understand that you have
Thanks for looking at this!
Noah Misch wrote:
For the benefit of the archives, I note that we almost need not truncate an
unlogged materialized view during crash recovery. MVs are refreshed in a
VACUUM FULL-like manner: fill a new relfilenode, fsync it, and point the MV's
pg_class to that
On Thu, Jan 24, 2013 at 01:29:10PM -0500, Kevin Grittner wrote:
Noah Misch wrote:
For the benefit of the archives, I note that we almost need not truncate an
unlogged materialized view during crash recovery. MVs are refreshed in a
VACUUM FULL-like manner: fill a new relfilenode, fsync it,
Noah Misch wrote:
On Thu, Jan 24, 2013 at 01:29:10PM -0500, Kevin Grittner wrote:
Noah Misch wrote:
For the benefit of the archives, I note that we almost need not truncate an
unlogged materialized view during crash recovery. MVs are refreshed in a
VACUUM FULL-like manner: fill a new
On Thu, Jan 24, 2013 at 03:14:15PM -0500, Kevin Grittner wrote:
Noah Misch wrote:
On Thu, Jan 24, 2013 at 01:29:10PM -0500, Kevin Grittner wrote:
Noah Misch wrote:
For the benefit of the archives, I note that we almost need not truncate
an
unlogged materialized view during crash
On 17 January 2013 16:03, Thom Brown t...@linux.com wrote:
On 16 January 2013 17:25, Thom Brown t...@linux.com wrote:
On 16 January 2013 17:20, Kevin Grittner kgri...@mail.com wrote:
Thom Brown wrote:
Some weirdness:
postgres=# CREATE VIEW v_test2 AS SELECT 1 moo;
CREATE VIEW
On Wed, Jan 16, 2013 at 1:38 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Where I really need someone to hit me upside the head with a
clue-stick is the code I added to the bottom of RelationBuildDesc()
in relcache.c. The idea is that on first access to an unlogged MV,
to detect that the heap has
On 16 January 2013 17:25, Thom Brown t...@linux.com wrote:
On 16 January 2013 17:20, Kevin Grittner kgri...@mail.com wrote:
Thom Brown wrote:
Some weirdness:
postgres=# CREATE VIEW v_test2 AS SELECT 1 moo;
CREATE VIEW
postgres=# CREATE MATERIALIZED VIEW mv_test2 AS SELECT moo, 2*moo
Kevin Grittner kgri...@mail.com writes:
I've been struggling with two areas:
- pg_dump sorting for MVs which depend on other MVs
Surely that should fall out automatically given that the dependency is
properly expressed in pg_depend?
If you mean you're trying to get it to cope with circular
Tom Lane wrote:
Kevin Grittner kgri...@mail.com writes:
I've been struggling with two areas:
- pg_dump sorting for MVs which depend on other MVs
Surely that should fall out automatically given that the
dependency is properly expressed in pg_depend?
If you mean you're trying to get it to
On 16 January 2013 05:40, Kevin Grittner kgri...@mail.com wrote:
Here is a new version of the patch, with most issues discussed in
previous posts fixed.
I've been struggling with two areas:
- pg_dump sorting for MVs which depend on other MVs
- proper handling of the relisvalid flag for
Thom Brown wrote:
Some weirdness:
postgres=# CREATE VIEW v_test2 AS SELECT 1 moo;
CREATE VIEW
postgres=# CREATE MATERIALIZED VIEW mv_test2 AS SELECT moo, 2*moo FROM
v_test2 UNION ALL SELECT moo, 3*moo FROM v_test2;
SELECT 2
postgres=# \d+ mv_test2
Materialized view public.mv_test2
On 16 January 2013 17:20, Kevin Grittner kgri...@mail.com wrote:
Thom Brown wrote:
Some weirdness:
postgres=# CREATE VIEW v_test2 AS SELECT 1 moo;
CREATE VIEW
postgres=# CREATE MATERIALIZED VIEW mv_test2 AS SELECT moo, 2*moo FROM
v_test2 UNION ALL SELECT moo, 3*moo FROM v_test2;
Kevin Grittner kgri...@mail.com writes:
Tom Lane wrote:
Surely that should fall out automatically given that the
dependency is properly expressed in pg_depend?
The *definitions* sort properly, but what I'm trying to do is
define them WITH NO DATA and load data after all the COPY
statements
Do we really need unlogged MVs in the first iteration? Seems like
that's adding a whole bunch of new issues, when you have quite enough
already without that.
While I think there is strong user demand for unlogged MVs, if we can
get MVs without unlogged ones for 9.3, I say go for that. We'll
Marko Tiikkaja wrote:
Kevin Grittner kgri...@mail.com wrote:
Marko Tiikkaja wrote:
T2 sees an empty table
As far as I know you are the first to notice this behavior.
Thanks for pointing it out.
I will take a look at the issue; I don't know whether it's
something small I can address in
Hi Kevin,
On Mon, 26 Nov 2012 22:24:33 +0100, Kevin Grittner kgri...@mail.com
wrote:
Marko Tiikkaja wrote:
T2 sees an empty table
As far as I know you are the first to notice this behavior. Thanks
for pointing it out.
I will take a look at the issue; I don't know whether it's
something
On Tue, Nov 27, 2012 at 10:58 AM, Pavel Stehule pavel.steh...@gmail.com wrote:
I unlike using keywords DO for this purpose - when we use it for
anonymous blocks
Yeah, I don't much like that either. My original suggestion when
Kevin and I discussed this over voice was ALTER MATERIALIZED VIEW
Robert Haas wrote:
I don't particularly like syntaxes involving DO or LOAD because
those words already have strong associations with completely
unrelated features. Now, if we don't want to do that and we don't
want to use ALTER for a data-modifying command either, another
option would be to
2012/11/28 Kevin Grittner kgri...@mail.com:
Robert Haas wrote:
I don't particularly like syntaxes involving DO or LOAD because
those words already have strong associations with completely
unrelated features. Now, if we don't want to do that and we don't
want to use ALTER for a data-modifying
Peter Eisentraut pete...@gmx.net writes:
I'm not fond of overloading LOAD as the refresh command. Maybe you
could go the Oracle route here as well and use a stored procedure. That
would also allow things like SELECT pg_refresh_mv(oid) FROM ... more
easily.
I would like that we have a way to
2012/11/27 Dimitri Fontaine dimi...@2ndquadrant.fr:
Peter Eisentraut pete...@gmx.net writes:
I'm not fond of overloading LOAD as the refresh command. Maybe you
could go the Oracle route here as well and use a stored procedure. That
would also allow things like SELECT pg_refresh_mv(oid) FROM
Pavel Stehule wrote:
2012/11/27 Dimitri Fontaine dimi...@2ndquadrant.fr:
I would like that we have a way to refresh a Materialized View
by calling a stored procedure, but I don't think it should be
the main UI.
I agree. I saw that Oracle uses a function for that without any
statement-level
On Nov 27, 2012, at 5:25, Dimitri Fontaine dimi...@2ndquadrant.fr wrote:
So my proposal for the current feature would be:
ALTER MATERIALIZED VIEW mv UPDATE [ CONCURRENTLY ];
UPDATE MATERIALIZED VIEW mv;
The choice of keywords and syntax here hopefully clearly hint the user
about the
Kevin Grittner kgri...@mail.com writes:
An ALTER MATERIALIZED VIEW option was my first thought on syntax to
do what LOAD does in the current patch. But it bothered me that I
couldn't think of any other cases where ALTER some-object-type
only changed the data contained within the object and had
Dimitri Fontaine wrote:
Kevin Grittner kgri...@mail.com writes:
An ALTER MATERIALIZED VIEW option was my first thought on syntax
to do what LOAD does in the current patch. But it bothered me
that I couldn't think of any other cases where ALTER
some-object-type only changed the data contained
Kevin Grittner kgri...@mail.com writes:
changing the structure of the table. Somehow I don't find that
pursuasive as an argument for what ALTER MATERIALIZED VIEW should
rescan the source relations and build a whole new set of data for
exactly the same MV definition.
Fair enough.
Consider
Dimitri Fontaine dimi...@2ndquadrant.fr writes:
Under that light, using ALTER is strange indeed.
Agreed, seems like a poor choice.
I still don't like
using LOAD that much, allow me to try a last syntax proposal. Well all I
can find just now would be:
UPDATE MATERIALIZED VIEW mv FOR EACH
views WIP patch
Kevin Grittner kgri...@mail.com writes:
changing the structure of the table. Somehow I don't find that
pursuasive as an argument for what ALTER MATERIALIZED VIEW should
rescan the source relations and build a whole new set of data for
exactly the same MV definition
Hackers
Subject: Re: [HACKERS] Materialized views WIP patch
Kevin Grittner kgri...@mail.com writes:
changing the structure of the table. Somehow I don't find that
pursuasive as an argument for what ALTER MATERIALIZED VIEW should
rescan the source relations and build a whole new set of data
On Sun, Nov 25, 2012 at 7:30 PM, Marko Tiikkaja pgm...@joh.to wrote:
As others have pointed out, replacing the contents of a table is something
which people have been wanting to do for a long time, and I think having
this ability would make this patch a lot better; now it just feels like
On 11/26/12 2:07 PM, Robert Haas wrote:
On Sun, Nov 25, 2012 at 7:30 PM, Marko Tiikkaja pgm...@joh.to wrote:
As others have pointed out, replacing the contents of a table is something
which people have been wanting to do for a long time, and I think having
this ability would make this patch a
On 26 November 2012 13:07, Robert Haas robertmh...@gmail.com wrote:
None of those patches were small patches. It's going to take multiple
years to get materialized views up to a state where they're really
useful to a broad audience in production applications, but I don't
think we should sneer
On 11/14/12 9:28 PM, Kevin Grittner wrote:
17. Since the data viewed in an MV is not up-to-date with the latest
committed transaction,
So, the way I understand it, in Oracle terms, this feature is a
snapshot, not a materialized view. Maybe that's what it should be
called then.
--
Sent
On Mon, Nov 26, 2012 at 8:14 AM, Marko Tiikkaja pgm...@joh.to wrote:
First of all, I have to apologize. Re-reading the email I sent out last
night, it does indeed feel a bit harsh and I can understand your reaction.
At no point did I mean to belittle Kevin's efforts or the patch itself. I
On 11/26/2012 09:46 AM, Peter Eisentraut wrote:
On 11/14/12 9:28 PM, Kevin Grittner wrote:
17. Since the data viewed in an MV is not up-to-date with the latest
committed transaction,
So, the way I understand it, in Oracle terms, this feature is a
snapshot, not a materialized view. Maybe
On Mon, Nov 26, 2012 at 09:46:33AM -0500, Peter Eisentraut wrote:
On 11/14/12 9:28 PM, Kevin Grittner wrote:
17. Since the data viewed in an MV is not up-to-date with the
latest committed transaction,
So, the way I understand it, in Oracle terms, this feature is a
snapshot, not a
On 26 November 2012 15:24, David Fetter da...@fetter.org wrote:
I hate to add to the bike-shedding, but we should probably add REFRESH
SNAPSHOT as an optional piece of the grammar, with more REFRESH
options to come.
I don't know that they should be called materialised views, but do we
really
On Mon, Nov 26, 2012 at 04:02:17PM +, Peter Geoghegan wrote:
On 26 November 2012 15:24, David Fetter da...@fetter.org wrote:
I hate to add to the bike-shedding, but we should probably add
REFRESH SNAPSHOT as an optional piece of the grammar, with more
REFRESH options to come.
I don't
Marko Tiikkaja wrote:
On 15/11/2012 03:28, Kevin Grittner wrote:
I have been testing the patch a bit
Thanks!
and I'm slightly disappointed by the fact that it still doesn't
solve this problem (and I apologize if I have missed discussion
about this in the docs or in this thread):
assume
David Fetter wrote:
On Mon, Nov 26, 2012 at 09:46:33AM -0500, Peter Eisentraut wrote:
So, the way I understand it, in Oracle terms, this feature is a
snapshot, not a materialized view. Maybe that's what it should
be called then.
Snapshot is one of the options for refreshing an Oracle
On Mon, Nov 26, 2012 at 04:34:36PM -0500, Kevin Grittner wrote:
David Fetter wrote:
On Mon, Nov 26, 2012 at 09:46:33AM -0500, Peter Eisentraut wrote:
So, the way I understand it, in Oracle terms, this feature is a
snapshot, not a materialized view. Maybe that's what it should
be called
On Mon, 2012-11-26 at 09:46 -0500, Peter Eisentraut wrote:
On 11/14/12 9:28 PM, Kevin Grittner wrote:
17. Since the data viewed in an MV is not up-to-date with the latest
committed transaction,
So, the way I understand it, in Oracle terms, this feature is a
snapshot, not a
-Original Message-
From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers-
ow...@postgresql.org] On Behalf Of Peter Eisentraut
Sent: 27 November 2012 13:35
To: Kevin Grittner
Cc: Pgsql Hackers
Subject: Re: [HACKERS] Materialized views WIP patch
On Mon, 2012-11-26 at 09:46
Hi Kevin,
On 15/11/2012 03:28, Kevin Grittner wrote:
Attached is a patch that is still WIP but that I think is getting
pretty close to completion.
I've been looking at this, but I unfortunately haven't had as much time
as I had hoped for, and have not looked at the code in detail yet. It's
Josh Berkus wrote:
It would be nice for the user to have some way to know that a matview is
empty due to never being LOADed or recently being TRUNCATEd. However, I
don't think that relisvalid flag -- and preventing scanning the relation
-- is a good solution. What I'd rather have instead is a
Simon Riggs wrote:
This seems very similar to the REPLACE command we discussed
earlier, except this is restricted to Mat Views.
I don't remember that discussion -- do you have a reference?
If we're going to have this, I would prefer a whole command.
e.g. REPLACE matviewname REFRESH
Albe Laurenz wrote:
Kevin Grittner wrote:
My take was more that MVs would often be refreshed by crontab, and
that you would want to keep subsequent steps from running and
generating potentially plausible but completely inaccurate results
if the LMV failed.
If one of these subsequent steps
Kevin Grittner kgri...@mail.com writes:
Simon Riggs wrote:
Either way, I don't much like overloading the use of LOAD, which
already has a very different meaning.
Well, it's hard to avoid creating new keywords without overloading
the meaning of exsiting ones.
FWIW, I'd much rather see us
Kevin,
I'm looking at whether there is some reasonable way to detect invalid
data as well as capture age of data. Every solution I've thought of
so far has at least one hard-to-solve race condition, but I have
hopes that I can either solve that for one of the ideas, or come up
with an idea
On 11/19/12 9:57 AM, Josh Berkus wrote:
Kevin,
I'm looking at whether there is some reasonable way to detect invalid
data as well as capture age of data. Every solution I've thought of
so far has at least one hard-to-solve race condition, but I have
hopes that I can either solve that for
Tom Lane wrote:
Kevin Grittner kgri...@mail.com writes:
Josh Berkus wrote:
What use would a temporary matview be?
It would be essentially like a temporary table, with all the same
persistence options. I'm not really sure how often it will be more
useful than a temporary table before we
On 16 November 2012 11:25, Kevin Grittner kgri...@mail.com wrote:
16. To get new data into the MV, the command is LOAD MATERIALIZED
VIEW mat view_name. This seemed more descriptive to me that the
alternatives and avoids declaring any new keywords beyond
MATERIALIZED. If the MV is flagged as
Josh Berkus j...@agliodbs.com writes:
You could make that same claim about plain views, but in point of
fact the demand for making them work in COPY has been minimal.
So I'm not convinced this is an essential first-cut feature.
We can always add it later.
Of course. I just had the
Jeff Davis pg...@j-davis.com writes:
The documentation says that a materialized view is basically a
create-table-as-select except that it remembers the query. Would you say
that there is a compelling use case for this alone, or is this a
building block for more sophisticated materialized view
Greg Smith wrote:
On 11/14/12 6:28 PM, Kevin Grittner wrote:
- Documentation is incomplete.
...
- There are no regression tests yet.
Do you have any simple test cases you've been using you could attach?
With epic new features like this, when things don't work it's hard to
distinguish
On Fri, Nov 16, 2012 at 7:13 AM, Dimitri Fontaine
dimi...@2ndquadrant.fr wrote:
Jeff Davis pg...@j-davis.com writes:
The documentation says that a materialized view is basically a
create-table-as-select except that it remembers the query. Would you say
that there is a compelling use case for
Jeff Davis wrote:
On Wed, 2012-11-14 at 21:28 -0500, Kevin Grittner wrote:
Attached is a patch that is still WIP but that I think is getting
pretty close to completion. It is not intended to be the be-all and
end-all for materialized views, but the minimum useful feature set --
which is
Alvaro Herrera wrote:
It's not clear to me that it's right to do this by doing regular heap
updates here instead of heap_inplace_update. Also, I think this might
end up causing a lot of pg_class tuple churn (at least for matviews that
delete rows at xact end), which would be nice to avoid.
Josh Berkus wrote:
1. CREATE MATERIALIZED VIEW syntax is stolen directly from CREATE
TABLE AS, with all the same clauses supported. That includes
declaring a materialized view to be temporary or unlogged.
What use would a temporary matview be?
It would be essentially like a temporary
On Thu, Nov 15, 2012 at 1:36 PM, Josh Berkus j...@agliodbs.com wrote:
Hmmm. I understand the distinction you're making here, but I'm not sure
it actually matters to the user. MVs, by their nature, always have
potentially stale data. Being empty (in an inaccurate way) is just one
kind of
On 16 November 2012 16:25, Kevin Grittner kgri...@mail.com wrote:
Josh Berkus wrote:
Unlogged is good.
I agree that there are likely to be more use cases for this than
temp MVs. Unfortunately, I've had a hard time figuring out how to
flag an MV which is empty because its contents were
Kevin Grittner kgri...@mail.com writes:
Josh Berkus wrote:
What use would a temporary matview be?
It would be essentially like a temporary table, with all the same
persistence options. I'm not really sure how often it will be more
useful than a temporary table before we have incremental
On 15 November 2012 02:28, Kevin Grittner kgri...@mail.com wrote:
Attached is a patch that...
Got this error:
postgres=# create view v_test as select 1;
CREATE VIEW
postgres=# create materialized view mv_test as select * from v_test;
ERROR: could not open file base/12064/16425: No such file
By chance (?) I got similar one today too, when dropping extension:
ERROR: could not open file base/12623/12548: No such file or directory
I thought something might have gone wrong during Linux upgrade 2 days
ago, but it's not likely that we both have the issue.
I wonder if something is
Kevin,
I agree that there are likely to be more use cases for this than
temp MVs. Unfortunately, I've had a hard time figuring out how to
flag an MV which is empty because its contents were lost after a
crash with preventing people from using an MV which hasn't been
populated, which has the
Kevin Grittner kgri...@mail.com writes:
UPDATE MATERIALIZED VIEW was problematic?
Not technically, really, but I saw two reasons that I preferred LOAD MV:
1. It seems to me to better convey that the entire contents of the MV
will be built from scratch, rather than incrementally adjusted.
Josh Berkus j...@agliodbs.com writes:
There's going to be a pretty strong demand for COPY FROM matviews.
Forcing the user to use COPY FROM ( SELECT ... ) will be seen as
arbitrary and unintuitive.
You could make that same claim about plain views, but in point of
fact the demand for making them
Thom Brown wrote:
postgres=# create view v_test as select 1;
CREATE VIEW
postgres=# create materialized view mv_test as select * from v_test;
ERROR: could not open file base/12064/16425: No such file or directory
Thanks for the report; will investigate.
-Kevin
--
Sent via pgsql-hackers
Robert Haas wrote:
Josh Berkus wrote:
Being empty (in an inaccurate way) is just one kind of stale data.
This is my feeling also.
If you had an MV summarizing Wisconsin courts cumulative case counts
by case type, empty would not have been a valid stale state for
over 150 years. That is a
You could make that same claim about plain views, but in point of
fact the demand for making them work in COPY has been minimal.
So I'm not convinced this is an essential first-cut feature.
We can always add it later.
Of course. I just had the impression that we could support COPY FROM by
101 - 200 of 204 matches
Mail list logo
