Immutable tables

Many secure systems contain records that must be provably immune to change.

Many secure systems contain records that must be provably immune to change. Protective strategies such as row and block checksums incur high overhead. Moreover, these approaches are not foolproof against unauthorized changes, whether deliberate or inadvertent, by database administrators or other users with sufficient privileges.

Immutable tables are insert-only tables in which existing data cannot be modified, regardless of user privileges. Updating row values and deleting rows are prohibited. Certain changes to table metadata—for example, renaming table columns—are also prohibited, in order to prevent attempts to circumvent these restrictions. Flattened or external tables, which obtain their data from outside sources, cannot be set to be immutable.

You define an existing table as immutable with ALTER TABLE:

ALTER TABLE table SET IMMUTABLE ROWS;

Once set, table immutability cannot be reverted, and is immediately applied to all existing table data, and all data that is loaded thereafter. In order to modify the data of an immutable table, you must copy the data to a new table—for example, with COPY, CREATE TABLE...AS, or COPY_TABLE.

When you execute ALTER TABLE...SET IMMUTABLE ROWS on a table, Vertica sets two columns for that table in the system table TABLES. Together, these columns show when the table was made immutable:

immutable_rows_since_timestamp: Server system time when immutability was applied. This is valuable for long-term timestamp retrieval and efficient comparison.
immutable_rows_since_epoch: The epoch that was current when immutability was applied. This setting can help protect the table from attempts to pre-insert records with a future timestamp, so that row's epoch is less than the table's immutability epoch.

Enforcement

The following operations are prohibited on immutable tables:

UPDATE
DELETE
ALTER TABLE...DROP COLUMN
ALTER TABLE...RENAME COLUMN
ALTER TABLE...ADD COLUMN where the new column is defined with a SET USING clause.

The following partition management functions are disallowed when the target table is immutable:

Allowed operations

In general, you can execute any DML operation on an immutable table that does not affect existing row data—for example, add rows with COPY or INSERT. After you add data to an immutable table, it cannot be changed.

Tip

A table's immutability can render meaningless certain operations that are otherwise permitted on an immutable table. For example, you can add a column to an immutable table with ALTER TABLE...ADD COLUMN. However, all values in the new column are set to NULL (unless the column is defined with a DEFAULT value), and they cannot be updated.

Other allowed operations fall generally into two categories:

Changes to a table's DDL that have no effect on its data:
- ALTER TABLE...SET SCHEMA: Change the table schema.
- ALTER TABLE...OWNER TO: Transfer table ownership.
- ALTER TABLE...ALTER COLUMN...SET DATATYPE: Change a column's data type. Column data type changes are allowed only if they have no effect on the column's stored data.
Block operations on multiple table rows, or the entire table: