Quantcast

Materialized views WIP patch

classic Classic list List threaded Threaded
61 messages Options
1234
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Materialized views WIP patch

David Fetter
On Mon, Nov 26, 2012 at 04:02:17PM +0000, Peter Geoghegan wrote:

> On 26 November 2012 15:24, David Fetter <[hidden email]> 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 need to overload the word snapshot? I'd just as soon
> invent a new word as use the Oracle one, since I don't think the
> term snapshot is widely recognised as referring to anything other
> than snapshot isolation.

I believe that the meaning here is unambiguous, and is used in other
descriptions than Oracle's, including the one on our wiki.

Cheers,
David.
--
David Fetter <[hidden email]> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: [hidden email]
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Materialized views WIP patch

Kevin Grittner-4
In reply to this post by Kevin Grittner-4
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 "foo" is a non-empty materialized view>
>
> T1: BEGIN;
> T1: LOAD MATERIALIZED VIEW foo;
>
> T2: SELECT * FROM foo;
>
> T1: COMMIT;
>
> <T2 sees an empty table>

As far as I know you are the first to notice this behavior. Thanks
for pointing it out.

I agree with Robert that we have to be careful about scope creep,
but this one looks to me like it should be considered a bug. IMO,
LOAD for a populated MV should move it from one state which
reflects the captured state of a previous point in time to a
captured state which is later, with no invalid or inconsistent
states visible in between.

I will take a look at the issue; I don't know whether it's
something small I can address in this CF or whether it will need to
be in the next CF, but I will fix it.

>> 19. LMV doesn't show a row count. It wouldn't be hard to add, it
>>     just seemed a little out of place to do that, when CLUSTER,
>>     etc., don't.
>
> This sounds like a useful feature, but your point about CLUSTER
> and friends still stands.

Other possible arguments against providing a count are:

 (1) For a populated MV, the LOAD might be replacing the contents
with fewer rows than were there before.

 (2) Once we have incremental updates of the MV, this count is only
one of the ways to update the view -- and the others won't show
counts. Showing it here might be considered inconsistent.

I don't feel strongly about it, and I don't think it's a big change
either way; just explaining what got me off the fence when I had to
pick one behavior or the other to post the WIP patch.

> I'll get back when I manage to get a better grasp of the code.

Thanks.

Keep in mind that the current behavior of behaving like a regular
view when the contents are invalid is not what I had in mind, that
was an accidental effect of commenting out the body of the
ExecCheckRelationsValid() function right before posting the patch
because I noticed a regression. When I noticed current behavior, it
struck me that someone might prefer it to the intended behavior of
showing an error like this:

  ereport(ERROR,
    (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
     errmsg("materialized view \"%s\" has not been populated",
            get_rel_name(rte->relid)),
     errhint("Use the LOAD MATERIALIZED VIEW command.")));

I mention it in case someone wants to argue for silently behaving
as a regular view when the MV is not populated.

-Kevin


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Materialized views WIP patch

Kevin Grittner-4
In reply to this post by Kevin Grittner-4
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
> materialized view. There are others, which we'll eventually add
> if past is any prologue :)

That's the way I understand it, too.

> 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 would prefer to leave the syntax for refreshing MVs to a later
patch. I'm kind of assuming that SNAPSHOT would be the default if
no other options are specified, but I would prefer not to get into
too much speculation about add-on patches for fear of derailing
this initial effort.

-Kevin


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Materialized views WIP patch

David Fetter
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 then.
> >
> > "Snapshot" is one of the options for refreshing an Oracle
> > materialized view. There are others, which we'll eventually add
> > if past is any prologue :)
>
> That's the way I understand it, too.

Great :)

> > 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 would prefer to leave the syntax for refreshing MVs to a later
> patch. I'm kind of assuming that SNAPSHOT would be the default if
> no other options are specified, but I would prefer not to get into
> too much speculation about add-on patches for fear of derailing
> this initial effort.

You're right.  I withdraw my suggestion until such time as this patch
(or descendent) is committed and actual working code implementing
other refresh strategies is written.

Cheers,
David.
--
David Fetter <[hidden email]> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: [hidden email]
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Materialized views WIP patch

