1 - ALTER ACCESS POLICY

Performs one of the following actions on existing access policies:.

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 the FOR 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 the FOR 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.

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 Managing access policies

See also

CREATE ACCESS POLICY

2 - ALTER AUTHENTICATION

Modifies the settings for a specified authentication method.

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 (HOST) connections.

For authentication methods that use LDAP, specify whether or not LDAP uses Transport Layer Security (TLS).

For remote (HOST) connections, you must specify the IP address of the host from which the user or application is connecting, VARCHAR.

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

Adds and removes certificates from or changes the owner of a certificate authority (CA) bundle.

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.

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:.

Use ALTER DATABASE to perform the following tasks:

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.

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.
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.

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.

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.
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 FUNCTION

7.2 - ALTER ANALYTIC FUNCTION

Alters a user-defined 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.
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)
  • CREATE: destination schema

  • USAGE: current schema

See also

CREATE ANALYTIC FUNCTION

7.3 - ALTER FILTER

Alters a user-defined 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.
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)
  • CREATE: destination schema

  • USAGE: current schema

See also

CREATE FILTER

7.4 - ALTER FUNCTION (scalar)

Alters a user-defined scalar function.

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.
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)
  • CREATE: destination schema

  • USAGE: current 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.

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.

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.
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)
  • CREATE: destination schema

  • USAGE: current schema

See also

CREATE PARSER

7.7 - ALTER SOURCE

Alters a user-defined load source function.

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.
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)
  • CREATE: destination schema

  • USAGE: current schema

See also

CREATE SOURCE

7.8 - ALTER TRANSFORM FUNCTION

Alters a user-defined 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.
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)
  • CREATE: destination schema

  • USAGE: current schema

See also

CREATE TRANSFORM FUNCTION

8 - ALTER HCATALOG SCHEMA

Alters parameter values on a schema that was created with CREATE 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.

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.

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.

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';

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.

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.

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.

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

This statement has been deprecated.

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.

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.

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.

Updates an existing notifier.

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'

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

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 the TLSMODE 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

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.

  1. Create a TLS Configuration with the desired TLS mode, certificate, and CA certificates.

  2. Use CREATE or ALTER to disable the notifier and set the TLS Configuration:

    => ALTER NOTIFIER encrypted_notifier
        DISABLE
        TLS CONFIGURATION kafka_tls_config;
    
  3. 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';
    
  4. 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.

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

See Altering stored procedures.

17 - ALTER PROFILE

Changes a 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

Name Description
name

The name of the profile to create, where *name*conforms to conventions described in Identifiers.

To modify the default profile, set name to default. For example:

ALTER PROFILE DEFAULT LIMIT PASSWORD_MIN_SYMBOLS 1;

PASSWORD_LIFE_TIME

Set to an integer value, one of the following:

  • ≥ 1: The number of days a password remains valid.

  • UNLIMITED: Password remains valid indefinitely.

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:

  • Default: 0

  • ≥ 1: The number of days a password must be set before it can be changed

  • UNLIMITED: Password can be reset at any time.

PASSWORD_GRACE_TIME

Set to an integer value, one of the following:

  • ≥ 1: The number of days a password can be used after it expires.

  • UNLIMITED: No grace period.

FAILED_LOGIN_ATTEMPTS

Set to an integer value, one of the following:

  • ≥ 1: The number of consecutive failed login attempts Vertica allows before locking your account.

  • UNLIMITED: Vertica allows an unlimited number of failed login attempts.

PASSWORD_LOCK_TIME
  • ≥ 1: The number of days (units configurable with PasswordLockTimeUnit) a user's account is locked after FAILED_LOGIN_ATTEMPTS number of login attempts. The account is automatically unlocked when the lock time elapses.

  • UNLIMITED: Account remains indefinitely inaccessible until a superuser manually unlocks it.

PASSWORD_REUSE_MAX

Set to an integer value, one of the following:

  • ≥ 1: The number of times you must change your password before you can reuse an earlier password.

  • UNLIMITED: You can reuse an earlier password without any intervening changes.

PASSWORD_REUSE_TIME

Set to an integer value, one of the following:

  • ≥ 1: The number of days that must pass after a password is set before you can reuse it.

  • UNLIMITED: You can reuse an earlier password immediately.

PASSWORD_MAX_LENGTH

The maximum number of characters allowed in a password, one of the following:

  • Integer between 8 and 512, inclusive
PASSWORD_MIN_LENGTH

