Vertica system tables

• 1: V_CATALOG schema
• 1.1: ACCESS_POLICY
• 1.2: ALL_TABLES
• 1.3: AUDIT_MANAGING_USERS_PRIVILEGES
• 1.4: CA_BUNDLES
• 1.5: CATALOG_SUBSCRIPTION_CHANGES
• 1.6: CATALOG_SYNC_STATE
• 1.7: CATALOG_TRUNCATION_STATUS
• 1.8: CERTIFICATES
• 1.9: CLIENT_AUTH
• 1.10: CLIENT_AUTH_PARAMS
• 1.11: CLUSTER_LAYOUT
• 1.12: COLUMNS
• 1.13: COMMENTS
• 1.14: COMPLEX_TYPES
• 1.15: CONSTRAINT_COLUMNS
• 1.16: CRYPTOGRAPHIC_KEYS
• 1.17: DATABASES
• 1.18: DIRECTED_QUERIES
• 1.19: DUAL
• 1.20: ELASTIC_CLUSTER
• 1.21: EPOCHS
• 1.22: FAULT_GROUPS
• 1.23: FOREIGN_KEYS
• 1.24: GRANTS
• 1.25: HCATALOG_COLUMNS
• 1.26: HCATALOG_SCHEMATA
• 1.27: HCATALOG_TABLE_LIST
• 1.28: HCATALOG_TABLES
• 1.29: INHERITED_PRIVILEGES
• 1.30: INHERITING_OBJECTS
• 1.31: KEYWORDS
• 1.32: LARGE_CLUSTER_CONFIGURATION_STATUS
• 1.33: LICENSE_AUDITS
• 1.34: LICENSES
• 1.35: LOAD_BALANCE_GROUPS
• 1.36: LOG_PARAMS
• 1.37: LOG_QUERIES
• 1.38: LOG_TABLES
• 1.39: MATERIALIZE_FLEXTABLE_COLUMNS_RESULTS
• 1.40: MODELS
• 1.41: NETWORK_ADDRESSES
• 1.42: NODE_SUBSCRIPTION_CHANGE_PHASES
• 1.43: NODE_SUBSCRIPTIONS
• 1.44: NODES
• 1.45: ODBC_COLUMNS
• 1.46: PASSWORD_AUDITOR
• 1.47: PASSWORDS
• 1.48: PRIMARY_KEYS
• 1.49: PROFILE_PARAMETERS
• 1.50: PROFILES
• 1.51: PROJECTION_CHECKPOINT_EPOCHS
• 1.52: PROJECTION_COLUMNS
• 1.53: PROJECTION_DELETE_CONCERNS
• 1.54: PROJECTIONS
• 1.55: RESOURCE_POOL_DEFAULTS
• 1.56: RESOURCE_POOLS
• 1.57: ROLES
• 1.58: ROUTING_RULES
• 1.59: SCHEMATA
• 1.60: SEQUENCES
• 1.61: SESSION_SUBSCRIPTIONS
• 1.62: SHARDS
• 1.63: STORAGE_LOCATIONS
• 1.64: SUBCLUSTER_RESOURCE_POOL_OVERRIDES
• 1.65: SUBCLUSTERS
• 1.66: SYSTEM_COLUMNS
• 1.67: SYSTEM_TABLES
• 1.68: TABLE_CONSTRAINTS
• 1.69: TABLES
• 1.70: TEXT_INDICES
• 1.71: TYPES
• 1.72: USER_AUDITS
• 1.73: USER_CLIENT_AUTH
• 1.74: USER_CONFIGURATION_PARAMETERS
• 1.75: USER_FUNCTION_PARAMETERS
• 1.76: USER_FUNCTIONS
• 1.77: USER_PROCEDURES
• 1.78: USER_TRANSFORMS
• 1.79: USERS
• 1.80: VIEW_COLUMNS
• 1.81: VIEW_TABLES
• 1.82: VIEWS
• 2: V_MONITOR schema
• 2.1: ACTIVE_EVENTS
• 2.2: ALLOCATOR_USAGE
• 2.3: COLUMN_STORAGE
• 2.4: COMMUNAL_CLEANUP_RECORDS
• 2.5: COMMUNAL_TRUNCATION_STATUS
• 2.6: CONFIGURATION_CHANGES
• 2.7: CONFIGURATION_PARAMETERS
• 2.8: CPU_USAGE
• 2.9: CRITICAL_HOSTS
• 2.10: CRITICAL_NODES
• 2.11: CRITICAL_SUBCLUSTERS
• 2.12: CURRENT_SESSION
• 2.13: DATA_COLLECTOR
• 2.14: DATA_READS
• 2.15: DATABASE_BACKUPS
• 2.16: DATABASE_CONNECTIONS
• 2.17: DATABASE_MIGRATION_STATUS
• 2.18: DELETE_VECTORS
• 2.19: DEPLOY_STATUS
• 2.20: DEPLOYMENT_PROJECTION_STATEMENTS
• 2.21: DEPLOYMENT_PROJECTIONS
• 2.22: DEPOT_EVICTIONS
• 2.23: DEPOT_FETCH_QUEUE
• 2.24: DEPOT_FETCHES
• 2.25: DEPOT_FILES
• 2.26: DEPOT_PIN_POLICIES
• 2.27: DEPOT_SIZES
• 2.28: DEPOT_UPLOADS
• 2.29: DESIGN_QUERIES
• 2.30: DESIGN_STATUS
• 2.31: DESIGN_TABLES
• 2.32: DESIGNS
• 2.33: DISK_RESOURCE_REJECTIONS
• 2.34: DISK_STORAGE
• 2.35: ERROR_MESSAGES
• 2.36: EVENT_CONFIGURATIONS
• 2.37: EXECUTION_ENGINE_PROFILES
• 2.38: EXTERNAL_TABLE_DETAILS
• 2.39: HIVE_CUSTOM_PARTITIONS_ACCESSED
• 2.40: HOST_RESOURCES
• 2.41: IO_USAGE
• 2.42: LDAP_LINK_DRYRUN_EVENTS
• 2.43: LDAP_LINK_EVENTS
• 2.44: LOAD_SOURCES
• 2.45: LOAD_STREAMS
• 2.46: LOCK_USAGE
• 2.47: LOCKS
• 2.48: LOGIN_FAILURES
• 2.49: MEMORY_EVENTS
• 2.50: MEMORY_USAGE
• 2.51: MERGEOUT_PROFILES
• 2.52: MONITORING_EVENTS
• 2.53: NETWORK_INTERFACES
• 2.54: NETWORK_USAGE
• 2.55: NODE_EVICTIONS
• 2.56: NODE_RESOURCES
• 2.57: NODE_STATES
• 2.58: NOTIFIER_ERRORS
• 2.59: OUTPUT_DEPLOYMENT_STATUS
• 2.60: OUTPUT_EVENT_HISTORY
• 2.61: PARTITION_COLUMNS
• 2.62: PARTITION_REORGANIZE_ERRORS
• 2.63: PARTITION_STATUS
• 2.64: PARTITIONS
• 2.65: PROCESS_SIGNALS
• 2.66: PROJECTION_RECOVERIES
• 2.67: PROJECTION_REFRESHES
• 2.68: PROJECTION_STORAGE
• 2.69: PROJECTION_USAGE
• 2.70: QUERY_CONSUMPTION
• 2.71: QUERY_EVENTS
• 2.72: QUERY_METRICS
• 2.73: QUERY_PLAN_PROFILES
• 2.74: QUERY_PROFILES
• 2.75: QUERY_REQUESTS
• 2.76: REBALANCE_OPERATIONS
• 2.77: REBALANCE_PROJECTION_STATUS
• 2.78: REBALANCE_TABLE_STATUS
• 2.79: RECOVERY_STATUS
• 2.80: REMOTE_REPLICATION_STATUS
• 2.81: REPARENTED_ON_DROP
• 2.82: RESOURCE_ACQUISITIONS
• 2.83: RESOURCE_POOL_MOVE
• 2.84: RESOURCE_POOL_STATUS
• 2.85: RESOURCE_QUEUES
• 2.86: RESOURCE_REJECTION_DETAILS
• 2.87: RESOURCE_REJECTIONS
• 2.88: RESOURCE_USAGE
• 2.89: SESSION_MARS_STORE
• 2.90: SESSION_PARAMETERS
• 2.91: SESSION_PROFILES
• 2.92: SESSIONS
• 2.93: SPREAD_STATE
• 2.94: STORAGE_BUNDLE_INFO_STATISTICS
• 2.95: STORAGE_CONTAINERS
• 2.96: STORAGE_POLICIES
• 2.97: STORAGE_TIERS
• 2.98: STORAGE_USAGE
• 2.99: STRATA
• 2.100: STRATA_STRUCTURES
• 2.101: SYSTEM
• 2.102: SYSTEM_RESOURCE_USAGE
• 2.103: SYSTEM_SERVICES
• 2.104: SYSTEM_SESSIONS
• 2.105: TABLE_RECOVERIES
• 2.106: TABLE_RECOVERY_STATUS
• 2.107: TABLE_STATISTICS
• 2.108: TLS_CONFIGURATIONS
• 2.109: TRANSACTIONS
• 2.110: TRUNCATED_SCHEMATA
• 2.111: TUNING_RECOMMENDATIONS
• 2.112: TUPLE_MOVER_OPERATIONS
• 2.113: UDFS_EVENTS
• 2.114: UDFS_OPS_PER_HOUR
• 2.115: UDFS_OPS_PER_MINUTE
• 2.116: UDFS_STATISTICS
• 2.117: UDX_EVENTS
• 2.118: UDX_FENCED_PROCESSES
• 2.119: USER_LIBRARIES
• 2.120: USER_LIBRARY_MANIFEST
• 2.121: USER_SESSIONS

1 - V_CATALOG schema

The system tables in this section reside in the v_catalog schema. These tables provide information (metadata) about the objects in a database; for example, tables, constraints, users, projections, and so on.

1.1 - ACCESS_POLICY

Provides information about existing access policies.

Privileges

By default, only the superuser can view this table. Superusers can grant non-superusers access to this table with the following statement. Non-superusers can only see rows for tables that they own:

=> GRANT SELECT ON access_policy TO PUBLIC

Examples

The following query returns all access policies on table public.customer_dimension:

=> \x
=> SELECT policy_type, is_policy_enabled, table_name, column_name, expression FROM access_policy WHERE table_name = 'public.customer_dimension';
-[ RECORD 1 ]-----+----------------------------------------------------------------------------------------
policy_type       | Column Policy
is_policy_enabled | Enabled
table_name        | public.customer_dimension
column_name       | customer_address
expression        | CASE WHEN enabled_role('administrator') THEN customer_address ELSE '**************' END

1.2 - ALL_TABLES

Provides summary information about tables in a Vertica database.

Examples

onenode=> SELECT DISTINCT table_name, table_type FROM all_tables
          WHERE table_name ILIKE 't%';
       table_name       |  table_type
------------------------+--------------
 types                  | SYSTEM TABLE
 trades                 | TABLE
 tuple_mover_operations | SYSTEM TABLE
 tables                 | SYSTEM TABLE
 tuning_recommendations | SYSTEM TABLE
 testid                 | TABLE
 table_constraints      | SYSTEM TABLE
 transactions           | SYSTEM TABLE
(8 rows)
onenode=> SELECT table_name, table_type FROM all_tables
          WHERE table_name ILIKE 'my%';
 table_name | table_type
------------+------------
 mystocks   | VIEW
(1 row)
=> SELECT * FROM all_tables LIMIT 4;
-[ RECORD 1 ]-------------------------------------------
schema_name | v_catalog
table_id    | 10206
table_name  | all_tables
table_type  | SYSTEM TABLE
remarks     | A complete listing of all tables and views
-[ RECORD 2 ]-------------------------------------------
schema_name | v_catalog
table_id    | 10000
table_name  | columns
table_type  | SYSTEM TABLE
remarks     | Table column information
-[ RECORD 3 ]-------------------------------------------
schema_name | v_catalog
table_id    | 10054
table_name  | comments
table_type  | SYSTEM TABLE
remarks     | User comments on catalog objects
-[ RECORD 4 ]-------------------------------------------
schema_name | v_catalog
table_id    | 10134
table_name  | constraint_columns
table_type  | SYSTEM TABLE
remarks     | Table column constraint information

1.3 - AUDIT_MANAGING_USERS_PRIVILEGES

Provides summary information about privileges, creating, modifying, and deleting users, and authentication changes. This table is a join of LOG_PARAMS, LOG_QUERIES, and LOG_TABLES filtered on the Managing_Users_Privileges category.

1.4 - CA_BUNDLES

Stores certificate authority (CA) bundles created by CREATE CA BUNDLE.

Privileges

• See CA bundle OID, name, and owner: Superuser or owner of the CA bundle.

• See CA bundle contents: Owner of the bundle

Joining with CERTIFICATES

CA_BUNDLES only stores OIDs. Since operations on CA bundles require certificate and owner names, you can use the following query to map bundles to certificate and owner names:

=> SELECT user_name AS owner_name,
       owner     AS owner_oid,
       b.name    AS bundle_name,
       c.name    AS cert_name
FROM   (SELECT name,
               STRING_TO_ARRAY(certificates) :: array[INT] AS certs
        FROM   ca_bundles) b
       LEFT JOIN certificates c
              ON CONTAINS(b.certs, c.oid)
       LEFT JOIN users
              ON user_id = owner
ORDER  BY 1;

 owner_name |     owner_oid     | bundle_name  | cert_name
------------+-------------------+--------------+-----------
 dbadmin    | 45035996273704962 | ca_bundle    | root_ca
 dbadmin    | 45035996273704962 | ca_bundle    | ca_cert
(2 rows)

See also

• Managing CA bundles
• ALTER CA BUNDLE
• DROP CA BUNDLE

1.5 - CATALOG_SUBSCRIPTION_CHANGES

Lists the changes made to catalog subscriptions.

1.6 - CATALOG_SYNC_STATE

Shows when an Eon Mode database node synchronized its catalog to communal storage.

1.7 - CATALOG_TRUNCATION_STATUS

Indicates how up to date the catalog is on communal storage. It is completely up to date when the current catalog version is the same as the catalog truncation version.

The catalog truncation version (CTV) is the version that Vertica cluster uses when it revives after a crash, shutdown, or hibernation. A cluster has only one CTV for all nodes in a cluster.

1.8 - CERTIFICATES

Stores certificates created by CREATE CERTIFICATE.

Examples

See Generating TLS certificates and keys.

1.9 - CLIENT_AUTH

Provides information about client authentication methods.

Higher values indicate higher priorities. Vertica tries to authenticate a user with an authentication method in order of priority from highest to lowest. For example:

• A priority of 10 is higher than a priority of 5.

• A priority 0 is the lowest possible value.

Examples

This example shows how to get information about each client authentication method that you created:

=> SELECT * FROM client_auth;
    auth_oid   |  auth_name  | is_auth_enabled | auth_host_type | auth_host_address | auth_method | auth_parameters | auth_priority | method_priority | address_priority
-------------------+-------------+-----------------+----------------+-------------------+-------------+-----------------+---------------+-----------------+------------------
 45035996274059694 | v_gss       | True            | HOST           | 0.0.0.0/0         | GSS         |                 |             0 |               5 |               96
 45035996274059696 | v_trust     | True            | LOCAL          |                   | TRUST       |                 |             0 |               0 |                0
 45035996274059698 | v_ldap      | True            | HOST           | 10.19.133.123/    | LDAP        |                 |             0 |               5 |              128
 45035996274059700 | RejectNoSSL | True            | HOSTNOSSL      | 0.0.0.0/0         | REJECT      |                 |             0 |              10 |               96
 45035996274059702 | v_hash      | True            | LOCAL          |                   | HASH        |                 |             0 |               2 |                0
 45035996274059704 | v_tls       | True            | HOSTSSL        | 1.1.1.1/0         | TLS         |                 |             0 |               5 |               96
(6 rows)

See also

• Client authentication
• Authentication record priority

1.10 - CLIENT_AUTH_PARAMS

Provides information about client authentication methods that have parameter values assigned.

Examples

This example shows how to retrieve parameter names and values for all authentication methods that you created. The authentication methods that have parameters are:

• v_ident

• v_ldap

• v_ldap1

=> SELECT * FROM CLIENT_AUTH_PARAMS;
     auth_oid      |   auth_name   | auth_parameter_name |    auth_parameter_value
-------------------+---------------+---------------------+------------------------------
 45035996273741304 | v_ident       | system_users        | root
 45035996273741332 | v_gss         |                     |
 45035996273741350 | v_password    |                     |
 45035996273741368 | v_trust       |                     |
 45035996273741388 | v_ldap        | host                | ldap://172.16.65.177
 45035996273741388 | v_ldap        | binddn_prefix       | cn=
 45035996273741388 | v_ldap        | binddn_suffix       | ,dc=qa_domain,dc=com
 45035996273741406 | RejectNoSSL   |                     |
 45035996273741424 | RejectWithSSL |                     |
 45035996273741450 | v_md5         |                     |
 45035996273904044 | l_tls         |                     |
 45035996273906566 | v_hash        |                     |
 45035996273910432 | v_ldap1       | host                | ldap://172.16.65.177
 45035996273910432 | v_ldap1       | basedn              | dc=qa_domain,dc=com
 45035996273910432 | v_ldap1       | binddn              | cn=Manager,dc=qa_domain,dc=com
 45035996273910432 | v_ldap1       | bind_password       | secret
 45035996273910432 | v_ldap1       | search_attribute    | cn
(17 rows)

1.11 - CLUSTER_LAYOUT

Shows the relative position of the actual arrangement of the nodes participating in the cluster and the fault groups (in an Enterprise Mode database) or subclusters (in an Eon Mode database) that affect them. Ephemeral nodes are not shown in the cluster layout ring because they hold no resident data.

See also

1.12 - COLUMNS

Provides table column information.

Examples

Retrieve table and column information from the COLUMNS table:

=> SELECT table_schema, table_name, column_name, data_type, is_nullable
   FROM columns WHERE table_schema = 'store'
   AND data_type = 'Date';
 table_schema |    table_name     |      column_name       | data_type | is_nullable
--------------+-------------------+------------------------+-----------+-------------
 store        | store_dimension   | first_open_date        | Date      | f
 store        | store_dimension   | last_remodel_date      | Date      | f
 store        | store_orders_fact | date_ordered           | Date      | f
 store        | store_orders_fact | date_shipped           | Date      | f
 store        | store_orders_fact | expected_delivery_date | Date      | f
 store        | store_orders_fact | date_delivered         | Date      | f
6 rows)

DATETIME_PRECISION is NULL because the table definition declares no precision:

=> CREATE TABLE c (c TIMESTAMP);
CREATE TABLE
=> SELECT table_name, column_name, datetime_precision FROM columns
   WHERE table_name = 'c';
 table_name | column_name | datetime_precision
------------+-------------+--------------------
 c          | c           |
(1 row)

DATETIME_PRECISION is 4 because the table definition declares precision as 4:

=> DROP TABLE c;
=> CREATE TABLE c (c TIMESTAMP(4));
CREATE TABLE
=> SELECT table_name, column_name, datetime_precision FROM columns
   WHERE table_name = 'c';
 table_name | column_name | datetime_precision
------------+-------------+--------------------
 c          | c           |                  4

An identity column is a sequence available only for numeric column types. To identify what column in a table, if any, is an identity column, search the COLUMNS table to find the identity column in a table testid:

=> CREATE TABLE testid (c1 IDENTITY(1, 1, 1000), c2 INT);
=> \x
Expanded display is on.
=> SELECT * FROM COLUMNS WHERE is_identity='t' AND table_name='testid';
-[ RECORD 1 ]------------+--------------------
table_id                 | 45035996273719486
table_schema             | public
table_name               | testid
is_system_table          | f
column_id                | 45035996273719486-1
column_name              | c1
data_type                | int
data_type_id             | 6
data_type_length         | 8
character_maximum_length |
numeric_precision        |
numeric_scale            |
datetime_precision       |
interval_precision       |
ordinal_position         | 1
is_nullable              | f
column_default           |
is_identity              | t

Use the SEQUENCES table to get detailed information about the sequence in testid:

=> SELECT * FROM sequences WHERE identity_table_name='testid';
-[ RECORD 1 ]-------+--------------------
sequence_schema     | public
sequence_name       | testid_c1_seq
owner_name          | dbadmin
identity_table_name | testid
session_cache_count | 1000
allow_cycle         | f
output_ordered      | f
increment_by        | 1
minimum             | 1
maximum             | 9223372036854775807
current_value       | 0
sequence_schema_id  | 45035996273704976
sequence_id         | 45035996273719488
owner_id            | 45035996273704962
identity_table_id   | 45035996273719486

For more information about sequences and identity columns, see Sequences.

1.13 - COMMENTS

Returns information about comments associated with objects in the database.

1.14 - COMPLEX_TYPES

Contains information about inlined complex types (Complex types).

Each complex type in each external table has a unique type internally, even if the types are structurally the same (like two different ROW(int,int) cases). This inlined type is created when the table using it is created and is automatically dropped when the table is dropped. Inlined complex types cannot be shared or reused in other tables.

Each row in the COMPLEX_TYPES table represents one component (field) in one complex type. A ROW produces one row per field, an ARRAY produces one, and a MAP produces two.

Arrays of primitive types used in native (ROS) tables are not included in the COMPLEX_TYPES table. They are included instead in the TYPES table.

Examples

The following example shows the type and field values after defining a single external table.

=> CREATE EXTERNAL TABLE warehouse(
    name VARCHAR, id_map MAP<INT,VARCHAR>,
    data row(record INT, total FLOAT, description VARCHAR(100)),
    prices ARRAY[INT], comment VARCHAR(200), sales_total FLOAT, storeID INT)
  AS COPY FROM ... PARQUET;

=> SELECT type_id,type_kind,type_name,field_id,field_name,field_type_name,field_position
    FROM COMPLEX_TYPES ORDER BY type_id,field_name;

      type_id      | type_kind |       type_name       | field_id | field_name  | field_type_name | field_position
-------------------+-----------+-----------------------+----------+-------------+-----------------+----------------
 45035996274278280 | Map       | _ct_45035996274278280 |        6 | key         | int             |              0
 45035996274278280 | Map       | _ct_45035996274278280 |        9 | value       | varchar(80)     |              1
 45035996274278282 | Row       | _ct_45035996274278282 |        9 | description | varchar(80)     |              2
 45035996274278282 | Row       | _ct_45035996274278282 |        6 | record      | int             |              0
 45035996274278282 | Row       | _ct_45035996274278282 |        7 | total       | float           |              1
 45035996274278284 | Array     | _ct_45035996274278284 |        6 |             | int             |              0
(6 rows)

1.15 - CONSTRAINT_COLUMNS

Records information about table column constraints.

Privileges

Non-superuser: No explicit privileges required. You only see records for tables that you have privileges to view.

1.16 - CRYPTOGRAPHIC_KEYS

