Re: Materialized views WIP patch

Dimitri Fontaine wrote:
> "Kevin Grittner" <kgrittn(at)mail(dot)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 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

"Kevin Grittner" <kgrittn(at)mail(dot)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 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

Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)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 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

> -----Original Message-----
> From: pgsql-hackers-owner(at)postgresql(dot)org [mailto:pgsql-hackers-
> owner(at)postgresql(dot)org] 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" <kgrittn(at)mail(dot)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 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.

2012/11/27 David Johnston <polobo(at)yahoo(dot)com>:
>> -----Original Message-----
>> From: pgsql-hackers-owner(at)postgresql(dot)org [mailto:pgsql-hackers-
>> owner(at)postgresql(dot)org] 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" <kgrittn(at)mail(dot)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 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.
>
>
>
>
>
>
>
>

On Tue, Nov 27, 2012 at 10:58 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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 ..
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