The minimum number of characters required in a password, one of the following:

  • 0 to PASSWORD_MAX_LENGTH

  • UNLIMITED: Minimum of PASSWORD_MAX_LENGTH

PASSWORD_MIN_LETTERS

Minimum number of letters (a-z and A-Z) that must be in a password, one of the following:

  • Integer between 0 and PASSWORD_MAX_LENGTH, inclusive

  • UNLIMITED: 0 (no minimum)

PASSWORD_MIN_UPPERCASE_LETTERS

Minimum number of uppercase letters (A-Z) that must be in a password, one of the following:

  • Integer between 0 and PASSWORD_MAX_LENGTH, inclusive

  • UNLIMITED: 0 (no minimum)

PASSWORD_MIN_LOWERCASE_LETTERS

Minimum number of lowercase letters (a-z) that must be in a password, one of the following:

  • Integer between 0 and PASSWORD_MAX_LENGTH, inclusive

  • UNLIMITED: 0 (no minimum)

PASSWORD_MIN_DIGITS

Minimum number of digits (0-9) that must be in a password, one of the following:

  • Integer between 0 and PASSWORD_MAX_LENGTH, inclusive

  • UNLIMITED: 0 (no minimum)

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:

  • Integer between 0 and PASSWORD_MAX_LENGTH, inclusive

  • UNLIMITED: 0 (no minimum)

PASSWORD_MIN_CHAR_CHANGE

Minimum number of characters that must be different from the previous password:

  • Default: 0

  • Integer between 0 and PASSWORD_MAX_LENGTH, inclusive

  • UNLIMITED: 0 (no minimum)

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.

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.

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 TOnew-name
The new projection name.
ON 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:

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.

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.

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.
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, and MEMORYSIZE parameters for built-in pools.

Parameters

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 its CPUAFFINITYSET CPUs with other Vertica resource pools.
  • EXCLUSIVE: Dedicates CPUAFFINITYSET CPUs to this resource pool only, and excludes other Vertica resource pools. If CPUAFFINITYSET 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 if CPUAFFINITYSET 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 indexes i through n 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

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 or 0: 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 memory
  • integer{K|M|G|T}: Amount of memory in kilobytes, megabytes, gigabytes, or terabytes
  • NONE (empty string): Unlimited, resource pool can borrow any amount of available memory from the GENERAL 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 of MAXMEMORYSIZE for this resource pool.
  • integer{K|M|G|T}: Amount of memory in kilobytes, megabytes, gigabytes, or terabytes, up to the value of MAXMEMORYSIZE.
  • NONE (empty string): Unlimited; resource pool can borrow any amount of available memory from the GENERAL pool, within the limits set by MAXMEMORYSIZE.

Default: NONE

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 memory
  • integer{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

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:

    • User-defined resource pool: -100 to 100

    • Built-in resource pools SYSQUERY, RECOVERY, and TM: -110 to 110

  • HOLD: Sets priority to -999. Queries in this resource pool are queued until QUEUETIMEOUT 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.

Renames an existing role.

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.

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.

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:.

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 the new-schema-name. You cannot rename a schema to a different namespace.

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:

  1. Schema ms is owned by user mayday, and contains two tables: ms.t1 owned by mayday, and ms.t2 owned by user joe:

    => \dt
                               List of tables
         Schema     |         Name          | Kind  |  Owner  | Comment
    ----------------+-----------------------+-------+---------+---------
     ms             | t1                    | table | mayday  |
     ms             | t2                    | table | joe     |
    
  2. User mayday transfers ownership of schema ms to user dbadmin, using CASCADE. On return, ALTER SCHEMA reports that it cannot transfer ownership of table ms.t2 and its projections, which are owned by user joe:

    
    => \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     |
    
  3. User dbadmin transfers ownership of schema ms to user pat, again using CASCADE. This time, because dbadmin is a superuser, ALTER SCHEMA transfers ownership of all ms tables to user pat

    => 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 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.
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.

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 SEQUENCE

26 - ALTER SESSION

ALTER SESSION sets and clears session-level configuration parameter values for the current 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.

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

Superuser

Examples

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.

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.

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 }
}

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 default AUTO.

  • 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 use ALL 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.

See Dropping table columns.

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.

See also Setting privilege inheritance on tables and views.

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 no namespace is provided, the namespace is set to default_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.

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 ...

30 - ALTER TLS CONFIGURATION