Stores private keys created by CREATE KEY.

Examples

See Generating TLS certificates and keys.

1.17 - DATABASES

Provides information about the databases in this Vertica installation.

Examples

This example queries the databases table from a master database.

=> SELECT * FROM DATABASES;
-[ RECORD 1 ]----------+-------------------------------------------------------------
database_id            | 45035996273704976
database_name          | VMart
owner_id               | 45035996273704962
owner_name             | dbadmin
start_time             | 2017-10-22 05:16:22.066961-04
compliance_message     | The database is in compliance with respect to raw data size.
export_subnet          | 0
load_balance_policy    | none
backend_address_family | ipv4
branch_name            |

1.18 - DIRECTED_QUERIES

Returns information about directed queries.

Privileges

Truncated query results

Query results for the fields INPUT_QUERY and ANNOTATED_QUERY are truncated after ~32K characters. You can get the full content of both fields in two ways:

• Use the statement GET DIRECTED QUERY.

• Use EXPORT_CATALOG to export directed queries.

1.19 - DUAL

DUAL is a single-column "dummy" table with one record whose value is X; for example:

=> SELECT * FROM DUAL;
 dummy
-------
 X
(1 row)

You can write the following types of queries:

=> SELECT 1 FROM dual;
 ?column?
----------
        1
(1 row)
=> SELECT current_timestamp, current_user FROM dual;
           ?column?            | current_user
-------------------------------+--------------
 2010-03-08 12:57:32.065841-05 | release
(1 row)
=> CREATE TABLE t1(col1 VARCHAR(20), col2 VARCHAR(2));
=> INSERT INTO T1(SELECT 'hello' AS col1, 1 AS col2 FROM dual);)
=> SELECT * FROM t1;
 col1  | col2
-------+------
 hello | 1