Peter Eisentraut-2
In reply to this post by Peter Eisentraut-2
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 materialized view.  Maybe that's what it should be
> called then.

OK, I take everything back and claim the opposite.

In current Oracle, SNAPSHOT is an obsolete alias for MATERIALIZED VIEW.
Materialized views have the option of REFRESH ON DEMAND and REFRESH ON
COMMIT, with the former being the default.  So it seems that the syntax
of what you are proposing is in line with Oracle.

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.




--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Materialized views WIP patch

David Rowley
> -----Original Message-----
> From: [hidden email] [mailto:pgsql-hackers-
> [hidden email]] 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 -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 materialized view.  Maybe that's what it should be
> > called then.
>
> OK, I take everything back and claim the opposite.
>
> In current Oracle, SNAPSHOT is an obsolete alias for MATERIALIZED VIEW.
> Materialized views have the option of REFRESH ON DEMAND and REFRESH
> ON COMMIT, with the former being the default.  So it seems that the syntax
> of what you are proposing is in line with Oracle.
>
> 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.
>
>
+1 to this.
I can see a use case where you might want to refresh all MVs that are X number of days/hours old. Rather than having to execute statements for each one. Something like pg_refresh_mv() within a query would allow this.

Pretty exciting work Kevin, I understand what Robert said about feature creep etc and agree 100%, but I'm really looking forward to when we can *one day* have the planner make use of an eager MV to optimise a query!

Regards

David Rowley

>
>
> --
> Sent via pgsql-hackers mailing list ([hidden email]) To make
> changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers



--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Materialized views WIP patch

Dimitri Fontaine-7
In reply to this post by Peter Eisentraut-2
Peter Eisentraut <[hidden email]> 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 refresh a Materialized View by
calling a stored procedure, but I don't think it should be the main UI.

The wholesale refreshing of a matview appears to me to be comparable to
TRUNCATE is that it's both a DDL and a DML. The incremental refreshing
modes we want to have later are clearly DML only, either on commit
refresh or incrementally on demand.

I would then propose that we use ALTER MATERIALIZED VIEW as the UI for
the wholesale on demand refreshing operation, and UPDATE MATERIALIZED
VIEW as the incremental command (to come later).

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 locking behavior of the commands, too. And as we said, the
bare minimum for this patch does *not* include the CONCURRENTLY option,
which we still all want to have (someday). :)

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support



--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Materialized views WIP patch

Pavel Stehule
2012/11/27 Dimitri Fontaine <[hidden email]>:

> Peter Eisentraut <[hidden email]> 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 refresh a Materialized View by
> calling a stored procedure, but I don't think it should be the main UI.
>
> The wholesale refreshing of a matview appears to me to be comparable to
> TRUNCATE is that it's both a DDL and a DML. The incremental refreshing
> modes we want to have later are clearly DML only, either on commit
> refresh or incrementally on demand.
>
> I would then propose that we use ALTER MATERIALIZED VIEW as the UI for
> the wholesale on demand refreshing operation, and UPDATE MATERIALIZED
> VIEW as the incremental command (to come later).
>
> 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 locking behavior of the commands, too. And as we said, the
> bare minimum for this patch does *not* include the CONCURRENTLY option,
> which we still all want to have (someday). :)
>

+1

Pavel

> Regards,
> --
> Dimitri Fontaine
> http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support
>
>
>
> --
> Sent via pgsql-hackers mailing list ([hidden email])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Materialized views WIP patch

Kevin Grittner-4
In reply to this post by Kevin Grittner-4
Pavel Stehule wrote:
> 2012/11/27 Dimitri Fontaine <[hidden email]>:

>> 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 support, and that would probably be easier to
implement; but it felt wrong to do it that way. I couldn't think of
any other cases where similar action is taken without statement
syntax for it.

>> The wholesale refreshing of a matview appears to me to be
>> comparable to TRUNCATE is that it's both a DDL and a DML. The
>> incremental refreshing modes we want to have later are clearly
>> DML only, either on commit refresh or incrementally on demand.

Personally, I expect the most popular update method to eventually
become a queued update. I've looked ahead far enough to see that I
want to structure the incremental updates to be controlled through
an API where changes to supporting tables produce records saying
what was done which are fed to consumers which do the updating.
Then it becomes a matter of whether that consumer performs the
related updates to the MV during commit processing of the producer,
by pulling from a queue, or by reading accumulated rows when the MV
is referenced.

