ALTER statements let you change existing database objects.
This is the multi-page printable view of this section. Click here to print.
ALTER statements
- 1: ALTER ACCESS POLICY
- 2: ALTER AUTHENTICATION
- 3: ALTER CA BUNDLE
- 4: ALTER DATA LOADER
- 5: ALTER DATABASE
- 6: ALTER FAULT GROUP
- 7: ALTER FUNCTION statements
- 7.1: ALTER AGGREGATE FUNCTION
- 7.2: ALTER ANALYTIC FUNCTION
- 7.3: ALTER FILTER
- 7.4: ALTER FUNCTION (scalar)
- 7.5: ALTER FUNCTION (SQL)
- 7.6: ALTER PARSER
- 7.7: ALTER SOURCE
- 7.8: ALTER TRANSFORM FUNCTION
- 8: ALTER HCATALOG SCHEMA
- 9: ALTER LIBRARY
- 10: ALTER LOAD BALANCE GROUP
- 11: ALTER MODEL
- 12: ALTER NETWORK ADDRESS
- 13: ALTER NETWORK INTERFACE
- 14: ALTER NODE
- 15: ALTER NOTIFIER
- 16: ALTER PROCEDURE (stored)
- 17: ALTER PROFILE
- 18: ALTER PROFILE RENAME
- 19: ALTER PROJECTION
- 20: ALTER RESOURCE POOL
- 21: ALTER ROLE
- 22: ALTER ROUTING RULE
- 23: ALTER SCHEDULE
- 24: ALTER SCHEMA
- 25: ALTER SEQUENCE
- 26: ALTER SESSION
- 27: ALTER SUBCLUSTER
- 28: ALTER SUBNET
- 29: ALTER TABLE
- 30: ALTER TLS CONFIGURATION
- 31: ALTER TRIGGER
- 32: ALTER USER
- 33: ALTER VIEW
1 - ALTER ACCESS POLICY
Performs one of the following actions on existing access policies:
-
Modify an access policy by changing its expression, and by enabling/disabling the policy.
-
Copy an access policy from one table to another.
Syntax
Modify policy:
ALTER ACCESS POLICY ON [[database.]schema.]table
{ FOR COLUMN column [ expression ] | FOR ROWS [ WHERE expression ] } { GRANT TRUSTED } { ENABLE | DISABLE }
Copy policy:
ALTER ACCESS POLICY ON [[database.]schema.]table
{ FOR COLUMN column | FOR ROWS } COPY TO TABLE table;
Parameters
-
`[database.]schema
Database and schema. The default schema is
public
. If you specify a database, it must be the current database.table
- The name of the table that contains the access policy you want to enable, disable, or copy.
FOR COLUMN
column
[
expression
]
- Replaces the access policy expression that was previously set for this column. Omit
expression
from theFOR COLUMN
clause in order to enable or disable this policy only, or copy it to another table. FOR ROWS [WHERE
expression
]
- Replaces the row access policy expression that was previously set for this table. Omit
WHERE
expression
from theFOR ROWS
clause in order to enable or disable this policy only, or copy it to another table. GRANT TRUSTED
Specifies that GRANT statements take precedence over the access policy in determining whether users can perform DML operations on the target table. If omitted, users can only modify table data if the access policy allows them to see the stored data in its original, unaltered state. For more information, see Access policies and DML operations.
Important
GRANT TRUSTED only affects DML operations and does not enable users to see data that the access policy would otherwise mask. Specifying this option may allow users with certain grants to update data that they cannot see.ENABLE | DISABLE
- Indicates whether to enable or disable the access policy at the table level.
COPY TO TABLE
tablename
- Copies the existing access policy to the specified table. The copied access policy includes its enabled/disabled and GRANT TRUSTED statuses.
The following requirements apply:
-
Copying a column access policy:
-
The target table must have a column of the same name and compatible data type.
-
The target colum must not have an access policy.
-
-
Copying a row access policy: The target table must not have an access policy.
-
Privileges
Modify access policy
Non-superuser: Ownership of the table
Copy access policy
Non-superuser: Ownership of the source and destination tables
Examples
See also
CREATE ACCESS POLICY2 - ALTER AUTHENTICATION
Modifies the settings for a specified authentication method.
Syntax
ALTER AUTHENTICATION auth_record {
| { ENABLE | DISABLE }
| { LOCAL | HOST [ { TLS | NO TLS } ] host_ip_address }
| RENAME TO new_auth_record_name
| METHOD value
| SET param=value[,...]
| PRIORITY value
| [ [ NO ] FALLTHROUGH ]
}
Parameters
Parameter Name | Description |
---|---|
auth_record |
Name of the authentication method to alter. Type: VARCHAR |
ENABLE | DISABLE |
Enable or disable the specified authentication method. Default: Enabled When you perform an upgrade and use Kerberos authentication, you must manually set the authentication to ENABLE as it is disabled by default. |
LOCAL | HOST [ { TLS | NO TLS } host_ip_address |
Specify that the authentication method applies to local or remote ( For authentication methods that use LDAP, specify whether or not LDAP uses Transport Layer Security (TLS). For remote ( Vertica supports IPv4 and IPv6 addresses. |
RENAME TO new_auth_record_name |
Rename the authentication record. Type: VARCHAR |
METHOD value |
The authentication method you are altering. |
SET param = value |
Set a parameter name and value for the authentication method that you are creating. This is required for LDAP, Ident, and OAuth authentication methods. |
PRIORITY value |
If the user is associated with multiple authentication methods, the priority value specifies which authentication method Vertica tries first. Default: 0 Type: INTEGER Greater values indicate higher priorities. For example, a priority of 10 is higher than a priority of 5; priority 0 is the lowest possible value. For details, see Authentication record priority. |
[ [ NO ] FALLTHROUGH ] |
Specifies whether to enable authentication fallthrough. For details, see Client authentication. |
Privileges
Superuser
Examples
Enabling and Disabling Authentication Methods
This example uses ALTER AUTHENTICATION to disable the v_ldap
authentication method and then enable it again:
=> ALTER AUTHENTICATION v_ldap DISABLE;
=> ALTER AUTHENTICATION v_ldap ENABLE;
Renaming Authentication Methods
This example renames the v_kerberos
authentication method to K5
. All users who have been granted the v_kerberos
authentication method now have the K5
method granted instead.
=> ALTER AUTHENTICATION v_kerberos RENAME TO K5;
Modifying Authentication Parameters
This example sets the system user for ident1
authentication to user1
:
=> CREATE AUTHENTICATION ident1 METHOD 'ident' LOCAL;
=> ALTER AUTHENTICATION ident1 SET system_users='user1';
When you set or modify LDAP or Ident parameters using ALTER AUTHENTICATION, Vertica validates them.
This example changes the IP address and specifies the parameters for an LDAP authentication method named Ldap1
. Specify the bind parameters for the LDAP server. Vertica connects to the LDAP server, which authenticates the database client. If authentication succeeds, Vertica authenticates any users who have been associated with (granted) the Ldap1
authentication method on the designated LDAP server:
=> CREATE AUTHENTICATION Ldap1 METHOD 'ldap' HOST '172.16.65.196';
=> ALTER AUTHENTICATION Ldap1 SET host='ldap://172.16.65.177',
binddn_prefix='cn=', binddn_suffix=',dc=qa_domain,dc=com';
The next example specifies the parameters for an LDAP authentication method named Ldap2
. Specify the LDAP search and bind parameters. Sometimes, Vertica does not have enough information to create the distinguished name (DN) for a user attempting to authenticate. In such cases, you must specify to use LDAP search and bind:
=> CREATE AUTHENTICATION Ldap2 METHOD 'ldap' HOST '172.16.65.196';
=> ALTER AUTHENTICATION Ldap2 SET basedn='dc=qa_domain,dc=com',
binddn='cn=Manager,dc=qa_domain,
dc=com',search_attribute='cn',bind_password='secret';
Changing the Authentication Method
This example changes the localpwd
authentication from hash to trust:
=> CREATE AUTHENTICATION localpwd METHOD 'hash' LOCAL;
=> ALTER AUTHENTICATION localpwd METHOD 'trust';
Set Multiple Realms
This example sets another realm for the authentication method krb_local:
=> ALTER AUTHENTICATION krb_local set realm = 'COMPANY.COM';
See also
3 - ALTER CA BUNDLE
Deprecated
CA bundles are only usable with certain deprecated parameters in Kafka notifiers. You should prefer using TLS configurations and the TLS CONFIGURATION parameter for notifiers instead.Adds and removes certificates from or changes the owner of a certificate authority (CA) bundle.
Syntax
ALTER CA BUNDLE name
[ADD CERTIFICATES ca_cert[, ca_cert[, ...]]
[REMOVE CERTIFICATES ca_cert[, ca_cert[, ...]]
[OWNER TO user]
Parameters
name
- The name of the CA bundle.
ca_cert
- The name of the CA certificate to add or remove from the bundle.
user
- The name of a database user.
Privileges
Ownership of the CA bundle.
Examples
See Managing CA bundles.
See also
4 - ALTER DATA LOADER
Changes the properties of a data loader created with CREATE DATA LOADER.
Syntax
ALTER DATA LOADER [schema.]name {
SET TO copy-statement
| RETRY LIMIT { NONE | DEFAULT | limit }
| RETENTION INTERVAL monitoring-retention
| RENAME TO new-name
| TRIGGERED BY integration-json
| DROP TRIGGER [IF EXISTS]
| OWNER TO user
| ENABLE | DISABLE
}
Arguments
schema
- Schema containing the data loader. The default schema is
public
. name
- Name of the data loader to alter.
SET TO
copy-statement
- The new COPY statement that the loader executes. The FROM clause typically uses a glob.
RETRY LIMIT { NONE | DEFAULT |
limit
}
- Maximum number of times to retry a failing file. Each time the data loader is executed, it attempts to load all files that have not yet been successfully loaded, up to this per-file limit. If set to DEFAULT, at load time the loader uses the value of the DataLoaderDefaultRetryLimit configuration parameter.
Default:
DEFAULT
RETENTION INTERVAL
monitoring-retention
- How long to keep records in the events table. DATA_LOADER_EVENTS records events for all data loaders, but each data loader has its own retention interval.
Default:
14 days
RENAME TO
new-name
- New name for the data loader.
TRIGGERED BY
integration-json
- New external trigger for the data loader. For details on the JSON fields, see CREATE DATA LOADER. Before altering the trigger you must disable the data loader using DISABLE. After making the change, enable it again using ENABLE.
DROP TRIGGER [IF EXISTS]
- Remove the trigger from this loader. You must first disable the data loader using DISABLE. After making the change, enable it again using ENABLE.
If the data loader does not have a trigger and you do not include IF EXISTS, the statement returns an error.
OWNER TO
user
- New owner for the data loader.
ENABLE | DISABLE
- Whether the data loader can be executed. Calling EXECUTE DATA LOADER on a disabled data loader returns an error. When created, data loaders with triggers are disabled by default; all others are enabled by default. If a data loader has a trigger, you must disable it before dropping it or altering the trigger.
If a data loader is disabled while it is executing, Vertica finishes the current execution.
Privileges
Non-superuser:
- USAGE on the schema.
- Owner or ALTER privilege on the data loader.
See also
5 - ALTER DATABASE
Use ALTER DATABASE to perform the following tasks:
-
Drop all fault groups and their child fault groups from a database.
-
Restore down nodes, and revert active standby nodes to standby status.
-
Specify the subnet name of a public network to use for import/export.
-
Set and clear database configuration parameters.
To see the current value of a parameter, query system table CONFIGURATION_PARAMETERS or use SHOW DATABASE.
Syntax
ALTER DATABASE db-spec {
DROP ALL FAULT GROUP
| EXPORT ON { subnet-name | DEFAULT }
| RESET STANDBY
| SET [PARAMETER] parameter=value [,...]
| CLEAR [PARAMETER] parameter[,...]
}
Parameters
db-spec
- Specifies the database to alter, one of the following:
-
The database name
-
DEFAULT
: The current database
-
DROP ALL FAULT GROUP
- Drops all fault groups defined on the specified database.
EXPORT ON
- Specifies the network to use for importing and exporting data, one of the following:
-
subnet-name
: A subnet of the public network. -
DEFAULT
: Specifies to use a private network.
For details, see Identify the database or nodes used for import/export, and Changing node export addresses.
-
RESET STANDBY
- Enterprise Mode only, restores all down nodes and reverts their replacement nodes to standby status. If any replaced nodes cannot resume activity, Vertica leaves their standby nodes in place.
SET [PARAMETER]
- Sets the specified parameters.
CLEAR [PARAMETER]
- Resets the specified parameters to their default values.
Privileges
Superuser
6 - ALTER FAULT GROUP
Modifies an existing fault group. ALTER FAULT GROUP can perform the following tasks:
-
Add a node to or drop a node from an existing fault group.
-
Add a child fault group to or drop a child fault group from a parent fault group.
-
Rename a fault group.
Syntax
ALTER FAULT GROUP fault-group-name {
| ADD NODE node-name
| DROP NODE node-name
| ADD FAULT GROUP child-fault-group-name
| DROP FAULT GROUP child-fault-group-name
| RENAME TO new-fault-group-name }
Parameters
fault-group-name
- The existing fault group name you want to modify.
Tip
For a list of all fault groups defined in the cluster, query the FAULT_GROUPS system table. node-name
- The node name you want to add to or drop from the existing (parent) fault group.
child-fault-group-name
- The name of the child fault group you want to add to or remove from an existing parent fault group.
new-fault-group-name
- The new name for the fault group you want to rename.
Privileges
Superuser
Examples
This example shows how to rename the parent0
fault group to parent100
:
=> ALTER FAULT GROUP parent0 RENAME TO parent100;
ALTER FAULT GROUP
Verify the change by querying the FAULT_GROUPS system table:
=> SELECT member_name FROM fault_groups;
member_name
----------------------
v_exampledb_node0003
parent100
mygroup
(3 rows)
See also
7 - ALTER FUNCTION statements
Vertica provides ALTER statements for each type of user-defined extension. Each ALTER statement modifies the metadata of a user-defined function in the Vertica catalog:
ALTER statement | Extension |
---|---|
ALTER FUNCTION (scalar) | User-defined scalar functions (UDSFs) |
ALTER AGGREGATE FUNCTION | User-defined aggregate functions (UDAFs) |
ALTER ANALYTIC FUNCTION | User-defined analytic functions (UDAnF) |
ALTER TRANSFORM FUNCTION | User-defined transform functions (UDTFs) |
ALTER statements for user-defined load: | |
• ALTER SOURCE | Load source functions |
• ALTER FILTER | Load filter functions |
• ALTER PARSER | Load parser functions |
Vertica also provides ALTER FUNCTION (SQL), which modifies the metadata of a user-defined SQL function.
7.1 - ALTER AGGREGATE FUNCTION
Alters a user-defined aggregate function.
Syntax
ALTER AGGREGATE FUNCTION [[db-name.]schema.]function-name( [ parameter-list ] ) {
OWNER TO new-owner
| RENAME TO new-name
| SET SCHEMA new-schema
}
Parameters
[
db-name.
]
schema
Database and schema. The default schema is
public
. If you specify a database, it must be the current database.function-name
``- Name of the SQL function to alter.
arg-list
- Comma-delimited list of parameters that are defined for this function. If none, specify an empty list.
Note
Vertica supports function overloading, and uses the parameter list to identify the function to alter. OWNER TO
new-owner
- Transfers function ownership to another user.
RENAME TO
new-name
- Renames this function.
SET SCHEMA
new-schema
- Moves the function to another schema.
Privileges
Non-superuser: USAGE on the schema and one of the following:
-
Function owner
-
ALTER privilege on the function
For certain operations, non-superusers must also have the following schema privileges:
For these operations... | Schema privileges required... |
---|---|
RENAME TO (rename function) | CREATE, USAGE |
SET SCHEMA (move function to another schema) |
CREATE: destination schema USAGE: current schema |
See also
CREATE AGGREGATE FUNCTION7.2 - ALTER ANALYTIC FUNCTION
Alters a user-defined analytic function.
Syntax
ALTER ANALYTIC FUNCTION [[db-name.]schema.]function-name( [ parameter-list ] ) {
OWNER TO new-owner
| RENAME TO new-name
| SET FENCED boolean-expr
| SET SCHEMA new-schema
}
Parameters
[
db-name.
]
schema
- Database and schema. The default schema is
public
. If you specify a database, it must be the current database. function-name
- Name of the function to alter.
parameter-list
- Comma-delimited list of parameters that are defined for this function. If none, specify an empty list.
Note
Vertica supports function overloading, and uses the parameter list to identify the function to alter. OWNER TO
new-owner
- Transfers function ownership to another user.
RENAME TO
new-name
- Renames this function.
SET FENCED { true | false }
- Specifies whether to enable fenced mode for this function.
SET SCHEMA
new-schema
- Moves the function to another schema.
Privileges
Non-superuser: USAGE on the schema and one of the following:
-
Function owner
-
ALTER privilege on the function
For certain operations, non-superusers must also have the following schema privileges:
Operation | Schema privileges required |
---|---|
RENAME TO (rename function) | CREATE, USAGE |
SET SCHEMA (move function to another schema) |
|
See also
CREATE ANALYTIC FUNCTION7.3 - ALTER FILTER
Alters a user-defined filter.
Syntax
ALTER FILTER [[db-name.]schema.]function-name( [ parameter-list ] ) {
OWNER TO new-owner
| RENAME TO new-name
| SET FENCED boolean-expr
| SET SCHEMA new-schema
}
Parameters
[
db-name.
]
schema
- Database and schema. The default schema is
public
. If you specify a database, it must be the current database. function-name
- Name of the function to alter.
parameter-list
- Comma-delimited list of parameters that are defined for this function. If none, specify an empty list.
Note
Vertica supports function overloading, and uses the parameter list to identify the function to alter. OWNER TO
new-owner
- Transfers function ownership to another user.
RENAME TO
new-name
- Renames this function.
SET FENCED { true | false }
- Specifies whether to enable fenced mode for this function.
SET SCHEMA
new-schema
- Moves the function to another schema.
Privileges
Non-superuser: USAGE on the schema and one of the following:
-
Function owner
-
ALTER privilege on the function
For certain operations, non-superusers must also have the following schema privileges:
Operation | Schema privileges required |
---|---|
RENAME TO (rename function) | CREATE, USAGE |
SET SCHEMA (move function to another schema) |
|
See also
CREATE FILTER7.4 - ALTER FUNCTION (scalar)
Alters a user-defined scalar function.
Syntax
ALTER FUNCTION [[db-name.]schema.]function-name( [ parameter-list] ) {
OWNER TO new-owner
| RENAME TO new-name
| SET FENCED boolean-expr
| SET SCHEMA new-schema
}
Parameters
[
db-name.
]
schema
- Database and schema. The default schema is
public
. If you specify a database, it must be the current database. function-name
- Name of the function to alter.
parameter-list
- Comma-delimited list of parameters that are defined for this function. If none, specify an empty list.
Note
Vertica supports function overloading, and uses the parameter list to identify the function to alter. OWNER TO
new-owner
- Transfers function ownership to another user.
RENAME TO
new-name
- Renames this function.
SET FENCED { true | false }
- Specifies whether to enable fenced mode for this function.
SET SCHEMA
new-schema
- Moves the function to another schema.
Privileges
Non-superuser: USAGE on the schema and one of the following:
-
Function owner
-
ALTER privilege on the function
For certain operations, non-superusers must also have the following schema privileges:
Operation | Schema privileges required |
---|---|
RENAME TO (rename function) | CREATE, USAGE |
SET SCHEMA (move function to another schema) |
|
Examples
Rename function UDF_one
to UDF_two
:
=> ALTER FUNCTION UDF_one (int, int) RENAME TO UDF_two;
Move function UDF_two
to schema macros
:
=> ALTER FUNCTION UDF_two (int, int) SET SCHEMA macros;
Disable fenced mode for function UDF_two
:
=> ALTER FUNCTION UDF_two (int, int) SET FENCED false;
See also
CREATE FUNCTION (scalar)7.5 - ALTER FUNCTION (SQL)
Alters a user-defined SQL function.
Syntax
ALTER FUNCTION [[db-name.]schema.]function-name( [arg-list] ) {
OWNER TO new-owner
| RENAME TO new-name
| SET SCHEMA new-schema
}
Parameters
[
db-name.
]
schema
Database and schema. The default schema is
public
. If you specify a database, it must be the current database.function-name
- The name of the SQL function to alter.
arg-list
- A comma-delimited list of function argument names. If none, specify an empty list.
OWNER TO
new-owner
- Transfers function ownership to another user.
RENAME TO
new-name
- Renames this function.
SET SCHEMA
new-schema
- Moves the function to another schema.
Privileges
Non-superuser: USAGE on the schema and one of the following:
-
Function owner
-
ALTER privilege on the function
For certain operations, non-superusers must also have the following schema privileges:
For these operations... | Schema privileges required... |
---|---|
RENAME TO (rename function) | CREATE, USAGE |
SET SCHEMA (move function to another schema) |
CREATE: destination schema USAGE: current schema |
Examples
Rename function SQL_one
to SQL_two
:
=> ALTER FUNCTION SQL_one (int, int) RENAME TO SQL_two;
Move function SQL_two
to schema macros
:
=> ALTER FUNCTION SQL_two (int, int) SET SCHEMA macros;
Reassign ownership of SQL_two
:
=> ALTER FUNCTION SQL_two (int, int) OWNER TO user1;
See also
7.6 - ALTER PARSER
Alters a user-defined parser.
Syntax
ALTER PARSER [[db-name.]schema.]function-name( [ parameter-list ] ) {
OWNER TO new-owner
| RENAME TO new-name
| SET FENCED boolean-expr
| SET SCHEMA new-schema
}
Parameters
[
db-name.
]
schema
- Database and schema. The default schema is
public
. If you specify a database, it must be the current database. function-name
- Name of the function to alter.
parameter-list
- Comma-delimited list of parameters that are defined for this function. If none, specify an empty list.
Note
Vertica supports function overloading, and uses the parameter list to identify the function to alter. OWNER TO
new-owner
- Transfers function ownership to another user.
RENAME TO
new-name
- Renames this function.
SET FENCED { true | false }
- Specifies whether to enable fenced mode for this function.
SET SCHEMA
new-schema
- Moves the function to another schema.
Privileges
Non-superuser: USAGE on the schema and one of the following:
-
Function owner
-
ALTER privilege on the function
For certain operations, non-superusers must also have the following schema privileges:
Operation | Schema privileges required |
---|---|
RENAME TO (rename function) | CREATE, USAGE |
SET SCHEMA (move function to another schema) |
|
See also
CREATE PARSER7.7 - ALTER SOURCE
Alters a user-defined load source function.
Syntax
ALTER SOURCE [[db-name.]schema.]function-name( [ parameter-list ] ) {
OWNER TO new-owner
| RENAME TO new-name
| SET FENCED boolean-expr
| SET SCHEMA new-schema
}
Parameters
[
db-name.
]
schema
- Database and schema. The default schema is
public
. If you specify a database, it must be the current database. function-name
- Name of the function to alter.
parameter-list
- Comma-delimited list of parameters that are defined for this function. If none, specify an empty list.
Note
Vertica supports function overloading, and uses the parameter list to identify the function to alter. OWNER TO
new-owner
- Transfers function ownership to another user.
RENAME TO
new-name
- Renames this function.
SET FENCED { true | false }
- Specifies whether to enable fenced mode for this function.
SET SCHEMA
new-schema
- Moves the function to another schema.
Privileges
Non-superuser: USAGE on the schema and one of the following:
-
Function owner
-
ALTER privilege on the function
For certain operations, non-superusers must also have the following schema privileges:
Operation | Schema privileges required |
---|---|
RENAME TO (rename function) | CREATE, USAGE |
SET SCHEMA (move function to another schema) |
|
See also
CREATE SOURCE7.8 - ALTER TRANSFORM FUNCTION
Alters a user-defined transform function.
Syntax
ALTER TRANSFORM FUNCTION [[db-name.]schema.]function-name( [ parameter-list ] ) {
OWNER TO new-owner
| RENAME TO new-name
| SET FENCED { true | false }
| SET SCHEMA new-schema
}
Parameters
[
db-name.
]
schema
- Database and schema. The default schema is
public
. If you specify a database, it must be the current database. function-name
- Name of the function to alter.
parameter-list
- Comma-delimited list of parameters that are defined for this function. If none, specify an empty list.
Note
Vertica supports function overloading, and uses the parameter list to identify the function to alter. OWNER TO
new-owner
- Transfers function ownership to another user.
RENAME TO
new-name
- Renames this function.
SET FENCED { true | false }
- Specifies whether to enable fenced mode for this function.
SET SCHEMA
new-schema
- Moves the function to another schema.
Privileges
Non-superuser: USAGE on the schema and one of the following:
-
Function owner
-
ALTER privilege on the function
For certain operations, non-superusers must also have the following schema privileges:
Operation | Schema privileges required |
---|---|
RENAME TO (rename function) | CREATE, USAGE |
SET SCHEMA (move function to another schema) |
|
See also
CREATE TRANSFORM FUNCTION8 - ALTER HCATALOG SCHEMA
Alters parameter values on a schema that was created with CREATE HCATALOG SCHEMA. HCatalog schemas are used by the HCatalog Connector to access data stored in a Hive data warehouse. For more information, see Using the HCatalog Connector.
Some parameters cannot be altered after creation. If you need to change one of those values, delete and recreate the schema instead. You can use ALTER HCATALOG SCHEMA to change the following parameters:
-
HOSTNAME
-
PORT
-
HIVESERVER2_HOSTNAME
-
WEBSERVICE_HOSTNAME
-
WEBSERVICE_PORT
-
WEBHDFS_ADDRESS
-
HCATALOG_CONNECTION_TIMEOUT
-
HCATALOG_SLOW_TRANSFER_LIMIT
-
HCATALOG_SLOW_TRANSFER_TIME
-
SSL_CONFIG
-
CUSTOM_PARTITIONS
Syntax
ALTER HCATALOG SCHEMA schema-name SET [param=value]+;
Parameters
Parameter | Description |
---|---|
schema-name |
The name of the schema in the Vertica catalog to alter. The tables in the Hive database are available through this schema. |
param |
The name of the parameter to alter. |
value |
The new value for the parameter. You must specify a value; this statement does not read default values from configuration files like CREATE HCATALOG SCHEMA . |
Privileges
One of the following:
-
Superuser
-
Schema owner
Examples
The following example shows how to change the Hive metastore hostname and port for the "hcat" schema. In this example, Hive uses High Availability metastore.
=> ALTER HCATALOG SCHEMA hcat SET HOSTNAME='thrift://ms1.example.com:9083,thrift://ms2.example.com:9083';
The following example shows the error you receive if you try to set an unalterable parameter.
=> ALTER HCATALOG SCHEMA hcat SET HCATALOG_USER='admin';
ERROR 4856: Syntax error at or near "HCATALOG_USER" at character 39
9 - ALTER LIBRARY
Replaces the library file that is currently associated with a UDx library in the Vertica catalog. Vertica automatically distributes copies of the updated file to all cluster nodes. UDxs defined in the catalog that reference the updated library automatically start using the updated library file. A UDx is considered to be the same if its name and signature match.
The current and replacement libraries must be written in the same language.
Caution
If a UDx function that is present in the original library is not present in the updated library, it is automatically dropped. This can result in loss of data if that function is in use, for example if a table depends on it to populate a column.Syntax
ALTER LIBRARY [[database.]schema.]name [DEPENDS 'depends-path'] AS 'path';
Arguments
schema
Database and schema. The default schema is
public
. If you specify a database, it must be the current database.name
- The name of an existing library created with CREATE LIBRARY.
DEPENDS '
depends-path
'
Files or libraries on which this library depends, one or more files or directories on the initiator node file system or other supported file systems or object stores. For a directory, end the path entry with a slash (
/
), optionally followed by a wildcard (*
). To specify more than one file, separate entries with colons (:
).If any path entry contain colons, such as a URI, place brackets around the entire DEPENDS path and use double quotes for the individual path elements, as in the following example:
DEPENDS '["s3://mybucket/gson-2.3.1.jar"]'
To specify libraries with multiple directory levels, see Multi-level Library Dependencies.
DEPENDS has no effect for libraries written in R. R packages must be installed locally on each node, including external dependencies.
Important
The performance of CREATE LIBRARY can degrade in Eon Mode, in proportion to the number and depth of dependencies specified by the DEPENDS clause.AS
path
- The absolute path on the initiator node file system of the replacement library file.
Privileges
Superuser, or UDXDEVELOPER and CREATE on the schema. Non-superusers must explicitly enable the UDXDEVELOPER role. See CREATE LIBRARY for examples.
Multi-level library dependencies
If a DEPENDS clause specifies a library with multiple directory levels, Vertica follows the library path to include all subdirectories of that library. For example, the following CREATE LIBRARY statement enables the UDx library mylib
to import all Python packages and modules that it finds in subdirectories of site-packages
:
=> CREATE LIBRARY mylib AS '/path/to/python_udx' DEPENDS '/path/to/python/site-packages' LANGUAGE 'Python';
Important
DEPENDS can specify Java library dependencies that are up to 100 levels deep.Examples
This example shows how to update an already-defined library named myFunctions
with a new file.
=> ALTER LIBRARY myFunctions AS '/home/dbadmin/my_new_functions.so';
See also
Developing user-defined extensions (UDxs)10 - ALTER LOAD BALANCE GROUP
Changes the configuration of a load balance group.
Syntax
ALTER LOAD BALANCE GROUP group-name {
RENAME TO new-name |
SET FILTER TO 'ip-cidr-addr' |
SET POLICY TO 'policy' |
ADD {ADDRESS | FAULT GROUP | SUBCLUSTER} add-list |
DROP {ADDRESS | FAULT GROUP | SUBCLUSTER} drop-list
}
Parameters
group-name
- Name of an existing load balance group to change.
RENAME TO
new-name
- Renames the group to new-name.
SET FILTER TO '
ip-cidr-addr
'
- An IPv4 or IPv6 CIDR to replace the existing IP address filter that selects which members of a fault group or subcluster to include in the load balance group. This setting is only valid if the load balance group contains fault groups or subclusters.
SET POLICY TO '
policy
'
- Changes the policy the load balance group uses to select the target node for the incoming connection. One of:
-
ROUNDROBIN
-
RANDOM
-
NONE
See CREATE LOAD BALANCE GROUP for details.
-
ADD {ADDRESS | FAULT GROUP | SUBCLUSTER }
- Adds objects of the specified type to the load balance group. Load balance groups can only contain one type of object. For example, if you created the load balance group using a list of addresses, you can only add additional addresses, not fault groups or subclusters.
add-list
- A comma-delimited list of objects (addresses, fault groups, or subclusters) to add to the fault group.
DROP {ADDRESS | FAULT GROUP | SUBCLUSTER}
- Removes objects of the specified type from the load balance group (addresses, fault groups, or subclusters). The object type must match the type of the objects already in the load balance group.
drop-list
- The list of objects to remove from the load balance group.
Privileges
Superuser
Examples
Remove an address from the load balance group named group_2.
=> SELECT * FROM LOAD_BALANCE_GROUPS;
name | policy | filter | type | object_name
---------+------------+--------+-----------------------+-------------
group_1 | ROUNDROBIN | | Network Address Group | node01
group_1 | ROUNDROBIN | | Network Address Group | node02
group_2 | ROUNDROBIN | | Network Address Group | node03
(3 rows)
=> ALTER LOAD BALANCE GROUP group_2 DROP ADDRESS node03;
ALTER LOAD BALANCE GROUP
=> SELECT * FROM LOAD_BALANCE_GROUPS;
name | policy | filter | type | object_name
---------+------------+--------+-----------------------+-------------
group_1 | ROUNDROBIN | | Network Address Group | node01
group_1 | ROUNDROBIN | | Network Address Group | node02
group_2 | ROUNDROBIN | | Empty Group |
(3 rows)
The following example adds three network addresses to the group named group_2:
=> ALTER LOAD BALANCE GROUP group_2 ADD ADDRESS node01,node02,node03;
ALTER LOAD BALANCE GROUP
=> SELECT * FROM load_balance_groups WHERE name = 'group_2';
-[ RECORD 1 ]----------------------
name | group_2
policy | ROUNDROBIN
filter |
type | Network Address Group
object_name | node01
-[ RECORD 2 ]----------------------
name | group_2
policy | ROUNDROBIN
filter |
type | Network Address Group
object_name | node02
-[ RECORD 3 ]----------------------
name | group_2
policy | ROUNDROBIN
filter |
type | Network Address Group
object_name | node03
See also
11 - ALTER MODEL
Allows users to rename an existing model, change ownership, or move it to a another schema.
Syntax
ALTER MODEL [[database.]schema.]model
{ OWNER TO owner
| RENAME TO new-name
| SET SCHEMA schema
| { INCLUDE | EXCLUDE | MATERIALIZE } [ SCHEMA ] PRIVILEGES }
Parameters
[
database
.]
schema
Database and schema. The default schema is
public
. If you specify a database, it must be the current database.model
- Identifies the model to alter.
OWNER TO
owner
- Reassigns ownership of this model to
owner
. If a non-superuser, you must be the current owner. RENAME TO
- Renames the mode, where
new-name
conforms to conventions described in Identifiers. It must also be unique among all names of sequences, tables, projections, views, and models within the same schema. SET SCHEMA
schema
- Moves the model from one schema to another. If privilege inheritance is enabled with INCLUDE SCHEMA PRIVILEGES, the model inherits the privileges of its new parent schema.
[ { INCLUDE | EXCLUDE | MATERIALIZE } [ SCHEMA ] PRIVILEGES ]
- The INCLUDE and EXCLUDE parameters determine whether the model inherits the privileges of its parent schema, overriding the schema-level setting:
-
INCLUDE SCHEMA PRIVILEGES: The model inherits the privileges of its parent schema. This parameter has no effect while privilege inheritance is disabled at the database level with DisableInheritedPrivileges.
-
EXCLUDE SCHEMA PRIVILEGES: The model does not inherit the privileges of its parent schema.
The MATERIALIZE parameter converts inherited privileges into explicit grants on the model. If privilege inheritance is disabled at the database level with DisableInheritedPrivileges, MATERIALIZE converts the set of inherited privileges that would be on the model if privilege inheritance was enabled.
-
Privileges
Non-superuser: USAGE on the schema and one of the following:
-
Model owner
-
ALTER privilege on the model
For certain operations, non-superusers must have the following schema privileges:
Schema privileges required... | For these operations... |
---|---|
CREATE, USAGE | Rename model |
CREATE: destination schema USAGE: current schema |
Move model to another schema |
Examples
To move a model to another schema:
=> ALTER MODEL my_kmeans_model SET SCHEMA clustering_models;
ALTER MODEL
To rename a model:
=> ALTER MODEL my_kmeans_model RENAME TO kmeans_model;
To change the owner of the model:
=> ALTER MODEL kmeans_model OWNER TO analytics_user;
12 - ALTER NETWORK ADDRESS
Changes the configuration of an existing network address.
Syntax
ALTER NETWORK ADDRESS name {
RENAME TO new-name
| SET TO 'ip-addr' [PORT port-number]
| { ENABLE | DISABLE }
}
Parameters
name
- Name of an existing network address to change.
RENAME TO
new-name
- Renames the network address to
new-name
. This name change has no effect on the network address's membership in load balance groups. SET TO '
ip-addr
'
- Changes the IP address assigned to the network address.
PORT
port-number
- Sets the port number for the network address. You must supply a network address when altering the port number.
ENABLE | DISABLE
- Enables or disables the network address.
Examples
Rename the network address from test_addr
to alt_node1
, then change its IP address to 192.168.1.200
with port number 4000
:
=> ALTER NETWORK ADDRESS test_addr RENAME TO alt_node1;
ALTER NETWORK ADDRESS
=> ALTER NETWORK ADDRESS alt_node1 SET TO '192.168.1.200' PORT 4000;
ALTER NETWORK ADDRESS
See also
13 - ALTER NETWORK INTERFACE
Deprecated
This statement has been deprecated. Instead, use ALTER NETWORK ADDRESS.Renames a network interface.
Syntax
ALTER NETWORK INTERFACE network-interface-name RENAME TO new-network-interface-name
Parameters
network-interface-name
- The name of the existing network interface.
new-network-interface-name
- The new name for the network interface.
Privileges
Superuser
Examples
Rename a network interface:
=> ALTER NETWORK INTERFACE myNetwork RENAME TO myNewNetwork;
14 - ALTER NODE
Sets and clears node-level configuration parameters on the specified node. ALTER NODE also performs the following management tasks:
-
Changes the node type.
-
Specifies the network interface of the public network on individual nodes that are used for import and export.
-
Replaces a down node.
For information about removing a node, see
Syntax
ALTER NODE node-name {
EXPORT ON { network-interface | DEFAULT }
| [IS] node-type
| REPLACE [ WITH standby-node ]
| RESET
| SET [PARAMETER] parameter=value[,...]
| CLEAR [PARAMETER] parameter[,...]
}
Parameters
node-name
- The name of the node to alter.
[IS]
node-type
- Changes the node type, where
node-type
is one of the following:-
PERMANENT: (default): A node that stores data.
-
EPHEMERAL: A node that is in transition from one type to another—typically, from PERMANENT to either STANDBY or EXECUTE.
-
STANDBY: A node that is reserved to replace any node when it goes down. A standby node stores no segments or data until it is called to replace a down node. When used as a replacement node, Vertica changes its type to PERMANENT. For more information, see Active standby nodes.
-
EXECUTE: A node that is reserved for computation purposes only. An execute node contains no segments or data.
Note
STANDBY and EXECUTE node types are supported only in Enterprise Mode. -
EXPORT ON
- Specifies the network to use for importing and exporting data, one of the following:
-
network-interface
: The name of a network interface of the public network. -
DEFAULT
: Use the default network interface of the public network, as specified by ALTER DATABASE.
-
REPLACE [WITH
standby-node
]
- Enterprise Mode only, replaces the specified node with an available active standby node. If you omit the
WITH
clause, Vertica tries to find a replacement node from the same fault group as the down node.If you specify a node that is not down, Vertica ignores this statement.
RESET
- Enterprise Mode only, restores the specified down node and returns its replacement to standby status. If the down node cannot resume activity, Vertica ignores this statement and leaves the standby node in place.
SET [PARAMETER]
- Sets one or more configuration parameters to the specified value at the node level.
CLEAR [PARAMETER]
- Clears one or more specified configuration parameters.
Privileges
Superuser
Examples
Specify to use the default network interface of public network on v_vmart_node0001
for import/export operations:
=> ALTER NODE v_vmart_node0001 EXPORT ON DEFAULT;
Replace down node v_vmart_node0001
with an active standby node, then restore it:
=> ALTER NODE v_vmart_node0001 REPLACE WITH standby1;
...
=> ALTER NODE v_vmart_node0001 RESET;
Set and clear configuration parameter MaxClientSessions
:
=> ALTER NODE v_vmart_node0001 SET MaxClientSessions = 0;
...
=> ALTER NODE v_vmart_node0001 CLEAR MaxClientSessions;
Set the node type as EPHEMERAL
:
=> ALTER NODE v_vmart_node0001 IS EPHEMERAL;
15 - ALTER NOTIFIER
Updates an existing notifier.
Note
To change the action URL associated with an existing identifier, drop the notifier and recreate it.Syntax
ALTER NOTIFIER notifier-name
[ ENABLE | DISABLE ]
[ MAXPAYLOAD 'max-payload-size' ]
[ MAXMEMORYSIZE 'max-memory-size' ]
[ TLS CONFIGURATION tls-configuration ]
[ TLSMODE 'tls-mode' ]
[ CA BUNDLE bundle-name [ CERTIFICATE certificate-name ] ]
[ IDENTIFIED BY 'uuid' ]
[ [NO] CHECK COMMITTED ]
[ PARAMETERS 'adapter-params' ]
Parameters
notifier-name
- Specifies the notifier to update.
[NO] CHECK COMMITTED
- Specifies to wait for delivery confirmation before sending the next message in the queue. Not all messaging systems support delivery confirmation.
ENABLE | DISABLE
- Specifies whether to enable or disable the notifier.
MAXPAYLOAD
- The maximum size of the message, up to 2 TB, specified in kilobytes, megabytes, gigabytes, or terabytes as follows:
MAXPAYLOAD integer{K|M|G|T}
The default setting is adapter-specific—for example, 1 M for Kafka.
Changes to this parameter take effect either after the notifier is disabled and reenabled or after the database restarts.
MAXMEMORYSIZE
- The maximum size of the internal notifier, up to 2 TB, specified in kilobytes, megabytes, gigabytes, or terabytes as follows:
MAXMEMORYSIZE integer{K|M|G|T}
If the queue exceeds this size, the notifier drops excess messages.
- TLS CONFIGURATION
tls-configuration
The TLS CONFIGURATION to use for TLS.
Notifiers support the following TLS modes:
-
DISABLE
-
TRY_VERIFY (behaves like VERIFY_CA)
-
VERIFY_CA
-
VERIFY_FULL
If the notifier
ACTION
is'syslog'
or'sns'
, this parameter has no effect.To encrypt messages sent to syslog, you must configure syslog for TLS.
To encrypt messages sent to an SNS endpoint, you must set the following configuration parameters:
-
SNSCAFile or AWSCAFile
-
SNSCAPath or AWSCAPath
-
SNSEnableHttps
-
TLSMODE '
tls-mode
'
-
Deprecated
This parameter has been superseded by the TLS CONFIGURATION parameter. If you use this parameter while the TLS CONFIGURATION parameter is not set, Vertica automatically creates a new TLS Configuration for the notifier uses the same values as the deprecated parameter.Specifies the type of connection between the notifier and an endpoint, one of the following:
-
disable
(default): Plaintext connection. -
verify-ca
: Encrypted connection, and the server's certificate is verified as being signed by a trusted CA.
If you set this parameter to
verify-ca
, the generated TLS Configuration will be set to TRY_VERIFY, which has the same behavior as VERIFY_CA.If the notifier
ACTION
is'syslog'
or'sns'
, this parameter has no effect.To encrypt messages sent to syslog, you must configure syslog for TLS.
To encrypt messages sent to an SNS endpoint, you must set the following configuration parameters:
-
SNSCAFile or AWSCAFile
-
SNSCAPath or AWSCAPath
-
SNSEnableHttps
-
CA BUNDLE
bundle-name
-
Deprecated
This parameter has been superseded by the TLS CONFIGURATION parameter. If you use this parameter while the TLS CONFIGURATION parameter is not set, Vertica automatically creates a new TLS Configuration for the notifier uses the same values as the deprecated parameter.Specifies a CA bundle. The certificates inside the bundle are used to validate the Kafka server's certificate if the
TLSMODE
requires it.If a CA bundle is specified for a notifier that currently uses
disable
, which doesn't validate the Kafka server's certificate, the bundle will go unused when connecting to the Kafka server. This behavior persists unless theTLSMODE
is changed to one that validates server certificates.Changes to contents of the CA bundle take effect either after the notifier is disabled and re-enabled or after the database restarts. However, changes to which CA bundle the notifier uses takes effect immediately.
If the notifier
ACTION
is'syslog'
or'sns'
, this parameter has no effect.To encrypt messages sent to syslog, you must configure syslog for TLS.
To encrypt messages sent to an SNS endpoint, you must set the following configuration parameters:
-
SNSCAFile or AWSCAFile
-
SNSCAPath or AWSCAPath
-
SNSEnableHttps
-
CERTIFICATE
certificate-name
-
Deprecated
This parameter has been superseded by the TLS CONFIGURATION parameter. If you use this parameter while the TLS CONFIGURATION parameter is not set, Vertica automatically creates a new TLS Configuration for the notifier uses the same values as the deprecated parameter.Specifies a client certificate for validation by the endpoint.
If the notifier
ACTION
is'syslog'
or'sns'
, this parameter has no effect.To encrypt messages sent to syslog, you must configure syslog for TLS.
To encrypt messages sent to an SNS endpoint, you must set the following configuration parameters:
-
SNSCAFile or AWSCAFile
-
SNSCAPath or AWSCAPath
-
SNSEnableHttps
-
IDENTIFIED BY '
uuid
'
- Specifies the notifier's unique identifier. If set, all the messages published by this notifier have this attribute.
PARAMETERS '
adapter-params
'
- Specifies one or more optional adapter parameters that are passed as a string to the adapter. Adapter parameters apply only to the adapter associated with the notifier.
Changes to this parameter take effect either after the notifier is disabled and reenabled or after the database restarts.
For Kafka notifiers, refer to Kafka and Vertica configuration settings.
Privileges
Superuser
Encrypted notifiers for SASL_SSL Kafka configurations
Follow this procedure to create or alter notifiers for Kafka endpoints that use SASL_SSL. Note that you must repeat this procedure whenever you change the TLSMODE, certificates, or CA bundle for a given notifier.
-
Create a TLS Configuration with the desired TLS mode, certificate, and CA certificates.
-
Use CREATE or ALTER to disable the notifier and set the TLS Configuration:
=> ALTER NOTIFIER encrypted_notifier DISABLE TLS CONFIGURATION kafka_tls_config;
-
ALTER the notifier and set the proper rdkafka adapter parameters for SASL_SSL:
=> ALTER NOTIFIER encrypted_notifier PARAMETERS 'sasl.username=user;sasl.password=password;sasl.mechanism=PLAIN;security.protocol=SASL_SSL';
-
Enable the notifier:
=> ALTER NOTIFIER encrypted_notifier ENABLE;
Examples
Update the settings on an existing notifier:
=> ALTER NOTIFIER my_dc_notifier
ENABLE
MAXMEMORYSIZE '2G'
IDENTIFIED BY 'f8b0278a-3282-4e1a-9c86-e0f3f042a971'
CHECK COMMITTED;
Add a TLS Configuration to a notifier. To create a custom TLS Configuration, see TLS configurations:
=> ALTER NOTIFIER my_notifier TLS CONFIGURATION notifier_tls_config
See also
16 - ALTER PROCEDURE (stored)
Alters a stored procedure, retaining any existing grants.
Syntax
ALTER PROCEDURE procedure ( [ [ parameter_mode ] [ parameter ] parameter_type [, ...] ] )
[ SECURITY { INVOKER | DEFINER }
| RENAME TO new_procedure_name
| OWNER TO new_owner
| SET SCHEMA new_schema
| SOURCE TO new_source
]
Parameters
procedure
- The procedure to alter.
parameter_mode
- The IN and INOUT parameters of the stored procedure.
parameter
- The name of the parameter.
parameter_type
- The type of the parameter.
SECURITY { INVOKER | DEFINER }
- Specifies whether to execute the procedure with the privileges of the invoker or its definer (owner).
For details, see Executing stored procedures.
RENAME TO
new_procedure_name
- The new name for the procedure.
OWNER TO
new_owner
- The new owner (definer) of the procedure.
SET SCHEMA
new_schema
- The new schema of the procedure.
SOURCE TO
new_source
- The new procedure source code. For details, see Scope and structure.
Privileges
OWNER TO
Superuser
RENAME and SCHEMA TO
Non-superuser:
-
CREATE on the procedure's schema
-
Ownership of the procedure
Other operations
Non-superuser: Ownership of the procedure
Examples
17 - ALTER PROFILE
Changes a profile. All parameters that are not set in a profile inherit their setting from the default profile. You can use ALTER PROFILE
to change the default profile.
Syntax
ALTER PROFILE name LIMIT [
PASSWORD_LIFE_TIME setting
PASSWORD_MIN_LIFE_TIME setting
PASSWORD_GRACE_TIME setting
FAILED_LOGIN_ATTEMPTS setting
PASSWORD_LOCK_TIME setting
PASSWORD_REUSE_MAX setting
PASSWORD_REUSE_TIME setting
PASSWORD_MAX_LENGTH setting
PASSWORD_MIN_LENGTH setting
PASSWORD_MIN_LETTERS setting
PASSWORD_MIN_UPPERCASE_LETTERS setting
PASSWORD_MIN_LOWERCASE_LETTERS setting
PASSWORD_MIN_DIGITS setting
PASSWORD_MIN_SYMBOLS setting
PASSWORD_MIN_CHAR_CHANGE setting ]
Parameters
Note
To reset a parameter to inherit from the default profile, set its value todefault
.
Name | Description |
---|---|
name |
The name of the profile to create, where * To modify the default profile, set
|
PASSWORD_LIFE_TIME |
Set to an integer value, one of the following:
After your password's lifetime and grace period expire, you must change your password on your next login, if you have not done so already. |
PASSWORD_MIN_LIFE_TIME |
Set to an integer value, one of the following:
|
PASSWORD_GRACE_TIME |
Set to an integer value, one of the following:
|
FAILED_LOGIN_ATTEMPTS |
Set to an integer value, one of the following:
|
PASSWORD_LOCK_TIME |
|
PASSWORD_REUSE_MAX |
Set to an integer value, one of the following:
|
PASSWORD_REUSE_TIME |
Set to an integer value, one of the following:
|
PASSWORD_MAX_LENGTH |
The maximum number of characters allowed in a password, one of the following:
|
PASSWORD_MIN_LENGTH |
The minimum number of characters required in a password, one of the following:
|
PASSWORD_MIN_LETTERS |
Minimum number of letters (a-z and A-Z) that must be in a password, one of the following:
|
PASSWORD_MIN_UPPERCASE_LETTERS |
Minimum number of uppercase letters (A-Z) that must be in a password, one of the following:
|
PASSWORD_MIN_LOWERCASE_LETTERS |
Minimum number of lowercase letters (a-z) that must be in a password, one of the following:
|
PASSWORD_MIN_DIGITS |
Minimum number of digits (0-9) that must be in a password, one of the following:
|
PASSWORD_MIN_SYMBOLS |
Minimum number of symbols—printable non-letter and non-digit characters such as $, #, @—that must be in a password, one of the following:
|
PASSWORD_MIN_CHAR_CHANGE |
Minimum number of characters that must be different from the previous password:
|
Privileges
Superuser
Profile settings and client authentication
The following profile settings affect client authentication methods, such as LDAP or GSS:
-
FAILED_LOGIN_ATTEMPTS
-
PASSWORD_LOCK_TIME
All other profile settings are used only by Vertica to manage its passwords.
Examples
ALTER PROFILE sample_profile LIMIT FAILED_LOGIN_ATTEMPTS 3;
See also
18 - ALTER PROFILE RENAME
Rename an existing profile.
Syntax
ALTER PROFILE name RENAME TO new-name;
Parameters
name
- The current name of the profile.
new-name
- The new name for the profile.
Privileges
Superuser
Examples
This example shows how to rename an existing profile.
ALTER PROFILE sample_profile RENAME TO new_sample_profile;
See also
19 - ALTER PROJECTION
Changes the DDL of the specified projection.
Syntax
ALTER PROJECTION [[database.]schema.]projection
{ RENAME TO new-name | ON PARTITION RANGE BETWEEN min-val AND max-val | { ENABLE | DISABLE } }
Parameters
[
database
.]
schema
Database and schema. The default schema is
public
. If you specify a database, it must be the current database.projection
- The projection to change, where
projection
can be one of the following:-
Projection base name: Rename all projections that share this base name.
-
Projection name: Rename the specified projection and its base name. If the projection is segmented, its buddies are unaffected by this change.
See Projection naming for projection name conventions.
-
RENAME TO
new-name
- The new projection name.
ON PARTITION RANGE
-
Note
Valid only for projections that were created with a partition range.Limits projection data to a range of partition keys. The minimum value must be less than or equal to the maximum value.
Values can be NULL. A null minimum value indicates no lower bound and a null maximum value indicates no upper bound. If both are NULL, this statement drops the projection endpoints, producing a regular projection instead of a range projection.
If the new range of keys is outside the previous range, Vertica throws a warning that the projection is out of date and must be refreshed before it can be used.
For other requirements and usage details, see Partition range projections.
- ENABLE | DISABLE
- Specifies whether to mark this projection as unavailable for queries on its anchor table. If a projection is the queried table's only superprojection, attempts to disable it return with a rollback message. ENABLE restores the projection's availability to query planning. You can also mark a projection as unavailable for individual queries using the hint SKIP_PROJS.
Default: ENABLE
Privileges
Non-superuser, CREATE and USAGE on the schema and one of the following anchor table privileges:
-
Table owner
-
ALTER privilege
-
SELECT privilege only if defining a partition range projection
Syntactic sugar
The statement
=> ALTER PROJECTION foo REMOVE PARTITION RANGE;
has the same effect as
=> ALTER PROJECTION foo ON PARTITION RANGE BETWEEN NULL AND NULL;
Examples
=> SELECT export_tables('','public.store_orders');
export_tables
---------------------------------------------
CREATE TABLE public.store_orders
(
order_no int,
order_date timestamp NOT NULL,
shipper varchar(20),
ship_date date NOT NULL
);
(1 row)
=> CREATE PROJECTION store_orders_p AS SELECT * from store_orders;
CREATE PROJECTION
=> ALTER PROJECTION store_orders_p RENAME to store_orders_new;
ALTER PROJECTION
=> ALTER PROJECTION store_orders_new DISABLE;
=> SELECT * FROM store_orders_new;
ERROR 3586: Insufficient projections to answer query
DETAIL: No projections eligible to answer query
HINT: Projection store_orders_new not used in the plan because the projection is disabled.
=> ALTER PROJECTION store_orders_new ENABLE;
See also
CREATE PROJECTION
20 - ALTER RESOURCE POOL
Modifies an existing resource pool by setting one or more parameters.
You can use ALTER RESOURCE POOL to modify some parameters in Vertica built-in resource pools. For details on default settings and restrictions, see Built-in resource pools configuration.
Important
Changes to parameters of the built-in GENERAL resource pool take effect only when the database restarts.Syntax
ALTER RESOURCE POOL pool-name [ FOR subcluster ] parameter-name setting[...]
Arguments
pool-name
- Name of the resource pool to modify.
FOR
subcluster
Eon Mode only, the subcluster to associate with this resource pool, where
subcluster
is one of the following:SUBCLUSTER
subcluster-name
: Resource pool for an existing subcluster. You cannot be connected to this subcluster, otherwise Vertica returns an error.CURRENT SUBCLUSTER
: Resource pool for the subcluster that you are connected to.
Important
You cannot use ALTER RESOURCE POOL to convert a global resource pool to a subcluster-level resource pool.parameter-name setting
- A resource pool parameter and its new setting. To reset this parameter to its default value, specify
DEFAULT
.If you specify a subcluster, you can alter only the
MAXMEMORYSIZE
,MAXQUERYMEMORYSIZE
, andMEMORYSIZE
parameters for built-in pools.
Parameters
Note
Default values specified here pertain only to user-defined resource pools. For built-in pool default values, see Built-in resource pools configuration, or query system table RESOURCE_POOL_DEFAULTS.-
CASCADE TO
Secondary resource pool for executing queries that exceed the
RUNTIMECAP
setting of their assigned resource pool:CASCADE TO secondary-pool
-
CPUAFFINITYMODE
Specifies whether the resource pool has exclusive or shared use of the CPUs specified in
CPUAFFINITYSET
:CPUAFFINITYMODE { SHARED | EXCLUSIVE | ANY }
SHARED
: Queries that run in this resource pool share itsCPUAFFINITYSET
CPUs with other Vertica resource pools.EXCLUSIVE
: DedicatesCPUAFFINITYSET
CPUs to this resource pool only, and excludes other Vertica resource pools. IfCPUAFFINITYSET
is set as a percentage, then that percentage of CPU resources available to Vertica is assigned solely for this resource pool.ANY
: Queries in this resource pool can run on any CPU, invalid ifCPUAFFINITYSET
designates CPU resources.
Default:
ANY
-
CPUAFFINITYSET
CPUs available to this resource pool. All cluster nodes must have the same number of CPUs. The CPU resources assigned to this set are unavailable to general resource pools.
CPUAFFINITYSET { 'cpu-index[,...]' | 'cpu-indexi-cpu-indexn' | 'integer%' | NONE }
cpu-index
[,...]
: Dedicates one or more comma-delimited CPUs to this resource pool.cpu-indexi-cpu-indexn
: Dedicates a range of contiguous CPU indexesi
throughn
to this resource pool.integer
%
: Percentage of all available CPUs to use for this resource pool. Vertica rounds this percentage down to include whole CPU units.NONE
(empty string): No affinity set is assigned to this resource pool. Queries associated with this pool are executed on any CPU.
Default:
NONE
Important
CPUAFFINITYSET
andCPUAFFINITYMODE
must be set together in the same statement.-
EXECUTIONPARALLELISM
Number of threads used to process any single query issued in this resource pool.
EXECUTIONPARALLELISM { limit | AUTO }
limit
: An integer value between 1 and the number of cores. Setting this parameter to a reduced value increases throughput of short queries issued in the resource pool, especially if queries are executed concurrently.AUTO
or0
: Vertica calculates the setting from the number of cores, available memory, and amount of data in the system. Unless memory is limited, or the amount of data is very small, Vertica sets this parameter to the number of cores on the node.
Default:
AUTO
-
MAXCONCURRENCY
Maximum number of concurrent execution slots available to the resource pool across the cluster:
MAXCONCURRENCY { integer | NONE }
NONE
(empty string): Unlimited number of concurrent execution slots.Default:
NONE
-
MAXMEMORYSIZE
Maximum size per node the resource pool can grow by borrowing memory from the
GENERAL
pool:MAXMEMORYSIZE { 'integer%' |'integer{K|M|G|T}' NONE }
integer
%
: Percentage of total memoryinteger
{K|M|G|T}
: Amount of memory in kilobytes, megabytes, gigabytes, or terabytesNONE
(empty string): Unlimited, resource pool can borrow any amount of available memory from theGENERAL
pool.
Default:
NONE
MAXQUERYMEMORYSIZE
Maximum amount of memory this resource pool can allocate at runtime to process a query. If the query requires more memory than this setting, Vertica stops execution and returns an error.
Set this parameter as follows:
MAXQUERYMEMORYSIZE { 'integer%' | 'integer{K|M|G|T}' | NONE }
integer
%
: Percentage ofMAXMEMORYSIZE
for this resource pool.integer
{K|M|G|T}
: Amount of memory in kilobytes, megabytes, gigabytes, or terabytes, up to the value ofMAXMEMORYSIZE
.NONE
(empty string): Unlimited; resource pool can borrow any amount of available memory from the GENERAL pool, within the limits set byMAXMEMORYSIZE
.
Default:
NONE
Important
Changes toMAXQUERYMEMORYSIZE
are applied retroactively to queries that are currently executing. If you reduce this setting, queries that were budgeted with the previous memory size are liable to fail if they try to allocate more memory than the new setting allows.-
MEMORYSIZE
Total per-node memory available to the Vertica resource manager that is allocated to this resource pool:
MEMORYSIZE { 'integer%' | 'integer{K|M|G|T}' }
integer
%
: Percentage of total memoryinteger
{K|M|G|T}
: Amount of memory in kilobytes, megabytes, gigabytes, or terabytes
Default: 0%. No memory allocated, the resource pool borrows memory from the
GENERAL
pool.-
PLANNEDCONCURRENCY
Preferred number of queries to execute concurrently in the resource pool. This setting applies to the entire cluster:
PLANNEDCONCURRENCY { num-queries | AUTO }
-
num-queries
: Integer value ≥ 1, the preferred number of queries to execute concurrently in the resource pool. When possible, query resource budgets are limited to allow this level of concurrent execution. -
AUTO
: Value is calculated automatically at query runtime. Vertica sets this parameter to the lower of these two calculations, but never less than 4:-
Number of logical cores
-
Memory divided by 2GB
If the number of logical cores on each node is different,
AUTO
is calculated differently for each node. Distributed queries run like the minimal effective planned concurrency. Single node queries run with the planned concurrency of the initiator. -
Default:
AUTO
Tip
Change this parameter only after evaluating performance over a period of time.-
-
PRIORITY
Priority of queries in this resource pool when they compete for resources in the
GENERAL
pool:PRIORITY { integer | HOLD }
-
integer
: Negative or positive integer value, where higher numbers denote higher priority: -
HOLD
: Sets priority to-999
. Queries in this resource pool are queued untilQUEUETIMEOUT
is reached.
Default: 0
-
-
QUEUETIMEOUT
Maximum time a request can wait for pool resources before it is rejected, not more than one year:
QUEUETIMEOUT { integer | 'interval' | 'NONE' }
-
integer
: Maximum wait time in seconds -
[interval](/en/sql-reference/language-elements/literals/datetime-literals/interval-literal/): Maximum wait time expressed in the following format:
num year num months num [days] HH:MM:SS.ms
-
NONE
(empty string): No maximum wait time, request can be queued indefinitely, up to one year.
If the value that you specify resolves to more than one year, Vertica returns with a warning and sets the parameter to 365 days:
=> ALTER RESOURCE POOL user_0 QUEUETIMEOUT '11 months 50 days 08:32'; WARNING 5693: Using 1 year for QUEUETIMEOUT ALTER RESOURCE POOL => SELECT QUEUETIMEOUT FROM resource_pools WHERE name = 'user_0'; QUEUETIMEOUT -------------- 365 (1 row)
Default: 00:05 (5 minutes)
-
-
RUNTIMECAP
Maximum execution time allowed to queries in this resource pool, not more than one year, otherwise Vertica returns with an error. If a query exceeds this setting, it tries to cascade to a secondary pool:
RUNTIMECAP { 'interval' | NONE }
-
interval
: Maximum wait time expressed in the following format:num year num month num [day] HH:MM:SS.ms
-
NONE
(empty string): No maximum wait time, request can be queued indefinitely, up to one year.
If the user or session also has a
RUNTIMECAP
, the shorter limit applies.-
-
RUNTIMEPRIORITY
Determines how the resource manager should prioritize dedication of run-time resources (CPU, I/O bandwidth) to queries already running in this resource pool:
RUNTIMEPRIORITY { HIGH | MEDIUM | LOW }
Default:
MEDIUM
-
RUNTIMEPRIORITYTHRESHOLD
Maximum time (in seconds) in which query processing must complete before the resource manager assigns to it the resource pool's
RUNTIMEPRIORITY
. All queries begin execution with a priority of HIGH.RUNTIMEPRIORITYTHRESHOLD seconds
Default:
2
SINGLEINITIATOR
Set to false for backward compatibility. Do not change this setting.
Privileges
Superuser
Examples
Set resource pool PRIORITY to 5:
=> ALTER RESOURCE POOL ceo_pool PRIORITY 5;
Designate a secondary resource pool:
=> CREATE RESOURCE POOL second_pool;
=> ALTER RESOURCE POOL ceo_pool CASCADE TO second_pool;
Decrease to 0% the MAXMEMORYSIZE and MEMORYSIZE settings on the dashboard
subcluster's built-in TM resource pool. Changing these settings to 0 prevents the subcluster from running mergeout operations:
=> ALTER RESOURCE POOL TM FOR SUBCLUSTER dashboard MEMORYSIZE '0%'
MAXMEMORYSIZE '0%';
See Tuning tuple mover pool settings for more information.
See also
21 - ALTER ROLE
Renames an existing role.
Note
You cannot use ALTER ROLE to rename a role that was added to the Vertica database with the LDAPLink service.Syntax
ALTER ROLE name RENAME TO new-name
Parameters
name
- The role to rename.
new-name
- The role's new name.
Privileges
Superuser
Examples
=> ALTER ROLE applicationadministrator RENAME TO appadmin;
ALTER ROLE
See also
22 - ALTER ROUTING RULE
Changes an existing load balancing policy routing rule.
Syntax
ALTER ROUTING RULE { rule_name | FOR WORKLOAD workload_name } {
RENAME TO new_name |
SET ROUTE TO 'cidr_range'|
SET GROUP TO group_name |
SET WORKLOAD TO workload_name |
SET SUBCLUSTER TO subcluster_name [,...] |
SET PRIORITY TO priority |
{ ADD | REMOVE } SUBCLUSTER subcluster_name [,...]
}
Parameters
rule_name
- The name of the existing routing rule to change.
FOR WORKLOAD
workload_name
- The name of a workload.
RENAME TO new_name
- The new name of the routing rule.
SET ROUTE TO '
cidr_range
'
- An IPv4 or IPv6 address range in CIDR format. Changes the address range of client connections this rule applies to.
SET GROUP TO
group_name
- The load balancing group that handles the connections that match this rule.
SET WORKLOAD TO
workload_name
- The name of the workload.
SET SUBCLUSTER TO
subcluster_name
- One or more subclusters to route clients to.
SET PRIORITY TO
priority
- Sets the priority for the routing rule, where
priority
is a non-negative INTEGER. The greater the value, the greater the priority. If a user or their role is granted multiple routing rules, the one with the greatest priority applies. You can set the priority for a routing rule only when applying a routing rule to a workload. {ADD | REMOVE} SUBCLUSTER
subcluster_name
- Add or remove one or more subclusters from the routing rule.
Examples
This example changes the routing rule named etl_rule so it uses the load balancing group named etl_rule to handle incoming connections in the IP address range of 10.20.100.0 to 10.20.100.255.
=> ALTER ROUTING RULE etl_rule SET GROUP TO etl_group;
ALTER ROUTING RULE
=> ALTER ROUTING RULE etl_rule SET ROUTE TO '10.20.100.0/24';
ALTER ROUTING RULE
=> \x
Expanded display is on.
=> SELECT * FROM routing_rules WHERE NAME = 'etl_rule';
-[ RECORD 1 ]----+---------------
name | etl_rule
source_address | 10.20.100.0/24
destination_name | etl_group
This example routes analytics
workloads to the sc_analytics_2
subcluster:
=> ALTER ROUTING RULE FOR WORKLOAD analytics SET SUBCLUSTER TO `sc_analytics_2`;
This example changes the workload rule to handle reporting
instead of analytics
workloads:
=> ALTER ROUTING RULE FOR WORKLOAD analytics SET WORKLOAD TO reporting;
This example changes the priority of the workload rule for reporting
workloads:
=> ALTER ROUTING RULE FOR WORKLOAD reporting SET PRIORITY TO 5;
This example adds a subcluster to the routing rule:
=> ALTER ROUTING RULE FOR WORKLOAD analytics ADD SUBCLUSTER sc_01, sc_02;
This example removes a subcluster from the routing rule:
=> ALTER ROUTING RULE FOR WORKLOAD analytics REMOVE SUBCLUSTER sc_01, sc_02;
See also
23 - ALTER SCHEDULE
Modifies a schedule.
Syntax
ALTER SCHEDULE [[database.]schema.]schedule {
OWNER TO new_owner
| SET SCHEMA new_schema
| RENAME TO new_schedule
| USING CRON new_cron_expression
| USING DATETIMES new_timestamp_list
}
Arguments
[
database
.]
schema
Database and schema. The default schema is
public
. If you specify a database, it must be the current database.schedule
- The schedule to modify.
new_owner
- The new owner of the schedule.
new_schema
- The new schema of the schedule.
new_schedule
- The new name for the schedule.
new_cron_expression
- A
cron
expression. You should use this for recurring tasks. Value separators are not currently supported. new_timestamp_list
- A comma-separated list of timestamps. You should use this to schedule non-recurring events at arbitrary times.
Privileges
Superuser
Examples
To change the cron
expression for a schedule:
=> ALTER SCHEDULE daily_schedule USING CRON '0 8 * * *';
To change a schedule that uses a cron
expression to use a timestamp list instead:
=> ALTER SCHEDULE my_schedule USING DATETIMES('2023-10-01 12:30:00', '2022-11-01 12:30:00');
To rename a schedule:
=> ALTER SCHEDULE daily_schedule RENAME TO daily_8am_gmt;
24 - ALTER SCHEMA
Changes one or more schemas in one of the following ways:
-
Enable or disable inheritance of schema privileges by tables created in the schemas.
-
Reassign schema ownership to another user.
-
Change schema disk quota.
-
Rename one or more schemas.
Syntax
ALTER SCHEMA [{ namespace. | database. }]schema
DEFAULT {INCLUDE | EXCLUDE} SCHEMA PRIVILEGES
| OWNER TO user-name [CASCADE]
| DISK_QUOTA { value | SET NULL }
You can rename more than one schema in a single operation:
ALTER SCHEMA [{ namespace. | database. }]schema[,...] RENAME TO [namespace.]new-schema-name[,...]
Parameters
namespace
- For Eon Mode databases, namespace of the schema to alter. If no namespace is specified, the namespace of the schema is assumed to be
default_namespace
. database
- For Enterprise Mode databases, name of the database containing the schema. If specified, it must be the current database.
schema
- Name of the schema to modify.
DEFAULT {INCLUDE | EXCLUDE} SCHEMA PRIVILEGES
Specifies whether to enable or disable default inheritance of privileges for new tables in the specified schema:
-
EXCLUDE SCHEMA PRIVILEGES
(default): Disables inheritance of schema privileges. -
INCLUDE SCHEMA PRIVILEGES
: Specifies to grant tables in the specified schema the same privileges granted to that schema. This option has no effect on existing tables in the schema.
See also Enabling schema inheritance.
-
OWNER TO
- Reassigns schema ownership to the specified user:
OWNER TO user-name [CASCADE]
By default, ownership of objects in the reassigned schema remain unchanged. To reassign ownership of schema objects to the new schema owner, qualify the OWNER TO clause with CASCADE. For details, see Cascading Schema Ownership below.
DISK_QUOTA
- One of the following:
-
A string, an integer followed by a supported unit: K, M, G, or T. If the new value is smaller than the current usage, the operation succeeds but no further disk space can be used until usage is reduced below the new quota.
-
SET NULL to remove a quota.
For more information, see Disk quotas.
-
RENAME TO
- Renames one or more schemas:
RENAME TO [namespace.]new-schema-name[,...]
The following requirements apply:
-
The new schema name conforms to conventions described in Identifiers. It must also be unique among all names of sequences, tables, projections, views, models, and schemas in the namespace.
-
If you specify multiple schemas to rename, the source and target lists must have the same number of names.
-
If you specified the
namespace
of the schema to alter, you must provide the same namespace for thenew-schema-name
. You cannot rename a schema to a different namespace.
Caution
Renaming a schema referenced by a view causes the view to fail unless another schema is created to replace it. -
Privileges
One of the following:
-
Superuser
-
Schema owner
Cascading schema ownership
By default, ALTER SCHEMA...OWNER TO does not affect ownership of objects in the target schema or the privileges granted on them. If you qualify the OWNER TO clause with CASCADE, Vertica acts as follows on objects in the target schema:
-
Transfers ownership of objects owned by the previous schema owner to the new owner.
-
Revokes all object privileges granted by the previous schema owner.
If issued by non-superusers, ALTER SCHEMA...OWNER TO CASCADE ignores all objects that belong to other users, and returns with notices on the objects that it cannot change. For example:
-
Schema
ms
is owned by usermayday
, and contains two tables:ms.t1
owned by mayday, andms.t2
owned by userjoe
:=> \dt List of tables Schema | Name | Kind | Owner | Comment ----------------+-----------------------+-------+---------+--------- ms | t1 | table | mayday | ms | t2 | table | joe |
-
User
mayday
transfers ownership of schemams
to userdbadmin
, using CASCADE. On return, ALTER SCHEMA reports that it cannot transfer ownership of tablems.t2
and its projections, which are owned by userjoe
:=> \c - mayday You are now connected as user "mayday". => ALTER SCHEMA ms OWNER TO dbadmin CASCADE; NOTICE 3583: Insufficient privileges on ms.t2 NOTICE 3583: Insufficient privileges on ms.t2_b0 NOTICE 3583: Insufficient privileges on ms.t2_b1 ALTER SCHEMA => \c You are now connected as user "dbadmin". => \dt List of tables Schema | Name | Kind | Owner | Comment ----------------+-----------------------+-------+---------+--------- ms | t1 | table | dbadmin | ms | t2 | table | joe |
-
User
dbadmin
transfers ownership of schemams
to userpat
, again using CASCADE. This time, becausedbadmin
is a superuser, ALTER SCHEMA transfers ownership of allms
tables to userpat
=> ALTER SCHEMA ms OWNER TO pat CASCADE; ALTER SCHEMA => \dt List of tables Schema | Name | Kind | Owner | Comment ----------------+-----------------------+-------+---------+--------- ms | t1 | table | pat | ms | t2 | table | pat |
Swapping schemas
Renaming schemas is useful for swapping schemas without actually moving data. To facilitate the swap, enter a non-existent, temporary placeholder schema. For example, the following ALTER SCHEMA
statement uses the temporary schema temps
to facilitate swapping schema S1 with schema S2. In this example, S1
is renamed to temps
. Then S2
is renamed to S1
. Finally, temps
is renamed to S2
.
=> ALTER SCHEMA S1, S2, temps RENAME TO temps, S1, S2;
Examples
The following example renames schemas S1
and S2
to S3
and S4
, respectively:
=> ALTER SCHEMA S1, S2 RENAME TO S3, S4;
This example sets the default behavior for new table t2
to automatically inherit the schema's privileges:
=> ALTER SCHEMA s1 DEFAULT INCLUDE SCHEMA PRIVILEGES;
=> CREATE TABLE s1.t2 (i, int);
This example sets the default for new tables to not automatically inherit privileges from the schema:
=> ALTER SCHEMA s1 DEFAULT EXCLUDE SCHEMA PRIVILEGES;
In an Eon Mode database, the following statement renames the train
schema to ferry
in the transit
namespace:
=> ALTER SCHEMA transit.train RENAME TO transit.ferry;
See also
25 - ALTER SEQUENCE
Changes a sequence in two ways:
- Changes values that control sequence behavior—for example, its start value and range of minimum and maximum values. These changes take effect only when you start a new database session.
- Changes sequence name, schema, or ownership. These changes take effect immediately.
Syntax
Change sequence behavior:
ALTER SEQUENCE [[database.]schema.]sequence
[ INCREMENT [ BY ] integer ]
[ MINVALUE integer | NO MINVALUE ]
[ MAXVALUE integer | NO MAXVALUE ]
[ RESTART [ WITH ] integer ]
[ CACHE integer | NO CACHE ]
[ CYCLE | NO CYCLE ]
Change sequence name, schema, or ownership:
ALTER SEQUENCE [schema.]sequence-name {
RENAME TO name
| SET SCHEMA schema]
| OWNER TO owner
}
Arguments
[
database
.]
schema
Database and schema. The default schema is
public
. If you specify a database, it must be the current database.sequence
- Name of the sequence to alter.
In the case of IDENTITY table columns, Vertica generates the sequence name using the following convention:
table-name_col-name_seq
To obtain this name, query the SEQUENCES system table.
INCREMENT
integer
Positive or negative minimum value change on each call to NEXTVAL. The default is 1.
This value is a minimum increment size. The sequence can increment by more than this value unless you also specify
NO CACHE
.MINVALUE
integer
| NO MINVALUE
- Minimum value the sequence can generate. If this change would invalidate the current sequence position, the operation fails.
MAXVALUE
integer
| NO MAXVALUE
- Maximum value the sequence can generate. If this change would invalidate the current sequence position, the operation fails.
RESTART
integer
- New start value of the sequence. The next call to NEXTVAL returns the new start value.
Caution
Using ALTER SEQUENCE to set a sequence start value below its current value can result in duplicate keys. CACHE | NO CACHE
How many unique sequence numbers to pre-allocate and store in memory on each node for faster access. Vertica sets up caching for each session and distributes it across all nodes. A value of 0 or 1 is equivalent to
NO CACHE
.Caution
If sequence caching is set to a low number, nodes might request a new set of cache values more frequently. While it supplies a new cache, Vertica must lock the catalog. Until Vertica releases the lock, other database activities such as table inserts are blocked, which can adversely affect overall performance.By default, the sequence cache is set to 250,000.
For details, see Distributing sequences.
CYCLE | NO CYCLE
- How to handle reaching the end of the sequence. The default is
NO CYCLE
, meaning that a call to NEXTVAL returns an error after the sequence reaches its upper or lower limit.CYCLE
instead wraps, as follows:- Ascending sequence: wraps to the minimum value.
- Descending sequence: wraps to the maximum value.
RENAME TO
name
- New name in the current schema for the sequence. Supported only for named sequences.
SET SCHEMA
schema
- Moves the sequence to a new schema. Supported only for named sequences.
OWNER TO
owner
- Reassigns sequence ownership to another user.
Privileges
For named sequences, USAGE on the schema and one of the following:
-
Sequence owner
-
ALTER privilege on the sequence
-
For certain operations, non-superusers must have the following schema privileges:
- To rename a sequence: CREATE, USAGE
- To move a sequence to another schema: CREATE on the destination, USAGE on the source
For IDENTITY column sequences, USAGE on the table schema and one of the following:
-
Table owner
-
ALTER privileges
Non-superusers must also have SELECT privileges to enable or disable constraint enforcement, or remove partitioning.
Examples
See Altering sequences.
See also
CREATE SEQUENCE26 - ALTER SESSION
ALTER SESSION sets and clears session-level configuration parameter values for the current session. To identify session-level parameters, query the CONFIGURATION_PARAMETERS system table.
Syntax
ALTER SESSION {
SET [PARAMETER] parameter=value[,...]
| CLEAR { [PARAMETER] parameter[,...] | PARAMETER ALL }
| SET UDPARAMETER [ FOR libname ] ud-parameter=value[,...]
| CLEAR UDPARAMETER { [ FOR libname ] ud-parameter[,...] | ALL }
}
Arguments
SET [PARAMETER]
parameter
=
value
- Sets one or more configuration parameters to the specified value.
CLEAR { [PARAMETER]
parameter
| PARAMETER ALL }
- Clears the specified configuration parameters, or all parameters, of changes that were set in the current session.
SET UDPARAMETER [ FOR
libname
]
ud-parameter
=
value
[,...]
- Sets one or more user-defined session parameters to be used with UDxs. The maximum length of a parameter name when set with ALTER SESSION is 128 characters.
If you specify a library, then only that library can access the parameter's value. Use this restriction to protect parameters that hold sensitive data, such as credentials.
CLEAR UDPARAMETER { [ FOR
libname
]
ud-parameter
[,...] | ALL }
- Clears the specified user-defined parameters, or all of them, of changes that were set in the current session.
Privileges
None
Examples
Set a configuration parameter:
=> ALTER SESSION SET ForceUDxFencedMode = 1;
ALTER SESSION
=> SELECT parameter_name, current_value, default_value
FROM CONFIGURATION_PARAMETERS
WHERE parameter_name = 'ForceUDxFencedMode';
parameter_name | current_value | default_value
--------------------+---------------+---------------
ForceUDxFencedMode | 1 | 0
(1 row)
Clear all session-level settings for configuration parameters:
=> ALTER SESSION CLEAR PARAMETER ALL;
ALTER SESSION
Set a UDx parameter in a single library:
=> ALTER SESSION SET UDPARAMETER FOR MyLibrary RowCount = 25;
ALTER SESSION
27 - ALTER SUBCLUSTER
Changes the configuration of a subcluster. You can use this statement to rename a subcluster or make it the default subcluster.
Syntax
ALTER SUBCLUSTER subcluster-name {
RENAME TO new-name |
SET DEFAULT
}
Parameters
subcluster-name
- The name of the subcluster to alter.
RENAME TO
new-name
- Changes the name of the subcluster to
new-name
. SET DEFAULT
- Makes the subcluster the default subcluster. When you add new nodes to the database and do not specify a subcluster to contain them, Vertica adds them to the default subcluster. There can be only one default subcluster at a time. The subcluster that was previously the default subcluster becomes a non-default subcluster.
Privileges
SuperuserExamples
This example makes the analytics_cluster the default subcluster:
=> SELECT DISTINCT subcluster_name FROM SUBCLUSTERS WHERE is_default = true;
subcluster_name
--------------------
default_subcluster
(1 row)
=> ALTER SUBCLUSTER analytics_cluster SET DEFAULT;
ALTER SUBCLUSTER
=> SELECT DISTINCT subcluster_name FROM SUBCLUSTERS WHERE is_default = true;
subcluster_name
-------------------
analytics_cluster
(1 row)
This example renames default_subcluster to load_subcluster:
=> ALTER SUBCLUSTER default_subcluster RENAME TO load_subcluster;
ALTER SUBCLUSTER
=> SELECT DISTINCT subcluster_name FROM subclusters;
subcluster_name
-------------------
load_subcluster
analytics_cluster
(2 rows)
See also
28 - ALTER SUBNET
Renames an existing subnet.
Syntax
ALTER SUBNET subnet-name RENAME TO new-subnet-name
Parameters
subnet-name
- The name of the existing subnet.
new-subnet-name
- The new name for the subnet.
Privileges
Superuser
Examples
=> ALTER SUBNET mysubnet RENAME TO myNewSubnet;
29 - ALTER TABLE
Modifies the metadata of an existing table. All changes are auto-committed.
Syntax
ALTER TABLE [[{ namespace. | database. } ]schema.]table {
ADD COLUMN [ IF NOT EXISTS ] column datatype
[ column‑constraint ]
[ ENCODING encoding-type ]
[ PROJECTIONS (projections-list) | ALL PROJECTIONS ]
[, ...]
| ADD table-constraint
| ALTER COLUMN column {
ENCODING encoding-type PROJECTIONS (projection-list)
| { SET | DROP } expression }
| ALTER CONSTRAINT constraint-name { ENABLED | DISABLED }
| DISK_QUOTA { value | SET NULL }
| DROP CONSTRAINT constraint-name [ CASCADE | RESTRICT ]
| DROP [ COLUMN ] [ IF EXISTS ] column [ CASCADE | RESTRICT ]
| FORCE OUTER integer
| { INCLUDE | EXCLUDE | MATERIALIZE } [ SCHEMA ] PRIVILEGES
| OWNER TO owner
| partition-clause [ REORGANIZE ]
| REMOVE PARTITIONING
| RENAME [ COLUMN ] name TO new‑name
| RENAME TO new-table-name[,...]
| REORGANIZE
| SET {
ActivePartitionCount { count | DEFAULT }
| IMMUTABLE ROWS
| MERGEOUT { 1 | 0 }
| SCHEMA [namespace.]schema }
}
Note
Several ALTER TABLE clauses cannot be specified with other clauses in the same statement (see Exclusive ALTER TABLE Clauses below). Otherwise, ALTER TABLE supports multiple comma-delimited clauses. For example, the following ALTER TABLE statement changes the my_table
table in two ways: reassigns ownership to Joe
, and sets a UNIQUE constraint on the b
column:
=> ALTER TABLE my_table OWNER TO Joe,
ADD CONSTRAINT unique_b UNIQUE (b) ENABLED;
Arguments
namespace
- For Eon Mode databases, namespace that contains the table to alter. If no namespace is specified, the namespace of the table is assumed to be
default_namespace
. database
- For Enterprise Mode databases, name of the database containing the table. If specified, it must be the current database.
schema
- Name of the schema that contains the table to alter.
table
- The table to alter.
-
ADD COLUMN
- Adds one or more columns to the table and to the specified projections (or all projections, if not specified).
Adding a column with the same name as one that already exists is an error unless you use IF NOT EXISTS.
Restrictions on columns of complex types also apply to columns that you add using ADD COLUMN.
You can qualify the new column definition with one or more of these options:
-
A column constraint:
[ CONSTRAINT constraint-name ] { [NOT] NULL | DEFAULT expression | SET USING expression | DEFAULT USING expression }
Columns that use DEFAULT with static values can be added in a single ALTER TABLE statement. Columns that use non-static DEFAULT values must be added in separate ALTER TABLE statements. For more information, see Column-constraint.
-
ENCODING
: the column encoding type, by defaultAUTO
. -
PROJECTIONS
: a comma-separated list of base names of existing projections to add the column to. Vertica adds the column to all buddies of each projection. The projection list cannot include projections with pre-aggregated data such as live aggregate projections. Instead of specifying projections, you can useALL PROJECTIONS
to add the column to all projections, excluding those with pre-aggregated data.
-
-
ADD table‑constraint
- Adds a constraint to a table that does not have any associated projections.
-
ALTER COLUMN
- You can alter an existing column in one of two ways:
-
Set encoding on a column for one or more projections of this table:
ENCODING encoding-type PROJECTIONS (projections-list)
where
projections-list
is a comma-delimited list of projections to update with the new encoding. You can specify each projection in two ways:-
Projection base name: Update all projections that share this base name.
-
Projection name: Update the specified projection. If the projection is segmented, the change is propagated to all buddies.
If one of the projections does not contain the target column, Vertica returns with a rollback error.
For details, see Projection Column Encoding.
-
-
Set or drop a setting for a column of scalar data, including primitive arrays:
SET { DEFAULT expression | USING expression | DEFAULT USING expression | NOT NULL | DATA TYPE datatype } DROP { DEFAULT | SET USING | DEFAULT USING | NOT NULL }
You cannot change the data type of a column of any complex type that is neither a scalar type nor an array of scalar types. One exception applies: in external tables, you can change a primitive column type to a complex type.
Setting a DEFAULT or SET USING expression has no effect on existing column values. To refresh the column with its DEFAULT or SET USING expression, update it as follows
-
SET USING column: Call REFRESH_COLUMNS on the table.
-
DEFAULT column: update the column as follows:
UPDATE table-name SET column-name=DEFAULT;
-
Altering a column with DEFAULT or SET USING can increase disk usage, which can cause the operation to fail if it would violate the table or schema disk quota.
-
-
ALTER CONSTRAINT
- Specifies whether to enforce primary key, unique key, and check constraints:
ALTER CONSTRAINT constraint-name {ENABLED | DISABLED}
DISK_QUOTA
- One of the following:
-
A string, an integer followed by a supported unit: K, M, G, or T. If the new value is smaller than the current usage, the operation succeeds but no further disk space can be used until usage is reduced below the new quota.
-
SET NULL to remove a quota.
For more information, see Disk quotas.
-
-
DROP CONSTRAINT
- Drops the specified table constraint from the table:
DROP CONSTRAINT constraint-name [CASCADE | RESTRICT]
You can qualify DROP CONSTRAINT with one of these options:
-
CASCADE: Drops a constraint and all dependencies in other tables.
-
RESTRICT: Does not drop a constraint if there are dependent objects. Same as the default behavior.
Dropping a table constraint has no effect on views that reference the table.
-
-
DROP [COLUMN]
- Drops the specified column from the table and that column's ROS containers:
DROP [COLUMN] [IF EXISTS] column [CASCADE | RESTRICT]
You can qualify DROP COLUMN with one of these options:
-
IF EXISTS generates an informational message if the column does not exist. If you omit this option and the column does not exist, Vertica generates a ROLLBACK error message.
-
CASCADE is required if the column has dependencies.
-
RESTRICT drops the column only from the given table.
The column's table cannot be immutable.
-
FORCE OUTER
integer
- Specifies whether a table is joined to another as an inner or outer input. For details, see Controlling join inputs.
{INCLUDE | EXCLUDE | MATERIALIZE} [SCHEMA] PRIVILEGES
- Specifies default inheritance of schema privileges for this table:
-
EXCLUDE PRIVILEGES (default) disables inheritance of privileges from the schema.
-
INCLUDE PRIVILEGES grants the table the same privileges granted to its schema.
-
MATERIALIZE PRIVILEGES copies grants to the table and creates a GRANT object on the table. This disables the inherited privileges flag on the table, so you can:
-
Grant more specific privileges at the table level.
-
Use schema-level privileges as a template.
-
Move the table to a different schema.
-
Change schema privileges without affecting the table.
Note
If inherited privileges are disabled at the database level, schema privileges can still be materialized. -
-
OWNER TO
owner
- Changes the table owner.
-
partition-clause [REORGANIZE]
- Invalid for external tables, logically divides table data storage through a PARTITION BY clause:
PARTITION BY expression [ GROUP BY expression ] [ SET ACTIVEPARTITIONCOUNT integer ]
For details, see Partition clause.
If you qualify the partition clause with REORGANIZE and the table previously specified no partitioning, the Vertica Tuple Mover immediately implements the partition clause. If the table previously specified partitioning, the Tuple Mover evaluates ROS storage containers and reorganizes them as needed to conform with the new partition clause.
REMOVE PARTITIONING
- Specifies to remove partitioning from a table definition. The Tuple Mover subsequently removes existing partitions from ROS containers.
-
RENAME [COLUMN]
- Renames the specified column within the table. The column's table cannot be immutable.
-
RENAME TO
- Renames one or more tables:
RENAME TO new-table-name[,...]
The following requirements apply:
-
The renamed table must be in the same schema as the original table.
-
The new table name conforms to conventions described in Identifiers. It must also be unique among all names of sequences, tables, projections, views, and models within the same schema.
-
If you specify multiple tables to rename, the source and target lists must have the same number of names.
-
-
REORGANIZE
- Valid only for partitioned tables, invokes the Tuple Mover to reorganize ROS storage containers as needed to conform with the table's current partition clause. ALTER TABLE...REORGANIZE and Vertica meta-function PARTITION_TABLE operate identically.
REORGANIZE can also qualify a new partition clause.
SET
- Changes a table setting, one of the following:
-
ActivePartitionCount {
count
| DEFAULT }, valid only for partitioned tables, specifies how many partitions are active for this table, one of the following:-
count
: Unsigned integer, supersedes configuration parameter ActivePartitionCount. -
DEFAULT: Removes the table-level active partition count. The table obtains its active partition count from the configuration parameter ActivePartitionCount.
For details on usage, see Active and inactive partitions.
-
-
IMMUTABLE ROWS prevents changes to table row values by blocking DML operations such as UPDATE and DELETE. Once set, table immutability cannot be reverted.
You cannot set a flattened table to be immutable. For details on all immutable table restrictions, see Immutable tables.
-
MERGEOUT { 1 | 0 } specifies whether to enable or disable mergeout to ROS containers that consolidate projection data of this table. By default, mergeout is enabled (1) on all tables.
-
SCHEMA [
namespace.
]schema-name
moves the table from its current schema to [namespace.
]schema-name
. For Eon Mode databases, the new schema must be in the same namespace as the old schema. When nonamespace
is provided, the namespace is set todefault_namespace
.
Vertica automatically moves all projections that are anchored to the source table to the destination schema. It also moves all IDENTITY columns to the destination schema. For details, see Moving tables to another schema
-
Privileges
Non-superuser: USAGE on the schema and one of the following:
-
Table owner
-
ALTER privileges
Non-superusers must also have SELECT privileges to enable or disable constraint enforcement, or remove partitioning.
For certain operations, non-superusers must have the following schema privileges:
-
To rename a table: CREATE, USAGE
-
To move a table to another schema: USAGE on the source schema, CREATE on the destination schema
Restrictions for complex types
Complex types used in native tables have some restrictions, in addition to the restrictions for individual types listed on their reference pages:
-
A native table must have at least one column that is a primitive type or a native array (one-dimensional array of a primitive type). If a flex table has real columns, it must also have at least one column satisfying this restriction.
-
Complex type columns cannot be used in ORDER BY or PARTITION BY clauses nor as FILLER columns.
-
Complex type columns cannot have constraints.
-
Complex type columns cannot use DEFAULT or SET USING.
-
Expressions returning complex types cannot be used as projection columns, and projections cannot be segmented or ordered by columns of complex types.
Exclusive ALTER TABLE clauses
The following ALTER TABLE clauses cannot be combined with another ALTER TABLE clause:
-
ADD COLUMN
-
DROP COLUMN
-
RENAME COLUMN
-
SET SCHEMA
-
RENAME [TO]
Node down limitations
Enterprise Mode only
The following ALTER TABLE operations are not supported when one or more database cluster nodes are down:
-
ALTER COLUMN ... ADD table-constraint
-
ALTER COLUMN ... SET DATA TYPE
-
ALTER COLUMN ... { SET DEFAULT | DROP DEFAULT }
-
ALTER COLUMN ... { SET USING | DROP SET USING }
-
ALTER CONSTRAINT
-
DROP COLUMN
-
DROP CONSTRAINT
Pre-aggregated projection restrictions
You cannot modify the metadata of anchor table columns that are included in live aggregate or Top-K projections. You also cannot drop these columns. To make these changes, you must first drop all live aggregate and Top-K projections that are associated with it.
External table restrictions
Not all ALTER TABLE options pertain to external tables. For instance, you cannot add a column to an external table, but you can rename the table:
=> ALTER TABLE mytable RENAME TO mytable2;
ALTER TABLE
Locked tables
If the operation cannot obtain an O lock on the target table, Vertica tries to close any internal Tuple Mover sessions that are running on that table. If successful, the operation can proceed. Explicit Tuple Mover operations that are running in user sessions do not close. If an explicit Tuple Mover operation is running on the table, the operation proceeds only when the operation is complete.
Examples
In an Eon Mode database, the following statement alters the schema of the flights
table from schema airline1
in namespace airport
to schema airline2
in namespace airport
:
=> ALTER TABLE airport.airline1.flights SET SCHEMA airport.airline2.flights;
See also
29.1 - Projection column encoding
After you create a table and its projections, you can call ALTER TABLE...ALTER COLUMN to set or change the encoding type of an existing column in one or more projections. For example:
ALTER TABLE store.store_dimension ALTER COLUMN store_region
ENCODING rle PROJECTIONS (store.store_dimension_p1_b0, store.store_dimension_p2);
In this example, the ALTER TABLE statement specifies to set RLE encoding on column store_region
for two projections: store_dimension_p1_b0
and store_dimension_p2
. The PROJECTIONS
list references the two projections by their projection name and base name, respectively. You can reference a projection either way; in both cases, the change is propagated to all buddies of the projection and stored in its DDL accordingly:
=> select export_objects('','store.store_dimension');
export_objects
------------------------------------------------------------------
CREATE TABLE store.store_dimension
(
store_key int NOT NULL,
store_name varchar(64),
store_number int,
store_address varchar(256),
store_city varchar(64),
store_state char(2),
store_region varchar(64)
);
CREATE PROJECTION store.store_dimension_p1
(
store_key,
store_name,
store_number,
store_address,
store_city,
store_state,
store_region ENCODING RLE
)
AS
SELECT store_dimension.store_key,
store_dimension.store_name,
store_dimension.store_number,
store_dimension.store_address,
store_dimension.store_city,
store_dimension.store_state,
store_dimension.store_region
FROM store.store_dimension
ORDER BY store_dimension.store_key
SEGMENTED BY hash(store_dimension.store_key) ALL NODES KSAFE 1;
CREATE PROJECTION store.store_dimension_p2
(
store_key,
store_name,
store_number,
store_address,
store_city,
store_state,
store_region ENCODING RLE
)
AS
SELECT ...
Important
When you add or change a column's encoding type, it has no immediate effect on existing projection data. Vertica applies the encoding only to newly loaded data, and to existing data on mergeout.30 - ALTER TLS CONFIGURATION
Alters a specified TLS Configuration object. For information on existing TLS Configuration objects, query TLS_CONFIGURATIONS.
Syntax
ALTER TLS CONFIGURATION tls_config_name {
[ CERTIFICATE { NULL | cert_name } ]
[ ADD CA CERTIFICATES ca_cert_name [,...] ]
[ REMOVE CA CERTIFICATES ca_cert_name [,...] ]
[ CIPHER SUITES { '' | 'openssl_cipher [,...]' } ]
[ TLSMODE 'tlsmode' ]
[ OWNER TO user_name ]
}
Parameters
tls_config_name
- The TLS Configuration object to alter.
NULL
- Removes the non-CA certificate from the TLS Configuration.
cert_name
- A certificate created with CREATE CERTIFICATE.
You must have USAGE privileges on the certificate (either from ownership of the certificate or USAGE on its key, if any) to add it to a TLS Configuration.
ca_cert_name
- A CA certificate created with CREATE CERTIFICATE.
You must have USAGE privileges on the certificate (either from ownership of the certificate or USAGE on its key, if any) to add it to a TLS Configuration.
openssl_cipher
- A comma-separated list of cipher suites to use instead of the default set of cipher suites. Providing an empty string for this parameter clears the alternate cipher suite list and instructs the specified TLS Configuration to use the default set of cipher suites.
To view enabled cipher suites, use LIST_ENABLED_CIPHERS.
tlsmode
- How Vertica establishes TLS connections and handles certificates, one of the following, in order of ascending security:
-
DISABLE
: Disables TLS. All other options for this parameter enable TLS. -
ENABLE
: Enables TLS. Vertica does not check client certificates. -
TRY_VERIFY
: Establishes a TLS connection if one of the following is true:-
the other host presents a valid certificate
-
the other host doesn't present a certificate
If the other host presents an invalid certificate, the connection will use plaintext.
-
-
VERIFY_CA
: Connection succeeds if Vertica verifies that the other host's certificate is from a trusted CA. If the other host does not present a certificate, the connection uses plaintext. -
VERIFY_FULL
: Connection succeeds if Vertica verifies that the other host's certificate is from a trusted CA and the certificate'scn
(Common Name) orsubjectAltName
attribute matches the hostname or IP address of the other host.Note that for client certificates,
cn
is used for the username, sosubjectAltName
must match the hostname or IP address of the other host.
VERIFY_FULL
is unsupported for client-server TLS (the connection type handled by ServerTLSConfig) and behaves likeVERIFY_CA
.Note
Whether Vertica or the other party acts as the client or server depends on the type of connection. For connections between the Vertica database and an LDAP server for LDAP Link or LDAP authentication, the Vertica database is the client and the LDAP server is the server:
For all other connection types, Vertica is the server and the other party is the client:
-
InternodeTLSConfig (the other Vertica nodes are both the client and server)
-
Privileges
Non-superuser: ALTER privileges on the TLS Configuration.
Examples
To remove all certificates and CA certificates from the LDAPLink TLS Configuration:
=> SELECT * FROM tls_configurations WHERE name='LDAPLink';
name | owner | certificate | ca_certificate | cipher_suites | mode
----------+---------+-------------+----------------+---------------+---------
LDAPLink | dbadmin | server_cert | ca | | DISABLE
LDAPLink | dbadmin | server_cert | ica | | DISABLE
(2 rows)
=> ALTER TLS CONFIGURATION LDAPLink CERTIFICATE NULL REMOVE CA CERTIFICATES ca, ica;
ALTER TLS CONFIGURATION
=> SELECT * FROM tls_configurations WHERE name='LDAPLink';
name | owner | certificate | ca_certificate | cipher_suites | mode
----------+---------+-------------+----------------+---------------+---------
LDAPLink | dbadmin | | | | DISABLE
(3 rows)
To use an alternate set of cipher suites for client-server TLS:
=> ALTER TLS CONFIGURATION server CIPHER SUITES
'DHE-PSK-AES256-CBC-SHA384,
DHE-PSK-AES128-GCM-SHA256,
PSK-AES128-CBC-SHA256';
ALTER TLS CONFIGURATION
=> SELECT name, cipher_suites FROM tls_configurations WHERE name='server';
name | cipher_suites
server | DHE-PSK-AES256-CBC-SHA384,DHE-PSK-AES128-GCM-SHA256,PSK-AES128-CBC-SHA256
(1 row)
For other examples, see:
31 - ALTER TRIGGER
Modifies a trigger.
Syntax
ALTER TRIGGER [[database.]schema.]trigger {
OWNER TO new_owner
| SET SCHEMA new_schema
| RENAME TO new_trigger
| PROCEDURE TO new_procedure
}
Parameters
[
database
.]
schema
Database and schema. The default schema is
public
. If you specify a database, it must be the current database.trigger
- The trigger to modify.
new_owner
- The new owner and definer of the trigger. This affects the behavior of AS DEFINER.
new_schema
- The new schema of the trigger.
new_trigger
- The new name for the trigger.
new_procedure
- The function signature of the stored procedure.
Privileges
Superuser
Examples
To attach a different procedure to the trigger:
=> ALTER TRIGGER daily_1am PROCEDURE TO log_user_actions(10, 20);
To rename a trigger:
=> ALTER TRIGGER daily_1am RENAME TO daily_1am_gmt;
To move the trigger to a different schema:
=> ALTER TRIGGER daily_1am_gmt
32 - ALTER USER
Changes user account parameters and user-level configuration parameters.
Syntax
ALTER USER user-name {
account-parameter value[,...]
| SET [PARAMETER] cfg-parameter=value[,...]
| CLEAR [PARAMETER] cfg-parameter[,...]
}
Parameters
user-name
- Name of the user. Names that contain special characters must be double-quoted. To enforce case-sensitivity, use double-quotes.
For details on name requirements, see Creating a database name and password.
account-parameter
value
- Specifies user account settings (see below).
Note
Changes to a user account apply only to the current session and to all later sessions launched by this user. SET [PARAMETER]
- Sets the specified configuration parameters. The new setting applies only to the current session, and to all later sessions launched by this user. Concurrent user sessions are unaffected by new settings unless they call meta-function RESET_SESSION.
CLEAR [PARAMETER]
- Resets the specified configuration parameters to their default values.
Important
SET | CLEAR PARAMETER can specify only user-level configuration parameters, otherwise Vertica returns an error. For details, see Setting User-Level Configuration Parameters below.User account parameters
Specify one or more user-account parameters and their settings as a comma-delimited list:
account-parameter value[,...]
Important
The following user-account parameters are invalid for a user who is added to the Vertica database with the LDAPLink service:
-
IDENTIFIED BY
-
PROFILE
-
SECURITY ALGORITHM
Parameter | Setting |
---|---|
ACCOUNT |
Locks or unlocks user access to the database, one of the following:
TipTo automate account locking, set a maximum number of failed login attempts with CREATE PROFILE. |
DEFAULT ROLE |
Specifies what roles are the default roles for this user, set to one of the following:
Default roles are automatically activated when a user logs in. The roles specified by this parameter supersede any roles assigned earlier. Note
|
GRACEPERIOD |
Specifies how long a user query can block on any session socket, one of the following:
For details, see Handling session socket blocking. |
IDENTIFIED BY |
Changes the user's password: IDENTIFIED BY '[new-password]' | ['hashed-password' SALT 'hash-salt'] [REPLACE 'current-password']
For details, see Password guidelines and Creating a database name and password. |
IDLESESSIONTIMEOUT |
The length of time the system waits before disconnecting an idle session, one of the following:
For details, see Managing client connections. |
MAXCONNECTIONS |
Sets the maximum number of connections the user can have to the server, one of the following:
For details, see Managing client connections. |
MEMORYCAP |
Sets how much memory can be allocated to user requests, one of the following:
|
PASSWORD EXPIRE |
Forces immediate expiration of the user's password. The user must change the password on the next login. Note
|
PROFILE |
Assigns a profile that controls password requirements for this user, one of the following:
|
RENAME TO |
Assigns the user a new user name. All privileges assigned to the user remain unchanged. Note
|
RESOURCE POOL pool-name [FOR SUBCLUSTER sc-name ] |
Assigns a resource pool to this user. The user must also be granted privileges to this pool, unless privileges to the pool are set to The |
RUNTIMECAP |
Sets how long this user's queries can execute, one of the following:
A query's runtime limit can be set at three levels: the user's runtime limit, the user's resource pool, and the session setting. For more information, see Setting a runtime limit for queries. |
SEARCH_PATH |
Specifies the user's default search path, that tells Vertica which schemas to search for unqualified references to tables and UDFs, one of the following:
For details, see Setting Search Paths. |
SECURITY_ALGORITHM ' algorithm ' |
Sets the user-level security algorithm for hash authentication, where
The user's password expires when you change the |
TEMPSPACECAP |
Sets how much temporary file storage is available for user requests, one of the following:
|
Privileges
Non-superusers can change the following options on their own user accounts:
-
IDENTIFIED BY
-
RESOURCE POOL
-
SEARCH_PATH
-
SECURITY_ALGORITHM
When changing a another user's resource pool to one outside of the PUBLIC schema, the user must have USAGE privileges on the resource pool from at least one of the following:
Setting user-level configuration parameters
SET | CLEAR PARAMETER can specify only user-level configuration parameters, otherwise Vertica returns an error. Only superusers can set and clear user-level parameters, unless they are also supported at the session level.
To get the names of user-level parameters, query system table CONFIGURATION_PARAMETERS. For example:
=> SELECT parameter_name, allowed_levels FROM configuration_parameters
WHERE allowed_levels ilike '%USER%' AND parameter_name ilike '%depot%' ORDER BY parameter_name;
parameter_name | allowed_levels
-----------------------------+-------------------------
BackgroundDepotWarming | SESSION, USER, DATABASE
DepotOperationsForQuery | SESSION, USER, DATABASE
EnableDepotWarmingFromPeers | SESSION, USER, DATABASE
UseDepotForReads | SESSION, USER, DATABASE
UseDepotForWrites | SESSION, USER, DATABASE
(5 rows)
The following example sets the user-level configuration parameter UseDepotForWrites for two users, Yvonne and Ahmed:
=> SHOW USER Yvonne PARAMETER ALL;
user | parameter | setting
--------+-------------------------+---------
Yvonne | DepotOperationsForQuery | Fetches
(1 row)
=> ALTER USER Yvonne SET PARAMETER UseDepotForWrites = 0;
ALTER USER
=> SHOW USER Yvonne PARAMETER ALL;
user | parameter | setting
--------+-------------------------+---------
Yvonne | DepotOperationsForQuery | Fetches
Yvonne | UseDepotForWrites | 0
(2 rows)
=> ALTER USER Ahmed SET PARAMETER DepotOperationsForQuery = 'Fetches';
ALTER USER
=> SHOW USER ALL PARAMETER ALL;
user | parameter | setting
--------+-------------------------+---------
Ahmed | DepotOperationsForQuery | Fetches
Yvonne | DepotOperationsForQuery | Fetches
Yvonne | UseDepotForWrites | 0
(3 rows)
Examples
Set a user's password
=> CREATE USER user1;
=> ALTER USER user1 IDENTIFIED BY 'newpassword';
Set user's security algorithm and password
This example sets a user's security algorithm and password to SHA-512
and newpassword
, respectively. When you execute the ALTER USER
statement, Vertica hashes the password with the SHA-512 algorithm and saves the hash:
=> CREATE USER user1;
=> ALTER USER user1 SECURITY_ALGORITHM 'SHA512' IDENTIFIED BY 'newpassword'
Assign default roles to a user
This example make a user's assigned roles their default roles. Default roles are automatically set (enabled) when a user logs in:
=> CREATE USER user1;
CREATE USER
=> GRANT role1, role2, role3 to user1;
=> ALTER USER user1 DEFAULT ROLE ALL;
You can pair ALL with EXCEPT to exclude certain roles:
=> CREATE USER user2;
CREATE USER
=> GRANT role1, role2, role3 to user2;
=> ALTER USER user2 DEFAULT ROLE ALL EXCEPT role1;
See also
33 - ALTER VIEW
Modifies the metadata of an existing view. The changes are auto-committed.
Syntax
General usage:
ALTER VIEW [[database.]schema.]view {
| OWNER TO owner
| SET SCHEMA schema
| { INCLUDE | EXCLUDE | MATERIALIZE } [ SCHEMA ] PRIVILEGES
}
Rename view:
ALTER VIEW [[database.]schema.]view[,...] RENAME TO new-view-name[,...]
Parameters
[
database
.]
schema
Database and schema. The default schema is
public
. If you specify a database, it must be the current database.view
- The view to alter.
SET SCHEMA
schema
``- Moves the view from one schema to another.
OWNER TO
owner
- Changes the view owner.
Important
The new view owner should also have SELECT privileges on the objects that the view references; otherwise the view is inaccessible to that user. -
{ INCLUDE | EXCLUDE | MATERIALIZE } [SCHEMA] PRIVILEGES
- Specifies default inheritance of schema privileges for this view:
-
EXCLUDE [SCHEMA] PRIVILEGES
(default) disables inheritance of privileges from the schema. -
INCLUDE [SCHEMA] PRIVILEGES
grants the view the same privileges granted to its schema. -
MATERIALIZE
: Copies grants to the view and creates a GRANT object on the view. This disables the inherited privileges flag on the view, so you can:-
Grant more specific privileges at the view level
-
Use schema-level privileges as a template
-
Move the view to a different schema
-
Change schema privileges without affecting the view
Note
If inherited privileges are disabled at the database level, schema privileges can still be materialized. -
-
RENAME TO
- Renames one or more views:
RENAME TO new-view-name[,...]
The following requirements apply:
-
The new view name conforms to conventions described in Identifiers. It must also be unique among all names of sequences, tables, projections, views, and models within the same schema.
-
If you specify multiple views to rename, the source and target lists must have the same number of names.
-
Renaming a view requires
USAGE
andCREATE
privileges on the schema that contains the view.
-
Privileges
Non-superuser: USAGE on the schema and one of the following:
-
View owner
-
ALTER privilege on the view
For certain operations, non-superusers must have the following schema privileges:
Schema privileges required... | For these operations... |
---|---|
CREATE, USAGE | Rename view |
CREATE: destination schema USAGE: current schema |
Move view to another schema |
Examples
Rename view view1
to view2
:
=> CREATE VIEW view1 AS SELECT * FROM t;
CREATE VIEW
=> ALTER VIEW view1 RENAME TO view2;
ALTER VIEW