(1 row

Restrictions

You cannot create projections for DUAL.

1.20 - ELASTIC_CLUSTER

Returns information about cluster elasticity, such as whether Elastic cluster is running.

Privileges

Superuser

See also

• ENABLE_ELASTIC_CLUSTER
• Elastic cluster

1.21 - EPOCHS

For the most recently closed epochs, lists the date and time of the close and the corresponding epoch number of the closed epoch. The EPOCHS table may return a varying number of rows depending on current commit activities.

Examples

=> SELECT * FROM EPOCHS;
       epoch_close_time        | epoch_number
-------------------------------+--------------
 2018-11-12 16:05:15.552571-05 |           16
(1 row)

Querying for historical data

If you need historical data about epochs and corresponding date information, query the DC_TRANSACTION_ENDS table.

=> select dc.end_epoch,min(dc.time),max(dc.time) from dc_transaction_ends dc group by end_epoch;
 end_epoch |             min               | max
-----------+-------------------------------+-------------------------------
       214 |  2018-10-12 08:05:47.02075-04 | 2018-10-15 10:22:24.015292-04
       215 | 2018-10-15 10:22:47.015172-04 | 2018-10-15 13:00:44.888984-04
                          ...
       226 | 2018-10-15 15:03:47.015235-04 | 2018-10-15 20:37:34.346667-04
       227 | 2018-10-15 20:37:47.008137-04 | 2018-10-16 07:39:00.29917-04
       228 | 2018-10-16 07:39:47.012411-04 | 2018-10-16 08:16:01.470232-04
       229 | 2018-10-16 08:16:47.018899-04 | 2018-10-16 08:21:13.854348-04
       230 | 2018-10-16 08:21:47.013767-04 | 2018-10-17 12:21:09.224094-04
       231 |  2018-10-17 12:21:09.23193-04 | 2018-10-17 15:11:59.338777-04

See also

• Epoch management parameters
• Epoch management functions

1.22 - FAULT_GROUPS

View the fault groups and their hierarchy in the cluster.

Examples

Show the current hierarchy of fault groups in the cluster:

vmartdb=> SELECT member_type, member_name, parent_type, CASE
          WHEN parent_type = 'DATABASE' THEN ''
          ELSE parent_name END FROM fault_groups
          ORDER BY member_name;
 member_type | member_name           | parent_type | parent_name
-------------+-----------------------+-------------+-------------
 NODE        | v_vmart_node0001      | FAULT GROUP | two
 NODE        | v_vmart_node0002      | FAULT GROUP | two
 NODE        | v_vmart_node0003      | FAULT GROUP | three
 FAULT GROUP | one                   | DATABASE    |
 FAULT GROUP | three                 | DATABASE    |
 FAULT GROUP | two                   | FAULT GROUP | one

View the distribution of the segment layout:

vmartdb=> SELECT segment_layout from elastic_cluster;
                           segment_layout
-------------------------------------------------------------------------
 v_vmart_node0001[33.3%] v_vmart_node0003[33.3%] v_vmart_node0004[33.3%]
(1 row)

See also

• High availability with fault groups
• Fault groups

1.23 - FOREIGN_KEYS

Provides foreign key information.

Examples

mydb=> SELECT
          constraint_name,
          table_name,
          ordinal_position,
          reference_table_name
       FROM foreign_keys ORDER BY 3;
      constraint_name      |    table_name     | ordinal_position | reference_table_name
---------------------------+-------------------+------------------+-----------------------
 fk_store_sales_date       | store_sales_fact  |                1 | date_dimension
 fk_online_sales_saledate  | online_sales_fact |                1 | date_dimension
 fk_store_orders_product   | store_orders_fact |                1 | product_dimension
 fk_inventory_date         | inventory_fact    |                1 | date_dimension
 fk_inventory_product      | inventory_fact    |                2 | product_dimension
 fk_store_sales_product    | store_sales_fact  |                2 | product_dimension
 fk_online_sales_shipdate  | online_sales_fact |                2 | date_dimension
 fk_store_orders_product   | store_orders_fact |                2 | product_dimension
 fk_inventory_product      | inventory_fact    |                3 | product_dimension
 fk_store_sales_product    | store_sales_fact  |                3 | product_dimension
 fk_online_sales_product   | online_sales_fact |                3 | product_dimension
 fk_store_orders_store     | store_orders_fact |                3 | store_dimension
 fk_online_sales_product   | online_sales_fact |                4 | product_dimension
 fk_inventory_warehouse    | inventory_fact    |                4 | warehouse_dimension
 fk_store_orders_vendor    | store_orders_fact |                4 | vendor_dimension
 fk_store_sales_store      | store_sales_fact  |                4 | store_dimension
 fk_store_orders_employee  | store_orders_fact |                5 | employee_dimension
 fk_store_sales_promotion  | store_sales_fact  |                5 | promotion_dimension
 fk_online_sales_customer  | online_sales_fact |                5 | customer_dimension
 fk_store_sales_customer   | store_sales_fact  |                6 | customer_dimension
 fk_online_sales_cc        | online_sales_fact |                6 | call_center_dimension
 fk_store_sales_employee   | store_sales_fact  |                7 | employee_dimension
 fk_online_sales_op        | online_sales_fact |                7 | online_page_dimension
 fk_online_sales_shipping  | online_sales_fact |                8 | shipping_dimension
 fk_online_sales_warehouse | online_sales_fact |                9 | warehouse_dimension
 fk_online_sales_promotion | online_sales_fact |               10 | promotion_dimension
(26 rows)

1.24 - GRANTS

Returns information about privileges that are explicitly granted on database objects. Information about inherited privileges is not included.

Examples

The following query shows the privileges that are granted to user Rob or role R1. An asterisk (*) appended to a privilege indicates that the user can grant the privilege to other users:

=> SELECT grantor,privileges_description,object_name,object_type,grantee FROM grants WHERE grantee='Rob' OR grantee='R1';
 grantor |  privileges_description   | object_name | object_type  | grantee
 --------+---------------------------+-------------+--------------+---------
 dbadmin | USAGE                     | general     | RESOURCEPOOL | Rob
 dbadmin | USAGE, CREATE             | s1          | SCHEMA       | Rob
 dbadmin | INSERT*, SELECT*, UPDATE* | t1          | TABLE        | Rob
 dbadmin | SELECT                    | t1          | TABLE        | R1
 dbadmin | USAGE                     | s1          | SCHEMA       | R1
 dbadmin |                           | R1          | ROLE         | Rob
 (6 rows)

See also

• HAS_ROLE
• ROLES
• USERS
• Database users and privileges

1.25 - HCATALOG_COLUMNS

Describes the columns of all tables available through the HCatalog Connector. Each row in this table corresponds to to a column in a table accessible through the HCatalog Connector. See Using the HCatalog Connector for more information.

Privileges

No explicit permissions are required; however, users see only the records that correspond to schemas they have permissions to access.

Notes

If you are using WebHCat instead of HiveServer2, querying this table results in one web service call to the WebHCat server for each table in each HCatalog schema. If you need to perform multiple queries on this table in a short period of time, consider creating a copy of the table using a CREATE TABLE AS statement to improve performance. The copy does not reflect any changes made to the schema of the Hive tables after it was created, but it is much faster to query.

Examples

The following example demonstrates finding the column information for a specific table:

=> SELECT * FROM HCATALOG_COLUMNS WHERE table_name = 'hcatalogtypes'
-> ORDER BY ordinal_position;
-[ RECORD 1 ]------------+-----------------
table_schema             | hcat
hcatalog_schema          | default
table_name               | hcatalogtypes
is_partition_column      | f
column_name              | intcol
hcatalog_data_type       | int
data_type                | int
data_type_id             | 6
data_type_length         | 8
character_maximum_length |
numeric_precision        |
numeric_scale            |
datetime_precision       |
interval_precision       |
ordinal_position         | 1
-[ RECORD 2 ]------------+-----------------
table_schema             | hcat
hcatalog_schema          | default
table_name               | hcatalogtypes
is_partition_column      | f
column_name              | floatcol
hcatalog_data_type       | float
data_type                | float
data_type_id             | 7
data_type_length         | 8
character_maximum_length |
numeric_precision        |
numeric_scale            |
datetime_precision       |
interval_precision       |
ordinal_position         | 2
-[ RECORD 3 ]------------+-----------------
table_schema             | hcat
hcatalog_schema          | default
table_name               | hcatalogtypes
is_partition_column      | f
column_name              | doublecol
hcatalog_data_type       | double
data_type                | float
data_type_id             | 7
data_type_length         | 8
character_maximum_length |
numeric_precision        |
numeric_scale            |
datetime_precision       |
interval_precision       |
ordinal_position         | 3
-[ RECORD 4 ]------------+-----------------
table_schema             | hcat
hcatalog_schema          | default
table_name               | hcatalogtypes
is_partition_column      | f
column_name              | charcol
hcatalog_data_type       | string
data_type                | varchar(65000)
data_type_id             | 9
data_type_length         | 65000
character_maximum_length | 65000
numeric_precision        |
numeric_scale            |
datetime_precision       |
interval_precision       |
ordinal_position         | 4
-[ RECORD 5 ]------------+-----------------
table_schema             | hcat
hcatalog_schema          | default
table_name               | hcatalogtypes
is_partition_column      | f
column_name              | varcharcol
hcatalog_data_type       | string
data_type                | varchar(65000)
data_type_id             | 9
data_type_length         | 65000
character_maximum_length | 65000
numeric_precision        |
numeric_scale            |
datetime_precision       |
interval_precision       |
ordinal_position         | 5
-[ RECORD 6 ]------------+-----------------
table_schema             | hcat
hcatalog_schema          | default
table_name               | hcatalogtypes
is_partition_column      | f
column_name              | boolcol
hcatalog_data_type       | boolean
data_type                | boolean
data_type_id             | 5
data_type_length         | 1
character_maximum_length |
numeric_precision        |
numeric_scale            |
datetime_precision       |
interval_precision       |
ordinal_position         | 6
-[ RECORD 7 ]------------+-----------------
table_schema             | hcat
hcatalog_schema          | default
table_name               | hcatalogtypes
is_partition_column      | f
column_name              | timestampcol
hcatalog_data_type       | string
data_type                | varchar(65000)
data_type_id             | 9
data_type_length         | 65000
character_maximum_length | 65000
numeric_precision        |
numeric_scale            |
datetime_precision       |
interval_precision       |
ordinal_position         | 7
-[ RECORD 8 ]------------+-----------------
table_schema             | hcat
hcatalog_schema          | default
table_name               | hcatalogtypes
is_partition_column      | f
column_name              | varbincol
hcatalog_data_type       | binary
data_type                | varbinary(65000)
data_type_id             | 17
data_type_length         | 65000
character_maximum_length | 65000
numeric_precision        |
numeric_scale            |
datetime_precision       |
interval_precision       |
ordinal_position         | 8
-[ RECORD 9 ]------------+-----------------
table_schema             | hcat
hcatalog_schema          | default
table_name               | hcatalogtypes
is_partition_column      | f
column_name              | bincol
hcatalog_data_type       | binary
data_type                | varbinary(65000)
data_type_id             | 17
data_type_length         | 65000
character_maximum_length | 65000
numeric_precision        |
numeric_scale            |
datetime_precision       |
interval_precision       |
ordinal_position         | 9

See also

• HCATALOG_SCHEMATA
• HCATALOG_TABLES
• HCATALOG_TABLE_LIST

1.26 - HCATALOG_SCHEMATA

Lists all of the schemas defined using the HCatalog Connector. See Using the HCatalog Connector.

Unlike other HCatalog Connector-related system tables, this table makes no calls to Hive, so querying incurs very little overhead.

Privileges

No explicit permissions are required; however, users see only the records that correspond to schemas they have permissions to access.

See also

• HCATALOG_COLUMNS
• HCATALOG_TABLE_LIST
• HCATALOG_TABLES

1.27 - HCATALOG_TABLE_LIST

A concise list of all tables contained in all Hive schemas and databases available through the HCatalog Connector. See Using the HCatalog Connector.

Privileges

No explicit permissions are required; however, users see only the records that correspond to schemas they have permissions to access.

Notes

• Querying this table results in one call to HiveServer2 for each Hive schema defined using the HCatalog Connector. This means that the query usually takes longer than querying other system tables.

• Querying this table is faster than querying HCATALOG_TABLES. Querying HCATALOG_TABLE_LIST only makes one HiveServer2 call per HCatalog schema versus one call per table for HCATALOG_TABLES.

Examples

The following example demonstrates defining a new HCatalog schema then querying HCATALOG_TABLE_LIST. Note that one table defined in a different HCatalog schema also appears. HCATALOG_TABLE_LIST lists all of the tables available in any of the HCatalog schemas:

=> CREATE HCATALOG SCHEMA hcat WITH hostname='hcathost'
-> HCATALOG_SCHEMA='default' HCATALOG_DB='default' HCATALOG_USER='hcatuser';
CREATE SCHEMA
=> \x
Expanded display is on.
=> SELECT * FROM v_catalog.hcatalog_table_list;
-[ RECORD 1 ]------+------------------
table_schema_id    | 45035996273748980
table_schema       | hcat
hcatalog_schema    | default
table_name         | weblogs
hcatalog_user_name | hcatuser
-[ RECORD 2 ]------+------------------
table_schema_id    | 45035996273748980
table_schema       | hcat
hcatalog_schema    | default
table_name         | tweets
hcatalog_user_name | hcatuser
-[ RECORD 3 ]------+------------------
table_schema_id    | 45035996273748980
table_schema       | hcat
hcatalog_schema    | default
table_name         | messages
hcatalog_user_name | hcatuser
-[ RECORD 4 ]------+------------------
table_schema_id    | 45035996273864948
table_schema       | hiveschema
hcatalog_schema    | default
table_name         | weblogs
hcatalog_user_name | hcatuser

See also

• HCATALOG_COLUMNS
• HCATALOG_SCHEMATA
• HCATALOG_TABLES

1.28 - HCATALOG_TABLES

Returns a detailed list of all tables made available through the HCatalog Connector. See Using the HCatalog Connector.

Privileges

No explicit permissions are required; however, users see only the records that correspond to schemas they have permissions to access.

See also

• HCATALOG_SCHEMATA
• HCATALOG_COLUMNS
• HCATALOG_TABLE_LIST

1.29 - INHERITED_PRIVILEGES

Provides summary information about privileges inherited by tables and views from GRANT statements on parent schemas, excluding inherited grant options.

For information about explicitly granted permissions, see system table GRANTS.

Examples

The following query returns the privileges that the tables and views inherit from their parent schema, customers.

=> SELECT object_schema,object_name,object_type,privileges_description,principal,grantor FROM inherited_privileges WHERE object_schema='customers';
 object_schema |  object_name  | object_type |                          privileges_description                           | principal | grantor
 --------------+---------------+-------------+---------------------------------------------------------------------------+-----------+---------
 customers     | cust_info     | Table       | INSERT, SELECT, UPDATE, DELETE, ALTER, REFERENCES, DROP, TRUNCATE         | dbadmin   | dbadmin
 customers     | shipping_info | Table       | INSERT, SELECT, UPDATE, DELETE, ALTER, REFERENCES, DROP, TRUNCATE         | dbadmin   | dbadmin
 customers     | cust_set      | View        | SELECT, ALTER, DROP                                                       | dbadmin   | dbadmin
 customers     | cust_info     | Table       | SELECT                                                                    | Val       | dbadmin
 customers     | shipping_info | Table       | SELECT                                                                    | Val       | dbadmin
 customers     | cust_set      | View        | SELECT                                                                    | Val       | dbadmin
 customers     | cust_info     | Table       | INSERT                                                                    | Pooja     | dbadmin
 customers     | shipping_info | Table       | INSERT                                                                    | Pooja     | dbadmin
 (8 rows)

See also

• INHERITING_OBJECTS
• Database users and privileges
• GET_PRIVILEGES_DESCRIPTION

1.30 - INHERITING_OBJECTS

Provides information about which tables and views inherit privileges from which schemas.

For information about the specific privileges inherited from schemas and their associated GRANT statements, see the INHERITED_PRIVILEGES table.

Examples

The following query returns the tables and views that inherit their privileges from their parent schema, customers.

=> SELECT * FROM inheriting_objects WHERE object_schema='customers';
     object_id     |     schema_id     | object_schema |  object_name  | object_type
 ------------------+-------------------+---------------+---------------+-------------
 45035996273980908 | 45035996273980902 | customers     | cust_info     | table
 45035996273980984 | 45035996273980902 | customers     | shipping_info | table
 45035996273980980 | 45035996273980902 | customers     | cust_set      | view
 (3 rows)

See also

• INHERITED_PRIVILEGES
• Inherited privileges
• Database users and privileges
• GET_PRIVILEGES_DESCRIPTION

1.31 - KEYWORDS

Identifies Vertica reserved and non-reserved keywords.

Examples

The following query gets all reserved keywords that begin with B:

=> SELECT * FROM keywords WHERE reserved = 'R' AND keyword ilike 'B%';
 keyword | reserved
---------+----------
 BETWEEN | R
 BIGINT  | R
 BINARY  | R
 BIT     | R
 BOOLEAN | R
 BOTH    | R
(6 rows)

See also

1.32 - LARGE_CLUSTER_CONFIGURATION_STATUS

Shows the current cluster nodes and control node (spread hosts) designations in the Catalog so you can see if they match.

See also

Large Cluster

1.33 - LICENSE_AUDITS

Lists the results of Vertica's license automatic compliance audits. See How Vertica Calculates Database Size.

1.34 - LICENSES

For all licenses, provides information on license types, the dates for which licenses are valid, and the limits the licenses impose.

1.35 - LOAD_BALANCE_GROUPS

Lists the objects contained by all load balance groups. Each row in this table represents a single object that is a member of a load balance group. If a load balance group does not contain any objects, it appears once in this table with its type column set to 'Empty Group.'

Examples

=> 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           |
 group_all     | ROUNDROBIN |           | Network Address Group | node01
 group_all     | ROUNDROBIN |           | Network Address Group | node02
 group_all     | ROUNDROBIN |           | Network Address Group | node03
 group_fault_1 | RANDOM     | 0.0.0.0/0 | Fault Group           | fault_1
(7 rows)

See also

• NETWORK_ADDRESSES
• ALTER LOAD BALANCE GROUP
• ALTER NETWORK ADDRESS
• ALTER ROUTING RULE
• CREATE LOAD BALANCE GROUP

1.36 - LOG_PARAMS

Provides summary information about changes to configuration parameters related to authentication and security run in your database.

Examples

The following example queries the LOG_PARAMS system table and shows only the most recent configuration parameter for this user under the Authentication category:

=> SELECT * FROM log_params limit 1;

--------------------------------------------------------------------------------------------------------

issued_time   | 2018-02-12 13:41:20.837452-05
user_name     | dbadmin
user_id       | 45035996273704962
hostname      | ::1:50690
session_id    | v_vmart_node0001-341751:0x13878
audit_type    | Param
audit_tag_name| SecurityAlgorithm
request_type  | UTILITY
request_id    | 8
subject       | MD5
request       | select set_config_parameter('SecurityAlgorithm','MD5',null);
success       | t
category      | Authentication
(1 row)

1.37 - LOG_QUERIES

Provides summary information about some queries related to authentication and security run in your database.

Examples

The following example queries the LOG_QUERIES system table and shows only the most recent query for this user under the Managing_Users_Privileges category:

=> SELECT * FROM log_queries limit 1;
---------------------------------------------------------------------------

issued_time   | 2018-01-22 10:36:55.634349-05
user_name     | dbadmin
user_id       | 45035996273704962
hostname      |
session_id    | v_vmart_node0001-237210:0x37e1d
audit_type    | Query
audit_tag_name| REVOKE ROLE
request_type  | DDL
request_id    | 2
subject       |
request       | revoke all privileges from Joe;
success       | f
category      | Managing_Users_Privileges
(1 row)

1.38 - LOG_TABLES

Provides summary information about queries on system tables.

Examples

The following example shows recent queries on configuration parameters:

dbadmin=> SELECT issued_time, audit_type, request_type, subject, request, category FROM log_tables
   WHERE category ilike '%Managing_Config_Parameters%' ORDER BY issued_time DESC LIMIT 4;
-[ RECORD 1 ]+-------------------------------------------------------------------------------------
issued_time  | 2020-05-14 14:14:53.453552-04
audit_type   | Table
request_type | QUERY
subject      | vs_nodes
request      | SELECT * from vs_nodes order by name  limit 1;
category     | Managing_Config_Parameters
-[ RECORD 2 ]+-------------------------------------------------------------------------------------
issued_time  | 2020-05-14 14:14:27.546474-04
audit_type   | Table
request_type | QUERY
subject      | vs_nodes
request      | SELECT * from vs_nodes order by name ;
category     | Managing_Config_Parameters
-[ RECORD 3 ]+-------------------------------------------------------------------------------------
issued_time  | 2020-05-14 08:54:32.86881-04
audit_type   | Table
request_type | QUERY
subject      | vs_parameters_mismatch
request      | select * from configuration_parameters where parameter_name = 'MaxDepotSizePercent';
category     | Managing_Config_Parameters
-[ RECORD 4 ]+-------------------------------------------------------------------------------------
issued_time  | 2020-05-14 08:54:32.86881-04
audit_type   | Table
request_type | QUERY
subject      | vs_nodes
request      | select * from configuration_parameters where parameter_name = 'MaxDepotSizePercent';
category     | Managing_Config_Parameters

1.39 - MATERIALIZE_FLEXTABLE_COLUMNS_RESULTS

Contains the results of running the MATERIALIZE_FLEXTABLE_COLUMNS function. The table contains information about keys that the function evaluated. It does not contain information about all keys.

Examples

=> \x
Expanded display is on.
=> SELECT table_id, table_schema, table_name, key_name, status, message FROM MATERIALIZE_FLEXTABLE_COLUMNS_RESULTS
WHERE table_name = 'mountains_hybrid';
-[ RECORD 1 ]+-------------------------------------------------------
table_id     | 45035996273708192
table_schema | public
table_name   | mountains_hybrid
key_name     | type
status       | ADDED
message      | Added successfully
-[ RECORD 2 ]+-------------------------------------------------------
table_id     | 45035996273708192
table_schema | public
table_name   | mountains_hybrid
key_name     | height
status       | ADDED
message      | Added successfully
-[ RECORD 3 ]+-------------------------------------------------------
table_id     | 45035996273708192
table_schema | public
table_name   | mountains_hybrid
key_name     | name
status       | EXISTS
message      | Column of same name already exists in table definition

1.40 - MODELS

Lists details about the machine-learning models in the database.

Examples

=> SELECT * FROM models;
-[ RECORD 1 ]------------------------------
model_id    | 45035996273714020
model_name  | myLinearRegModel
schema_id   | 45035996273704980
schema_name | public
owner_id    | 45035996273704962
owner_name  | dbadmin
category    | VERTICA_MODELS
model_type  | LINEAR_REGRESSION
is_complete | t
create_time | 2018-01-22 11:13:35.018412-05
size        | 1671

1.41 - NETWORK_ADDRESSES

Lists information about the network addresses defined in your database using the CREATE NETWORK ADDRESS statement.

Examples

=> \x
Expanded display is on.

=> SELECT * FROM v_catalog.network_addresses;
-[ RECORD 1 ]----+-----------------
name             | node01
node             | v_vmart_node0001
address          | 10.20.100.247
port             | 5433
address_family   | ipv4
is_enabled       | t
is_auto_detected | f
-[ RECORD 2 ]----+-----------------
name             | node02
node             | v_vmart_node0002
address          | 10.20.100.248
port             | 5433
address_family   | ipv4
is_enabled       | t
is_auto_detected | f
-[ RECORD 3 ]----+-----------------
name             | node03
node             | v_vmart_node0003
address          | 10.20.100.249
port             | 5433
address_family   | ipv4
is_enabled       | t
is_auto_detected | f

See also

• LOAD_BALANCE_GROUPS
• ALTER LOAD BALANCE GROUP
• ALTER NETWORK ADDRESS
• ALTER ROUTING RULE
• CREATE LOAD BALANCE GROUP

1.42 - NODE_SUBSCRIPTION_CHANGE_PHASES

In an Eon Mode database, stores information about changes to node's shard subscriptions.

Examples

=> SELECT NODE_NAME, SUBSCRIPTION_CHANGE_TYPE, SHARD_NAME,
   ACTION  FROM node_subscription_change_phases
   ORDER BY start_time ASC LIMIT 10;

      NODE_NAME       | SUBSCRIPTION_CHANGE_TYPE | SHARD_NAME  |         ACTION
----------------------+--------------------------+-------------+------------------------
 v_verticadb_node0001 | CREATE SUBSCRIPTION      | segment0007 | COLLECT SHARD METADATA
 v_verticadb_node0001 | CREATE SUBSCRIPTION      | segment0010 | COLLECT SHARD METADATA
 v_verticadb_node0001 | CREATE SUBSCRIPTION      | segment0004 | COLLECT SHARD METADATA
 v_verticadb_node0001 | CREATE SUBSCRIPTION      | segment0005 | COLLECT SHARD METADATA
 v_verticadb_node0001 | CREATE SUBSCRIPTION      | replica     | COLLECT SHARD METADATA
 v_verticadb_node0001 | CREATE SUBSCRIPTION      | segment0005 | COLLECT SHARD METADATA
 v_verticadb_node0001 | CREATE SUBSCRIPTION      | segment0006 | COLLECT SHARD METADATA
 v_verticadb_node0001 | CREATE SUBSCRIPTION      | segment0008 | COLLECT SHARD METADATA
 v_verticadb_node0001 | CREATE SUBSCRIPTION      | segment0011 | COLLECT SHARD METADATA
 v_verticadb_node0001 | CREATE SUBSCRIPTION      | segment0002 | COLLECT SHARD METADATA

1.43 - NODE_SUBSCRIPTIONS

Eon Mode only

Lists information about database node subscriptions to shards.

Examples

The following example queries the NODE_SUBSCRIPTIONS table in a database with two three-node subclusters (a primary and a secondary) in a 12-shard database.

=> SELECT node_name, shard_name, subscription_state, is_primary,
          is_participating_primary AS is_p_primary
       FROM NODE_SUBSCRIPTIONS ORDER BY node_name, shard_name;

      node_name       | shard_name  | subscription_state | is_primary | is_p_primary
----------------------+-------------+--------------------+------------+--------------
 v_verticadb_node0001 | replica     | ACTIVE             | t          | t
 v_verticadb_node0001 | segment0001 | ACTIVE             | t          | t
 v_verticadb_node0001 | segment0003 | ACTIVE             | f          | f
 v_verticadb_node0001 | segment0004 | ACTIVE             | t          | t
 v_verticadb_node0001 | segment0006 | ACTIVE             | f          | f
 v_verticadb_node0001 | segment0007 | ACTIVE             | t          | t
 v_verticadb_node0001 | segment0009 | ACTIVE             | f          | f
 v_verticadb_node0001 | segment0010 | ACTIVE             | t          | t
 v_verticadb_node0001 | segment0012 | ACTIVE             | f          | f
 v_verticadb_node0002 | replica     | ACTIVE             | f          | t
 v_verticadb_node0002 | segment0001 | ACTIVE             | f          | f
 v_verticadb_node0002 | segment0002 | ACTIVE             | t          | t
 v_verticadb_node0002 | segment0004 | ACTIVE             | f          | f
 v_verticadb_node0002 | segment0005 | ACTIVE             | t          | t
 v_verticadb_node0002 | segment0007 | ACTIVE             | f          | f
 v_verticadb_node0002 | segment0008 | ACTIVE             | t          | t
 v_verticadb_node0002 | segment0010 | ACTIVE             | f          | f
 v_verticadb_node0002 | segment0011 | ACTIVE             | t          | t
 v_verticadb_node0003 | replica     | ACTIVE             | f          | t
 v_verticadb_node0003 | segment0002 | ACTIVE             | f          | f
 v_verticadb_node0003 | segment0003 | ACTIVE             | t          | t
 v_verticadb_node0003 | segment0005 | ACTIVE             | f          | f
 v_verticadb_node0003 | segment0006 | ACTIVE             | t          | t
 v_verticadb_node0003 | segment0008 | ACTIVE             | f          | f
 v_verticadb_node0003 | segment0009 | ACTIVE             | t          | t
 v_verticadb_node0003 | segment0011 | ACTIVE             | f          | f
 v_verticadb_node0003 | segment0012 | ACTIVE             | t          | t
 v_verticadb_node0004 | replica     | ACTIVE             | f          | t
 v_verticadb_node0004 | segment0001 | ACTIVE             | f          | t
 v_verticadb_node0004 | segment0003 | ACTIVE             | f          | f
 v_verticadb_node0004 | segment0004 | ACTIVE             | f          | t
 v_verticadb_node0004 | segment0006 | ACTIVE             | f          | f
 v_verticadb_node0004 | segment0007 | ACTIVE             | f          | t
 v_verticadb_node0004 | segment0009 | ACTIVE             | f          | f
 v_verticadb_node0004 | segment0010 | ACTIVE             | f          | t
 v_verticadb_node0004 | segment0012 | ACTIVE             | f          | f
 v_verticadb_node0005 | replica     | ACTIVE             | f          | t
 v_verticadb_node0005 | segment0001 | ACTIVE             | f          | f
 v_verticadb_node0005 | segment0002 | ACTIVE             | f          | t
 v_verticadb_node0005 | segment0004 | ACTIVE             | f          | f
 v_verticadb_node0005 | segment0005 | ACTIVE             | f          | t
 v_verticadb_node0005 | segment0007 | ACTIVE             | f          | f
 v_verticadb_node0005 | segment0008 | ACTIVE             | f          | t
 v_verticadb_node0005 | segment0010 | ACTIVE             | f          | f
 v_verticadb_node0005 | segment0011 | ACTIVE             | f          | t
 v_verticadb_node0006 | replica     | ACTIVE             | f          | t
 v_verticadb_node0006 | segment0002 | ACTIVE             | f          | f
 v_verticadb_node0006 | segment0003 | ACTIVE             | f          | t
 v_verticadb_node0006 | segment0005 | ACTIVE             | f          | f
 v_verticadb_node0006 | segment0006 | ACTIVE             | f          | t
 v_verticadb_node0006 | segment0008 | ACTIVE             | f          | f
 v_verticadb_node0006 | segment0009 | ACTIVE             | f          | t
 v_verticadb_node0006 | segment0011 | ACTIVE             | f          | f
 v_verticadb_node0006 | segment0012 | ACTIVE             | f          | t
(54 rows)

1.44 - NODES

Lists details about the nodes in the database.

Example

=> SELECT NODE_NAME, NODE_STATE, IS_PRIMARY, IS_READONLY, NODE_TYPE,
          SUBCLUSTER_NAME FROM NODES ORDER BY NODE_NAME ASC;
      NODE_NAME       | NODE_STATE | IS_PRIMARY | IS_READONLY | NODE_TYPE |  SUBCLUSTER_NAME
----------------------+------------+------------+-------------+-----------+--------------------
 v_verticadb_node0001 | UP         | t          | f           | PERMANENT | default_subcluster
 v_verticadb_node0002 | UP         | t          | f           | PERMANENT | default_subcluster
 v_verticadb_node0003 | UP         | t          | f           | PERMANENT | default_subcluster
 v_verticadb_node0004 | UP         | f          | f           | PERMANENT | analytics
 v_verticadb_node0005 | UP         | f          | f           | PERMANENT | analytics
 v_verticadb_node0006 | UP         | f          | f           | PERMANENT | analytics
(6 rows)

1.45 - ODBC_COLUMNS

Provides table column information. The format is defined by the ODBC standard for the ODBC SQLColumns metadata. Details on the ODBC SQLColumns format are available in the ODBC specification: http://msdn.microsoft.com/en-us/library/windows/desktop/ms711683%28v=vs.85%29.aspx.

1.46 - PASSWORD_AUDITOR

Stores information about individual users and their password information. This table also indicates if users are using hash authentication, which is the associated security algorithm.

1.47 - PASSWORDS

Contains information on current user passwords. This table also includes information on past passwords if any Profiles have PASSWORD_REUSE_TIME or PASSWORD_REUSE_MAX parameters set. See CREATE PROFILE for details.

Examples

The following query returns the SHA-512 hashed password and salt of user 'u1'.

=> SELECT user_name, password, salt FROM passwords WHERE user_name='u1';
 user_name |                         password                       |               salt
-----------+--------------------------------------------------------+----------------------------------
 u1        | sha512f3f802f1c56e2530cd9c3164cc7b8002ba444c0834160f10 | f05e9d859fb441f9f612f8a787bfc872
(1 row)

1.48 - PRIMARY_KEYS

Provides primary key information.

1.49 - PROFILE_PARAMETERS

Defines what information is stored in profiles.

1.50 - PROFILES

Provides information about password policies that you set using the CREATE PROFILE statement.

Notes

Non-superusers querying this table see only the information for the profile to which they are assigned.

See also

• CREATE PROFILE
• ALTER PROFILE

1.51 - PROJECTION_CHECKPOINT_EPOCHS

Provides details on checkpoint epochs, applies only to Enterprise Mode.

Privileges

Non-superuser: No explicit privileges required. You only see records for tables that you have privileges to view.

Examples

=> SELECT node_name, projection_schema, projection_name, is_up_to_date, checkpoint_epoch FROM projection_checkpoint_epochs
    WHERE projection_name ilike 't1_b%' ORDER BY projection_name, node_name;
    node_name     | projection_schema | projection_name | is_up_to_date | checkpoint_epoch
------------------+-------------------+-----------------+---------------+------------------
 v_vmart_node0001 | public            | t1_b1           | t             |              965
 v_vmart_node0002 | public            | t1_b1           | t             |              965
 v_vmart_node0003 | public            | t1_b1           | t             |              965
 v_vmart_node0001 | public            | t1_b0           | t             |              965
 v_vmart_node0002 | public            | t1_b0           | t             |              965
 v_vmart_node0003 | public            | t1_b0           | t             |              965
(6 rows)

dbadmin=> INSERT INTO t1 VALUES (100, 101, 102);
 OUTPUT
--------
      1
(1 row)

dbadmin=> COMMIT;
COMMIT
dbadmin=> SELECT node_name, projection_schema, projection_name, is_up_to_date, checkpoint_epoch FROM projection_checkpoint_epochs
           WHERE projection_name ILIKE 't1_b%' ORDER BY projection_name, node_name;
    node_name     | projection_schema | projection_name | is_up_to_date | checkpoint_epoch
------------------+-------------------+-----------------+---------------+------------------
 v_vmart_node0001 | public            | t1_b1           | t             |              966
 v_vmart_node0002 | public            | t1_b1           | t             |              966
 v_vmart_node0003 | public            | t1_b1           | t             |              966
 v_vmart_node0001 | public            | t1_b0           | t             |              966
 v_vmart_node0002 | public            | t1_b0           | t             |              966
 v_vmart_node0003 | public            | t1_b0           | t             |              966
(6 rows)

1.52 - PROJECTION_COLUMNS

Provides information about projection columns, such as encoding type, sort order, type of statistics, and the time at which columns statistics were last updated.

Examples

See Statistics Data in PROJECTION_COLUMNS

See also

• PROJECTIONS
• ANALYZE_STATISTICS
• CREATE PROJECTION

1.53 - PROJECTION_DELETE_CONCERNS

Lists projections whose design are liable to cause performance issues when deleting data. This table is generated by calling the EVALUATE_DELETE_PERFORMANCE function. See Optimizing DELETE and UPDATE for more information.

1.54 - PROJECTIONS

Provides information about projections.

=> CREATE PROJECTION ytd_orders AS SELECT * FROM store_orders ORDER BY order_date
    ON PARTITION RANGE BETWEEN date_trunc('year',now())::date AND NULL;

=> SELECT projection_name partition_range_min, partition_range_min, partition_range_max FROM projections WHERE projection_name ILIKE 'ytd_orders%';
 partition_range_min | partition_range_min | partition_range_max
---------------------+---------------------+---------------------
 ytd_orders_b1       | 2021-01-01          | infinity
 ytd_orders_b0       | 2021-01-01          | infinity
(2 rows)

=> CREATE PROJECTION q3_td AS SELECT * FROM store_orders ORDER BY order_date
    ON PARTITION RANGE BETWEEN add_months(date_trunc('year',now()), -3)::date AND NULL;

=> SELECT projection_name, partition_range_min_expression, partition_range_max_expression FROM projections WHERE projection_name ILIKE 'Q3_td%';
 projection_name |       partition_range_min_expression        | partition_range_max_expression
-----------------+---------------------------------------------+--------------------------------
 q3_td_b1        | add_months(date_trunc('year', now()), (-3)) | NULL
 q3_td_b0        | add_months(date_trunc('year', now()), (-3)) | NULL

See also

1.55 - RESOURCE_POOL_DEFAULTS

Returns default parameter settings for built-in and user-defined resource pools. Use ALTER RESOURCE POOL to restore resource pool parameters to their default settings.

For information about valid parameters for built-in resource pools and their default settings, see Built-in resource pools configuration.

To obtain a resource pool's current settings, query system table RESOURCE_POOLS.

Privileges

None

1.56 - RESOURCE_POOLS

Displays settings for built-in and user-defined resource pools. For information about defining resource pools, see CREATE RESOURCE POOL and ALTER RESOURCE POOL.

1.57 - ROLES

Contains the names of all roles the user can access, along with any roles that have been assigned to those roles.

See also

• GRANTS
• HAS_ROLE
• USERS

1.58 - ROUTING_RULES

Lists the routing rules that map incoming IP addresses to a load balancing group.

Examples

=> SELECT * FROM routing_rules;
-[ RECORD 1 ]----+-----------------
name             | internal_clients
source_address   | 192.168.1.0/24
destination_name | group_1
-[ RECORD 2 ]----+-----------------
name             | etl_rule
source_address   | 10.20.100.0/24
destination_name | group_2
-[ RECORD 3 ]----+-----------------
name             | subnet_192
source_address   | 192.0.0.0/8
destination_name | group_all
-[ RECORD 4 ]----+--------------
name             | all_ipv6
source_address   | 0::0/0
destination_name | default_ipv6

See also

1.59 - SCHEMATA

Provides information about schemas in the database.

Privileges

Non-superuser: No explicit privileges required. You only see records for tables that you have privileges to view.

1.60 - SEQUENCES

Displays information about sequences.

Examples

Create a simple sequence:

=> CREATE SEQUENCE my_seq MAXVALUE 5000 START 150;
CREATE SEQUENCE

Return information about the sequence you just created:

=> \x
Expanded display is on.
=> SELECT * FROM sequences;
-[ RECORD 1 ]-------+------------------
sequence_schema     | public
sequence_name       | my_seq
owner_name          | dbadmin
identity_table_name |
session_cache_count | 250000
allow_cycle         | f
output_ordered      | f
increment_by        | 1
minimum             | 1
maximum             | 5000
current_value       | 149
sequence_schema_id  | 45035996273704966
sequence_id         | 45035996273844996
owner_id            | 45035996273704962
identity_table_id   | 0

An identity column is a sequence available only for numeric column types. To identify what column in a table, if any, is an identity column, search the COLUMNS table to find the identity column in a table:

=> CREATE TABLE testid (c1 IDENTITY(1, 1, 1000), c2 INT);
=> \x
Expanded display is on.
=> SELECT * FROM COLUMNS WHERE is_identity='t' AND table_name='testid';
-[ RECORD 1 ]------------+------------------
table_id                 | 45035996274150730
table_schema             | public
table_name               | testid
is_system_table          | f
column_name              | c1
data_type                | int
data_type_id             | 6
data_type_length         | 8
character_maximum_length |
numeric_precision        |
numeric_scale            |
datetime_precision       |
interval_precision       |
ordinal_position         | 1
is_nullable              | f
column_default           |
is_identity              | t

Use the SEQUENCES table to get detailed information about the sequence in testid:

=> SELECT * FROM sequences WHERE identity_table_name='testid';
-[ RECORD 1 ]-------+--------------------
sequence_schema     | public
sequence_name       | testid_c1_seq
owner_name          | dbadmin
identity_table_name | testid
session_cache_count | 1000
allow_cycle         | f
output_ordered      | f
increment_by        | 1
minimum             | 1
maximum             | 9223372036854775807
current_value       | 0
sequence_schema_id  | 45035996273704976
sequence_id         | 45035996274150770
owner_id            | 45035996273704962
identity_table_id   | 45035996274150768

Use the vsql command \ds to return a list of sequences. The following results show the two sequences created in the preceding examples. If more sequences existed, the table would list them.

The CurrentValue of the new sequence is one less than the start number you specified in the CREATE SEQUENCE and IDENTITY commands, because you have not yet used NEXTVAL to instantiate the sequences to assign their cache or supply their first start values.

=> \ds
 List of Sequences
-[ RECORD 1 ]+--------------------
Schema       | public
Sequence     | my_seq
CurrentValue | 149
IncrementBy  | 1
Minimum      | 1
Maximum      | 5000
AllowCycle   | f
Comment      |
-[ RECORD 2 ]+--------------------
Schema       | public
Sequence     | testid_c1_seq
CurrentValue | 0
IncrementBy  | 1
Minimum      | 1
Maximum      | 9223372036854775807
AllowCycle   | f
Comment      |

1.61 - SESSION_SUBSCRIPTIONS

In an Eon Mode database, lists the shard subscriptions for all nodes, and whether the subscriptions are used to resolve queries for the current session. Nodes that will participate in resolving queries in this session have TRUE in their IS_PARTICIPATING column.

Examples

The following example demonstrates listing the subscriptions that are either participating or collaborating in the current session:

=> SELECT node_name, shard_name, is_collaborating, is_participating
        FROM V_CATALOG.SESSION_SUBSCRIPTIONS
        WHERE is_participating = TRUE OR is_collaborating = TRUE
        ORDER BY shard_name, node_name;
      node_name       | shard_name  | is_collaborating | is_participating
----------------------+-------------+------------------+------------------
 v_verticadb_node0004 | replica     | f                | t
 v_verticadb_node0005 | replica     | f                | t
 v_verticadb_node0006 | replica     | t                | f
 v_verticadb_node0007 | replica     | f                | t
 v_verticadb_node0008 | replica     | t                | f
 v_verticadb_node0009 | replica     | t                | f
 v_verticadb_node0007 | segment0001 | f                | t
 v_verticadb_node0008 | segment0001 | t                | f
 v_verticadb_node0005 | segment0002 | f                | t
 v_verticadb_node0009 | segment0002 | t                | f
 v_verticadb_node0004 | segment0003 | f                | t
 v_verticadb_node0006 | segment0003 | t                | f
(12 rows)

1.62 - SHARDS

Lists the shards in your database.

Examples

=> SELECT * FROM SHARDS;
-[ RECORD 1 ]----+------------------
shard_oid        | 45035996273704980
shard_name       | replica
shard_type       | Replica
lower_hash_bound |
upper_hash_bound |
is_replicated    | t
has_objects      | t
...

1.63 - STORAGE_LOCATIONS

Provides information about storage locations, including IDs, labels, and status.

Privileges

Superuser

See also

• DISK_STORAGE
• MEASURE_LOCATION_PERFORMANCE
• SET_LOCATION_PERFORMANCE
• STORAGE_POLICIES
• STORAGE_USAGE
• Storage management functions

1.64 - SUBCLUSTER_RESOURCE_POOL_OVERRIDES

Displays subcluster-level overrides of settings for built-in global resource pools.

1.65 - SUBCLUSTERS

This table lists all of the subclusters defined in the database. It contains an entry for each node in the database listing which subcluster it belongs to. Any subcluster that does not contain a node has a single entry in this table with empty NODE_NAME and NODE_OID columns. This table is only populated if the database is running in Eon Mode.

Examples

=> \x
Expanded display is on.
dbadmin=> SELECT * FROM SUBCLUSTERS;
-[ RECORD 1 ]----+---------------------
subcluster_oid   | 45035996273704978
subcluster_name  | default_subcluster
node_oid         | 45035996273704982
node_name        | v_verticadb_node0001
parent_oid       | 45035996273704976
parent_name      | verticadb
is_default       | t
is_primary       | t
control_set_size | -1
-[ RECORD 2 ]----+---------------------
subcluster_oid   | 45035996273704978
subcluster_name  | default_subcluster
node_oid         | 45035996273840970
node_name        | v_verticadb_node0002
parent_oid       | 45035996273704976
parent_name      | verticadb
is_default       | t
is_primary       | t
control_set_size | -1
-[ RECORD 3 ]----+---------------------
subcluster_oid   | 45035996273704978
subcluster_name  | default_subcluster
node_oid         | 45035996273840974
node_name        | v_verticadb_node0003
parent_oid       | 45035996273704976
parent_name      | verticadb
is_default       | t
is_primary       | t
control_set_size | -1

See also

• CRITICAL_SUBCLUSTERS
• ALTER SUBCLUSTER
• DEMOTE_SUBCLUSTER_TO_SECONDARY
• PROMOTE_SUBCLUSTER_TO_PRIMARY
• SHUTDOWN_SUBCLUSTER

1.66 - SYSTEM_COLUMNS

Provides table column information for SYSTEM_TABLES.

1.67 - SYSTEM_TABLES

Returns a list of all system table names.

1.68 - TABLE_CONSTRAINTS

Provides information about table constraints.

See also

1.69 - TABLES

Provides information about all tables in the database.

SELECT table_schema, table_name FROM v_catalog.tables WHERE table_schema ILIKE 'Store%';

Examples

Find when tables were created:

=> SELECT table_schema, table_name, create_time FROM tables;
 table_schema |      table_name       |          create_time
--------------+-----------------------+-------------------------------
 public       | customer_dimension    | 2011-08-15 11:18:25.784203-04
 public       | product_dimension     | 2011-08-15 11:18:25.815653-04
 public       | promotion_dimension   | 2011-08-15 11:18:25.850592-04
 public       | date_dimension        | 2011-08-15 11:18:25.892347-04
 public       | vendor_dimension      | 2011-08-15 11:18:25.942805-04
 public       | employee_dimension    | 2011-08-15 11:18:25.966985-04
 public       | shipping_dimension    | 2011-08-15 11:18:25.999394-04
 public       | warehouse_dimension   | 2011-08-15 11:18:26.461297-04
 public       | inventory_fact        | 2011-08-15 11:18:26.513525-04
 store        | store_dimension       | 2011-08-15 11:18:26.657409-04
 store        | store_sales_fact      | 2011-08-15 11:18:26.737535-04
 store        | store_orders_fact     | 2011-08-15 11:18:26.825801-04
 online_sales | online_page_dimension | 2011-08-15 11:18:27.007329-04
 online_sales | call_center_dimension | 2011-08-15 11:18:27.476844-04
 online_sales | online_sales_fact     | 2011-08-15 11:18:27.49749-04
(15 rows)

Find out whether certain tables are temporary and flex tables:

=> SELECT distinct table_name, table_schema, is_temp_table, is_flextable FROM v_catalog.tables
   WHERE table_name ILIKE 't%';
  table_name  | table_schema | is_temp_table | is_flextable
--------------+--------------+---------------+-----------------
 t2_temp      | public       | t             | t
 tt_keys      | public       | f             | f
 t2_temp_keys | public       | f             | f
 t3           | public       | t             | f
 t1           | public       | f             | f
 t9_keys      | public       | f             | f
 t2_keys      | public       | f             | t
 t6           | public       | t             | f
 t5           | public       | f             | f
 t2           | public       | f             | t
 t8           | public       | f             | f
 t7           | public       | t             | f
 tt           | public       | t             | t
 t2_keys_keys | public       | f             | f
 t9           | public       | t             | t
(15 rows)

1.70 - TEXT_INDICES

Provides summary information about the text indices in Vertica.

1.71 - TYPES

Provides information about supported data types. This table does not include inlined complex types; see COMPLEX_TYPES instead. This table does include arrays and sets of primitive types.

1.72 - USER_AUDITS

Lists the results of database and object size audits generated by users calling the AUDIT function. See Monitoring database size for license compliance for more information.

1.73 - USER_CLIENT_AUTH

Provides information about the client authentication methods that are associated with database users. You associate an authentication method with a user using GRANT (Authentication).

1.74 - USER_CONFIGURATION_PARAMETERS

Provides information about user-level configuration parameters that are in effect for database users.

Privileges

Superuser only

Examples

=> SELECT * FROM user_configuration_parameters;
 user_name |      parameter_name       | current_value | default_value
-----------+---------------------------+---------------+---------------
 Yvonne    | LoadSourceStatisticsLimit | 512           | 256
(1 row)

=> ALTER USER Ahmed SET DepotOperationsForQuery='FETCHES';
ALTER USER
=> ALTER USER Yvonne SET DepotOperationsForQuery='FETCHES';
ALTER USER
=> SELECT * FROM user_configuration_parameters;
 user_name |      parameter_name       | current_value | default_value
-----------+---------------------------+---------------+---------------
 Ahmed     | DepotOperationsForQuery   | FETCHES       | ALL
 Yvonne    | DepotOperationsForQuery   | FETCHES       | ALL
 Yvonne    | LoadSourceStatisticsLimit | 512           | 256
(3 rows)

1.75 - USER_FUNCTION_PARAMETERS

Provides information about the parameters of a C++ user-defined function (UDx). You can only view parameters that have the Properties.visible parameter set to TRUE.

Privileges

Any user can query the USER_FUNCTION_PARAMETERS table. However, users can only see table information about those UDx functions which the user has permission to use.

See also

• Developing user-defined extensions (UDxs)
• UDx parameters

1.76 - USER_FUNCTIONS

Returns metadata about user-defined SQL functions (which store commonly used SQL expressions as a function in the Vertica catalog) and user-defined functions.

Notes

• The volatility and strictness of a SQL function are automatically inferred from the function definition in order that Vertica determine the correctness of usage, such as where an immutable function is expected but a volatile function is provided.

• The volatility and strictness of a UDx is defined by the UDx's developer.

Examples

Create a SQL function called myzeroifnull in the public schema:

=> CREATE FUNCTION myzeroifnull(x INT) RETURN INT
   AS BEGIN
     RETURN (CASE WHEN (x IS NOT NULL) THEN x ELSE 0 END);
   END;

Now query the USER_FUNCTIONS table. The query returns just the myzeroifnull macro because it is the only one created in this schema:

=> SELECT * FROM user_functions;
-[ RECORD 1 ]----------+---------------------------------------------------
schema_name            | public
function_name          | myzeroifnull
procedure_type         | User Defined Function
function_return_type   | Integer
function_argument_type | x Integer
function_definition    | RETURN CASE WHEN (x IS NOT NULL) THEN x ELSE 0 END
volatility             | immutable
is_strict              | f
is_fenced              | f
comment                |

See also

• CREATE FUNCTION (SQL)
• ALTER FUNCTION (scalar)
• DROP FUNCTION

1.77 - USER_PROCEDURES

Provides information about stored procedures and external procedures. Users can only view procedures that they can execute.

Privileges

Non-superusers can only view information on a procedure if they have:

• USAGE privileges on the procedure's schema.

• Ownership or EXECUTE privileges on the procedure.

Examples

=> SELECT * FROM user_procedures;
   procedure_name   |  owner  | language | security |                               procedure_arguments                                 | schema_name
--------------------+---------+----------+----------+-----------------------------------------------------------------------------------+-------------
 accurate_auc       | dbadmin | PL/vSQL  | INVOKER  | relation varchar, observation_col varchar, probability_col varchar, epsilon float | public
 conditionalTable   | dbadmin | PL/vSQL  | INVOKER  | b boolean                                                                         | public
 update_salary      | dbadmin | PL/vSQL  | INVOKER  | x int, y varchar                                                                  | public
(3 rows)

1.78 - USER_TRANSFORMS

Lists the currently-defined user-defined transform functions (UDTFs).

Privileges

No explicit permissions are required; however, users see only UDTFs contained in schemas to which they have read access.

See also

• Transform functions (UDTFs)
• CREATE TRANSFORM FUNCTION

1.79 - USERS

Provides information about all users in the database.

See also

• GRANTS
• HAS_ROLE

1.80 - VIEW_COLUMNS

Provides view attribute information.

See also

1.81 - VIEW_TABLES

Shows details about view-related dependencies, including the table that reference a view, its schema, and owner.

1.82 - VIEWS

Provides information about all views within the system. See Views for more information.

See also

2 - V_MONITOR schema

The system tables in this section reside in the v_monitor schema. These tables provide information about the health of the Vertica database.

2.1 - ACTIVE_EVENTS

Returns all active events in the cluster. See Monitoring events.

2.2 - ALLOCATOR_USAGE

Provides real-time information on the allocation and reuse of memory pools for a Vertica node.

There are two memory pools in Vertica, global and SAL. The global memory pool is related to Vertica catalog objects. The SAL memory pool is related to the system storage layer. These memory pools are physical structures from which Vertica allocates and reuses portions of memory.

Within the memory pools, there are two allocation types. Both global and SAL memory pools include chunk and object memory allocation types.

• Chunk allocations are from tiered storage, and are grouped into sizes, in bytes, that are powers of 2.

• Object allocations are object types, for example, a table or projection. Each object assumes a set size.

The table provides detailed information on these memory pool allocations.

Sample: how memory pool memory is allotted, retained, and freed

The following table shows sample column values based upon a hypothetical example. The sample illustrates how column values change based upon addition or deletion of a table object.

• When you add a table object (t1), Vertica assumes a UNIT_SIZE of 8 bytes, with a USED_COUNT of 1.

• When you add a second table object (t2), the USED_COUNT increases to 2. Since each object assumes 8 bytes, USED_BYTES increases to 16.

• When you delete one of the two table objects, Vertica USED_COUNT decreases to 1, and USED_BYTES decreases to 8. Since Vertica retains the memory for future use, FREE_BYTES increases to 8, and FREE_COUNT increases to 1.

• Finally, when you create a new table object (t3), Vertica frees the memory for reuse. FREE_COUNT and FREE_BYTES return to 0.

Examples

The following example shows one sample record for a chunk allocation type, and one for an object type.

=> \x
Expanded display is on.


=> select * from allocator_usage;
-[ RECORD 1 ]---+-----------------------------
node_name       | v_vmart_node0004
pool_name       | global
allocation_type | chunk
unit_size       | 8
free_count      | 1069
free_bytes      | 8552
used_count      | 7327
used_bytes      | 58616
total_size      | 67168
capture_time    | 2017-05-24 13:28:07.83855-04
allocation_name | 2^3
.
.
.
-[ RECORD 105 ]-+------------------------------
node_name       | v_vmart_node0004
pool_name       | SAL
allocation_type | object
unit_size       | 128
free_count      | 0
free_bytes      | 0
used_count      | 2
used_bytes      | 256
total_size      | 256
capture_time    | 2017-05-24 14:44:30.153892-04
allocation_name | SAL::WOSAlloc
.
.
.

2.3 - COLUMN_STORAGE

Returns the amount of disk storage used by each column of each projection on each node.

2.4 - COMMUNAL_CLEANUP_RECORDS

Eon Mode only

This system table lists files that Vertica considers leaked on an Eon Mode communal storage. Leaked files are files that are detected as needing deletion but were missed by the normal cleanup mechanisms. This information helps you determine how much space on the communal storage you can reclaim or have reclaimed by cleaning up the leaked files.

Examples

=> SELECT clean_communal_storage('true');
                                                                     clean_communal_storage
----------------------------------------------------------------------------------------------------------------------------------------------
 CLEAN COMMUNAL STORAGE
Total leaked files: 10
Total size: 217088
Files have been queued for deletion.
Check communal_cleanup_records for more information.
(1 row)

=> SELECT * FROM communal_cleanup_records;
      detection_timestamp      | location_path     |                       file_name                       | size_in_bytes | queued_for_delete
-------------------------------+-------------------+-------------------------------------------------------+---------------+-------------------
 2018-05-01 17:01:34.045955-04 | s3://chumbucket/  | 020000000000000000000000000000000000000000000003_0.gt |         28672 | t
 2018-05-01 17:01:34.045966-04 | s3://chumbucket/  | 020000000000000000000000000000000000000000000006_0.gt |         28672 | t
 2018-05-01 17:01:34.045952-04 | s3://chumbucket/  | 020000000000000000000000000000000000000000000001_0.gt |         36864 | t
 2018-05-01 17:01:34.045974-04 | s3://chumbucket/  | 020000000000000000000000000000000000000000000008_0.gt |         36864 | t
 2018-05-01 17:01:34.045981-04 | s3://chumbucket/  | 020000000000000000000000000000000000000000000002_0.gt |         12288 | t
 2018-05-01 17:01:34.045986-04 | s3://chumbucket/  | 020000000000000000000000000000000000000000000007_0.gt |          8192 | t
 2018-05-01 17:01:34.045991-04 | s3://chumbucket/  | 020000000000000000000000000000000000000000000010_0.gt |         16384 | t
 2018-05-01 17:01:34.046001-04 | s3://chumbucket/  | 020000000000000000000000000000000000000000000005_0.gt |         24576 | t
 2018-05-01 17:01:34.046026-04 | s3://chumbucket/  | 020000000000000000000000000000000000000000000009_0.gt |          4096 | t
 2018-05-01 17:01:34.046033-04 | s3://chumbucket/  | 020000000000000000000000000000000000000000000004_0.gt |         20480 | t
(10 rows)

See also

2.5 - COMMUNAL_TRUNCATION_STATUS

Eon Mode only

Stores information on the state of the cluster in the case of a catalog truncation event.

Examples

=> SELECT * FROM COMMUNAL_TRUNCATION_STATUS;
current_catalog_version  | cluster_truncation_version
-------------------------+----------------------------
                    35   |                        35

2.6 - CONFIGURATION_CHANGES

Records the change history of system configuration parameters. This information is useful for identifying:

• Who changed the configuration parameter value

• When the configuration parameter was changed

• Whether nonstandard settings were in effect in the past

Privileges

Superuser

2.7 - CONFIGURATION_PARAMETERS

Provides information about all configuration parameters that are currently in use by the system.

Examples

The following example shows a case where the parameter requires a restart for the new setting to take effect:

=> SELECT * FROM CONFIGURATION_PARAMETERS WHERE parameter_name = 'RequireFIPS';
-[ RECORD 1 ]-----------------+----------------------
node_name                     | ALL
parameter_name                | RequireFIPS
current_value                 | 0
restart_value                 | 0
database_value                | 0
default_value                 | 0
current_level                 | DEFAULT
restart_level                 | DEFAULT
is_mismatch                   | f
groups                        |
allowed_levels                | DATABASE
superuser_visible_only        | f
change_under_support_guidance | f
change_requires_restart       | t
description                   | Execute in FIPS mode

The following example shows a case where a non-superuser is viewing a parameter where superuser_only is true.

=> \c VMart nonSuperuser
You are now connected to database "VMart" as user "nonSuperuser".
=> SELECT * FROM CONFIGURATION_PARAMETERS WHERE superuser_visible_only = 't';
-[ RECORD 1 ]-----------------+-------------------------------------------------------
node_name                     | ALL
parameter_name                | S3BucketCredentials
current_value                 | ********
restart_value                 | ********
database_value                | ********
default_value                 | ********
current_level                 | DEFAULT
restart_level                 | DEFAULT
is_mismatch                   | f
groups                        |
allowed_levels                | SESSION, DATABASE
superuser_visible_only        | t
change_under_support_guidance | f
change_requires_restart       | f
description                   | JSON list mapping S3 buckets to specific credentials.

See also

2.8 - CPU_USAGE

Records CPU usage history on the system.

Privileges

Superuser

2.9 - CRITICAL_HOSTS

Lists the critical hosts whose failure would cause the database to become unsafe and force a shutdown.

Privileges

None

2.10 - CRITICAL_NODES

Lists the critical nodes whose failure would cause the database to become unsafe and force a shutdown.

2.11 - CRITICAL_SUBCLUSTERS

Lists the primary subclusters whose loss would cause the database to become unsafe and force it to shutdown. Vertica checks this table before stopping a subcluster to ensure it will not trigger a database shutdown. If you attempt to stop or remove a subcluster in this table, Vertica returns an error message. See Starting and stopping subclusters for more information.

This table only has contents when the database is in Eon Mode and when one or more subclusters are critical.

Examples

=> SELECT * FROM critical_subclusters;
  subcluster_oid   |  subcluster_name
-------------------+--------------------
 45035996273704996 | default_subcluster
(1 row)

See also

• SUBCLUSTERS
• ALTER SUBCLUSTER
• DEMOTE_SUBCLUSTER_TO_SECONDARY
• PROMOTE_SUBCLUSTER_TO_PRIMARY
• SHUTDOWN_SUBCLUSTER

2.12 - CURRENT_SESSION

Returns information about the current active session. Use this table to find out the current session's sessionID and get the duration of the previously-run query.

Profiling settings

The following columns show settings for different profiling categories:

• EXECUTION_ENGINE_PROFILING_CONFIGURATION

• QUERY_PROFILING_CONFIGURATION

• SESSION_PROFILING_CONFIGURATION

These are set to one of the following:

For information about controlling profiling settings, see Enabling profiling.

2.13 - DATA_COLLECTOR

Shows settings for all Data collector components: their current retention policy properties and other data collection statistics.

Data Collector is on by default. To turn it off, set configuration parameter EnableDataCollector to 0.

Examples

Get the current status of resource pools:

=> SELECT * FROM data_collector WHERE component = 'ResourcePoolStatus' ORDER BY node_name;
-[ RECORD 1 ]----------+---------------------------------
node_name              | v_vmart_node0001
component              | ResourcePoolStatus
table_name             | dc_resource_pool_status
description            | Resource Pool status information
access_restricted      | t
memory_buffer_size_kb  | 64
disk_size_kb           | 25600
interval_set           | f
interval_time          | 0
record_too_big_errors  | 0
lost_buffers           | 0
lost_records           | 0
retired_files          | 385
retired_records        | 3492335
current_memory_records | 0
current_disk_records   | 30365
current_memory_bytes   | 0
current_disk_bytes     | 21936993
first_time             | 2020-08-14 11:03:28.007894-04
last_time              | 2020-08-14 11:59:41.005675-04
kb_per_day             | 548726.098227313
-[ RECORD 2 ]----------+---------------------------------
node_name              | v_vmart_node0002
component              | ResourcePoolStatus
table_name             | dc_resource_pool_status
description            | Resource Pool status information
access_restricted      | t
memory_buffer_size_kb  | 64
disk_size_kb           | 25600
interval_set           | f
interval_time          | 0
record_too_big_errors  | 0
lost_buffers           | 0
lost_records           | 0
retired_files          | 385
retired_records        | 3492335
current_memory_records | 0
current_disk_records   | 28346
current_memory_bytes   | 0
current_disk_bytes     | 20478345
first_time             | 2020-08-14 11:07:12.006484-04
last_time              | 2020-08-14 11:59:41.004825-04
kb_per_day             | 548675.811828872
-[ RECORD 3 ]----------+---------------------------------
node_name              | v_vmart_node0003
component              | ResourcePoolStatus
table_name             | dc_resource_pool_status
description            | Resource Pool status information
access_restricted      | t
memory_buffer_size_kb  | 64
disk_size_kb           | 25600
interval_set           | f
interval_time          | 0
record_too_big_errors  | 0
lost_buffers           | 0
lost_records           | 0
retired_files          | 385
retired_records        | 3492335
current_memory_records | 0
current_disk_records   | 28337
current_memory_bytes   | 0
current_disk_bytes     | 20471843
first_time             | 2020-08-14 11:07:13.008246-04
last_time              | 2020-08-14 11:59:41.006729-04
kb_per_day             | 548675.63541403

See also

• Configuring data retention policies
• Data collector utility

2.14 - DATA_READS

Eon Mode only

Lists each storage location that a query reads in Eon Mode. If the query fetches data from multiple locations, this table provides a row for each location per node that read data. For example, a query might run on three nodes and fetch data from the depot and communal storage. In this case, the table displays six rows for the query: three rows for each node's depot read, and three for each node's communal storage read.

Examples

=> SELECT * FROM V_MONITOR.DATA_READS WHERE TRANSACTION_ID = 45035996273707457;
-[ RECORD 1 ]--+---------------------------------------------------
start_time     | 2019-02-13 19:43:43.840836+00
node_name      | v_verticadb_node0001
session_id     | v_verticadb_node0001-11828:0x6f3
user_id        | 45035996273704962
user_name      | dbadmin
transaction_id | 45035996273707457
statement_id   | 1
request_id     | 230
location_id    | 45035996273835000
location_path  | /vertica/data/verticadb/v_verticadb_node0001_depot
bytes_read     | 329460142
-[ RECORD 2 ]--+---------------------------------------------------
start_time     | 2019-02-13 19:43:43.8421+00
node_name      | v_verticadb_node0002
session_id     | v_verticadb_node0001-11828:0x6f3
user_id        | 45035996273704962
user_name      | dbadmin
transaction_id | 45035996273707457
statement_id   | 1
request_id     | 0
location_id    | 45035996273835002
location_path  | /vertica/data/verticadb/v_verticadb_node0002_depot
bytes_read     | 329473033
-[ RECORD 3 ]--+---------------------------------------------------
start_time     | 2019-02-13 19:43:43.841845+00
node_name      | v_verticadb_node0003
session_id     | v_verticadb_node0001-11828:0x6f3
user_id        | 45035996273704962
user_name      | dbadmin
transaction_id | 45035996273707457
statement_id   | 1
request_id     | 0
location_id    | 45035996273835004
location_path  | /vertica/data/verticadb/v_verticadb_node0003_depot
bytes_read     | 329677294

2.15 - DATABASE_BACKUPS

Lists historical information for each backup that successfully completed after running the vbr utility. This information is useful for determining whether to create a new backup before you advance the AHM. Because this system table displays historical information, its contents do not always reflect the current state of a backup repository. For example, if you delete a backup from a repository, the DATABASE_BACKUPS system table continues to display information about it.

To list existing backups, run vbr as described in Viewing backups.

Privileges

Superuser

2.16 - DATABASE_CONNECTIONS

Lists the connections to other databases for importing and exporting data. See Moving Data Between Vertica Databases.

Examples

=> CONNECT TO VERTICA vmart USER dbadmin PASSWORD '' ON '10.10.20.150',5433;
CONNECT
=> SELECT * FROM DATABASE_CONNECTIONS;
 database | username |     host     | port | isvalid
----------+----------+--------------+------+---------
 vmart    | dbadmin  | 10.10.20.150 | 5433 | t
(1 row)

2.17 - DATABASE_MIGRATION_STATUS

Provides real-time and historical data on Enterprise-to-Eon database migration attempts.