But I'm getting ahead of things with such speculation...

>> I would then propose that we use ALTER MATERIALIZED VIEW as the
>> UI for the wholesale on demand refreshing operation, and UPDATE
>> MATERIALIZED VIEW as the incremental command (to come later).

Honestly, I have managed to keep myself from speculating on syntax
for incremental updates. There will be enough complexity involved
that I expect months of bikeshedding. :-/

>> So my proposal for the current feature would be:
>>
>> ALTER MATERIALIZED VIEW mv UPDATE [ CONCURRENTLY ];
>> UPDATE MATERIALIZED VIEW mv;

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 no other
impact. Are you both really comfortable with an ALTER MATERIALIZED
VIEW which has no effect other than to update the data? It seems
wrong to me.

>> The choice of keywords and syntax here hopefully clearly hint
>> the user about the locking behavior of the commands, too. And as
>> we said, the bare minimum for this patch does *not* include the
>> CONCURRENTLY option, which we still all want to have (someday).
>> :)
>
> +1

Sure -- a CONCURRENTLY option for LMV (or AMVU) seems like one of
the next steps. I'll feel more confident about implementing that
when it appears that we have shaken the last bugs out of
CREATE/DROP INDEX CONCURRENTLY, since anything which affects those
statements will probably also matter here.

-Kevin


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Materialized views WIP patch

David Johnston
In reply to this post by Dimitri Fontaine-7
On Nov 27, 2012, at 5:25, Dimitri Fontaine <[hidden email]> 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 locking behavior of the commands, too. And as we said, the
> bare minimum for this patch does *not* include the CONCURRENTLY option,
> which we still all want to have (someday). :)
>

I dislike using ALTER syntax to perform a data-only action.

The other advantage of non-functional syntax is that you could more easily supply some form of where clause should you only want to perform a partial refresh.  With a function call that becomes more obtuse.

David J.

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Materialized views WIP patch

Dimitri Fontaine-7
In reply to this post by Kevin Grittner-4
"Kevin Grittner" <[hidden email]> 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 no other
> impact. Are you both really comfortable with an ALTER MATERIALIZED
> VIEW which has no effect other than to update the data? It seems
> wrong to me.

I think you can already do that with some clever use of alter table ...
type using, or alter table set default.

> Sure -- a CONCURRENTLY option for LMV (or AMVU) seems like one of
> the next steps. I'll feel more confident about implementing that
> when it appears that we have shaken the last bugs out of
> CREATE/DROP INDEX CONCURRENTLY, since anything which affects those
> statements will probably also matter here.

Sure.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Materialized views WIP patch

Kevin Grittner-4
In reply to this post by Kevin Grittner-4
Dimitri Fontaine wrote:

> "Kevin Grittner" <[hidden email]> 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 no other impact. Are you both really comfortable
>> with an ALTER MATERIALIZED VIEW which has no effect other than
>> to update the data? It seems wrong to me.
>
> I think you can already do that with some clever use of alter
> table ... type using, or alter table set default.

You mean, specifying an ALTER TABLE which appears to specify a
change to some non-data aspect of a table but which is really
setting it to the existing state? And it therefore rewrites the
table? I suppose that with USING you could actually even have it
rewritten with data different from what was there before without
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.

Consider that in relational theory a table is considered a relation
variable. ALTER is supposed to change the definition of the
variable in some way. Other statements are used to change the value
contained in the variable. Sure there are some grey areas already,
but I don't see where we need to muddy the waters in this case.

-Kevin


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Materialized views WIP patch

Dimitri Fontaine-7
"Kevin Grittner" <[hidden email]> 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 that in relational theory a table is considered a relation
> variable. ALTER is supposed to change the definition of the
> variable in some way. Other statements are used to change the value
> contained in the variable. Sure there are some grey areas already,
> but I don't see where we need to muddy the waters in this case.

Under that light, using ALTER is strange indeed. 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 ROW;
  UPDATE MATERIALIZED VIEW mv FOR EACH STATEMENT [ CONCURRENTLY ];

