Alter table options

[killme2008]

What type of enhancement is this?

API improvement, Configuration

What does the enhancement do?

For example, modify the table's TTL to 30 days:

ALTER TABLE monitor MODIFY TTL '30d';

Not all of the table options can be modified. The regions option can't be changed right now. Maybe we can manage a white list that contains alterable options.

Implementation challenges

How to make the change takes effect dynamically? I am not sure. I think we can implement a observer pattern for it.

[killme2008]

After discussing and investigating, decide to use the following syntax:

alter table set options(ttl='30d', write_buffer_size='32mb');

The syntax for options is the same as that for creating tables. The other keywords at the options position can be used to set other table features.

[NiwakaDev]

I guess that we don't allow changing storage option.

[killme2008]

I guess that we don't allow changing storage option.

Yes, of course. I've done some preliminary work in #2051 .

[chienguo]

@killme2008 Hi! why did you stop this work? I am also interested in this issue, and I also noticed the discussion under #4394. Seems that alter table options and alter database options can be done parallelly. Can I have a try on this issue?

[evenyag]

Feel free to try it. Could you please share your design and steps for implementing this feature?

[chienguo]

here is my rough design:

• add AlterKind::ChangeOptions, record the option names that are about to change and their corresponding value
• for all the places that deal with AlterKind, add support for ChangeOptions, like alter_expr_to_request, create_proto_alter_kind to create region proto, builder_with_alter_kind to construct a TableMetaBuilder actually to set table options for the upper AlterTableProcedure to use
• for the table options white list, for now, I can only think of doing it in a hardcode way
• modify parser to support input like this alter table set options(ttl='30d', write_buffer_size='32mb');

And where the proto file to generate greptime.v1.meta.rs locates? I cannot find it

[evenyag]

And where the proto file to generate greptime.v1.meta.rs locates? I cannot find it

Are you looking for this?
https://github.com/GreptimeTeam/greptime-proto/tree/main/proto/greptime/v1

What's more, we also need to support altering options in the region engine and update the option of the region.

[chienguo]

Since tables are split and stored in regions, should altering table options result in region options being updated cascadingly? And besides that, do we also need to support altering region options directly?

[evenyag]

should altering table options result in region options being updated cascadingly

Yes, we should also support altering region options.

[zyy17]

here is my rough design:

• add AlterKind::ChangeOptions, record the option names that are about to change and their corresponding value
• for all the places that deal with AlterKind, add support for ChangeOptions, like alter_expr_to_request, create_proto_alter_kind to create region proto, builder_with_alter_kind to construct a TableMetaBuilder actually to set table options for the upper AlterTableProcedure to use
• for the table options white list, for now, I can only think of doing it in a hardcode way
• modify parser to support input like this alter table set options(ttl='30d', write_buffer_size='32mb');

And where the proto file to generate greptime.v1.meta.rs locates? I cannot find it

@chienguo I'm also very interested in this issue. Are you considering implementing it? If so, I can assign it to you.

[chienguo]

@zyy17 Yes, indeed, could you please assign it to me?

[evenyag]

It's recommended to support altering region options first. We don't store the option in the manifest so we can update the version of the region here if the request is ModifyOptions.

greptimedb/src/mito2/src/worker/handle_alter.rs

Lines 115 to 126 in d628079

	let new_meta = match metadata_after_alteration(&version.metadata, request) {
	Ok(new_meta) => new_meta,
	Err(e) => {
	sender.send(Err(e));
	return;
	}
	};
	// Persist the metadata to region's manifest.
	let change = RegionChange {
	metadata: new_meta.clone(),
	};
	self.handle_manifest_region_change(region, change, sender)

[WenyXu]

BTW, maybe we don't need the options keywords, the stmt without options is more concise.

ALTER TABLE table_name SET ( storage_parameter [= value] [, ... ] )

ALTER TABLE table_name UNSET ( storage_parameter [, ... ] )

[killme2008]

BTW, maybe we don't need the options keywords, the stmt without options is more concise.

ALTER TABLE table_name SET ( storage_parameter [= value] [, ... ] )

ALTER TABLE table_name UNSET ( storage_parameter [, ... ] )

We may support altering other features in the future, so please keep the options keyword.

[WenyXu]

We may support altering other features in the future, so please keep the options keyword.

The ALTER TABLE table_name SET (ttl='30d', write_buffer_size='32mb'), can be considered as starting with LeftParen ((), so I think we can distinguish between SET ( and SET STH in the future.

[killme2008]

We may support altering other features in the future, so please keep the options keyword.

The ALTER TABLE table_name SET (ttl='30d', write_buffer_size='32mb'), can be considered as starting with LeftParen ((), so I think we can distinguish between SET ( and SET STH in the future.

It's a bad practice to distinguish something with (.