Catalog functions

• 1: DROP_LICENSE
• 2: DUMP_CATALOG
• 3: EXPORT_CATALOG
• 4: EXPORT_OBJECTS
• 5: EXPORT_TABLES
• 6: INSTALL_LICENSE
• 7: MARK_DESIGN_KSAFE
• 8: RELOAD_ADMINTOOLS_CONF

1 - DROP_LICENSE

Drops a license key from the global catalog. Dropping expired keys is optional. Vertica automatically ignores expired license keys if a valid, alternative license key is installed.

This is a meta-function. You must call meta-functions in a top-level SELECT statement.

Behavior type

Syntax

DROP_LICENSE( 'license-name' )

Parameters

license-name

The name of the license to drop. Use the name (or long license key) in the NAME column of system table LICENSES.

Privileges

Superuser

Examples

=> SELECT DROP_LICENSE('9b2d81e2-aab1-4cfb-bc07-fa9a696e8f5e');

See also

2 - DUMP_CATALOG

Returns an internal representation of the Vertica catalog. This function is used for diagnostic purposes.

DUMP_CATALOG returns only the objects that are visible to the user.

This is a meta-function. You must call meta-functions in a top-level SELECT statement.

Behavior type

Syntax

DUMP_CATALOG()

Privileges

None

Examples

The following query obtains an internal representation of the Vertica catalog:

=> SELECT DUMP_CATALOG();

The output is written to the specified file:

\o /tmp/catalog.txt
SELECT DUMP_CATALOG();
\o

3 - EXPORT_CATALOG

Generates a SQL script for recreating a physical schema design on another cluster.

The SQL script conforms to the following requirements:

• Only includes objects to which the user has access.

• Orders CREATE statements according to object dependencies so they can be recreated in the correct sequence. For example, if a table is in a non-PUBLIC schema, the required CREATE SCHEMA statement precedes the CREATE TABLE statement. Similarly, a table's CREATE ACCESS POLICY statement follows the table's CREATE TABLE statement.

• If possible, creates projections with their KSAFE clause, if any, otherwise with their OFFSET clause.

This is a meta-function. You must call meta-functions in a top-level SELECT statement.

Behavior type

Syntax

EXPORT_CATALOG ( [ '[destination]' [, 'scope'] ] )

Arguments

destination

Specifies where to send output, one of the following:

• Empty string, writes the script to standard output.

• Path and name of an SQL output file, valid only for superusers. If you specify a file that does not exist, the function creates one. If you specify only a file name, Vertica creates it in the catalog directory. If the file already exists, the function silently overwrites its contents.

scope

Determines what to export. Within the specified scope, EXPORT_CATALOG exports all the objects to which you have access:

• DESIGN: Exports all catalog objects, including schemas, tables, constraints, views, access policies, projections, SQL macros, and stored procedures.

• DESIGN_ALL: Deprecated.

• TABLES: Exports all tables and their access policies. See also EXPORT_TABLES.

• DIRECTED_QUERIES: Exports all directed queries that are stored in the database. For details, see Managing directed queries.

Default: DESIGN

Privileges

None

Examples

See Exporting the catalog.

See also

• EXPORT_OBJECTS
• EXPORT_TABLES
• Exporting the catalog

4 - EXPORT_OBJECTS

Generates a SQL script you can use to recreate non-virtual catalog objects on another cluster.

The SQL script conforms to the following requirements:

• Only includes objects to which the user has access.

• Orders CREATE statements according to object dependencies so they can be recreated in the correct sequence. For example, if a table is in a non-PUBLIC schema, the required CREATE SCHEMA statement precedes the CREATE TABLE statement. Similarly, a table's CREATE ACCESS POLICY statement follows the table's CREATE TABLE statement.

• If possible, creates projections with their KSAFE clause, if any, otherwise with their OFFSET clause.

This is a meta-function. You must call meta-functions in a top-level SELECT statement.

Behavior type

Syntax

EXPORT_OBJECTS( ['[destination]' [, '[scope]'] [, 'mark-ksafe']] )

Parameters

destination

Specifies where to send output, one of the following:

• An empty string ('') writes the script to standard output.

• The path and name of a SQL output file. This option is valid only for superusers. If you specify a file that does not exist, the function creates one. If you specify only a file name, Vertica creates it in the catalog directory. If the file already exists, the function silently overwrites its contents.

scope

Specifies one or more objects to export as a comma-delimited list:

{ [database.]schema[.object] | [[database.]schema.]object }[,...]

• If set to an empty string, Vertica exports all objects to which the user has access.

• If you specify a schema only, Vertica exports all objects in that schema.

• If you specify a database, it must be the current database.

For stored procedures with the same name but different formal parameters, you can export all implementations by exporting its parent schema:

mydb.myschema

Specifying the types or both the names and types of a particular implementation's formal parameters exports that implementation:

mydb.myschema.my_procedure()  -- no formal parameters
mydb.myschema.my_procedure(int, int) -- formal parameter types (parameter names are optional)

mark-ksafe