The only value of such a proposal is that it's not LOAD and it's still
not introducing any new keyword. Oh it's also avoiding to overload the
SNAPSHOT keyword. Well, it still does not look like the best candidate.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Materialized views WIP patch

Tom Lane-2
Dimitri Fontaine <[hidden email]> 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 ROW;
>   UPDATE MATERIALIZED VIEW mv FOR EACH STATEMENT [ CONCURRENTLY ];

> The only value of such a proposal is that it's not LOAD and it's still
> not introducing any new keyword. Oh it's also avoiding to overload the
> SNAPSHOT keyword. Well, it still does not look like the best candidate.

I think this syntax would require making MATERIALIZED (and possibly also
VIEW) fully reserved keywords, which would be better avoided.

                        regards, tom lane


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Materialized views WIP patch

David Johnston
In reply to this post by Dimitri Fontaine-7
> -----Original Message-----
> From: [hidden email] [mailto:pgsql-hackers-
> [hidden email]] On Behalf Of Dimitri Fontaine
> Sent: Tuesday, November 27, 2012 10:03 AM
> To: Kevin Grittner
> Cc: Pavel Stehule; Peter Eisentraut; Pgsql Hackers
> Subject: Re: [HACKERS] Materialized views WIP patch
>
> "Kevin Grittner" <[hidden email]> 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 that in relational theory a table is considered a relation
> > variable. ALTER is supposed to change the definition of the variable
> > in some way. Other statements are used to change the value contained
> > in the variable. Sure there are some grey areas already, but I don't
> > see where we need to muddy the waters in this case.
>
> Under that light, using ALTER is strange indeed. 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 ROW;
>   UPDATE MATERIALIZED VIEW mv FOR EACH STATEMENT [ CONCURRENTLY ];
>
> The only value of such a proposal is that it's not LOAD and it's still not
> introducing any new keyword. Oh it's also avoiding to overload the
> SNAPSHOT keyword. Well, it still does not look like the best candidate.
>
> Regards,

Just a thought but how about something like:

DO REFRESH OF MATERIALIZED VIEW mat_view;

In effect we begin overloading the meaning of "DO" to not only mean
anonymous code blocks but to also call pre-defined internal routines that
can be executed without having to use function-call syntax.  "MATERIALIZED
VIEW" can be more generic "e.g., TABLE" if the need arises, the REFRESH
"Action" is generic, and additional clauses can be added after the object
name (FOR, CONCURRENTLY, WHERE, etc...)

David J.










--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Materialized views WIP patch

Pavel Stehule
2012/11/27 David Johnston <[hidden email]>:

>> -----Original Message-----
>> From: [hidden email] [mailto:pgsql-hackers-
>> [hidden email]] On Behalf Of Dimitri Fontaine
>> Sent: Tuesday, November 27, 2012 10:03 AM
>> To: Kevin Grittner
>> Cc: Pavel Stehule; Peter Eisentraut; Pgsql Hackers
>> Subject: Re: [HACKERS] Materialized views WIP patch
>>
>> "Kevin Grittner" <[hidden email]> 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 that in relational theory a table is considered a relation
>> > variable. ALTER is supposed to change the definition of the variable
>> > in some way. Other statements are used to change the value contained
>> > in the variable. Sure there are some grey areas already, but I don't
>> > see where we need to muddy the waters in this case.
>>
>> Under that light, using ALTER is strange indeed. 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 ROW;
>>   UPDATE MATERIALIZED VIEW mv FOR EACH STATEMENT [ CONCURRENTLY ];
>>
>> The only value of such a proposal is that it's not LOAD and it's still not
>> introducing any new keyword. Oh it's also avoiding to overload the
>> SNAPSHOT keyword. Well, it still does not look like the best candidate.
>>
>> Regards,
>
> Just a thought but how about something like:
>
> DO REFRESH OF MATERIALIZED VIEW mat_view;
>
> In effect we begin overloading the meaning of "DO" to not only mean
> anonymous code blocks but to also call pre-defined internal routines that
> can be executed without having to use function-call syntax.  "MATERIALIZED
> VIEW" can be more generic "e.g., TABLE" if the need arises, the REFRESH
> "Action" is generic, and additional clauses can be added after the object
> name (FOR, CONCURRENTLY, WHERE, etc...)