Alters a specified TLS Configuration object.

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's cn (Common Name) or subjectAltName attribute matches the hostname or IP address of the other host.

    Note that for client certificates, cn is used for the username, so subjectAltName 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 like VERIFY_CA.

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.

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.

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).
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.

User account parameters

Specify one or more user-account parameters and their settings as a comma-delimited list:

account-parameter value[,...]

Parameter Setting
ACCOUNT

Locks or unlocks user access to the database, one of the following:

  • UNLOCK (default)

  • LOCK prevents a new user from logging in. This can be useful when creating an account for a user who does not need immediate access.

DEFAULT ROLE

Specifies what roles are the default roles for this user, set to one of the following:

  • NONE (default): Removes all default roles.

  • role[,...]: Comma-delimited list of roles.

  • ALL: Sets as default all user roles.

  • ALL EXCEPT role[,...]: Comma-delimited list of roles to exclude as default roles.

Default roles are automatically activated when a user logs in. The roles specified by this parameter supersede any roles assigned earlier.

GRACEPERIOD

Specifies how long a user query can block on any session socket, one of the following:

  • NONE (default): Removes any grace period previously set on session queries.

  • 'interval': Specifies as an interval the maximum grace period for current session queries, up to 20 days.

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']
  • new-password: ASCII password that Vertica then hashes for internal storage. An empty string enables this user to access the database with no password.

  • hashed-password: A pre-hashed password and its associated hex string hash-salt. Setting a password this way bypasses all password complexity requirements.

  • REPLACE: Required for non-superusers, who must supply their current password. Non-superusers can only change their own passwords.

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:

  • NONE (default): No limit set for this user. If you omit this parameter, no limit is set for this user.

  • 'interval': An interval value, up to one year.

For details, see Managing client connections.

MAXCONNECTIONS

Sets the maximum number of connections the user can have to the server, one of the following:

  • NONE (default): No limit set. If you omit this parameter, the user can have an unlimited number of connections across the database cluster.

  • integer ON DATABASE: Sets to integer the maximum number of connections across the database cluster.

  • integer ON NODE: Sets to integer the maximum number of connections to each node.

For details, see Managing client connections.

MEMORYCAP

Sets how much memory can be allocated to user requests, one of the following:

  • NONE (default): No limit

  • A string value that specifies the memory limit, one of the following:

    • 'int%' expresses the maximum as a percentage of total memory available to the Resource Manager, where int is an integer value between 0 and 100.For example:

      MEMORYCAP '40%'

    • 'int{K|M|G|T}' expresses memory allocation in kilobytes, megabytes, gigabytes, or terabytes. For example:

      MEMORYCAP '10G'

PASSWORD EXPIRE

Forces immediate expiration of the user's password. The user must change the password on the next login.

PROFILE

Assigns a profile that controls password requirements for this user, one of the following:

  • DEFAULT (default): Assigns the default database profile to this user.

  • profile-name: A profile that is defined by CREATE PROFILE.

RENAME TO

Assigns the user a new user name. All privileges assigned to the user remain unchanged.

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 PUBLIC.

The FOR SUBCLUSTER clause assigns a subcluster-specific resource pool to the user. You can assign only one subcluster-specific resource pool to each user.

RUNTIMECAP

Sets how long this user's queries can execute, one of the following:

  • NONE (default): No limit set for this user. If you omit this parameter, no limit is set for this user.

  • 'interval': An interval value, up to one year.

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:

  • DEFAULT (default): Sets the search path as follows:

    "$user", public, v_catalog, v_monitor, v_internal
    
  • Comma-delimited list of schemas.

For details, see Setting Search Paths.

SECURITY_ALGORITHM 'algorithm'

Sets the user-level security algorithm for hash authentication, where algorithm is one of the following:

  • NONE (default): Uses the system-level parameter, SecurityAlgorithm

  • SHA512

  • MD5

The user's password expires when you change the SECURITY_ALGORITHM value and must be reset.

TEMPSPACECAP

Sets how much temporary file storage is available for user requests, one of the following:

  • NONE (default): No limit

  • String value that specifies the storage limit, one of the following:

    • int% expresses the maximum as a percentage of total temporary storage available to the Resource Manager, where int is an integer value between 0 and 100. For example:

      TEMPSPACECAP '40%'

    • int{K|M|G|T} expresses storage allocation in kilobytes, megabytes, gigabytes, or terabytes. For example:

      TEMPSPACECAP '10G'

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.

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.
{ 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

See also Setting privilege inheritance on tables and views.

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 and CREATE 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