Boolean argument, specifies whether the generated script calls the Vertica function MARK_DESIGN_KSAFE . If set to true (default), MARK_DESIGN_KSAFE uses the correct K-safe argument for the current database.

Privileges

None

Examples

See Exporting objects.

See also

• EXPORT_CATALOG
• EXPORT_TABLES

5 - EXPORT_TABLES

Generates a SQL script that can be used to recreate a logical schema—schemas, tables, constraints, and views—on another cluster. EXPORT_TABLES only exports objects to which the user has access.

The SQL script conforms to the following requirements:

• Only includes objects to which the user has access.

• Orders CREATE statements according to object dependencies so they can be recreated in the correct sequence. For example, if a table references a named sequence, a CREATE SEQUENCE statement precedes the CREATE TABLE statement. Similarly, a table's CREATE ACCESS POLICY statement follows the table's CREATE TABLE statement.

This is a meta-function. You must call meta-functions in a top-level SELECT statement.

Behavior type

Syntax

EXPORT_TABLES( ['[destination]' [, '[scope]']] )

Parameters

destination

Specifies where to send output, one of the following:

• An empty string ('') writes the script to standard output.

• The path and name of a SQL output file. This option is valid only for superusers. If you specify a file that does not exist, the function creates one. If you specify only a file name, Vertica creates it in the catalog directory. If the file already exists, the function silently overwrites its contents.

scope

Specifies one or more tables to export, as follows:

[database.]schema[.table][,...]

• If set to an empty string, Vertica exports all non-virtual table objects to which you have access, including table schemas, sequences, and constraints.
• If you specify a schema, Vertica exports all non-virtual table objects in that schema.
• If you specify a database, it must be the current database.

Privileges

None

Examples

See Exporting tables.

See also

• Exporting tables
• EXPORT_CATALOG
• EXPORT_OBJECTS

6 - INSTALL_LICENSE

Installs the license key in the global catalog.

This is a meta-function. You must call meta-functions in a top-level SELECT statement.

Behavior type

Syntax

INSTALL_LICENSE( 'filename' )

Parameters

filename

The absolute path name of a valid license file.

Privileges

Superuser

Examples

=> SELECT INSTALL_LICENSE('/tmp/vlicense.dat');

See also

7 - MARK_DESIGN_KSAFE

Enables or disables high availability in your environment, in case of a failure. Before enabling recovery, MARK_DESIGN_KSAFE queries the catalog to determine whether a cluster's physical schema design meets the following requirements:

• Small, unsegmented tables are replicated on all nodes.

• Large table superprojections are segmented with each segment on a different node.

• Each large table projection has at least one buddy projection for K-safety=1 (or two buddy projections for K-safety=2).

  Buddy projections are also segmented across database nodes, but the distribution is modified so segments that contain the same data are distributed to different nodes. See High availability with projections.

MARK_DESIGN_KSAFE does not change the physical schema.

This is a meta-function. You must call meta-functions in a top-level SELECT statement.

Behavior type

Syntax

MARK_DESIGN_KSAFE ( k )

Parameters

k

Specifies the level of K-safety, one of the following:

• 2: Enables high availability if the schema design meets requirements for K-safety=2

• 1: Enables high availability if the schema design meets requirements for K-safety=1

• 0: Disables high availability

Privileges

Superuser

Return messages

If you specify a k value of 1 or 2, Vertica returns one of the following messages.

Success:

 Marked design n-safe

Failure:

 The schema does not meet requirements for K=n.
 Fact table projection projection-name
 has insufficient "buddy" projections.

where n is a K-safety setting.

Notes

• The database's internal recovery state persists across database restarts but it is not checked at startup time.

• When one node fails on a system marked K-safe=1, the remaining nodes are available for DML operations.

Examples

=> SELECT MARK_DESIGN_KSAFE(1);
  mark_design_ksafe
----------------------
 Marked design 1-safe
(1 row)

If the physical schema design is not K-safe, messages indicate which projections do not have a buddy:

=> SELECT MARK_DESIGN_KSAFE(1);
The given K value is not correct;
the schema is 0-safe
Projection pp1 has 0 buddies,
which is smaller that the given K of 1
Projection pp2 has 0 buddies,
which is smaller that the given K of 1
.
.
.
(1 row)

See also

• Identical segmentation
• Failure recovery

8 - RELOAD_ADMINTOOLS_CONF

Updates the admintools.conf on each UP node in the cluster. Updates include:

• IP addresses and catalog paths

• Node names for all nodes in the current database

This function provides a manual method to instruct the server to update admintools.conf on all UP nodes. For example, if you restart a node, call this function to confirm its admintools.conf file is accurate.

This is a meta-function. You must call meta-functions in a top-level SELECT statement.

Behavior type

Syntax

RELOAD_ADMINTOOLS_CONF()

Privileges

Examples

Update admintools.conf on each UP node in the cluster:

=> SELECT RELOAD_ADMINTOOLS_CONF();
  RELOAD_ADMINTOOLS_CONF
--------------------------
 admintools.conf reloaded
(1 row)