-1

I  unlike using keywords DO for this purpose - when we use it for
anonymous blocks

Regards

Pavel

>
> David J.
>
>
>
>
>
>
>
>


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Materialized views WIP patch

Robert Haas
On Tue, Nov 27, 2012 at 10:58 AM, Pavel Stehule <[hidden email]> 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 ..
REFRESH or ALTER MATERIALIZED VIEW .. UPDATE.  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 invent a new
toplevel command:

REFRESH <view_name>;

Of course, that does introduce another keyword, but the penalty for a
new unreserved keyword is pretty small.  It seems like a rough
analogue of CLUSTER, which could be spelled ALTER TABLE <table_name>
UPDATE TABLE ORDER TO if keyword minimization trumped both concision
and clarity, but it doesn't.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Materialized views WIP patch

Kevin Grittner-4
In reply to this post by Kevin Grittner-4
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 invent a new toplevel command:
>
> REFRESH <view_name>;
>
> Of course, that does introduce another keyword, but the penalty
> for a new unreserved keyword is pretty small.

Of the alternatives to LOAD MATERIALIZED VIEW, something involving
REFRESH seems the best to me. The question is whether REFRESH
MATERIALIZED VIEW (or just REFRESH) is more clear, and whether it
is so by enough to merit another keyword. Of course, there is a
chance that we may wind up needing that keyword for declaring
incremental updates anyway, so it might be a matter of *when* we do
it rather than *whether* we do it -- depending on the yet-to-be-
determined syntax for specifying incremental updates.

My personal preference is still for LOAD MATERIALIZED VIEW because
it implies a complete regeneration rather than something more
incremental, but I realize that is subjective.

-Kevin


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Materialized views WIP patch

Pavel Stehule
2012/11/28 Kevin Grittner <[hidden email]>:

> 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 invent a new toplevel command:
>>
>> REFRESH <view_name>;
>>
>> Of course, that does introduce another keyword, but the penalty
>> for a new unreserved keyword is pretty small.
>
> Of the alternatives to LOAD MATERIALIZED VIEW, something involving
> REFRESH seems the best to me. The question is whether REFRESH
> MATERIALIZED VIEW (or just REFRESH) is more clear, and whether it
> is so by enough to merit another keyword. Of course, there is a
> chance that we may wind up needing that keyword for declaring
> incremental updates anyway, so it might be a matter of *when* we do
> it rather than *whether* we do it -- depending on the yet-to-be-
> determined syntax for specifying incremental updates.
>
> My personal preference is still for LOAD MATERIALIZED VIEW because
> it implies a complete regeneration rather than something more
> incremental, but I realize that is subjective.

In this context I prefer REFRESH keyword - I have a LOAD associated
with BULKLOAD, a this is different

Regards

Pavel

>
> -Kevin


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Materialized views WIP patch

Marko Tiikkaja-3
In reply to this post by Kevin Grittner-4
Hi Kevin,

On Mon, 26 Nov 2012 22:24:33 +0100, Kevin Grittner <[hidden email]>  
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 this CF or whether it will need to
> be in the next CF, but I will fix it.

Any news on this front?

>> I'll get back when I manage to get a better grasp of the code.

The code looks relatively straightforward and good to my eyes.  It passes  
my testing and looks to be changing all the necessary parts of the code.

> Keep in mind that the current behavior of behaving like a regular
> view when the contents are invalid is not what I had in mind, that
> was an accidental effect of commenting out the body of the
> ExecCheckRelationsValid() function right before posting the patch
> because I noticed a regression. When I noticed current behavior, it
> struck me that someone might prefer it to the intended behavior of
> showing an error like this:
>
>   ereport(ERROR,
>     (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
>      errmsg("materialized view \"%s\" has not been populated",
>             get_rel_name(rte->relid)),
>      errhint("Use the LOAD MATERIALIZED VIEW command.")));
>
> I mention it in case someone wants to argue for silently behaving
> as a regular view when the MV is not populated.

FWIW, I'd prefer an error in this case, but I don't feel strongly about it.


Regards,
Marko Tiikkaja


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
1234
Loading...