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 you can use to recreate a physical schema design on another cluster.

If you omit all arguments, this function exports to standard output all objects to which you have access.

The SQL script:

• Only includes objects to which the user has access.

• Orders CREATE statements based on 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 a KSAFE clause, if any, otherwise with an 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

Where to send output. To write the script to standard output, use an empty string (''). A superusers can specify a file path. 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

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

Export all design elements in order of their dependencies:

=> SELECT EXPORT_CATALOG(
     '/home/dbadmin/xtest/sql_cat_design.sql',
     'DESIGN' );
           EXPORT_CATALOG
-------------------------------------
 Catalog data exported successfully
(1 row)

Export only tables and their dependencies:

=> SELECT EXPORT_CATALOG (
     '/home/dbadmin/xtest/sql_cat_tables.sql',
     'TABLES');
           EXPORT_CATALOG
-------------------------------------
 Catalog data exported successfully
(1 row)

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.

If you omit all arguments, this function exports to standard output all objects to which you have access.

The SQL script:

• Only includes objects to which the user has access.

• Orders CREATE statements based on 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 a KSAFE clause, if any, otherwise with an 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']] )

Arguments

destination

Where to send output. To write the script to standard output, use an empty string (''). A superusers can specify a file path. 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

The objects to export, a comma-delimited list, or an empty string to export all objects to which the user has access. If you specify a schema, the function exports all accessible objects in that schema.

For stored procedures with the same name but different formal parameters, you can export all implementations by exporting the parent schema, or specify the types or types and names of the formal parameters to identify the implementation to export.

mark-ksafe

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

Privileges

None

Examples

The following query exports all accessible objects to a named file:

=> SELECT EXPORT_OBJECTS(
     '/home/dbadmin/xtest/sql_objects_all.sql',
     '',
     'true');
           EXPORT_OBJECTS
-------------------------------------
 Catalog data exported successfully
(1 row)

To export a particular implementation of a stored procedure, specify either the types or both the names and types of the procedure's formal parameters. The following example specifies the types:

=> SELECT EXPORT_OBJECTS('','raiseXY(int, int)');
    EXPORT_OBJECTS
----------------------

CREATE PROCEDURE public.raiseXY(x int, y int)
LANGUAGE 'PL/vSQL'
SECURITY INVOKER
AS '
BEGIN
RAISE NOTICE ''x = %'', x;
RAISE NOTICE ''y = %'', y;
-- some processing statements
END
';

SELECT MARK_DESIGN_KSAFE(0);

(1 row)

To export all implementations of an overloaded stored procedure, export its parent schema:

=> SELECT EXPORT_OBJECTS('','public');
    EXPORT_OBJECTS
----------------------

...

CREATE PROCEDURE public.raiseXY(x int, y varchar)
LANGUAGE 'PL/vSQL'
SECURITY INVOKER
AS '
BEGIN
RAISE NOTICE ''x = %'', x;
RAISE NOTICE ''y = %'', y;
-- some processing statements
END
';

CREATE PROCEDURE public.raiseXY(x int, y int)
LANGUAGE 'PL/vSQL'
SECURITY INVOKER
AS '
BEGIN
RAISE NOTICE ''x = %'', x;
RAISE NOTICE ''y = %'', y;
-- some processing statements
END
';

SELECT MARK_DESIGN_KSAFE(0);

(1 row)

See also

• EXPORT_CATALOG
• EXPORT_TABLES
• Exporting objects

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)