RESET (storage parameter)

On this page

The RESET (storage parameter) statement reverts the value of a storage parameter on a table to its default value.

Note:

The RESET (storage parameter) is a subcommand of ALTER TABLE.

To reset a storage parameter on an existing index, you must drop and recreate the index with the storage parameter.

Syntax

alter_table_reset_storage_param ::=

Command parameters

Parameter	Description
`table`	The table to which you are setting the parameter.
`parameter_name`	The name of the storage parameter you are changing. See Storage parameters for a list of available parameters.

Storage parameters

Table parameters

Parameter name	Description	Data type	Default value
`exclude_data_from_backup`	New in v22.1: Excludes the data in this table from any future backups.	Boolean	`false`
`sql_stats_automatic_collection_enabled`	Enable automatic statistics collection for this table.	Boolean	`true`
`sql_stats_automatic_collection_min_stale_rows`	Minimum number of stale rows in this table that will trigger a statistics refresh.	Integer	500
`sql_stats_automatic_collection_fraction_stale_rows`	Fraction of stale rows in this table that will trigger a statistics refresh.	Float	0.2
`ttl`	Signifies if a TTL is active. Automatically set and controls the reset of all TTL-related storage parameters.	N/A	N/A
`ttl_automatic_column`	If set, use the value of the `crdb_internal_expiration` hidden column. Always set to `true` and cannot be reset.	Boolean	`true`
`ttl_delete_batch_size`	The number of rows to delete at a time. Minimum: `1`.	Integer	`100`
`ttl_delete_rate_limit`	The maximum number of rows to be deleted per second (rate limit). `0` means no limit.	Integer	`0`
`ttl_expire_after`	The interval when a TTL will expire. This parameter is required to enable TTL. Minimum: `'1 microsecond'`. Use `RESET (ttl)` to remove from the table.	Interval	N/A
`ttl_job_cron`	The frequency at which the TTL job runs.	CRON syntax	`'@hourly'`
`ttl_label_metrics`	Whether or not TTL metrics are labelled by table name (at the risk of added cardinality).	Boolean	`false`
`ttl_pause`	If set, stops the TTL job from executing.	Boolean	`false`
`ttl_range_concurrency`	The Row-Level TTL queries split up scans by ranges, and this determines how many concurrent ranges are processed at a time. Minimum: `1`.	Integer	`1`
`ttl_row_stats_poll_interval`	If set, counts rows and expired rows on the table to report as Prometheus metrics while the TTL job is running. Unset by default, meaning no stats are fetched and reported.	Interval	N/A
`ttl_select_batch_size`	The number of rows to select at one time during the row expiration check. Minimum: `1`.	Integer	`500`

The following parameters are included for PostgreSQL compatibility and do not affect how CockroachDB runs:

autovacuum_enabled
fillfactor

Required privileges

The user must be a member of the admin or owner roles, or have the CREATE privilege on the table.

Examples

Reset a storage parameter

Following the example in WITH (storage parameter), the ttl_test table has three TTL-related storage parameters active on the table:

SHOW CREATE TABLE ttl_test;

  table_name |                                                                                           create_statement
-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  ttl_test   | CREATE TABLE public.ttl_test (
             |     id UUID NOT NULL DEFAULT gen_random_uuid(),
             |     description STRING NULL,
             |     inserted_at TIMESTAMP NULL DEFAULT current_timestamp():::TIMESTAMP,
             |     crdb_internal_expiration TIMESTAMPTZ NOT VISIBLE NOT NULL DEFAULT current_timestamp():::TIMESTAMPTZ + '3 mons':::INTERVAL ON UPDATE current_timestamp():::TIMESTAMPTZ + '3 mons':::INTERVAL,
             |     CONSTRAINT ttl_test_pkey PRIMARY KEY (id ASC)
             | ) WITH (ttl = 'on', ttl_automatic_column = 'on', ttl_expire_after = '3 mons':::INTERVAL)
(1 row)

To remove these settings, run the following command:

ALTER TABLE ttl_test RESET (ttl);

SHOW CREATE TABLE ttl_test;

  table_name |                            create_statement
-------------+--------------------------------------------------------------------------
  ttl_test   | CREATE TABLE public.ttl_test (
             |     id UUID NOT NULL DEFAULT gen_random_uuid(),
             |     description STRING NULL,
             |     inserted_at TIMESTAMP NULL DEFAULT current_timestamp():::TIMESTAMP,
             |     CONSTRAINT ttl_test_pkey PRIMARY KEY (id ASC)
             | )
(1 row)

Pricing

Contact us

Sign In