Document ALTER MATERIALIZED VIEW/TABLE ... SET PROPERTIES

[mosabua]

Follow up to #9613

Maybe @sopel39 and @sunningCGo can provide information here or a draft docs PR. Either way .. I can help with review or more.

[sopel39]

@sunningCGo Could you help here?

[sunningCGo]

Hi @mosabua , here is the info regarding #9613.

The PR implements two features.

First of all, it implements support for ALTER MATERIALIZED VIEW ... SET PROPERTIES ... in the core engine. The syntax is similar to ALTER TABLE ...SET PROPERTIES ... and looks like:

ALTER MATERIALIZED VIEW name SET PROPERTIES property_name = expression [, ...]

It sets the specified properties of the specified materialized view to the specified values (without changing the existing value of any other property).

For example, to set the property x of a materialized view named people to the string value y, one can write:

ALTER MATERIALIZED VIEW people SET PROPERTIES x = 'y'

Currently, no connector supports ALTER MATERIALIZED VIEW ... SET PROPERTIES ... But support in Starburst Hive connector is being added in https://github.com/starburstdata/starburst-enterprise/pull/4020.

Secondly, the PR introduces a new keyword DEFAULT that one can use on the right hand side of a property assignment to specify that the property should take on its default value (whatever that is). Continuing the example above, if one wants to set the property x to its default value, one can write:

ALTER MATERIALIZED VIEW people SET PROPERTIES x = DEFAULT

In addition, the following six kinds of SQL statements also gain support for the DEFAULT keyword:

ALTER TABLE... ADD COLUMN...
ANALYZE
CREATE MATERIALIZED VIEW
CREATE SCHEMA
CREATE TABLE (both column properties and table properties)
CREATE TABLE AS

So in any one of the six kinds of statements above, wherever a property value is expected, one can use the DEFAULT keyword to specify that the table/column property should take on the default value of that property. Note, however, that for these six kinds of statements, a user does not really have to use the DEFAULT keyword to accomplish that - the user can just omit the property altogether. So for example,

CREATE TABLE T (
    v BIGINT
) WITH (
    p1 = 100,
    p2 = DEFAULT
);

has the same effect has

CREATE TABLE T (
    v BIGINT
) WITH (
    p1 = 100
);

Note, however, that the following two statements are different

ALTER MATERIALIZED VIEW people SET PROPERTIES p1 = 100, p2 = DEFAULT
ALTER MATERIALIZED VIEW people SET PROPERTIES p1 = 100

The first statement above sets p2 to its default value whereas the second one leaves the existing p2 value untouched.

[mosabua]

Okay .. so given all that .. we need to document this in https://trino.io/docs/current/sql/alter-materialized-view.html and edit that document to support the different use cases of RENAME and SET PROPERTIES

Do you want me (or someone from my team) to create a draft and then you can help with review and merge @kokosing @joshthoward ?

[joshthoward]

@mosabua that would be great. Please tag @sunningCGo and I to review.

[sunningCGo]

Hi all, previously I mentioned that, in addition to ALTER MATERIALIZED VIEW...SET PROPERTIES..., six other kinds of statements also gain support for the DEFAULT keyword via #9613.

Another PR, namely #10331, has just been merged. That PR adds support for the DEFAULT keyword in ALTER TABLE...SET PROPERTIES...

Together, those two PR's mean that, in any statement in which one can specify a property, one can use DEFAULT.

The way DEFAULT works in ALTER TABLE..SET PROPERTIES... is similar to the way it works in ALTER MATERIALIZED VIEW...SET PROPERTIES... In particular, the following two statements are different

ALTER TABLE people SET PROPERTIES p1 = 100, p2 = DEFAULT
ALTER TABLE people SET PROPERTIES p1 = 100

The first statement above sets p2 to its default value whereas the second one leaves the existing p2 value untouched.

[jhlodin]

Closing as ALTER MV SET PROPERTIES is in, which is the core ask for 370. Following up with other issues for the remaining work.