This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

V_MONITOR schema

The system tables in this section reside in the v_monitor schema.

1: ACTIVE_EVENTS
2: ALLOCATOR_USAGE
3: ARCHIVE_RESTORE_POINTS
4: ARCHIVES
5: COLUMN_STORAGE
6: COMMUNAL_CLEANUP_RECORDS
7: COMMUNAL_TRUNCATION_STATUS
8: CONFIGURATION_CHANGES
9: CONFIGURATION_PARAMETERS
10: CPU_USAGE
11: CRITICAL_HOSTS
12: CRITICAL_NODES
13: CRITICAL_SUBCLUSTERS
14: CURRENT_SESSION
15: DATA_COLLECTOR
16: DATA_LOADER_EVENTS
17: DATA_LOADERS
18: DATA_READS
19: DATABASE_BACKUPS
20: DATABASE_CONNECTIONS
21: DATABASE_MIGRATION_STATUS
22: DELETE_VECTORS
23: DEPLOY_STATUS
24: DEPLOYMENT_PROJECTION_STATEMENTS
25: DEPLOYMENT_PROJECTIONS
26: DEPOT_EVICTIONS
27: DEPOT_FETCH_QUEUE
28: DEPOT_FETCHES
29: DEPOT_FILES
30: DEPOT_PIN_POLICIES
31: DEPOT_SIZES
32: DEPOT_UPLOADS
33: DESIGN_QUERIES
34: DESIGN_STATUS
35: DESIGN_TABLES
36: DESIGNS
37: DIRECTED_QUERY_STATUS
38: DISK_QUOTA_USAGES
39: DISK_RESOURCE_REJECTIONS
40: DISK_STORAGE
41: DRAINING_STATUS
42: ERROR_MESSAGES
43: EVENT_CONFIGURATIONS
44: EXECUTION_ENGINE_PROFILES
45: EXTERNAL_TABLE_DETAILS
46: HEALTH_WATCHDOG_BLOCKED_TRANSACTIONS
47: HIVE_CUSTOM_PARTITIONS_ACCESSED
48: HOST_RESOURCES
49: IO_USAGE
50: LDAP_LINK_DRYRUN_EVENTS
51: LDAP_LINK_EVENTS
52: LOAD_SOURCES
53: LOAD_STREAMS
54: LOCK_USAGE
55: LOCKS
56: LOG_ROTATE_EVENTS
57: LOGIN_FAILURES
58: MEMORY_EVENTS
59: MEMORY_USAGE
60: MERGEOUT_PROFILES
61: MODEL_STATUS_HISTORY
62: MONITORING_EVENTS
63: NETWORK_INTERFACES
64: NETWORK_USAGE
65: NODE_EVICTIONS
66: NODE_RESOURCES
67: NODE_STATES
68: NOTIFIER_ERRORS
69: OUTPUT_DEPLOYMENT_STATUS
70: OUTPUT_EVENT_HISTORY
71: PARTITION_COLUMNS
72: PARTITION_REORGANIZE_ERRORS
73: PARTITION_STATUS
74: PARTITIONS
75: PROCESS_SIGNALS
76: PROJECTION_RECOVERIES
77: PROJECTION_REFRESHES
78: PROJECTION_STORAGE
79: PROJECTION_USAGE
80: QUERY_CONSUMPTION
81: QUERY_EVENTS
82: QUERY_METRICS
83: QUERY_PLAN_PROFILES
84: QUERY_PROFILES
85: QUERY_REQUESTS
86: REBALANCE_OPERATIONS
87: REBALANCE_PROJECTION_STATUS
88: REBALANCE_TABLE_STATUS
89: RECOVERY_STATUS
90: REMOTE_REPLICATION_STATUS
91: REPARENTED_ON_DROP
92: REPLICATION_STATUS
93: RESHARDING_EVENTS
94: RESOURCE_ACQUISITIONS
95: RESOURCE_POOL_MOVE
96: RESOURCE_POOL_STATUS
97: RESOURCE_QUEUES
98: RESOURCE_REJECTION_DETAILS
99: RESOURCE_REJECTIONS
100: RESOURCE_USAGE
101: SESSION_MARS_STORE
102: SESSION_PARAMETERS
103: SESSION_PROFILES
104: SESSIONS
105: SPREAD_STATE
106: SQS_LISTENER_ERROR_HISTORY
107: STATEMENT_OUTCOMES
108: STORAGE_BUNDLE_INFO_STATISTICS
109: STORAGE_BUNDLES
110: STORAGE_CONTAINERS
111: STORAGE_POLICIES
112: STORAGE_TIERS
113: STORAGE_USAGE
114: STRATA
115: STRATA_STRUCTURES
116: SYSTEM
117: SYSTEM_RESOURCE_USAGE
118: SYSTEM_SERVICES
119: SYSTEM_SESSIONS
120: TABLE_RECOVERIES
121: TABLE_RECOVERY_STATUS
122: TABLE_STATISTICS
123: TLS_CONFIGURATIONS
124: TRANSACTIONS
125: TRUNCATED_SCHEMATA
126: TUNING_RECOMMENDATIONS
127: TUPLE_MOVER_OPERATIONS
128: UDFS_EVENTS
129: UDFS_OPS_PER_HOUR
130: UDFS_OPS_PER_MINUTE
131: UDFS_STATISTICS
132: UDX_EVENTS
133: UDX_FENCED_PROCESSES
134: USER_LIBRARIES
135: USER_LIBRARY_MANIFEST
136: USER_SESSIONS

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

1 - ACTIVE_EVENTS

Returns all active events in the cluster.

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

Column Name	Data Type	Description
`NODE_NAME`	VARCHAR	The node name where the event occurred.
`EVENT_CODE`	INTEGER	A numeric ID that indicates the type of event. See Event Types for a list of event type codes.
`EVENT_ID`	INTEGER	A unique numeric ID assigned by the Vertica catalog, which identifies the specific event.
`EVENT_SEVERITY`	VARCHAR	The severity of the event from highest to lowest. These events are based on standard syslog severity types. 0—Emergency 1—Alert 2—Critical 3—Error 4—Warning 5—Notice 6—Informational 7—Debug
`EVENT_POSTED_TIMESTAMP`	TIMESTAMP	The year, month, day, and time the event was reported. The time is posted in military time.
`EVENT_EXPIRATION`	VARCHAR	The year, month, day, and time the event expire. The time is posted in military time. If the cause of the event is still active, the event is posted again.
`EVENT_CODE_DESCRIPTION`	VARCHAR	A brief description of the event and details pertinent to the specific situation.
`EVENT_PROBLEM_DESCRIPTION`	VARCHAR	A generic description of the event.
`REPORTING_NODE`	VARCHAR	The name of the node within the cluster that reported the event.
`EVENT_SENT_TO_CHANNELS`	VARCHAR	The event logging mechanisms that are configured for Vertica. These can include `vertica.log`, (configured by default) syslog, and SNMP.
`EVENT_POSTED_COUNT`	INTEGER	Tracks the number of times an event occurs. Rather than posting the same event multiple times, Vertica posts the event once and then counts the number of additional instances in which the event occurs.

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.

Column Name	Data Type	Description
`NODE_NAME`	VARCHAR	The name of the node from which Vertica has collected this allocator information.
`POOL_NAME`	VARCHAR	One of two memory pools: global: Memory pool is related to Vertica catalog objects. SAL: Memory pool is related to the system storage layer.
`ALLOCATION_TYPE`	VARCHAR	One of two memory allocation types: chunk: Chunk allocations are grouped into sizes that are powers of 2. object: Object allocations assume a set amount of memory based upon the specific object.
`UNIT_SIZE`	INTEGER	The size, in bytes, of the memory allocation. For example, if the allocation type is a table (an object type), then Vertica allots 8 bytes.
`FREE_COUNT`	INTEGER	Indicates the count of blocks of freed memory that Vertica has reserved for future memory needs. For example, if you delete a table, Vertica reserves the 8 bytes originally allotted for the table. The 8 bytes freed become 1 unit of memory that Vertica adds to this column.
`FREE_BYTES`	INTEGER	Indicates the number of freed memory bytes. For example, with a table deletion, Vertica adds 8 bytes to this column. Note Vertica does not release memory after originally allocating it, unless the node or database is restarted.
`USED_COUNT`	INTEGER	Indicates the count of in-use blocks for this allocation. For example, if your database includes two table objects, Vertica adds 2 to this column.
`USED_BYTES`	INTEGER	The number of bytes of in-use blocks of memory. For example, if your database includes two table objects, each of which assume 8 bytes, Vertica adds 16 to this column.
`TOTAL_SIZE`	INTEGER	Indicates the number of bytes that is the sum of all free and used memory.
`CAPTURE_TIME`	TIMESTAMPTZ	Indicates the current timestamp for when Vertica collected the for this table.
`ALLOCATION_NAME`	VARCHAR	Provides the name of the allocation type. If the allocation is an object type, provides the name of the object. For example, `CAT::Schema`. Object types can also have the name `internal`, meaning that the object is an internal data structure. Those object types that are not internal are prefaced with either `CAT` or `SAL`. Those prefaced with `CAT` indicate memory from the global memory pool. `SAL` indicates memory from the system storage memory pool. If the allocation type is chunk, indicates a power of 2 in this field to represent the number of bytes assumed by the chunk. For example, `2^5`.

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.

Column Names	Add One Table Object (t1)	Add a Second Table Object (t2)	Delete a Table Object (t2)	Create a New Table Object (t3)
`NODE_NAME`	`v_vmart_node0001`	`v_vmart_node0001`	`v_vmart_node0001`	`v_vmart_node0001`
`POOL_NAME`	`global`	`global`	`global`	`global`
`ALLOCATION_TYPE`	`object`	`object`	`object`	`object`
`UNIT_SIZE`	8	8	8	8
`FREE_COUNT`	0	0	1	0
`FREE_BYTES`	0	0	8	0
`USED_COUNT`	1	2	1	2
`USED_BYTES`	8	16	8	16
`TOTAL_SIZE`	8	16	16	16
`CAPTURE_TIME`	`2017-05-24 13:28:07.83855-04`	`2017-05-24 14:16:04.480953-04`	`2017-05-24 14:16:32.077322-04`	`2017-05-24 14:17:07.320745-04`
`ALLOCATION_NAME`	`CAT::Table`	`CAT::Table`	`CAT::Table`	`CAT::Table`

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

3 - ARCHIVE_RESTORE_POINTS

Eon Mode only

Lists information about In-DB restore points.

Column Name	Column Type	Description
`ID`	INT	Unique object ID of the restore point.
`SAVE_TIME`	DATETIME	Time at which the restore point was saved.
`INDEX`	INT	Index of the restore point in the archive, where 1 specifies the most recent restore point.
`ARCHIVE`	VARCHAR	Name of the archive that contains the restore point.
`STATE`	VARCHAR	Status of the restore points, one of the following: `SAVING` `COMPLETE` `REMOVING`

Examples

List details for all restore points in your database:

=> SELECT * FROM ARCHIVE_RESTORE_POINTS;
                  id                  |         save_time          | index |     archive     |  state 
--------------------------------------+----------------------------+-------+-----------------+----------
 2616f6cd-e98c-472c-8d5c-a7f459dff82b | 2023-11-30 10:19:58.565311 |     1 | vertica-restore | COMPLETE
 116066cc-29cb-4ce5-9a27-170538ba95cf | 2023-11-10 11:02:48.059941 |     2 | vertica-restore | COMPLETE
(2 rows)

4 - ARCHIVES

Eon Mode only

Lists information about In-DB restore points archives.

Column Name	Column Type	Description
`NAME`	VARCHAR	Archive name.
`LIMIT_COUNT`	INT	Maximum number of restore points stored in the archive.
`OWNER`	VARCHAR	Owner of the archive.
`OBJECT_INCLUSION_RULES`	VARCHAR	Currently, only database-level restore points are supported, so this column is always empty.
`DATA_INCLUSION RULES`	VARCHAR	Currently, only database-level restore points are supported, so this column is always empty.

Examples

List all archives and their attributes:

=> select * from archives;
 name | limit_count | owner | object_inclusion_rules | data_inclusion_rules
------+-------------+-------+------------------------+----------------------
 eon  | 10          | luis  |                        |
(1 row)

5 - COLUMN_STORAGE

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

Column Name	Data Type	Description
NODE_NAME	VARCHAR	The node name for which information is listed.
COLUMN_ID	INTEGER	Catalog-assigned integer value that uniquely identifies thecolumn.
COLUMN_NAME	VARCHAR	The column name for which information is listed.
ROW_COUNT	INTEGER	The number of rows in the column.
USED_BYTES	INTEGER	The disk storage allocation of the column in bytes.
ENCODINGS	VARCHAR	The encoding type for the column.
COMPRESSION	VARCHAR	The compression type for the column. You can compare `ENCODINGS` and `COMPRESSION` columns to see how different encoding types affect column storage when optimizing for compression.
ROS_COUNT	INTEGER	The number of ROS containers.
PROJECTION_ID	INTEGER	Catalog-assigned numeric value that uniquely identifies the projection.
PROJECTION_NAME	VARCHAR	The associated projection name for the column.
PROJECTION_SCHEMA	VARCHAR	The name of the schema associated with the projection.
ANCHOR_TABLE_ID	INTEGER	Catalog-assigned integer value that uniquely identifies theanchor table.
ANCHOR_TABLE_NAME	VARCHAR	The associated table name.
ANCHOR_TABLE_SCHEMA	VARCHAR	The associated table's schema name. If the schema belongs to a non-default namespace in an Eon Mode database, the schema name is front-qualified with the name of the schema's namespace. For example, `n1.s` refers to schema `s` in namespace `n1`.
ANCHOR_TABLE_NAMESPACE	VARCHAR	For Eon Mode databases, name of the namespace that contains the table.
ANCHOR_TABLE_COLUMN_ID	VARCHAR	Catalog-assigned VARCHAR value that uniquely identifies a table column.
ANCHOR_TABLE_COLUMN_NAME	VARCHAR	The name of the anchor table.

6 - COMMUNAL_CLEANUP_RECORDS

This system table lists files that Vertica considers leaked on an Eon Mode communal storage.

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.

Column Name	Data Type	Description
detection_timestamp	TIMESTAMPTZ	Timestamp at which the file was detected as leaked.
location_path	VARCHAR	The path of communal storage location.
file_name	VARCHAR	The name of the leaked file.
size_in_bytes	INTEGER	The size of the leaked file in bytes.
queued_for_delete	BOOLEAN	Specifies whether the file was queued for deletion. Files queued for deletion might not be deleted right away. Also, a subsequent call to clean_communal_storage reports these files as leaked if the files hadn't already been deleted.

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)

7 - COMMUNAL_TRUNCATION_STATUS

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

Eon Mode only

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

Column Name	Data Type	Description
`CURRENT_CATALOG_VERSION`	`VARCHAR`	Current value of the catalog truncation version (CTV).
`CLUSTER_TRUNCATION_VERSION`	`VARCHAR`	The value of the CTV from the `cluster_config.json` file.

Examples

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

8 - CONFIGURATION_CHANGES

Records the change history of system configuration parameters.

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

Column Name	Data Type	Description
`EVENT_TIMESTAMP`	TIMESTAMPTZ	Time when the row was recorded.
`NODE_NAME`	VARCHAR	Name of the node that is reporting the requested information.
`USER_ID`	INTEGER	Identifier of the user who changed configuration parameters.
`USER_NAME`	VARCHAR	Name of the user who changed configuration parameters at the time Vertica recorded the session.
`SESSION_ID`	VARCHAR	Identifier for this session. This identifier is unique within the cluster at any point in time but can be reused when the session closes.
`PARAMETER`	VARCHAR	Name of the changed parameter. See Configuration parameter management for a detailed list of supported parameters.
`VALUE`	VARCHAR	New value of the configuration parameter.

Privileges

Superuser

9 - CONFIGURATION_PARAMETERS

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

Column Name	Data Type	Description
NODE_NAME	VARCHAR	Node names of the database cluster. ALL indicates that all nodes have the same value.
PARAMETER_NAME	VARCHAR	The parameter name.
CURRENT_VALUE	VARCHAR	The parameter's current setting.
RESTART_VALUE	VARCHAR	The parameter's value after the next restart.
DATABASE_VALUE	VARCHAR	The value that is set at the database level. If no database-level value is set, the value reflects the default value.
DEFAULT_VALUE	VARCHAR	The parameter's default value.
CURRENT_LEVEL	VARCHAR	Level at which CURRENT_VALUE is set, one of the following: NODE DATABASE SESSION DEFAULT
RESTART_LEVEL	VARCHAR	Level at which the parameter will be set after the next restart, one of the following: NODE DATABASE DEFAULT
IS_MISMATCH	BOOLEAN	Whether CURRENT_VALUE and RESTART_VALUE match.
GROUPS	VARCHAR	A group to which the parameter belongs—for example, OptVOptions. Note Most configuration parameters do not belong to any group.
ALLOWED_LEVELS	VARCHAR	Levels at which the specified parameter can be set, a comma-delimited list of any of the following values: DATABASE NODE SESSION USER
SUPERUSER_VISIBLE_ONLY	BOOLEAN	Whether non-superusers can view all parameter settings. If true, the following columns are masked to non-superusers: CURRENT_VALUE RESTART_VALUE DATABASE_VALUE DEFAULT_VALUE
CHANGE_UNDER_SUPPORT_GUIDANCE	BOOLEAN	Whether the parameter is intended for use only under guidance from Vertica technical support.
CHANGE_REQUIRES_RESTART	BOOLEAN	Whether the configuration change requires a restart.
DESCRIPTION	VARCHAR	Describes the parameter's usage.

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 with restricted visibility:

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

10 - CPU_USAGE

Records CPU usage history on the system.

Column Name	Data Type	Description
`NODE_NAME`	VARCHAR	Name of the node that is reporting the requested information.
`START_TIME`	TIMESTAMP	Beginning of history interval.
`END_TIME`	TIMESTAMP	End of history interval.
`AVERAGE_CPU_USAGE_PERCENT`	FLOAT	Average CPU usage in percent of total CPU time (0-100) during history interval.
`PHYSICAL_CORES`	INTEGER	Number of physical cores used by the node.

Privileges

Superuser

11 - CRITICAL_HOSTS

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

Column Name	Data Type	Description
`HOST_NAME`	VARCHAR	Name of a critical host

Privileges

None

12 - CRITICAL_NODES

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

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

Column Name	Data Type	Description
`NODE_ID`	INTEGER	Catalog-assigned integer value that uniquely identifies thenode.
`NODE_NAME`	VARCHAR	Name of a critical node.

13 - CRITICAL_SUBCLUSTERS

Lists the primary subclusters whose loss would cause the database to become unsafe and force it to shutdown.

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.

Column Name	Data Type	Description
SUBCLUSTER_OID	INTEGER	Unique identifier for the subcluster.
SUBCLUSTER_NAME	VARCHAR	The name of the subcluster in a critical state.

Examples

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

14 - CURRENT_SESSION

Returns information about the current active 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.

Column Name	Data Type	Description
`NODE_NAME`	`VARCHAR`	Name of the node for which information is listed
`USER_NAME`	`VARCHAR`	Name used to log into the database, NULL if the session is internal
`CLIENT_OS_HOSTNAME`	`VARCHAR`	The hostname of the client as reported by their operating system.
`CLIENT_HOSTNAME`	`VARCHAR`	The IP address and port of the TCP socket from which the client connection was made; NULL if the session is internal. Vertica accepts either IPv4 or IPv6 connections from a client machine. If the client machine contains mappings for both IPv4 and IPv6, the server randomly chooses one IP address family to make a connection. This can cause the CLIENT_HOSTNAME column to display either IPv4 or IPv6 values, based on which address family the server chooses.
`TYPE`	`INTEGER`	Identifies the session type, one of the following integer values: 1: Client 2: DBD 3: Merge out 4: Move out 5: Rebalance cluster 6: Recovery 7: Refresh 8: Shutdown 9: License audit 10: Timer service 11: Connection 12: VSpread 13: Sub-session 14: Repartition table
`CLIENT_PID`	`INTEGER`	Process identifier of the client process that issued this connection. This process might be on a different machine than the server.
`LOGIN_TIMESTAMP`	`TIMESTAMP`	When the user logged into the database or the internal session was created. This column can help identify open sessions that are idle.
`SESSION_ID`	`VARCHAR`	Identifier required to close or interrupt a session. This identifier is unique within the cluster at any point in time, but can be reused when the session closes.
`CLIENT_LABEL`	`VARCHAR`	User-specified label for the client connection that can be set when using ODBC. See `Label` in ODBC DSN connection properties.
`TRANSACTION_START`	`TIMESTAMP`	When the current transaction started, NULL if no transaction is running
`TRANSACTION_ID`	`VARCHAR`	Hexadecimal identifier of the current transaction, NULL if no transaction is in progress
`TRANSACTION_DESCRIPTION`	`VARCHAR`	Description of the current transaction
`STATEMENT_START`	`TIMESTAMP`	When the current statement started execution, NULL if no statement is running
`STATEMENT_ID`	`VARCHAR`	Unique numeric ID for the currently-running statement, NULL if no statement is being processed. Combined, `TRANSACTION_ID` and `STATEMENT_ID` uniquely identify a statement within a session.
`LAST_STATEMENT_DURATION_US`	`INTEGER`	Duration in microseconds of the last completed statement
`CURRENT_STATEMENT`	`VARCHAR`	The currently-running statement, if any. NULL indicates that no statement is currently being processed.
`LAST_STATEMENT`	`VARCHAR`	NULL if the user has just logged in, otherwise the currently running statement or most recently completed statement.
`EXECUTION_ENGINE_PROFILING_CONFIGURATION`	`VARCHAR`	See Profiling Settings below.
`QUERY_PROFILING_CONFIGURATION`	`VARCHAR`	See Profiling Settings below.
`SESSION_PROFILING_CONFIGURATION`	`VARCHAR`	See Profiling Settings below.
`CLIENT_TYPE`	`VARCHAR`	Type of client from which the connection was made, one of the following: ADO.NET Driver ODBC Driver JDBC Driver vsql
`CLIENT_VERSION`	`VARCHAR`	Client version
`CLIENT_OS`	`VARCHAR`	Client operating system
`CLIENT_OS_USER_NAME`	`VARCHAR`	Identifies the user that logged into the database, also set for unsuccessful login attempts.
`REQUESTED_PROTOCOL`	`VARCHAR`	Communication protocol version that the ODBC client driver sends to Vertica server, used to support backward compatibility with earlier server versions.
`EFFECTIVE_PROTOCOL`	`VARCHAR`	Minimum protocol version supported by client and driver.

Profiling settings

The following columns show settings for different profiling categories:

EXECUTION_ENGINE_PROFILING_CONFIGURATION
QUERY_PROFILING_CONFIGURATION
SESSION_PROFILING_CONFIGURATION

These can have the following values:

Empty: No profiling is set
Session: On for current session.
Global: On by default for all sessions.
>Session, Global: On by default for all sessions, including current session.

For information about controlling profiling settings, see Enabling profiling.

15 - DATA_COLLECTOR

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

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.

Column Name	Data Type	Description
NODE_NAME	VARCHAR	Node name on which data is stored.
COMPONENT	VARCHAR	Name of the component.
TABLE_NAME	VARCHAR	The data collector table name for which information is listed.
DESCRIPTION	VARCHAR	Short description about the component.
ACCESS_RESTRICTED	BOOLEAN	Indicates whether access to the table is restricted to the DBADMIN, PSEUDOSUPERUSER, or SYSMONITOR roles.
MEMORY_BUFFER_SIZE_KB	INTEGER	Specifies in kilobytes the maximum amount of data that is buffered in memory before moving it to disk. You can modify this value with SET_DATA_COLLECTOR_POLICY.
DISK_SIZE_KB	INTEGER	Specifies in kilobytes the maximum disk space allocated for this component's Data Collector table. If set to 0, the Data Collector retains only as much component data as it can buffer in memory, as specified by MEMORY_BUFFER_SIZE_KB. You can modify this value with SET_DATA_COLLECTOR_POLICY.
INTERVAL_SET	BOOLEAN	Boolean, specifies whether time-based retention is enabled (INTERVAL_TIME is ≥ 0).
INTERVAL_TIME	INTERVAL	INTERVAL data type that specifies how long data of a given component is retained in that component's Data Collector table. You can modify this value with SET_DATA_COLLECTOR_POLICY or SET_DATA_COLLECTOR_TIME_POLICY. For example, if you specify component TupleMoverEvents and set interval-time to an interval of two days (`'2 days'::interval`), the Data Collector table `dc_tuple_mover_events` retains records of Tuple Mover activity over the last 48 hours. Older Tuple Mover data are automatically dropped from this table. Note Setting a component's policy's interval_time property has no effect on how much data storage the Data Collector retains on disk for that component. Maximum disk storage capacity is determined by the disk_size_kb property. Setting the interval_time property only affects how long data is retained by the component's Data Collector table. For details, see Configuring data retention policies.
RECORD_TOO_BIG_ERRORS	INTEGER	Integer that increments by one each time an error is thrown because data did not fit in memory (based on the data collector retention policy).
LOST_BUFFERS	INTEGER	Number of buffers lost.
LOST_RECORDS	INTEGER	Number of records lost.
RETIRED_FILES	INTEGER	Number of retired files.
RETIRED_RECORDS	INTEGER	Number of retired records.
CURRENT_MEMORY_RECORDS	INTEGER	The current number of rows in memory.
CURRENT_DISK_RECORDS	INTEGER	The current number of rows stored on disk.
CURRENT_MEMORY_BYTES	INTEGER	Total current memory used in kilobytes.
CURRENT_DISK_BYTES	INTEGER	Total current disk space used in kilobytes.
FIRST_TIME	TIMESTAMP	Timestamp of the first record.
LAST_TIME	TIMESTAMP	Timestamp of the last record
KB_PER_DAY	FLOAT	Total kilobytes used per day.

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

16 - DATA_LOADER_EVENTS

Lists events from all data loaders.

Lists load events from all data loaders, including the file path, whether the load succeeded, and how many times it has been retried. You must own or have any privilege (EXECUTE, ALTER, or DROP) on a data loader to see its events in this table.

To prevent unbounded growth, records in this table are purged periodically. You can set the purge frequency for individual data loaders when creating or altering the loader. See CREATE DATA LOADER and ALTER DATA LOADER. The default is 14 days.

For SQS errors that precede data load, such as authorization errors or malformed SQS events, see SQS_LISTENER_ERROR_HISTORY.

Column Name	Column Type	Description
`DATA_LOADER_NAMESPACE`	VARCHAR	For Eon Mode databases, namespace of the data loader that performed this load.
`DATA_LOADER_SCHEMA`	VARCHAR	Schema for the data loader that performed this load. If the schema belongs to a non-default namespace in an Eon Mode database, the schema name is front-qualified with the name of the schema's namespace. For example, `n1.s` refers to schema `s` in namespace `n1`.
`DATA_LOADER_NAME`	VARCHAR	Name of the data loader that performed this load.
`TIME_STAMP`	TIMESTAMPTZ	Time when Vertica recorded the event.
`FILE_NAME`	VARCHAR	Path that was loaded (or attempted).
`TRANSACTION_ID`	VARCHAR	Unique identifier for the transaction in which the load occurred.
`LOAD_STATUS`	VARCHAR	`OK` if the load succeeded or `FAIL` if it did not.
`ROWS_LOADED`	INTEGER	If a single file was loaded, the number of rows. If more than one file was loaded in a single batch, this value is null. For batch loads, `FILE_SIZE_BYTES` can be helpful in place of row counts.
`FAILURE_REASON`	VARCHAR	If the load failed, an explanatory message.
`RETRY_ATTEMPT`	INTEGER	0 if this was the first attempt to load the file, otherwise the number of retries including this one.
`FILE_SIZE_BYTES`	INTEGER	File size in bytes when the data loader was executed.
`BATCH_ID`	INTEGER	Identifier, unique within the transaction, for the batch this file was part of. If a file was loaded individually, this value is null.

17 - DATA_LOADERS

Lists all defined data loaders.

Column Name	Column Type	Description
`NAME`	VARCHAR	Loader name.
`NAMESPACENAME`	VARCHAR	For Eon Mode databases, namespace in which the loader is defined.
`SCHEMANAME`	VARCHAR	Schema in which the loader is defined. If the schema belongs to a non-default namespace in an Eon Mode database, the schema name is front-qualified with the name of the schema's namespace. For example, `n1.s` refers to schema `s` in namespace `n1`.
`OWNER`	VARCHAR	Vertica user who created the loader.
`COPYSTMT`	VARCHAR	COPY statement used by this loader.
`RETRYLIMIT`	INT	Number of times to retry a failed file load. For loaders that do not specify an explicit value, this value is updated if the value of the DataLoaderDefaultRetryLimit configuration parameter changes.
`HISTORYRETENTIONINTERVAL`	INTERVAL	How long to wait before purging rows in the loader's monitoring table. See CREATE DATA LOADER.
`ENABLED`	BOOLEAN	Whether the data loader is enabled. EXECUTE DATA LOADER does not run a disabled data loader.
`TRIGGER`	VARCHAR	External trigger for this data loader, if any.

Examples

=> SELECT * FROM DATA_LOADERS;
 name | schemaname |  owner  |             copystmt              | retrylimit | historyretentioninterval | enabled | trigger
------+------------+---------+-----------------------------------+------------+---------------------------|--------+---------
 dl   | public     | dbadmin | copy sales from 's3://data/*.dat' |          3 | 14 days                   |      t |
(1 row)

18 - DATA_READS

Lists each storage location that a query reads in Eon Mode.

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.

Note

This table is only populated in Eon Mode.

Column Name	Column Type	Description
`START_TIME`	TIMESTAMP	When Vertica started reading data from the location.
`NODE_NAME`	VARCHAR	Name of the node that fetched the data
`SESSION_ID`	VARCHAR	Unique numeric ID assigned by the Vertica catalog, which identifies the session for which profiling information is captured. This identifier is unique within the cluster at any point in time but can be reused when the session closes.
`USER_ID`	INT	Unique numeric ID assigned by the Vertica catalog, which identifies the user.
`USER_NAME`	VARCHAR	Name of the user running the query.
`TRANSACTION_ID`	INT	Identifier for the transaction within the session, if any. If a session is active but no transaction has begun, TRANSACTION_ID returns NULL.
`STATEMENT_ID`	INT	Unique numeric ID for the statement that read the data. NULL indicates that no statement is currently being processed. The combination of TRANSACTION_ID and STATEMENT_ID uniquely identifies a statement within a session; these columns are useful for creating joins with other system tables.
`REQUEST_ID`	INT	ID of the data request.
`LOCATION_ID`	INT	ID of the storage location read.
`LOCATION_PATH`	VARCHAR	Path of the storage container read by the query.
`BYTES_READ`	INT	Number of bytes read by the query from this location.

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

19 - DATABASE_BACKUPS

Lists historical information for each backup that successfully completed after running the vbr utility.

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.

Column Name	Data Type	Description
`BACKUP_TIMESTAMP`	TIMESTAMP	The timestamp of the backup.
`NODE_NAME`	VARCHAR	The name of the initiator node that performed the backup.
`SNAPSHOT_NAME`	VARCHAR	The name of the backup, as specified in the `snapshotName` parameter of the `vbr` configuration file.
`BACKUP_EPOCH`	INTEGER	The database epoch at which the backup was saved.
`NODE_COUNT`	INTEGER	The number of nodes backed up in the completed backup, and as listed in the `[Mapping`n`]` sections of the configuration file.
`OBJECTS`	VARCHAR	The name of the object(s) contained in an object-level backup. This column is empty if the record is for a full cluster backup.
`FILE_SYSTEM_TYPE`	VARCHAR	The type of file system, such as Linux.

Privileges

Superuser

20 - DATABASE_CONNECTIONS

Lists the connections to other databases for importing and exporting data.

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

Column Name	Data Type	Description
`DATABASE`	VARCHAR	The name of the connected database
`USERNAME`	VARCHAR	The username used to create the connection
`HOST`	VARCHAR	The host name used to create the connection
`PORT`	VARCHAR	The port number used to create the connection
`ISVALID`	BOOLEAN	Whether the connection is still open and usable or not

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)

21 - DATABASE_MIGRATION_STATUS

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

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

Column Name	Data Type	Description
NODE_NAME	VARCHAR	Name of a node in the source Enterprise database.
TRANSACTION_ID	VARCHAR	Hexadecimal identifier of the migration process transaction.
PHASE	VARCHAR	A stage of database migration on a given node, one of the following, listed in order of execution: Catalog Conversion: Conversion of enterprise-mode catalog to Eon-compatible catalog. Note No data is transferred during this phase, so BYTES_TO_TRANSFER and BYTES_TRANSFERED are always set to 0. Data Transfer: Transfer of data files and library files to communal storage Catalog Transfer: Includes transfer of checkpoint and transaction log files.
STATUS	VARCHAR	Specifies status of a given phase, one of the following: RUNNING COMPLETED ABORT ABORT indicates a given migration phase was unable to complete—for example, the client disconnected, or a network outage occurred—and the migration returned with an error. In this case, call MIGRATE_ENTERPRISE_TO_EON again to restart migration. For details, see Handling Interrupted Migration.
BYTES_TO_TRANSFER	INTEGER	For each migration phase, the size of data to transfer to communal storage, set when phase status is RUNNING: Catalog Conversion: 0 Data Transfer: Size of data files and library files Catalog Transfer: Size of transaction logs
BYTES_TRANSFERRED	INTEGER	For each migration phase, the size of data transfered to communal storage. This value is updated while phase status is RUNNING, and set to the total number of bytes transferred when status is COMPLETED: Catalog Conversion: 0 Data Transfer: Size of data files and library files Catalog Transfer: Size of transaction logs
COMMUNAL_STORAGE_LOCATION	VARCHAR	URL of targeted communal storage location
START_TIME	TIMESTAMP	Demarcate the start and end of each PHASE-specified migration operation.
END_TIME	TIMESTAMP

Privileges

Superuser

Examples

The following example shows data of a migration that is in progress:

=> SELECT node_name, phase, status, bytes_to_transfer, bytes_transferred, communal_storage_location FROM database_migration_status ORDER BY node_name, start_time;
     node_name    |       phase        |  status   | bytes_to_transfer | bytes_transferred | communal_storage_location
------------------+--------------------+-----------+-------------------+------------------+---------------------------
 v_vmart_node0001 | Catalog Conversion | COMPLETED |                 0 |                0 | s3://verticadbbucket/
 v_vmart_node0001 | Data Transfer      | COMPLETED |              1134 |             1134 | s3://verticadbbucket/
 v_vmart_node0001 | Catalog Transfer   | COMPLETED |              3765 |             3765 | s3://verticadbbucket/
 v_vmart_node0002 | Catalog Conversion | COMPLETED |                 0 |                0 | s3://verticadbbucket/
 v_vmart_node0002 | Data Transfer      | COMPLETED |              1140 |             1140 | s3://verticadbbucket/
 v_vmart_node0002 | Catalog Transfer   | COMPLETED |              3766 |             3766 | s3://verticadbbucket/
 v_vmart_node0003 | Catalog Conversion | COMPLETED |                 0 |                0 | s3://verticadbbucket/
 v_vmart_node0003 | Data Transfer      | RUNNING   |           5272616 |           183955 | s3://verticadbbucket/

22 - DELETE_VECTORS

Holds information on deleted rows to speed up the delete process.

Column Name	Data Type	Description
NODE_NAME	VARCHAR	The name of the node storing the deleted rows.
NAMESPACE_NAME	VARCHAR	For Eon Mode databases, namespace where the deleted rows are located.
SCHEMA_NAME	VARCHAR	The name of the schema where the deleted rows are located. If the schema belongs to a non-default namespace in an Eon Mode database, the schema name is front-qualified with the name of the schema's namespace. For example, `n1.s` refers to schema `s` in namespace `n1`.
PROJECTION_NAME	VARCHAR	The name of the projection where the deleted rows are located.
DV_OID	INTEGER	The unique numeric ID (OID) that identifies this delete vector.
STORAGE_OID	INTEGER	The unique numeric ID (OID) that identifies the storage container that holds the delete vector.
SAL_STORAGE_ID	VARCHAR	Unique hexadecimal numeric ID assigned by the Vertica catalog, which identifies the storage.
DELETED_ROW_COUNT	INTEGER	The number of rows deleted.
USED_BYTES	INTEGER	The number of bytes used to store the deletion.
START_EPOCH	INTEGER	The start epoch of the data in the delete vector.
END_EPOCH	INTEGER	The end epoch of the data in the delete vector.

Examples

After you delete data from a Vertica table, that data is marked for deletion. To see the data that is marked for deletion, query the DELETE_VECTORS system table.

Run PURGE to remove the delete vectors from ROS containers.

=> SELECT * FROM test1;
 number
--------
      3
     12
     33
     87
     43
     99
(6 rows)
=> DELETE FROM test1 WHERE number > 50;
 OUTPUT
--------
      2
(1 row)
=> SELECT * FROM test1;
 number
--------
     43
      3
     12
     33
(4 rows)
=> SELECT node_name, projection_name, deleted_row_count FROM DELETE_VECTORS;
    node_name     | projection_name | deleted_row_count
------------------+-----------------+-------------------
 v_vmart_node0002 | test1_b1        |                 1
 v_vmart_node0001 | test1_b1        |                 1
 v_vmart_node0001 | test1_b0        |                 1
 v_vmart_node0003 | test1_b0        |                 1
(4 rows)
=> SELECT PURGE();
...
(Table: public.test1) (Projection: public.test1_b0)
(Table: public.test1) (Projection: public.test1_b1)
...
(4 rows)

After the ancient history mark (AHM) advances:

=> SELECT * FROM DELETE_VECTORS;
 (No rows)

23 - DEPLOY_STATUS

Records the history of deployed Database Designer designs and their deployment steps.

Column Name	Data Type	Description
`EVENT_TIME`	TIMESTAMP	Time when the row recorded the event.
`USER_NAME`	VARCHAR	Name of the user who deployed a design at the time Vertica recorded the session.
`DEPLOY_NAME`	VARCHAR	Name the deployment, same as the user-specified design name.
`DEPLOY_STEP`	VARCHAR	Steps in the design deployment.
`DEPLOY_STEP_STATUS`	VARCHAR	Textual status description of the current step in the deploy process.
`DEPLOY_STEP_COMPLETE_PERCENT`	FLOAT	Progress of current step in percentage (0–100).
`DEPLOY_COMPLETE_PERCENT`	FLOAT	Progress of overall deployment in percentage (0–100).
`ERROR_MESSAGE`	VARCHAR	Error or warning message during deployment.

Privileges

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

24 - DEPLOYMENT_PROJECTION_STATEMENTS

Contains information about CREATE PROJECTION statements used to deploy a database design.

Contains information about CREATE PROJECTION statements used to deploy a database design. Each row contains information about a different CREATE PROJECTION statement. The function DESIGNER_RUN_POPULATE_DESIGN_AND_DEPLOY populates this table.

Column Name	Column Type	Description
`DEPLOYMENT_ID`	INTEGER	Unique ID that Database Designer assigned to the deployment.
`DESIGN_NAME`	VARCHAR	Unique name that the user assigned to the design.
`DEPLOYMENT_PROJECTION_ID`	INTEGER	Unique ID assigned to the output projection by Database Designer.
`STATEMENT_ID`	INTEGER	Unique ID assigned to the statement type that creates the projection.
`STATEMENT`	VARCHAR	Text for the statement that creates the projection.

25 - DEPLOYMENT_PROJECTIONS

Contains information about projections created and dropped during the design.

Contains information about projections created and dropped during the design. Each row contains information about a different projection. Database Designer populates this table after the design is deployed.

Column Name	Column Type	Description
`deployment_id`	INTEGER	Unique ID that Database Designer assigned to the deployment.
`deployment_projection_id`	INTEGER	Unique ID that Database Designer assigned to the output projection.
`design_name`	VARCHAR	Name of the design being deployed.
`deployment_projection_name`	VARCHAR	Name that Database Designer assigned to the projection.
`anchor_table_schema`	VARCHAR	Name of the schema that contains the table the projection is based on. If the schema belongs to a non-default namespace in an Eon Mode database, the schema name is front-qualified with the name of the schema's namespace. For example, `n1.s` refers to schema `s` in namespace `n1`.
`anchor_table_name`	VARCHAR	Name of the table the projection is based on.
`deployment_operation`	VARCHAR	Action being taken on the projection, for example, add or drop.
`deployment_projection_type`	VARCHAR	Indicates whether Database Designer has proposed new projections for this design (DBD) or is using the existing catalog design (CATALOG). The REENCODED suffix indicates that the projection sort order and segmentation are the same, but the projection columns have new encodings: DBD CATALOG DBD_REENCODED CATALOG_REENCODED
`deploy_weight`	INTEGER	Weight of this projection in creating the design. This field is always 0 for projections that have been dropped.
`estimated_size_on_disk`	INTEGER	Approximate size of the projection on disk, in MB.

26 - DEPOT_EVICTIONS

Records data on eviction of objects from the depot.

Eon Mode only

Records data on eviction of objects from the depot.

Column Name	Data Type	Description
START_TIME	TIMESTAMP	Demarcate the start and end of each depot eviction operation.
END_TIME	TIMESTAMP
NODE_NAME	VARCHAR	Name of a node where the eviction occurred.
SESSION_ID	VARCHAR	Unique numeric ID assigned by the Vertica catalog, which identifies the session for which profiling information is captured. This identifier is unique within the cluster at any point in time but can be reused when the session closes.
USER_ID	INTEGER	Unique numeric ID assigned by the Vertica catalog, which identifies the user.
USER_NAME	VARCHAR	The user who made changes to the depot.
TRANSACTION_ID	INTEGER	Identifier for the transaction within the session, if any. If a session is active but no transaction has begun, TRANSACTION_ID returns NULL.
STATEMENT_ID	INTEGER	Unique numeric ID for the statement that caused the eviction. NULL indicates that no statement is currently being processed. The combination of TRANSACTION_ID and STATEMENT_ID uniquely identifies a statement within a session; these columns are useful for creating joins with other system tables.
REQUEST_ID	INTEGER	Unique identifier of the query request in the user session.
STORAGE_ID	VARCHAR	Unique hexadecimal numeric ID assigned by the Vertica catalog, which identifies the storage.
STORAGE_OID	INTEGER	Numeric ID assigned by the Vertica catalog, which identifies the storage.
FILE_SIZE_BYTES	INTEGER	The size of the file in bytes that was evicted.
NUMBER_HITS	INTEGER	The number of times the file was accessed.
LAST_ACCESS_TIME	TIMESTAMP	The last time the file was read.
REASON	VARCHAR	The reason the file was evicted, one of the following: CLEAR DEPOT DEPOT FILL AT STARTUP DEPOT SIZE CHANGE DROP OBJECT DROP SUBSCRIPTION EVICTION DUE TO NEW LOAD PEER TO PEER FILL QUERY
IS_PINNED	BOOLEAN	Whether the object has a pinning policy.
IS_ANTI_PINNED	BOOLEAN	Whether the object has an anti-pinning policy.

Examples

=> SELECT * FROM V_MONITOR.DEPOT_EVICTIONS LIMIT 2;
-[ RECORD 1 ]----+-------------------------------------------------
start_time       | 2019-02-20 15:32:26.765937+00
end_time         | 2019-02-20 15:32:26.766019+00
node_name        | v_verticadb_node0001
session_id       | v_verticadb_node0001-8997:0x3e
user_id          | 45035996273704962
user_name        | dbadmin
transaction_id   | 45035996273705450
statement_id     | 1
request_id       | 406
storage_id       | 0000000000000000000000000000000000a000000001fbf6
storage_oid      | 45035996273842065
file_size_bytes  | 61
number_hits      | 1
last_access_time | 2019-02-20 15:32:26.668094+00
reason           | DROP OBJECT
is_pinned        | f
-[ RECORD 2 ]----+-------------------------------------------------
start_time       | 2019-02-20 15:32:26.812803+00
end_time         | 2019-02-20 15:32:26.812866+00
node_name        | v_verticadb_node0001
session_id       | v_verticadb_node0001-8997:0x3e
user_id          | 45035996273704962
user_name        | dbadmin
transaction_id   | 45035996273705453
statement_id     | 1
request_id       | 409
storage_id       | 0000000000000000000000000000000000a000000001fbf6
storage_oid      | 45035996273842079
file_size_bytes  | 91
number_hits      | 1
last_access_time | 2019-02-20 15:32:26.770807+00
reason           | DROP OBJECT
is_pinned        | f

27 - DEPOT_FETCH_QUEUE

Lists all pending depot requests for queried file data to fetch from communal storage.

Eon Mode only

Lists all pending depot requests for queried file data to fetch from communal storage.

Column Name	Data Type	Description
NODE_NAME	VARCHAR	Name of the node that requested the fetch.
SAL_STORAGE_ID	VARCHAR	Unique hexadecimal numeric ID assigned by the Vertica catalog, which identifies the storage.
TRANSACTION_ID	INTEGER	Identifies the transaction that contains the fetch-triggering query.
SOURCE_FILE_NAME	VARCHAR	Full path in communal storage of the file to fetch.
DESTINATION_FILE_NAME	VARCHAR	Destination path of the file to fetch.

Examples

=> \x
Expanded display is on.
=> SELECT * FROM depot_fetch_queue;
-[ RECORD 1 ]----------+-------------------------------------------------------------
node_name              | v_example_db_node0002
sal_storage_id         | 029b6fac864e1982531dcde47d00edc500d000000001d5e7
transaction_id         | 45035996273705983
source_file_name       | s3://mydata/mydb/14a/029b6fac864e1982531dcde47d00edc500d000
                              000001d5e7_0.gt
destination_file_name  | /vertica/example_db/v_example_db_node0002_depot/747/029b6fac
                              864e1982531dcde47d00edc500d000000001d5eb_0.gt
-[ RECORD 2 ]----------+-------------------------------------------------------------
node_name              | v_example_db_node0003
sal_storage_id         | 026635d69719c45e8d2d86f5a4d62c7b00b000000001d5e7
transaction_id         | 45035996273705983
source_file_name       | s3://mydata/mydb/4a5/029b6fac864e1982531dcde47d00edc500d0000
                              00001d5eb_0.gt
destination_file_name  | /vertica/example_db/v_example_db_node0002_depot/751/026635d6
                              9719c45e8d2d86f5a4d62c7b00b000000001d5e7_0.gt

28 - DEPOT_FETCHES

Records data of depot fetch requests.

Eon Mode only

Records data of depot fetch requests.

Note

Vertica reports all fetches to this table, including failed fetch attempts and their causes.

Column Name	Data Type	Description
START_TIME	TIMESTAMP	Demarcate the start and end of each depot fetch operation.
END_TIME	TIMESTAMP	Demarcate the start and end of each depot fetch operation.
NODE_NAME	VARCHAR	Identifies the node that initiated fetch request.
TRANSACTION_ID	INTEGER	Uniquely identifies the transaction of the query that required the fetched file.
STORAGE_ID	VARCHAR	Unique hexadecimal numeric ID assigned by the Vertica catalog, to identify the storage.
STORAGE_OID	INTEGER	Numeric ID assigned by the Vertica catalog, which identifies the storage.
FILE_SIZE_BYTES	INTEGER	Fetch size in bytes.
SOURCE_FILE	VARCHAR	Source file path used, set to null if the file was fetched from a peer.
DESTINATION_FILE	VARCHAR	Destination file path
SOURCE_NODE	VARCHAR	Source node from which the file was fetched, set to one of the following: Name of the source node if file was fetched from a peer Null if file was fetched from communal storage
IS_SUCCESSFUL	BOOLEAN	Boolean, specifies whether this fetch succeeded.
REASON	VARCHAR	Reason why the fetch failed, null if IS_SUCCESSFUL is true.

Examples

=> \x
Expanded display is on.

=> SELECT * FROM DEPOT_FETCHES LIMIT 2;
-[ RECORD 1 ]----+-------------------------------------------------------------
start_time       | 2019-08-30 15:16:15.125962+00
end_time         | 2019-08-30 15:16:15.126791+00
node_name        | v_verticadb_node0001
transaction_id   | 45035996273706225
storage_id       | 0239ef74126e70db410b301610f1e5b500b0000000020d65
storage_oid      | 45035996273842065
file_size_bytes  | 53033
source_file      |
destination_file | /vertica/data/verticadb/v_verticadb_node0001_depot/957/0239e
                     f74126e70db410b301610f1e5b500b0000000020d65_0.gt
source_node      | v_verticadb_node0002
is_successful    | t
reason           |
-[ RECORD 2 ]----+-------------------------------------------------------------
start_time       | 2019-08-30 15:16:15.285208+00
end_time         | 2019-08-30 15:16:15.285949+00
node_name        | v_verticadb_node0001
transaction_id   | 45035996273706234
storage_id       | 0239ef74126e70db410b301610f1e5b500b0000000020dc7
storage_oid      | 45035996273842075
file_size_bytes  | 69640
source_file      |
destination_file | /vertica/data/verticadb/v_verticadb_node0001_depot/055/0239e
                     f74126e70db410b301610f1e5b500b0000000020dc7_0.gt
source_node      | v_verticadb_node0002
is_successful    | t
reason           |

=> select node_name,transaction_id,storage_id,is_successful,reason FROM
   depot_fetches WHERE is_successful = 'f' LIMIT 3;
-[ RECORD 1 ]--+-------------------------------------------------
node_name      | v_verticadb_node0001
transaction_id | 45035996273721070
storage_id     | 0289281ac4c1f6580b95096fab25290800b0000000027d09
is_successful  | f
reason         | Could not create space in the depot
-[ RECORD 2 ]--+-------------------------------------------------
node_name      | v_verticadb_node0001
transaction_id | 45035996273721070
storage_id     | 0289281ac4c1f6580b95096fab25290800b0000000027d15
is_successful  | f
reason         | Could not create space in the depot
-[ RECORD 3 ]--+-------------------------------------------------
node_name      | v_verticadb_node0002
transaction_id | 45035996273721070
storage_id     | 02693f1c68266e38461084a840ee42aa00c0000000027d09
is_successful  | f
reason         | Could not create space in the depot

29 - DEPOT_FILES

Lists objects that are currently cached in the database depots.

Eon Mode only

Lists objects that are currently cached in the database depots.

Column Name	Data Type	Description
NODE_NAME	VARCHAR	Name of the depot node.
SAL_STORAGE_ID	VARCHAR	Unique hexadecimal numeric ID assigned by the Vertica catalog, which identifies the storage.
STORAGE_OID	INTEGER	Numeric ID assigned by the Vertica catalog, which identifies the storage.
COMMUNAL_FILE_PATH	VARCHAR	Communal storage path to the file. On AWS, this is an S3 URI.
DEPOT_FILE_PATH	VARCHAR	Depot path to the file.
SHARD_NAME	VARCHAR	Name of the shard this file is a part of.
STORAGE_TYPE	VARCHAR	Type of system storing this file.
NUMBER_OF_ACCESSES	INTEGER	Number of times this file has been accessed.
FILE_SIZE_BYTES	INTEGER	How large the file is in bytes.
LAST_ACCESS_TIME	TIMESTAMPTZ	When the file was last accessed.
ARRIVAL_TIME	TIMESTAMPTZ	When Vertica loaded the file into the depot.
SOURCE	VARCHAR	How the data came to be cached, one of the following: LOAD QUERY PEER TO PEER FILL DEPOT FILL AT STARTUP
IS_PINNED	BOOLEAN	Whether a pinning policy is set on the cached data.
IS_ANTI_PINNED	BOOLEAN	Whether an anti-pinning policy is set on the cached data.

Examples

=> \x
Expanded display is on.
=> SELECT * FROM depot_files LIMIT 2;

-[ RECORD 1 ]------+---------------------------------------------------------------
node_name          | v_verticadb_node0001
sal_storage_id     | 0275d4a7c99795d22948605e5940758900a000000001d1b1
storage_oid        | 45035996273842075
communal_file_path | s3://mybucket/myfolder/mydb/475/0275d4a7c99795d22948605e5940758900a000000001d1
b1/0275d4a7c99795d22948605e5940758900a000000001d1b1_
depot_file_path    | /vertica/data/verticadb/v_verticadb_node0001_depot/177/0275d4a7c99795d229486
05e5940758900a000000001d1b1/0275d4a7c99795d22948605e
shard_name         | replica
storage_type       | DFS
number_of_accesses | 0
file_size_bytes    | 456465645
last_access_time   | 2018-09-05 17:34:30.417274+00
arrival_time       | 2018-09-05 17:34:30.417274+00
source             | DEPOT FILL AT STARTUP
is_pinned          | f
-[ RECORD 2 ]------+---------------------------------------------------------------
node_name          | v_verticadb_node0001
sal_storage_id     | 0275d4a7c99795d22948605e5940758900a000000001d187
storage_oid        | 45035996273842079
communal_file_path | s3://mybucket/myfolder/mydb/664/0275d4a7c99795d22948605e5940758900a000000001d1
87/0275d4a7c99795d22948605e5940758900a000000001d187_
depot_file_path    | /vertica/data/verticadb/v_verticadb_node0001_depot/135/0275d4a7c99795d229486
05e5940758900a000000001d187/0275d4a7c99795d22948605e
shard_name         | replica
storage_type       | DFS
number_of_accesses | 0
file_size_bytes    | 40
last_access_time   | 2018-09-05 17:34:30.417244+00
arrival_time       | 2018-09-05 17:34:30.417244+00
source             | DEPOT FILL AT STARTUP
is_pinned          | f

30 - DEPOT_PIN_POLICIES

Lists all objects —tables, projections, and table partitions—with depot eviction policies.

Eon Mode only

Lists all objects —tables, projections, and table partitions—with depot eviction policies.

Column Name	Data Type	Description
NAMPESPACE_NAME	VARCHAR	For Eon Mode databases, object namespace.
SCHEMA_NAME	VARCHAR	Object schema. If the schema belongs to a non-default namespace in an Eon Mode database, the schema name is front-qualified with the name of the schema's namespace. For example, `n1.s` refers to schema `s` in namespace `n1`.
OBJECT_NAME	VARCHAR	Object name.
SUBCLUSTER_NAME	VARCHAR	Name of the subcluster where the pinning policy is set, null if the policy is set on all database depots.
POLICY_DETAILS	VARCHAR	Object type, one of the following: Table Projection Partition
MIN_VAL	VARCHAR	If POLICY_DETAILS is set to Partition, the range of contiguous partition keys specified by this policy.
MAX_VAL	VARCHAR
LOCATION_LABEL	VARCHAR	Depot's storage location label.
POLICY_TYPE	VARCHAR	Policy type set on this object (PIN or ANTI-PIN), if any.

31 - DEPOT_SIZES

Reports depot caching capacity on Vertica nodes.

Eon Mode only

Reports depot caching capacity on Vertica nodes.

Column Name	Data Type	Description
`NODE_NAME`	VARCHAR	The name of the node containing the depot.
`LOCATION_OID`	INTEGER	Catalog-assigned integer value that uniquely identifies the storage location storing the depot.
`LOCATION_PATH`	VARCHAR	The path where the depot is stored.
`LOCATION_LABEL`	VARCHAR	The label associated with the depot's storage location.
`MAX_SIZE_BYTES`	INTEGER	The maximum size the depot can contain, in bytes.
`CURRENT_USAGE_BYTES`	INTEGER	The current size of the depot, in bytes.

Examples

=> \x
Expanded display is on.
=> SELECT * FROM Depot_Sizes;

-[ RECORD 1 ]-------+---------------------------------------------------

node_name           | v_verticadb_node0003
location_oid        | 45035996273823200
location_path       | /vertica/data/verticadb/v_verticadb_node0003_depot
location_label      | auto-data-depot
max_size_bytes      | 0
current_usage_bytes | 0

-[ RECORD 2 ]-------+---------------------------------------------------
node_name           | v_verticadb_node0001
location_oid        | 45035996273823196
location_path       | /vertica/data/verticadb/v_verticadb_node0001_depot
location_label      | auto-data-depot
max_size_bytes      | 33686316032
current_usage_bytes | 206801871

-[ RECORD 3 ]-------+---------------------------------------------------
node_name           | v_verticadb_node0002
location_oid        | 45035996273823198
location_path       | /vertica/data/verticadb/v_verticadb_node0002_depot
location_label      | auto-data-depot
max_size_bytes      | 0
current_usage_bytes | 0

32 - DEPOT_UPLOADS

Lists details about depot uploads to communal storage.

Eon Mode only

Lists details about depot uploads to communal storage.

Column Name	Data Type	Description
`NODE_NAME`	VARCHAR	The name of the node on which the depot resides.
`PLAN_ID`	VARCHAR	A unique node-specific numeric ID for each plan run by the Optimizer.
`SUBMIT_TIME`	TIMESTAMP	The time the task was submitted to the uploader.
`START_TIME`	TIMESTAMP	The time the upload started.
`END_TIME`	TIMESTAMP	The time the upload ended.
`SOURCE_FILE`	VARCHAR	The source file path used.
`DESTINATION_FILE`	VARCHAR	The destination file path.
`FILE_SIZE_BYTES`	INTEGER	The size of the uploaded file, in bytes.
`MEMORY_USED_KB`	INTEGER	The size of the uploader file buffer for the task. Valid for a task with a RUNNING or COMPLETED status. For a RUNNING status, this shows the current file buffer size, (whatever the uploader is using, which may grow over time for large uploads). For a COMPLETED status, this shows the largest size used in case the buffer grew during the upload.
`STATUS`	VARCHAR	The status of the task, valid values are: COMPLETED - the task has completed QUEUED - the task is still in the queue, but haven't been picked up by the uploader. RUNNING - the task is currently running and the corresponding file is uploading.

33 - DESIGN_QUERIES

Contains info about design queries for a given design.

Contains info about design queries for a given design. The following functions populate this table:

Column Name	Column Type	Description
`DESIGN_ID`	INTEGER	Unique id that Database Designer assigned to the design.
`DESIGN_NAME`	VARCHAR	Name that you specified for the design.
`DESIGN_QUERY_ID`	INTEGER	Unique id that Database Designer assigned to the design query.
`DESIGN_QUERY_ID_INDEX`	INTEGER	Database Designer chunks the query text if it exceeds the maximum attribute size before storing it in this table. Database Designer stored all chunks stored under the same value of DESIGN_QUERY_ID. DESIGN_QUERY_ID_INDEX keeps track of the order of the chunks, starting with 0 and ending in n, the index of the final chunk.
`QUERY_TEXT`	VARCHAR	Text of the query chunk, or the entire query text if it does not exceed the maximum attribute size.
`WEIGHT`	FLOAT	A value from 0 to 1 that indicates the importance of that query in creating the design. Assign a higher weight to queries that you run frequently so that Database Designer prioritizes those queries in creating the design. Default: 1.
`DESIGN_QUERY_SEARCH_PATH`	VARCHAR	The search path with which the query is to be parsed.
`DESIGN_QUERY_SIGNATURE`	INTEGER	Categorizes queries that affect the design that Database Designer creates in the same way. Database Designer assigns a signature to each query, weights one query for each signature group, depending on how many queries there are with that signature, and Database Designer considers that query when creating the design.

Example

Add queries to VMART_DESIGN and query the DESIGN_QUERIES table:


=> SELECT DESIGNER_ADD_DESIGN_QUERIES('VMART_DESIGN', '/tmp/examples/vmart_queries.sql','true');
 DESIGNER_ADD_DESIGN_QUERIES
-----------------------------
Number of accepted queries                      =9
Number of queries referencing non-design tables =0
Number of unsupported queries                   =0
Number of illegal queries                       =0
=> \x
Expanded display is on.
=> SELECT * FROM V_MONITOR.DESIGN.QUERIES
-[ RECORD 1 ]------------+-------------------
design_id                | 45035996273705090
design_name              | vmart_design
design_query_id          | 1
design_query_id_index    | 0
query_text               | SELECT fat_content
FROM (
SELECT DISTINCT fat_content
  FROM product_dimension
  WHERE department_description
  IN ('Dairy') ) AS food
  ORDER BY fat_content
  LIMIT 5;
weight                   | 1
design_query_search_path | v_dbd_vmart_design_vmart_design_ltt, "$user", public, v_catalog, v_monitor, v_internal
design_query_signature   | 45035996273724651

-[ RECORD 2]-------------+-------------------
design_query_id          | 2
design_query_id_index    | 0
query_text               | SELECT order_number, date_ordered
        FROM store.store_orders_fact orders
        WHERE orders.store_key IN (
        SELECT store_key
        FROM store.store_dimension
        WHERE store_state = 'MA')
        AND orders.vendor_key NOT IN (
        SELECT vendor_key
        FROM public.vendor_dimension
        WHERE vendor_state = 'MA')
        AND date_ordered < '2012-03-01';

weight                   | 1
design_query_search_path | v_dbd_vmart_design_vmart_design_ltt, "$user", public, v_catalog, v_monitor, v_internal
design_query_signature   | 45035996273724508
-[ RECORD 3]-------------+-------------------
...

34 - DESIGN_STATUS

Records the progress of a running Database Designer design or history of the last Database Designer design executed by the current user.

Column Name	Data Type	Description
`EVENT_TIME`	TIMESTAMP	Time when the row recorded the event.
`USER_NAME`	VARCHAR	Name of the user who ran a design at the time Vertica recorded the session.
`DESIGN_NAME`	VARCHAR	Name of the user-specified design.
`DESIGN_PHASE`	VARCHAR	Phase of the design.
`PHASE_STEP`	VARCHAR	Substep in each design phase
`PHASE_STEP_COMPLETE_PERCENT`	FLOAT	Progress of current substep in percentage (0–100).
`PHASE_COMPLETE_PERCENT`	FLOAT	Progress of current design phase in percentage (0–100).

Privileges

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

Examples

The following example shows the content of the DESIGN_STATUS table of a complete Database Designer run:

=> SELECT event_time, design_name, design_phase, phase_complete_percent
   FROM v_monitor.design_status;
event_time          | design_name | design_phase                                   | phase_complete_percent
--------------------+-------------+------------------------------------------------+------------------------
2012-02-14 10:31:20 | design1     | Design started                                 |
2012-02-14 10:31:21 | design1     | Design in progress: Analyze statistics phase   |
2012-02-14 10:31:21 | design1     | Analyzing data statistics                      | 33.33
2012-02-14 10:31:22 | design1     | Analyzing data statistics                      | 66.67
2012-02-14 10:31:24 | design1     | Analyzing data statistics                      | 100
2012-02-14 10:31:25 | design1     | Design in progress: Query optimization phase   |
2012-02-14 10:31:25 | design1     | Optimizing query performance                   | 37.5
2012-02-14 10:31:31 | design1     | Optimizing query performance                   | 62.5
2012-02-14 10:31:36 | design1     | Optimizing query performance                   | 75
2012-02-14 10:31:39 | design1     | Optimizing query performance                   | 87.5
2012-02-14 10:31:41 | design1     | Optimizing query performance                   | 87.5
2012-02-14 10:31:42 | design1     | Design in progress: Storage optimization phase |
2012-02-14 10:31:44 | design1     | Optimizing storage footprint                   | 4.17
2012-02-14 10:31:44 | design1     | Optimizing storage footprint                   | 16.67
2012-02-14 10:32:04 | design1     | Optimizing storage footprint                   | 29.17
2012-02-14 10:32:04 | design1     | Optimizing storage footprint                   | 31.25
2012-02-14 10:32:05 | design1     | Optimizing storage footprint                   | 33.33
2012-02-14 10:32:05 | design1     | Optimizing storage footprint                   | 35.42
2012-02-14 10:32:05 | design1     | Optimizing storage footprint                   | 37.5
2012-02-14 10:32:05 | design1     | Optimizing storage footprint                   | 62.5
2012-02-14 10:32:39 | design1     | Optimizing storage footprint                   | 87.5
2012-02-14 10:32:39 | design1     | Optimizing storage footprint                   | 87.5
2012-02-14 10:32:41 | design1     | Optimizing storage footprint                   | 100
2012-02-14 10:33:12 | design1     | Design completed successfully                  |
(24 rows)

35 - DESIGN_TABLES

Contains information about all the design tables for all the designs for which you are the owner.

Contains information about all the design tables for all the designs for which you are the owner. Each row contains information about a different design table. Vertica creates this table when you run DESIGNER_CREATE_DESIGN.

Column Name	Column Type	Description
`DESIGN_NAME`	VARCHAR	Unique name that the user specified for the design.
`DESIGN_TABLE_ID`	INTEGER	Unique ID that Database Designer assigned to the design table.
`TABLE_SCHEMA`	VARCHAR	Name of the schema that contains the design table. If the schema belongs to a non-default namespace in an Eon Mode database, the schema name is front-qualified with the name of the schema's namespace. For example, `n1.s` refers to schema `s` in namespace `n1`.
`TABLE_ID`	INTEGER	System object identifier (OID) assigned to the design table.
`TABLE_NAME`	VARCHAR	Name of the design table.

Example

Add all the tables from the VMart database to the design VMART_DESIGN. This operation populates the DESIGN_TABLES table:


=> SELECT DESIGNER_ADD_DESIGN_TABLES('VMART_DESIGN','online_sales.*');
DESIGNER_ADD_DESIGN_TABLES
----------------------------
 3
(1 row)
=> SELECT DESIGNER_ADD_DESIGN_TABLES('VMART_DESIGN','public.*');
DESIGNER_ADD_DESIGN_TABLES
----------------------------
 9
(1 row)
=> SELECT DESIGNER_ADD_DESIGN_TABLES('VMART_DESIGN','store.*');
DESIGNER_ADD_DESIGN_TABLES
----------------------------
 3
(1 row)
=> SELECT * FROM DESIGN_TABLES;
design_name  | design_table_id | table_schema |     table_id      |      table_name
-------------+-----------------+--------------+-------------------+-----------------------
VMART_DESIGN |               1 | online_sales | 45035996373718754 | online_page_dimension
VMART_DESIGN |               2 | online_sales | 45035996373718758 | call_center_dimension
VMART_DESIGN |               3 | online_sales | 45035996373718762 | online_sales_fact
VMART_DESIGN |               4 | public       | 45035996373718766 | customer_dimension
VMART_DESIGN |               5 | public       | 45035996373718770 | product_dimension
VMART_DESIGN |               6 | public       | 45035996373718774 | promotion_dimension
VMART_DESIGN |               7 | public       | 45035996373718778 | date_dimension
VMART_DESIGN |               8 | public       | 45035996373718782 | vendor_dimension
VMART_DESIGN |               9 | public       | 45035996373718786 | employee_dimension
VMART_DESIGN |              10 | public       | 45035996373718822 | shipping_dimension
VMART_DESIGN |              11 | public       | 45035996373718826 | warehouse_dimension
VMART_DESIGN |              12 | public       | 45035996373718830 | inventory_face
VMART_DESIGN |              13 | store        | 45035996373718794 | store_dimension
VMART_DESIGN |              14 | store        | 45035996373718798 | store_sales_fact
VMART_DESIGN |              15 | store        | 45035996373718812 | store_orders_fact
        (15 rows)

36 - DESIGNS

Contains information about a Database Designer design.

Contains information about a Database Designer design. After you create a design and specify certain parameters for Database Designer, DESIGNER_CREATE_DESIGN creates this table in the V_MONITOR schema.

Column Name	Column Type	Description
`DESIGN_ID`	INTEGER	Unique ID that Database Designer assigns to this design.
`DESIGN_NAME`	VARCHAR	Name that the user specifies for the design.
`KSAFETY_LEVEL`	INTEGER	K-safety level for the design. Database Designer assigns a K-safety value of 0 for clusters with 1 or 2 nodes, and assigns a value of 1 for clusters with 3 or more nodes.
`OPTIMIZATION_OBJECTIVE`	VARCHAR	Name of the optimization objective for the design. Valid values are: `QUERY` `LOAD` `BALANCED` (default)
`DESIGN_TYPE`	VARCHAR	Name of the design type. Valid values are: `COMPREHENSIVE` (default) `INCREMENTAL`
`PROPOSE_SUPER_FIRST`	BOOLEAN	Specifies to propose superprojections before projections, by default `f`. If `DESIGN_MODE` is `COMPREHENSIVE`, this field has no impact.
`DESIGN_AVAILABLE`	BOOLEAN	`t` if the design is currently available, otherwise, `f` (default).
`COLLECTED_STATISTICS`	BOOLEAN	`t` if statistics are to be collected when creating the design, otherwise, `f` (default).
`POPULATE_DESIGN_TABLES_FROM_QUERIES`	BOOLEAN	`t` if you want to populate the design tables from the design queries, otherwise, `f` (default).
`ENCODING_DESIGN`	BOOLEAN	`t` if the design is an encoding optimization design on pre-existing projections, otherwise, `f` (default).
`DEPLOYMENT_PARALLELISM`	INTEGER	Number of tables to be deployed in parallel when the design is complete. Default: 0
`UNSEGMENTED_PROJECTIONS`	BOOLEAN	`t` if you specify unsegmented projections, otherwise, `f` (default).
`ANALYZE_CORRELATIONS_MODE`	INTEGER	Specifies how Database Designer should handle existing column correlations in a design and whether or not Database Designer should reanalyze existing column correlations. 0: (default) Ignore column correlations when creating the design. 1: Consider the existing correlations in the tables when creating the design. 2: Analyze column correlations if not previously performed, and consider the column correlations when creating the design. 3: Analyze all column correlations in the tables and consider them when creating the design, even if they have been analyzed previously.

37 - DIRECTED_QUERY_STATUS

Returns information about execution of directed queries.

Returns information about executions of directed queries.

Column Name	Data Type	Description
NODE_NAME	VARCHAR	Node on which the directed query ran.
OID	INTEGER	Unique numeric ID assigned by the Vertica catalog, which identifies the specific directed query.
QUERY_NAME	VARCHAR	Directed query's unique identifier, used by statements such as ACTIVATE DIRECTED QUERY.
DIGEST	INTEGER	Hash of the saved query plan data, used by the optimizer to map identical input queries to the same active directed query.
HITS	INTEGER	Number of times this query has been executed since the last reset. Use CLEAR_DIRECTED_QUERY_USAGE to reset this counter to 0.

Privileges

Superuser

38 - DISK_QUOTA_USAGES

Provides information about schemas and tables that have disk quotas.

Provides information about schemas and tables that have disk quotas. Schemas and tables without quotas are not included.

Column Name	Data Type	Description
OBJECT_OID	INTEGER	Unique identifier for a schema or table.
OBJECT_NAME	VARCHAR	Name of the schema or table. Table names include the schema prefix.
IS_SCHEMA	BOOLEAN	Whether the object is a schema. If false, the object is a table.
TOTAL_DISK_USAGE_IN_BYTES	INTEGER	Current usage of the object. For information about what is counted, see Disk quotas.
DISK_QUOTA_IN_BYTES	INTEGER	Current quota for the object.

Examples

=> SELECT * FROM DISK_QUOTA_USAGES;
    object_oid     | object_name | is_schema | total_disk_usage_in_bytes | disk_quota_in_bytes
-------------------+-------------+-----------+---------------------+---------------------
 45035996273705100 | s           | t         |                 307 |               10240
 45035996273705104 | public.t    | f         |                 614 |                1024
 45035996273705108 | s.t         | f         |                 307 |                2048
(3 rows)

39 - DISK_RESOURCE_REJECTIONS

Returns requests for resources that are rejected due to disk space shortages.

Returns requests for resources that are rejected due to disk space shortages. Output is aggregated by both RESOURCE_TYPE and REJECTED_REASON to provide more comprehensive information.

Column Name	Data Type	Description
`NODE_NAME`	VARCHAR	The node name for which information is listed.
`RESOURCE_TYPE`	VARCHAR	The resource request requester (example: Temp files).
`REJECTED_REASON`	VARCHAR	One of the following: `Insufficient disk space` `Failed volume`
`REJECTED_COUNT`	INTEGER	Number of times this `REJECTED_REASON` has been given for this `RESOURCE_TYPE`.
`FIRST_REJECTED_TIMESTAMP`	TIMESTAMP	The time of the first rejection for this `REJECTED_REASON` and RESOURCE_TYPE.
`LAST_REJECTED_TIMESTAMP`	TIMESTAMP	The time of the most recent rejection for this `REJECTED_REASON` and `RESOURCE_TYPE`.
`LAST_REJECTED_VALUE`	INTEGER	The value of the most recent rejection for this `REJECTED_REASON` and `RESOURCE_TYPE`.

40 - DISK_STORAGE

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

Returns the amount of disk storage used by the database on each node. Each node can have one or more storage locations, and the locations can be on different disks with separate properties, such as free space, used space, and block size. The information in this system table is useful in determining where data files reside.

All returned values for this system table are in the context of the file system of the host operating system, and are not specific to Vertica-specific space.

The storage usage annotation called CATALOG indicates that the location is used to store the catalog. Each CATALOG location is specified only when creating a new database. You cannot add a CATALOG location annotation using CREATE LOCATION, nor remove an existing CATALOG annotation.

Storage location performance

The performance of a storage location is measured with two values:

Throughput in MB/sec
Latency in seeks/sec

These two values are converted to a single number (Speed) with the following formula:

read-time = (1/throughput) + (1/latency)

read-time: Time to read 1MB of data
1/throughput: Time to read 1MB of data
1/latency: Time to seek to the data.

A disk is faster than another disk if its read-time is less.

Column Name	Data Type	Description
NODE_NAME	VARCHAR	Node name for which information is listed.
STORAGE_PATH	VARCHAR	Path where the storage location is mounted.
STORAGE_USAGE	VARCHAR	Type of information stored in the location, one of the following: `DATA`: Only data is stored in the location. `TEMP`: Only temporary files that are created during loads or queries are stored in the location. `DATA,TEMP`: Both types of files are stored in the location. `USER`: The storage location can be used by non-dbadmin users, who are granted access to the storage location `CATALOG`: The area is used for the Vertica catalog. This usage is set internally and cannot be removed or changed.
RANK	INTEGER	Integer rank assigned to the storage location based on its performance. Ranks are used to create a storage locations on which projections, columns, and partitions are stored on different disks based on predicted or measured access patterns. See Managing storage locations.
THROUGHPUT	INTEGER	Integer that measures a storage location's performance in MB/sec. `1/``throughput` is the time taken to read 1MB of data.
LATENCY	INTEGER	Integer that measures a storage location's performance in seeks/sec. `1/``latency` is the time taken to seek to the data.
STORAGE_STATUS	VARCHAR	Status of the storage location, one of the following: `active` `retired`
DISK_BLOCK_SIZE_BYTES	INTEGER	Block size of the disk in bytes
DISK_SPACE_USED_BLOCKS	INTEGER	Number of disk blocks in use
DISK_SPACE_USED_MB	INTEGER	Number of megabytes of disk storage in use
DISK_SPACE_FREE_BLOCKS	INTEGER	Number of free disk blocks available
DISK_SPACE_FREE_MB	INTEGER	Number of megabytes of free storage available
DISK_SPACE_FREE_PERCENT	VARCHAR	Percentage of free disk space remaining

41 - DRAINING_STATUS

Returns the draining status of each node in a database.

Returns the draining status of each node in a database. The table also provides aggregate user session counts and information about the oldest user session connected to each node. For more information about the user sessions connected to a database, see SESSIONS.

Note

If you aren't a superuser, the OLDEST_SESSION columns contain only information about sessions for which you have privileges. Unprivileged users do not see session details, but they do see the node draining status and the user session aggregate count.

Column Name	Data Type	Description
`NODE_NAME`	VARCHAR	Name of the node for which information is listed.
`SUBCLUSTER_NAME`	VARCHAR	Name of the subcluster that contains the node.
`IS_DRAINING`	BOOLEAN	True if the node is draining; otherwise, false.
`COUNT_CLIENT_USER_SESSIONS`	INTEGER	Number of user client sessions connected to the node.
`OLDEST_SESSION_USER`	VARCHAR	Name of the user with the oldest live session connected to the node. NULL if no users are connected.
`OLDEST_SESSION_ID`	VARCHAR	Identifier associated with `OLDEST_SESSION_USER`. This is required to close or interrupt a session. NULL if no users are connected.
`OLDEST_SESSION_LOGIN_TIMESTAMP`	TIMESTAMP	Date and time the `OLDEST_SESSION_USER` logged into the database. NULL if no users are connected.

42 - ERROR_MESSAGES

Lists system error messages and warnings Vertica encounters while processing queries.

Lists system error messages and warnings Vertica encounters while processing queries. Some errors occur when no transaction is in progress, so the transaction identifier or statement identifier columns might return NULL.

Column Name	Data Type	Description
`EVENT_TIMESTAMP`	TIMESTAMPTZ	Time when the row recorded the event
`NODE_NAME`	VARCHAR	Name of the node that is reporting the requested information
`USER_ID`	INTEGER	Identifier of the user who received the error message
`USER_NAME`	VARCHAR	Name of the user who received the error message when Vertica recorded the session
`SESSION_ID`	VARCHAR	Identifier for this session. This identifier is unique within the cluster at any point in time but can be reused when the session closes.
`REQUEST_ID`	INTEGER	Unique identifier of the query request in the user session
`TRANSACTION_ID`	INTEGER	Identifier for the transaction within the session, if any; otherwise `NULL`
`STATEMENT_ID`	INTEGER	Unique numeric ID for the currently-running statement. NULL indicates that no statement is currently being processed. The combination of TRANSACTION_ID, STATEMENT_ID, and REQUEST_ID uniquely identifies a statement within a session.
`ERROR_LEVEL`	VARCHAR	Severity of the error, one of the following: LOG INFO NOTICE WARNING ERROR ROLLBACK INTERNAL FATAL PANIC
`ERROR_CODE`	INTEGER	Error code that Vertica reports
`MESSAGE`	VARCHAR	Textual output of the error message
`DETAIL`	VARCHAR	Additional information about the error message, in greater detail
`HINT`	VARCHAR	Actionable hint about the error. For example: `HINT: Set the locale in this session to en_US@collation=binary using the command "\locale en_US@collation=binary"`

Privileges

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

43 - EVENT_CONFIGURATIONS

Monitors the configuration of events.

Column Name	Data Type	Description
`EVENT_ID`	VARCHAR	The name of the event.
`EVENT_DELIVERY_CHANNELS`	VARCHAR	The delivery channel on which the event occurred.

44 - EXECUTION_ENGINE_PROFILES

Provides profiling information about runtime query execution.

Provides profiling information about runtime query execution. The hierarchy of IDs, from highest level to actual execution, is:

PATH_ID
BASEPLAN_ID
LOCALPLAN_ID
OPERATOR_ID

Counters (output from the COUNTER_NAME column) are collected for each actual Execution Engine (EE) operator instance.

The following columns combine to form a unique key:

TRANSACTION_ID
STATEMENT_ID
NODE_NAME
OPERATOR_ID
COUNTER_NAME
COUNTER_TAG

Column Name	Data Type	Description
`NODE_NAME`	VARCHAR	Node name for which information is listed.
`USER_ID`	INTEGER	Unique numeric ID assigned by the Vertica catalog, which identifies the user.
`USER_NAME`	VARCHAR	User name for which query profile information is listed.
`SESSION_ID`	VARCHAR	Identifier of the session for which profiling information is captured. This identifier is unique within the cluster at any point in time but can be reused when the session closes.
`TRANSACTION_ID`	INTEGER	Identifier for the transaction within the session if any; otherwise `NULL`.
`STATEMENT_ID`	INTEGER	Unique numeric ID for the currently-running statement. `NULL` indicates that no statement is currently being processed.
`OPERATOR_NAME`	VARCHAR	Name of the Execution Engine (EE) component; for example, `NetworkSend`.
`OPERATOR_ID`	INTEGER	Identifier assigned by the EE operator instance that performs the work. `OPERATOR_ID` is different from `LOCALPLAN_ID` because each logical operator, such as `Scan`, may be executed by multiple threads concurrently. Each thread operates on a different operator instance, which has its own ID.
`BASEPLAN_ID`	INTEGER	Assigned by the optimizer on the initiator to EE operators in the original base (EXPLAIN) plan. Each EE operator in the base plan gets a unique ID.
`PATH_ID`	INTEGER	Identifier that Vertica assigns to a query operation or path; for example to a logical grouping operation that might be performed by multiple execution engine operators. For each path, the same PATH ID is shared between the query plan (using EXPLAIN output) and in error messages that refer to joins.
`LOCALPLAN_ID`	INTEGER	Identifier assigned by each local executor while preparing for plan execution (local planning). Some operators in the base plan, such as the `Root` operator, which is connected to the client, do not run on all nodes. Similarly, certain operators, such as `ExprEval`, are added and removed during local planning due to implementation details.
`ACTIVITY_ID`	INTEGER	Identifier of the plan activity.
`RESOURCE_ID`	INTEGER	Identifier of the plan resource.
`COUNTER_NAME`	VARCHAR	Name of the counter (see Counter Names below). The counter counts events for one statement.
`COUNTER_TAG`	VARCHAR	String that uniquely identifies the counter for operators that might need to distinguish between different instances. For example, `COUNTER_TAG` is used to identify to which of the node bytes are being sent to or received from the `NetworkSend` operator.
`COUNTER_VALUE`	INTEGER	Value of the counter.
`IS_EXECUTING`	BOOLEAN	Indicates whether the profile is active or completed.

Privileges

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

Counter names

The value of COUNTER_NAME can be any of the following:

active threads

A counter of the LoadUnion operator, indicates the number of input threads (Load operators) that are currently processing input.

blocks analyzed by SIPs expression

Number of data blocks (for scan operations) or files (for load operations) analyzed by SIPS expression.

blocks filtered by SIPs expression

Number of data blocks (for scan operations) or files (for load operations) filtered by SIPS expression.

blocks filtered by SIPs value lists

Number of data blocks (for scan operations) or files (for load operations) filtered by SIPS sorted value lists.

buffers spilled

[NetworkSend] Buffers spilled to disk by NetworkSend.

bytes read from disk

[Scan] Amount of data read (locally or remotely) from ROS containers on disk.

bytes read from disk cache

[Scan] Amount of data read from cache.

bytes received

Number of bytes received over the network for query execution.

bytes sent

[NetworkSend] Size of data after encoding and compression sent over the network (actual network bytes).

bytes spilled

[NetworkSend] Bytes spilled to disk by NetworkSend.

bytes total

[SendFiles] (recover-by-container plan): Total number of bytes to send/receive.

cached storages cumulative size (bytes)

[StorageMerge] Total amount of temp space used by operator for caching.

cached storages current size (bytes)

[StorageMerge] Current amount of temp space used for caching.

cached storages peak size (bytes)

[StorageMerge] Peak amount of temp space an operator used for caching.

clock time (µs)

Real-time clock time spent processing the query, in microseconds.

clock time (µs) of UDChunker

Real-time clock time spent in the UDChunker phase of a load operation, in microseconds. Use the COUNTER_TAG column to distinguish among load sources.

clock time (µs) of UDFilter(s)

Real-time clock time spent in all UDFilter phases of a load operation, in microseconds. Use the COUNTER_TAG column to distinguish among load sources.

clock time (µs) of UDParser

Real-time clock time spent in the UDParser phase of a load operation, in microseconds. Use the COUNTER_TAG column to distinguish among load sources.

clock time (µs) of UDSource

Real-time clock time spent in the UDSource phase of a load operation, in microseconds. Use the COUNTER_TAG column to distinguish among load sources.

completed merge phases

Number of merge phases already completed by an LSort or DataTarget operator. Compare to the total merge phases. Variants on this value include join inner completed merge phases.

cumulative size of raw temp data (bytes)

Total amount of temporary data the operator has written to files. Compare to cumulative size of temp files (bytes) to understand impact of encoding and compression in an externalizing operator. Variants on this value include join inner cumulative size of raw temp files (bytes).

cumulative size of temp files (bytes)

For externalizing operators only, the total number of encoded and compressed temp data the operator has written to files. A sort operator might go through multiple merge phases, where at each pass sorted chunks of data are merged into fewer chunks. This counter remembers the cumulative size of all temp files past and present. Variants on this value include join inner cumulative size of temp files (bytes).

current allocated rid memory (bytes)

Per-rid memory tracking: current allocation amount under this rid.

current file handles

Number of files open.

current memory allocations (count)

Number of actual allocator calls made.

current memory capacity (bytes)

Amount of system memory held, which includes chunks that are only partially consumed.

current memory overhead (bytes)

Memory consumed, for example, by debug headers. (Normally no overhead.)

current memory padding (bytes)

Memory padding for free list tiers (2^n bytes).

current memory requested (bytes)

Memory actually requested by the caller.

current size of temp files (bytes)

For externalizing operators only, the current size of the encoded and compressed temp data that the operator has written to files. Variants on this value include join inner current size of temp files (bytes).

current threads

Unused.

current unbalanced memory allocations (count)

current unbalanced memory capacity (bytes)

current unbalanced memory overhead (bytes)

current unbalanced memory requested (bytes)

Pooled version of "current memory XXX" counters.

distinct value estimation time (µs)

[Analyze Statistics] Time (in microcseconds) spent to estimate number of distinct values from the sample after data is read off disk and into the statistical sample.

encoded bytes received

[NetworkRecv] Size of received data after decompressed (but still encoded) received over the network.

encoded bytes sent

[NetworkSend] Size of data sent over the network after encoding.

end time

Time (timestamp) when Vertica stopped processing the operation

estimated rows produced

Number of rows that the optimizer estimated would be produced. See rows produced for the actual number of rows that are produced.

exceptions cumulative size of raw temp data (bytes)

exceptions rows cumulative size of temp files (bytes)

exceptions rows current size of temp files (bytes)

Counters that store the total or current size of exception data.

execution time (µs)

CPU clock time spent processing the query, in microseconds.

fast aggregated rows

Number of rows being processed by fast aggregations in the hash groupby operator (no group/aggregation).

files completed

Relevant only to SendFiles/RecvFiles operators (that is, recover-by-container plan) number of files sent/received.

files total

Relevant only to SendFiles/RecvFiles operators (that is, recover-by-container plan) total number of files to send/receive.

Hadoop FS bytes read through native libhdfs++ client

[Scan, Load] Number of bytes read from an hdfs source (using libhdfs++).

Hadoop FS bytes read through webhdfs

[Scan, Load] Number of bytes read from a webhdfs source.

Hadoop FS bytes written through webhdfs

[DataTarget] Number of bytes written to webhdfs storage.

Hadoop FS hdfs:// operations that used native libhdfs++ calls

[Scan, Load, DataTarget] Number of times Vertica opened a file with an hdfs:// URL and used the native hdfs protocol

Hadoop FS hdfs:// operations that used webhdfs calls

[Scan, Load, DataTarget] Number of times Vertica opened a file with an hdfs:// URL and used the webhdfs protocol

Hadoop FS read operations through native libhdfs++ client failure count

[Scan, Load] Number of times a native libhdfs++ source encountered an error and gave up

Hadoop FS read operations through native libhdfs++ client retry count

[Scan, Load] Number of times a native libhdfs++ source encountered an error and retried

Hadoop FS read operations through webhdfs failure count

[Scan, Load] Number of times a webhdfs source encountered an error and gave up

Hadoop FS read operations through webhdfs retry count

[Scan, Load] Number of times a webhdfs source encountered an error and retried

Hadoop FS write operations through webhdfs failure count

[DataTarget] Number of times a webhdfs write encountered an error and gave up

Hadoop FS write operations through webhdfs retry count

[DataTarget] Number of times a webhdfs write encountered an error and retried

histogram creation time(µs)

[Analyze Statistics] Time spent estimating the number of distinct values from the sample after data is read off disk and into the statistical sample.

initialization time (µs)

Time in microseconds spent initializing an operator during the CompilePlan step of query processing. For example, initialization time could include the time spent compiling expressions and gathering resources.

input queue wait (µs)

Time in microseconds that an operator spends waiting for upstream operators.

input rows

Actual number of rows that were read into the operator.

input size (bytes)

Total number of bytes of the Load operator's input source, where NULL is unknown (read from FIFO).

inputs processed

Number of sources processed by a Load operator.

intermediate rows to process

Number of rows to process in a phase as determined by a sort or GROUP BY (HASH).

join inner clock time (µs)

Real clock time spent on processing the inner input of the join operator.

join inner completed mergephases

join inner cumulative size of raw temp data (bytes)

join inner cumulative size of temp files (bytes)

join inner current size of temp files (bytes)

See the completed merge phases counter.

join inner execution time (µs)

The CPU clock time spent on processing the inner input of the join operator.

join inner hash table building time (µs)

Time spent for building the hash table for the inner input of the join operator.

join inner hash table collisions

Number of hash table collisions that occurred when building the hash table for the inner input of the join operator.

join inner hash table entries

Number of hash table entries for the inner input of the join operator.

join inner total merge phases

See the completed merge phases counter.

join outer clock time (µs)

Real clock time spent on processing the outer input of the join operator (including doing the join).

join outer execution time (µs)

CPU clock time spent on processing the outer input of the join operator (including doing the join).

max sample size (rows)

[Analyze Statistics] Maximum number of rows that will be stored in the statistical sample.

memory reserved (bytes)

Memory reserved by this operator. Deprecated.

network wait (µs)

[NetworkSend, NetworkRecv] Time in microseconds spent waiting on the network.

number of bytes read from persistent storage

Estimated number of bytes read from persistent storage to process this query.

number of bytes read from depot storage

Estimated number of bytes read from the depot to process this query.

number of cancel requests received

Number of cancel requests received (per operator) when cancelling a call to the execution engine.

number of invocations

Number of times a UDSF function was invoked.

number of storage containers opened

[Scan] Number of containers opened by the operator, at least 1. If the scan operator switches containers, this counter increases accordingly. See Local caching of storage containers for details.

output queue wait (µs)

Time in microseconds that an operator spends waiting for the output buffer to be consumed by a downstream operator.

peak allocated rid memory (bytes)

Per-rid memory tracking: peak allocation amount under this rid.

peak cooperating threads

Peak number of threads which parsed (in parallel) a single load source, using "cooperative parse." counter_tag indicates the source when joining with dc_load_events.

peak file handles

peak memory allocations (count)

peak memory capacity (bytes)

peak memory overhead (bytes)

peak memory padding (bytes)

peak memory requested (bytes)

peak temp space

peak threads

peak unbalanced memory allocations (count)

peak unbalanced memory capacity (bytes)

peak unbalanced memory overhead (bytes)

peak unbalanced memory padding (bytes)

peak unbalanced memory requested (bytes)

Peak value of the corresponding "current XXX" counters.

portion offset

Offset value of a portion descriptor in an apportioned load. counter_tag indicates the source when joining with dc_load_events.

portion size

Size value of a portion descriptor in an apportioned load. counter_tag indicates the source when joining with dc_load_events.

producer stall (µs)

[NetworkSend] Time in microseconds spent by NetworkSend when stalled waiting for network buffers to clear.

producer wait (µs)

[NetworkSend] Time in microseconds spent by the input operator making rows to send.

read (bytes)

Number of bytes read from the input source by the Load operator.

receive time (µs)

Time in microseconds that a Recv operator spends reading data from its socket.

rejected data cumulative size of raw temp data (bytes)

rejected data cumulative size of temp files (bytes)

rejected data current sizeof temp files (bytes)

rejected rows cumulative size of raw temp data (bytes)

rejected rows cumulative size of temp files (bytes)

rejected rows current size of temp files (bytes)

Counters that store total or current size of rejected row numbers. Are variants of:

cumulative size of raw temp data (bytes)

cumulative size of temp files (bytes)

current size of temp files (bytes)

reserved rid memory (bytes)

Per-rid memory tracking: total memory reservation under this rid.

rle rows produced

Number of physical tuples produced by an operator. Complements the rows produced counter, which shows the number of logical rows produced by an operator. For example, if a value occurs 1000 rows consecutively and is RLE encoded, it counts as 1000 rows produced not only 1 rle rows produced.

ROS blocks bounded

[DataTarget] Number of ROS blocks created, due to boundary alignment with RLE prefix columns, when an EE DataTarget operator is writing to ROS containers.

ROS blocks encoded

[DataTarget] Number of ROS blocks created when an EE DataTarget operator is writing to ROS containers.

ROS bytes written

[DataTarget] Number of bytes written to disk when an EE DataTarget operator is writing to ROS containers.

rows added by predicate analysis

Number of rows in the query results that were added without individual evaluation, based on the predicate and range of possible results in a block.

rows filtered by SIPs expression

Number of rows filtered by the SIPS expression from the Scan operator.

rows filtered by query predicate

Number of rows excluded from query results because they failed a condition (predicate), for example in a WHERE clause.

rows in sample

[Analyze Statistics] Actual number of rows that will be stored in the statistical sample.

rows output by sort

[DataTarget] Number of rows sorted when an EE DataTarget operator is writing to ROS containers.

rows processed

[DataSource] Number of rows processed when an EE DataSource operator is reading from ROS containers.

rows processed by SIPs expression

Number of rows processed by the SIPS expression in the Scan operator.

rows produced

Number of logical rows produced by an operator. See also the rle rows produced counter.

rows pruned by query predicates

Number of rows discarded from query results because, based on predicates and value ranges, no row in the block could satisfy the predicate.

rows pruned by valindex

[DataSource] Number of rows it skips direct scanning with help of valindex when an EE DataSource operator is writing to ROS containers. This counter's value is not greater than "rows processed" counter.

rows read in sort

See the counter total rows read in sort.

rows received

[NetworkRecv] Number of received sent over the network.

rows rejected

Number of rows rejected by the Load operator.

rows sent

[NetworkSend] Number of rows sent over the network.

rows to process

Total number of rows to be processed in a phase, based upon the number of table accesses. Compare to the counter, rows processed. Divide the rows processed value by the rows to process value for percent completion.

rows written in join sort

Total number of rows being read out of the sort facility in Join.

rows written in sort

Number of rows read out of the sort by the SortManager. This counter and the counter total rows read from sort are typically equal.

send time (µs)

Time in microseconds that a Send operator spends writing data to its socket.

start time

Time (timestamp) when Vertica started to process the operation.

total merge phases

Number of merge phases an LSort or DataTarget operator must complete to finish sorting its data. NULL until the operator can compute this value (all data must first be ingested by the operator). Variants on this value include join inner total merge phases.

total rows read in join sort

Total number of rows being put into the sort facility in Join.

total rows read in sort total

Total number of rows ingested into the sort by the SortManager. This counter and the counter rows written in sort are typically equal.

total rows written in sort

See the counter, rows written in sort.

total sources

Total number of distinct input sources processed in a load.

unpacked (bytes)

Number of bytes produced by a compressed source in a load (for example, for a gzip file, the size of the file when decompressed).

wait clock time (µs)

StorageUnion wait time in microseconds.

written rows

[DataTarget] Number of rows written when an EE DataTarget operator writes to ROS containers

Examples

The two queries below show the contents of the EXECUTION_ENGINE_PROFILES table:

=> SELECT operator_name, operator_id, counter_name, counter_value
    FROM EXECUTION_ENGINE_PROFILES WHERE operator_name = 'Scan'
    ORDER BY counter_value DESC;
 operator_name | operator_id | counter_name |  counter_value
---------------+-------------+--------------+------------------
 Scan          |          20 | end time     | 1559929719983785
 Scan          |          20 | start time   | 1559929719983737
 Scan          |          18 | end time     | 1559929719983358
 Scan          |          18 | start time   | 1559929718069860
 Scan          |          16 | end time     | 1559929718069319
 Scan          |          16 | start time   | 1559929718069188
 Scan          |          14 | end time     | 1559929718068611
 Scan          |          18 | end time     | 1559929717579145
 Scan          |          18 | start time   | 1559929717579083
 Scan          |          16 | end time     | 1559929717578509
 Scan          |          18 | end time     | 1559929717379346
 Scan          |          18 | start time   | 1559929717379307
 Scan          |          16 | end time     | 1559929717378879
 Scan          |          16 | start time   | 1559929716894312
 Scan          |          14 | end time     | 1559929716893599
 Scan          |          14 | start time   | 1559929716893501
 Scan          |          12 | end time     | 1559929716892721
 Scan          |          16 | start time   | 1559929716666110
...

=> SELECT DISTINCT counter_name FROM execution_engine_profiles;
                    counter_name
-----------------------------------------------------
 reserved rid memory (bytes)
 rows filtered by SIPs expression
 rows output by sort
 chunk rows scanned squared
 join inner execution time (us)
 current unbalanced memory requested (bytes)
 clock time (us)
 join outer clock time (us)
 exception handling execution time (us)
 peak memory capacity (bytes)
 bytes received
 peak memory requested (bytes)
 send time (us)
 ROS blocks encoded
 current size of temp files (bytes)
 peak memory allocations (count)
 current unbalanced memory overhead (bytes)
 rows segmented
...

The following query includes the path_id column, which links the path that the query optimizer takes (via the EXPLAIN command's textual output) with join error messages.

=> SELECT operator_name, path_id, counter_name, counter_value FROM execution_engine_profiles where operator_name = 'Join';
 operator_name | path_id |                    counter_name                     |  counter_value
---------------+---------+-----------------------------------------------------+------------------
 Join          |      64 | current memory allocations (count)                  |                0
 Join          |      64 | peak memory allocations (count)                     |               57
 Join          |      64 | current memory requested (bytes)                    |                0
 Join          |      64 | peak memory requested (bytes)                       |          1698240
 Join          |      64 | current memory overhead (bytes)                     |                0
 Join          |      64 | peak memory overhead (bytes)                        |                0
 Join          |      64 | current memory padding (bytes)                      |                0
 Join          |      64 | peak memory padding (bytes)                         |           249840
 Join          |      64 | current memory capacity (bytes)                     |                0
 Join          |      64 | peak memory capacity (bytes)                        |           294912
 Join          |      64 | current unbalanced memory allocations (count)       |              145
 Join          |      64 | peak unbalanced memory allocations (count)          |              146
 Join          |      64 | current unbalanced memory requested (bytes)         |           116506
 Join          |      64 | peak unbalanced memory requested (bytes)            |          1059111
 Join          |      64 | current unbalanced memory overhead (bytes)          |             3120
 Join          |      64 | peak unbalanced memory overhead (bytes)             |             3120
...

45 - EXTERNAL_TABLE_DETAILS

Returns the amount of disk storage used by the source files backing external tables in the database.

Returns the amount of disk storage used by the source files backing external tables in the database. The information in this system table is useful in determining Hadoop license compliance.

When computing the size of an external table, Vertica counts all data found in the location specified by the COPY FROM clause. If you have a directory that contains ORC and delimited files, for example, and you define your external table with "COPY FROM *" instead of "COPY FROM *.orc", this table includes the size of the delimited files. (You would probably also encounter errors when querying that external table.) When you query this system table Vertica does not validate your table definition; it just uses the path to find files to report.

Restrict your queries to filter by schema, table, or format to avoid expensive queries. Vertica calculates the values in this table at query time, so "SELECT *" accesses every input file contributing to every external table.

Predicates in queries may use only the TABLE_SCHEMA, TABLE_NAME, and SOURCE_FORMAT columns. Values are case-sensitive.

This table includes TEMP external tables.

This table reports only data that the current user can read. To include all the data backing external tables, either query this table as a user that has access to all HDFS data or use a session delegation token that grants this access. For more information about using delegation tokens, see Accessing kerberized HDFS data.

Column Name	Data Type	Description
`SCHEMA_OID`	INTEGER	The unique identification number of the schema in which the external table resides.
`TABLE_NAMESPACE`	VARCHAR	For Eon Mode databases, namespace that contains the external table.
`TABLE_SCHEMA`	VARCHAR	The name of the schema in which the external table resides. If the schema belongs to a non-default namespace in an Eon Mode database, the schema name is front-qualified with the name of the schema's namespace. For example, `n1.s` refers to schema `s` in namespace `n1`.
`TABLE_OID`	INTEGER	A unique numeric ID assigned by the Vertica catalog that identifies the table.
`TABLE_NAME`	VARCHAR	The table name.
`SOURCE_FORMAT`	VARCHAR	The data format the source file used, one of ORC, PARQUET, DELIMITED, USER DEFINED, or NULL if another format.
`TOTAL_FILE_COUNT`	INTEGER	The number of files used to store this table's data, expanding globs and partitions.
`TOTAL_FILE_SIZE_BYTES`	INTEGER	Total number of bytes used by all of this table's data files.
`SOURCE_STATEMENT`	VARCHAR	The load statement used to copy data from the source files.
`FILE_ACCESS_ERROR`	VARCHAR	The access error returned during the source statement. NULL, if there was no access error during the source statement.

46 - HEALTH_WATCHDOG_BLOCKED_TRANSACTIONS

Provides detailed information about transactions blocked by the Heath Watchdog.

Column Name	Data Type	Description
`NODE_NAME`	VARCHAR	Name of the node.
`BLOCK_START_TIME`	TIMESTAMP	Time when the transaction was blocked.
`TRANSACTION_DESCRIPTION`	VARCHAR	The description of the transaction that was blocked.
`BLOCK_TYPE`	VARCHAR	Lists the health metric that initiated the block.

Privileges

None

Example

verticadb21249=> select * from health_watchdog_blocked_transactions;
 node_name |       block_start_time        |                          transaction_description                          |    block_type   
-----------+-------------------+-------------------------------+---------------------------------------------------------------------------+------------------
 initiator | 2024-06-17 22:24:08.636447-04 | Txn: a00000000000de 'create table table_t2(b int, c varchar(10), a int);' | GCLX QUEUE BLOAT
 initiator | 2024-06-17 22:28:43.340537-04 | Txn: a00000000000e9 'create table table_t3(b int, c varchar(10), a int);' | GCLX QUEUE BLOAT
(2 rows)

47 - HIVE_CUSTOM_PARTITIONS_ACCESSED

This table provides information about all custom locations for Hive partition data that Vertica has accessed.

This table provides information about all custom locations for Hive partition data that Vertica has accessed. It applies when Hive uses a non-default location for partition data, the HCatalog Connector is used to access that data, and the CREATE HCATALOG SCHEMA statement for the schema sets the CUSTOM_PARTITIONS parameter.

Column Name	Data Type	Description
`ACCESS_TIME`	TIMESTAMPTZ	Time when Vertica accessed the partition data.
`ACCESS_NODE`	VARCHAR(128)	Name of the node that performed the access.
`TRANSACTION_ID`	INTEGER	Identifier for the query that produced the access.
`FILESYSTEM`	VARCHAR(128)	File system of the partition data. This value is the scheme portion of the URL.
`AUTHORITY`	VARCHAR(128)	If the file system is HDFS, this value is the nameservice. If the file system is S3, it is the name of the bucket.
`URL`	VARCHAR(6400)	Full path to the partition.

Privileges

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

48 - HOST_RESOURCES

Provides a snapshot of the node.

Provides a snapshot of the node. This is useful for regularly polling the node with automated tools or scripts.

Column Name	Data Type	Description
`HOST_NAME`	VARCHAR	The host name for which information is listed.
`OPEN_FILES_LIMIT`	INTEGER	The maximum number of files that can be open at one time on the node.
`THREADS_LIMIT`	INTEGER	The maximum number of threads that can coexist on the node.
`CORE_FILE_LIMIT_MAX_SIZE_BYTES`	INTEGER	The maximum core file size allowed on the node.
`PROCESSOR_COUNT`	INTEGER	The number of system processors.
`PROCESSOR_CORE_COUNT`	INTEGER	The number of processor cores in the system.
`PROCESSOR_DESCRIPTION`	VARCHAR	A description of the processor. For example: Inter(R) Core(TM)2 Duo CPU T8100 @2.10GHz (1 row)
`OPENED_FILE_COUNT`	INTEGER	The total number of open files on the node.
`OPENED_SOCKET_COUNT`	INTEGER	The total number of open sockets on the node.
`OPENED_NONFILE_NONSOCKET_COUNT`	INTEGER	The total number of other file descriptions open in which 'other' could be a directory or FIFO. It is not an open file or socket.
`TOTAL_MEMORY_BYTES`	INTEGER	The total amount of physical RAM, in bytes, available on the system.
`TOTAL_MEMORY_FREE_BYTES`	INTEGER	The amount of physical RAM, in bytes, left unused by the system.
`TOTAL_BUFFER_MEMORY_BYTES`	INTEGER	The amount of physical RAM, in bytes, used for file buffers on the system
`TOTAL_MEMORY_CACHE_BYTES`	INTEGER	The amount of physical RAM, in bytes, used as cache memory on the system.
`TOTAL_SWAP_MEMORY_BYTES`	INTEGER	The total amount of swap memory available, in bytes, on the system.
`TOTAL_SWAP_MEMORY_FREE_BYTES`	INTEGER	The total amount of swap memory free, in bytes, on the system.
`DISK_SPACE_FREE_MB`	INTEGER	The free disk space available, in megabytes, for all storage location file systems (data directories).
`DISK_SPACE_USED_MB`	INTEGER	The disk space used, in megabytes, for all storage location file systems.
`DISK_SPACE_TOTAL_MB`	INTEGER	The total free disk space available, in megabytes, for all storage location file systems.

Examples

=> SELECT * FROM HOST_RESOURCES;
-[ RECORD 1 ]------------------+------------------------------------------
host_name                      | 10.20.100.247
open_files_limit               | 65536
threads_limit                  | 3833
core_file_limit_max_size_bytes | 0
processor_count                | 2
processor_core_count           | 2
processor_description          | Intel(R) Xeon(R) CPU E5-2695 v2 @ 2.40GHz
opened_file_count              | 8
opened_socket_count            | 15
opened_nonfile_nonsocket_count | 7
total_memory_bytes             | 4018823168
total_memory_free_bytes        | 126550016
total_buffer_memory_bytes      | 191803392
total_memory_cache_bytes       | 2418753536
total_swap_memory_bytes        | 2147479552
total_swap_memory_free_bytes   | 2145771520
disk_space_free_mb             | 18238
disk_space_used_mb             | 30013
disk_space_total_mb            | 48251
-[ RECORD 2 ]------------------+------------------------------------------
host_name                      | 10.20.100.248
open_files_limit               | 65536
threads_limit                  | 3833
core_file_limit_max_size_bytes | 0
processor_count                | 2
processor_core_count           | 2
processor_description          | Intel(R) Xeon(R) CPU E5-2695 v2 @ 2.40GHz
opened_file_count              | 8
opened_socket_count            | 9
opened_nonfile_nonsocket_count | 7
total_memory_bytes             | 4018823168
total_memory_free_bytes        | 356466688
total_buffer_memory_bytes      | 327278592
total_memory_cache_bytes       | 2744279040
total_swap_memory_bytes        | 2147479552
total_swap_memory_free_bytes   | 2147479552
disk_space_free_mb             | 37102
disk_space_used_mb             | 11149
disk_space_total_mb            | 48251
-[ RECORD 3 ]------------------+------------------------------------------
host_name                      | 10.20.100.249
open_files_limit               | 65536
threads_limit                  | 3833
core_file_limit_max_size_bytes | 0
processor_count                | 2
processor_core_count           | 2
processor_description          | Intel(R) Xeon(R) CPU E5-2695 v2 @ 2.40GHz
opened_file_count              | 8
opened_socket_count            | 9
opened_nonfile_nonsocket_count | 7
total_memory_bytes             | 4018823168
total_memory_free_bytes        | 241610752
total_buffer_memory_bytes      | 309395456
total_memory_cache_bytes       | 2881675264
total_swap_memory_bytes        | 2147479552
total_swap_memory_free_bytes   | 2147479552
disk_space_free_mb             | 37430
disk_space_used_mb             | 10821
disk_space_total_mb            | 48251

49 - IO_USAGE

Provides disk I/O bandwidth usage history for the system.

Column Name	Data Type	Description
`NODE_NAME`	VARCHAR	Name of the node that is reporting the requested information.
`START_TIME`	TIMESTAMP	Beginning of history interval.
`END_TIME`	TIMESTAMP	End of history interval.
`READ_KBYTES_PER_SEC`	FLOAT	Counter history of the number of bytes read measured in kilobytes per second.
`WRITTEN_KBYTES_PER_SEC`	FLOAT	Counter history of the number of bytes written measured in kilobytes per second.

Privileges

Superuser

50 - LDAP_LINK_DRYRUN_EVENTS

Collects the results from LDAP dry run meta-functions:.

Collects the results from LDAP dry run meta-functions:

For detailed instructions on using these meta-functions, see Configuring LDAP link with dry runs.

Column Name	Data Type	Description
EVENT_TIMESTAMP	TIMESTAMP	The date and time of an LDAP server and Vertica LDAP Link interaction.
NODE_NAME	VARCHAR	The clerk node.
SESSION_ID	VARCHAR	The identification number of the LDAP Link session.
USER_ID	INTEGER	The unique, system-generated user identification number.
USER_NAME	VARCHAR	The name of the user for which the information is listed.
TRANSACTION_ID	INTEGER	The system-generated transaction identification number. Is NULL if a transaction id does not exist.
EVENT_TYPE	VARCHAR	The result of a dry run.
ENTRY_NAME	VARCHAR	The name of the object on which the event occurred, if applicable. For example, the event SYNC-STARTED does not use an object.
ROLE_NAME	VARCHAR	The name of a role.
LDAPURIHASH	INTEGER	The URI hash number for the LDAP user.
LDAP_URI	VARCHAR	The URI for the LDAP server.
BIND_DN	VARCHAR	The Distinguished Name used for the dry run bind.
FILTER_GROUP	VARCHAR	The group attribute passed to the dry run meta-functions as LDAPLinkFilterGroup.
FILTER_USER	VARCHAR	The user attribute passed to the dry run meta-functions as LDAPLinkFilterUser.
LINK_SCOPE	VARCHAR	The DN level to replicate, passed to the dry run meta-functions as LDAPLinkScope.
SEARCH_BASE	VARCHAR	The DN level from which LDAP Link begins the search, passed to the dry run meta-functions as LDAPLinkSearchBase.
GROUP_MEMBER	VARCHAR	Identifies the members of an LDAP group, passed to the dry run meta-functions as LDAPLinkGroupMembers.
GROUP_NAME	VARCHAR	The LDAP field to use when creating a role name in Vertica, passed to the dry run meta-functions as LDAPLinkGroupName.
LDAP_USER_NAME	VARCHAR	The attribute that identifies individual users, passed to the dry run meta-functions as LDAPLinkUserName.
TLS_REC_CERT	VARCHAR	The connection policy used for the dry run connection for certificate management. This connection policy is set through the LDAPLink TLS Configuration.
TLS_CA_CERT	VARCHAR	The CA certificate used for the dry run connection specified by the LDAPLink TLS Configuration.

51 - LDAP_LINK_EVENTS

Monitors events that occurred during an LDAP Link synchronization.

Column Name	Data Type	Description
`EVENT_TIMESTAMP`	TIMESTAMP	The time the event occurred.
`NODE_NAME`	VARCHAR	The name of the node or nodes for which the information is listed.
`SESSION_ID`	VARCHAR	The identification number of the LDAP Link session.
`USER_ID`	INTEGER	The unique, system-generated user identification number.
`USER_NAME`	VARCHAR	The name of the user for which the information is listed.
`TRANSACTION_ID`	INTEGER	The system-generated transaction identification number. Is NULL if a transaction id does not exist.
`EVENT_TYPE`	VARCHAR	The type of event being logged, for example USER_CREATED and PROCESSING_STARTED.
`ENTRY_NAME`	VARCHAR	The name of the object on which the event occurred, if applicable. For example, the event SYNC-STARTED does not use an object.
`ENTRY_OID`	INTEGER	The unique identification number for the object on which the event occurred, if applicable.
`LDAPURIHASH`	INTEGER	The URI hash number for the LDAP user.

52 - LOAD_SOURCES

Like LOAD_STREAMS, monitors active and historical load metrics on each node.

Like LOAD_STREAMS, monitors active and historical load metrics on each node. The LOAD_SOURCES table breaks information down by source and portion. Rows appear in this table only for COPY operations that are profiled or run for more than one second. LOAD_SOURCES does not record information about loads from ORC or Parquet files or COPY LOCAL.

A row is added to this table when the loading of a source or portion begins. Column values related to the progress of the load are updated during the load operation.

Columns that uniquely identify the load source (the various ID and name columns) and column IS_EXECUTING always have non-NULL values.

Column Name	Data Type	Description
SESSION_ID	VARCHAR	Identifier of the session for which Vertica captures load stream information. This identifier is unique within the cluster for the current session but can be reused in a subsequent session.
TRANSACTION_ID	INTEGER	Identifier for the transaction within a session. If a session is active, but no transaction has begun, this value is NULL.
STATEMENT_ID	INTEGER	Unique numeric ID for the currently running statement. NULL indicates that no statement is currently being processed. The combination of TRANSACTION_ID, STATEMENT_ID uniquely identifies a statement within a session.
STREAM_NAME	VARCHAR	Load stream identifier. If the user does not supply a specific name, the STREAM_NAME default value is `tablename`-`ID`, where: `tablename` is the table into which data is being loaded. `ID` is an integer value. `ID` is guaranteed to be unique within the current session on a node. This system table includes stream names for every COPY statement that takes more than 1 second to run. The 1-second duration includes the time to plan and execute the statement.
SCHEMA_NAME	VARCHAR	Schema name for which load information is listed. Lets you identify two streams that are targeted at tables with the same name in different schemas. NULL, if selecting from an external table.
TABLE_OID	INTEGER	A unique numeric ID assigned by the Vertica catalog that identifies the table. NULL, if selecting from an external table.
TABLE_NAME	VARCHAR	Name of the table being loaded. NULL, if selecting from an external table.
NODE_NAME	VARCHAR	Name of the node loading the source.
SOURCE_NAME	VARCHAR	Full file path if copying from a file. Value returned by `getUri()` if the source is a user-defined source. STDIN if loading from standard input.
PORTION_OFFSET	INTEGER	Offset of the source portion, or NULL if not apportioned.
PORTION_SIZE	INTEGER	Size of the source portion, or NULL if not apportioned.
IS_EXECUTING	BOOLEAN	Whether this source is currently being parsed, where t is true and f is false.
READ_BYTES	INTEGER	Number of bytes read from the input file.
ROWS_PRODUCED	INTEGER	Number of rows produced from parsing the source.
ROWS_REJECTED	INTEGER	Number of rows rejected from parsing the source. If CopyFaultTolerantExpressions is true, also includes rows rejected during expression evaluation.
INPUT_SIZE	INTEGER	Size of the input source in bytes, or NULL for unsized sources. For UDSources, this value is the value returned by `getSize()`.
PARSE_COMPLETE_PERCENT	INTEGER	Percent of rows from the input file that have been parsed.
FAILURE_REASON	VARCHAR	Indicates cause for failure, one of the following: Load source aborted, error message indicates cause. For example: `COPY: Could not open file [``filename``] for reading; Permission denied` Load canceled, displays error message: `Statement interrupted` In all other cases, set to NULL.
PEAK_COOPERATING_THREADS	INTEGER	The peak number of threads parsing this source in parallel.
CLOCK_TIME_SOURCE	INTEGER	Displays in real-time how many microseconds (µs) have been consumed by the UDSource phase of a load operation.
CLOCK_TIME_FILTERS	INTEGER	Displays in real-time how many microseconds (µs) have been consumed by all UDFilter phases of a load operation.
CLOCK_TIME_CHUNKER	INTEGER	Displays in real-time how many microseconds (µs) have been consumed by the UDChunker phase of a load operation.
CLOCK_TIME_PARSER	INTEGER	Displays in real-time how many microseconds (µs) have been consumed by the UDParser phase of a load operation.

Privileges

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

53 - LOAD_STREAMS

Monitors active and historical load metrics for load streams.

Monitors active and historical load metrics for load streams. This is useful for obtaining statistics about how many records got loaded and rejected from the previous load. Vertica maintains system table metrics until they reach a designated size quota (in kilobytes). This quota is set through internal processes, which you cannot set or view directly.

Column Name	Data Type	Description
`SESSION_ID`	VARCHAR	Identifier of the session for which Vertica captures load stream information. This identifier is unique within the cluster for the current session, but can be reused in a subsequent session.
`TRANSACTION_ID`	INTEGER	Identifier for the transaction within a session. If a session is active but no transaction has begun, this is NULL.
`STATEMENT_ID`	INTEGER	Unique numeric ID for the currently-running statement. NULL indicates that no statement is currently being processed. The combination of TRANSACTION_ID, STATEMENT_ID uniquely identifies a statement within a session.
`STREAM_NAME`	VARCHAR	Load stream identifier. If the user does not supply a specific name, the STREAM_NAME default value is: tablename-ID where tablename is the table into which data is being loaded, and ID is an integer value, guaranteed to be unique with the current session on a node. This system table includes stream names for every COPY statement that takes more than 1-second to run. The 1-second duration includes the time to plan and execute the statement.
`SCHEMA_NAME`	VARCHAR	Schema name for which load stream information is listed. Lets you identify two streams that are targeted at tables with the same name in different schemas
`TABLE_ID`	INTEGER	Catalog-assigned numeric value that uniquely identifies the table.
`TABLE_NAME`	VARCHAR	Name of the table being loaded.
`LOAD_START`	VARCHAR	Linux system time when the load started.
`LOAD_DURATION_MS`	NUMERIC(54,0)	Duration of the load stream in milliseconds.
`IS_EXECUTING`	BOOLEAN	Indicates whether the load is executing, where t is true and f is false.
`ACCEPTED_ROW_COUNT`	INTEGER	Number of rows loaded.
`REJECTED_ROW_COUNT`	INTEGER	Number of rows rejected.
`READ_BYTES`	INTEGER	Number of bytes read from the input file.
`INPUT_FILE_SIZE_BYTES`	INTEGER	Size of the input file in bytes. Note: When using STDIN as input, the input file size is zero (0).
`PARSE_COMPLETE_PERCENT`	INTEGER	Percent of rows from the input file that have been parsed.
`UNSORTED_ROW_COUNT`	INTEGER	Cumulative number rows not sorted across all projections. Note: `UNSORTED_ROW_COUNT` could be greater than `ACCEPTED_ROW_COUNT` because data is copied and sorted for every projection in the target table.
`SORTED_ROW_COUNT`	INTEGER	Cumulative number of rows sorted across all projections.
`SORT_COMPLETE_PERCENT`	INTEGER	Percent of rows from the input file that have been sorted.

Privileges

If you have the SYSMONITOR role or are the dbadmin user, this table shows all loads. Otherwise it shows only your loads.

54 - LOCK_USAGE

Provides aggregate information about lock requests, releases, and attempts, such as wait time/count and hold time/count.

Provides aggregate information about lock requests, releases, and attempts, such as wait time/count and hold time/count. Vertica records:

Lock attempts at the end of the locking process
Lock releases after lock attempts are released

Column Name	Data Type	Description
`NODE_NAME`	VARCHAR	Name of the node that is reporting the requested information on which lock interaction occurs.
`SESSION_ID`	VARCHAR	Identifier for this session. This identifier is unique within the cluster at any point in time but can be reused when the session closes.
`OBJECT_NAME`	VARCHAR	Name of object being locked; can be a table or an internal structure (projection, global catalog, or local catalog).
`MODE`	VARCHAR	Intended operations of the transaction. Otherwise, this value is NONE. For a list of lock modes and compatibility, see Lock modes.
`AVG_HOLD_TIME`	INTERVAL	Average time (measured in intervals) that Vertica holds a lock.
`MAX_HOLD_TIME`	INTERVAL	Maximum time (measured in intervals) that Vertica holds a lock.
`HOLD_COUNT`	INTEGER	Total number of times the lock was granted in the given mode.
`AVG_WAIT_TIME`	INTERVAL	Average time (measured in intervals) that Vertica waits on the lock.
`MAX_WAIT_TIME`	INTERVAL	Maximum time (measured in intervals) that Vertica waits on a lock.
`WAIT_COUNT`	INTEGER	Total number of times lock was unavailable at the time it was first requested.

Privileges

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

55 - LOCKS

Monitors lock grants and requests for all nodes.

Monitors lock grants and requests for all nodes. If no locks are active, a query on LOCKS returns no rows.

Column Name	Data Type	Description
`NODE_NAMES`	VARCHAR	Comma-separated list of nodes where lock interaction occurs. A transaction can have the same lock in the same mode in the same scope on multiple nodes. However, the transaction gets only one line in the table.
`OBJECT_NAME`	VARCHAR	Name of object to lock, either a table or an internal structure: projection, global catalog, or local catalog.
`OBJECT_ID`	INTEGER	Unique numeric ID assigned by the Vertica catalog that identifies the object to lock.
`TRANSACTION_ID`	VARCHAR	Identifier of transaction within the session, if any; otherwise `NULL`. Transaction IDs can be used to join other system tables.
`TRANSACTION_DESCRIPTION`	VARCHAR	Identifier of transaction and associated description. Typically, this query caused the transaction's creation.
`LOCK_MODE`	VARCHAR	Transaction's lock type.
`LOCK_SCOPE`	VARCHAR	Expected duration of the lock after it is granted. Before the lock is granted, Vertica lists the scope as `REQUESTED`. After a lock is granted, its scope is set to one of the following: `STATEMENT_LOCALPLAN` `STATEMENT_COMPILE` `STATEMENT_EXECUTE` `TRANSACTION_POSTCOMMIT` `TRANSACTION` All scopes other than `TRANSACTION` are transient and used only as part of normal query processing.
`REQUEST_TIMESTAMP`	TIMESTAMP	Time when the transaction began waiting on the lock.
`GRANT_TIMESTAMP`	TIMESTAMP	Time the transaction acquired or upgraded the lock: Return values are NULL until the grant occurs. If the grant occurs immediately, values might be the same as `REQUEST_TIMESTAMP`.

56 - LOG_ROTATE_EVENTS

Lists the rotation events from do_logrotate_local.

Lists the rotation events from DO_LOGROTATE_LOCAL.

Column Name	Data Type	Description
`NODE_NAME`	VARCHAR	The name of the node from which logs were rotated.
`SUCCESS`	BOOLEAN	Whether the call to DO_LOGROTATE_LOCAL succeeded.
`MAX_SIZE`	INTEGER	The size, in bytes, set for LogRotateMaxSize.
`MAX_AGE`	INTEGER	The age, in days, set for LogRotateMaxAge.
`LOG_FILE`	VARCHAR	The path of the original log file.
`NEED_ROTATION`	BOOLEAN	Whether the `LOG_FILE` needed to be rotated.
`MESSAGE`	VARCHAR	The actions performed by DO_LOGROTATE_LOCAL for `LOG_FILE`.

Example

To view all successful rotation events:

=> SELECT * FROM log_rotate_events WHERE need_rotation='t' AND success='t';
-[ RECORD 1 ]-+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------
node_name     | v_vmart_node0001
success       | t
max_size      | 104857600
max_age       | 7
log_file      | /scratch_b/VMart/v_vmart_node0001_catalog/vertica.log
need_rotation | t
message       | /scratch_b/VMart/v_vmart_node0001_catalog/vertica.log was rotated to /scratch_b/VMart/v_vmart_node0001_catalog/vertica.log.2023-12-08-15-18-26-965729-05.gz due to force rotation
-[ RECORD 2 ]-+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------
node_name     | v_vmart_node0001
success       | t
max_size      | 104857600
max_age       | 7
log_file      | /scratch_b/VMart/v_vmart_node0001_catalog/UDxLogs/UDxFencedProcesses.log
need_rotation | t
message       | /scratch_b/VMart/v_vmart_node0001_catalog/UDxLogs/UDxFencedProcesses.log was rotated to /scratch_b/VMart/v_vmart_node0001_catalog/UDxLogs/UDxFencedProcesses.log.2023-12-15-13-18-27-23141-05.gz due to force rotation

57 - LOGIN_FAILURES

This system table lists failures for each failed login attempt.

This system table lists failures for each failed login attempt. This information helps you determine if a user is having difficulty getting into the database or identify a possible intrusion attempt.

Column Name	Data Type	Description
`LOGIN_TIMESTAMP`	TIMESTAMPTZ	Time when Vertica recorded the login.
`DATABASE_NAME`	VARCHAR	The name of the database for the login attempt.
`NODE_NAME`	VARCHAR	Name of the node that is reporting the requested information.
`USER_NAME`	VARCHAR	Name of the user whose login failed at the time Vertica recorded the session.
`CLIENT_HOSTNAME`	VARCHAR	Host name and port of the TCP socket from which the client connection was made. NULL if the session is internal.
`CLIENT_PID`	INTEGER	Identifier of the client process that issued this connection. In some cases, the client process is on a different machine from the server.
`CLIENT_VERSION`	VARCHAR	Unused.
`CLIENT_OS_USER_NAME`	VARCHAR	The name of the user that logged into, or attempted to log into, the database. This is logged even when the login attempt is unsuccessful.
`AUTHENTICATION_METHOD`	VARCHAR	Name of the authentication method used to validate the client application or user who is trying to connect to the server using the database user name provided Valid values: `Trust` `Reject` `GSS` `LDAP` `Ident` `Hash` `TLS` See Configuring client authentication for further information.
`CLIENT_AUTHENTICATION_NAME`	VARCHAR	Locally created name of the client authentication method.
`REASON`	VARCHAR	Description of login failure reason. Valid values: `INVALID USER` `ACCOUNT LOCKED` `REJECT` `FAILED` `INVALID AUTH METHOD` `INVALID DATABASE`

Privileges

Superuser

58 - MEMORY_EVENTS

Records events related to Vertica memory usage.

Column Name	Data Type	Description
`NODE_NAME`	VARCHAR	Node where the event occurred
`EVENT_TIME`	TIMESTAMPTZ	Event start time
`EVENT_TYPE`	VARCHAR	Type of event, one of the following: `MEMORY_REPORT`: The Vertica memory poller created a report on memory usage, for the reason specified in `EVENT_REASON`. For details, see Memory usage reporting. `MALLOC_TRIM`: Vertica ran the glibc function `malloc_trim()` to reclaim glibc-allocated memory. For details, see Memory trimming.
`EVENT_REASON`	VARCHAR	Reason for the event—for example, trim threshold was greater than `RSS` / `available-memory`.
`EVENT_DETAILS`	VARCHAR	Additional information about the event—for example, how much memory `malloc_trim()` reclaimed.
`DURATION_US`	INTEGER	Duration of the event in microseconds (µs).

Privileges

None

Examples

=> SELECT * FROM MEMORY_EVENTS;
-[ RECORD 1 ]-+-----------------------------------------------------------------
event_time    | 2019-05-02 13:17:20.700892-04
node_name     | v_vmart_node0001
event_type    | MALLOC_TRIM
event_reason  | memory_trim()
event_details | pre-trim RSS 378822656 post-trim RSS 372129792 benefit 0.0176675
duration_us   | 7724

59 - MEMORY_USAGE

Records system resource history for memory usage.

Records system resource history for memory usage. This is useful for comparing memory that Vertica uses versus memory in use by the entire system.

Column Name	Data Type	Description
`NODE_NAME`	VARCHAR	Name of the node that is reporting the requested information.
`START_TIME`	TIMESTAMP	Beginning of history interval.
`END_TIME`	TIMESTAMP	End of history interval.
`AVERAGE_MEMORY_USAGE_PERCENT`	FLOAT	Records the average memory usage in percent of total memory (0-100) during the history interval.

Privileges

Superuser

60 - MERGEOUT_PROFILES

Returns information about and status of automatic mergeout operations.

This table excludes operations with a REQUEST_TYPE of NO_WORK. It also excludes the operations of user-invoked mergeout functions, such as DO_TM_TASK.

Column Name	Data Type	Description
START_TIME	TIMESTAMP	When the Tuple Mover began processing storage location mergeout requests.
END_TIME	TIMESTAMP	When the mergeout finished.
TRANSACTION_ID	INTEGER	Identifier for the transaction within the session.
NODE_NAME	VARCHAR	Node name for which information is listed.
SCHEMA_NAME	VARCHAR	The schema for which information is listed.
TABLE_NAME	VARCHAR	The table for which information is listed.
PROJECTION_NAME	VARCHAR	The projection for which information is listed.
PROJECTION_OID	INTEGER	Projection's unique catalog identifier.
REQUEST_TYPE	VARCHAR	Identifies the type of operation performed by the tuple mover. Possible values: PURGE MERGEOUT DVMERGEOUT
EVENT_TYPE	VARCHAR	Displays the status of the mergeout operation. Possible values: ERROR RETRY REQUEST_QUEUED REQUEST_COMPLETED
THREAD_ID	INTEGER	The ID of the thread that performed the mergeout.
STRATA_NO	INTEGER	The ID of the strata the ROS container belongs to.
PARTITION_KEY	INTEGER	The key of the partition.
CONTAINER_COUNT	INTEGER	The number of ROS containers in the mergeout operation.
TOTAL_SIZE_IN_BYTES	INTEGER	Size in bytes of all ROS containers in the mergeout operation.

Privileges

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

Examples

To following statement returns failed mergeout operations for table public.store_orders.

=> SELECT node_name, schema_name, table_name, request_type, event_type FROM mergeout_profiles WHERE event_type='ERROR';
    node_name     | schema_name |  table_name  | request_type | event_type
------------------+-------------+--------------+--------------+------------
 v_vmart_node0002 | public      | store_orders | MERGEOUT     | ERROR
 v_vmart_node0002 | public      | store_orders | MERGEOUT     | ERROR
 v_vmart_node0001 | public      | store_orders | MERGEOUT     | ERROR
 v_vmart_node0001 | public      | store_orders | MERGEOUT     | ERROR
 v_vmart_node0003 | public      | store_orders | MERGEOUT     | ERROR
 v_vmart_node0003 | public      | store_orders | MERGEOUT     | ERROR
 v_vmart_node0003 | public      | store_orders | MERGEOUT     | ERROR
 v_vmart_node0002 | public      | store_orders | MERGEOUT     | ERROR
 v_vmart_node0001 | public      | store_orders | MERGEOUT     | ERROR
 (9 rows)

61 - MODEL_STATUS_HISTORY

Lists the status history of registered machine learning models in the database, including models that have been unregistered or dropped.

Lists the status history of registered machine learning models in the database, including models that have been unregistered or dropped. Only superusers or users to whom superusers have granted sufficient privileges can query the table. Vertica recommends granting access to the MLSUPERVISOR role.

Column Name	Data Type	Description
`REGISTERED_NAME`	VARCHAR	Abstract name to which the model, identified by `MODEL_ID`, was registered at the time of the status change. This `REGISTERED_NAME` can represent a group of models for a higher-level application, where each model in the group has a unique version number.
`REGISTERED_VERSION`	INTEGER	Unique version number of the registered model under its specified `REGISTERED_NAME`.
`NEW_STATUS`	VARCHAR	New status of the registered model.
`OLD_STATUS`	VARCHAR	Old status of the registered model.
`STATUS_CHANGE_TIME`	TIMESTAMPTZ	Time at which the model status was changed.
`OPERATOR_ID`	INTEGER	Internal ID of the user who performed the status change.
`OPERATOR_NAME`	VARCHAR	Name of the user who performed the status change.
`MODEL_ID`	INTEGER	Internal ID of the model for which information is listed.
`SCHEMA_NAME`	VARCHAR	Name of the schema that contains the model. This value is NULL if the model has been dropped.
`MODEL_NAME`	VARCHAR	Name of the model. This value is NULL if the model has been dropped. Each existing model can be uniquely identified by either its [`schema_name.`]`model_name` or the combination of its `REGISTERED_NAME` and `REGISTERED_VERSION`.

Example

If a superuser grants SELECT access of the table to the MLSUPERVISOR role, users with that role can then query the MODEL_STATUS_HISTORY table:

-- as superuser
=> GRANT SELECT ON TABLE v_monitor.model_status_history TO MLSUPERVISOR;
WARNING 8555:  You are granting privilege on a system table used by superuser only. Revoke the grant if you are unsure
GRANT PRIVILEGE

-- as user with MLSUPERVISOR role
=> SELECT * FROM MODEL_STATUS_HISTORY;
 registered_name | registered_version |  new_status  |  old_status  |       status_change_time      | operator_id | operator_name |  model_id  | schema_name |    model_name
-----------------+--------------------+--------------+------------- +-------------------------------+-------------+---------------+------------+-------------+-------------------
 app1            |                  1 | UNDER_REVIEW | UNREGISTERED | 2023-01-29 09:09:00.082166-05 | 1224567790  | u1            | 0113756739 | public      | native_linear_reg
 app1            |                  1 | STAGING      | UNDER_REVIEW | 2023-01-29 11:33:02.052464-05 | 2341679901  | supervisor1   | 0113756739 | public      | native_linear_reg
 app1            |                  1 | PRODUCTION   | STAGING      | 2023-01-30 04:12:30.481136-05 | 2341679901  | supervisor1   | 0113756739 | public      | native_linear_reg
(3 rows)

62 - MONITORING_EVENTS

Reports significant events that can affect database performance and functionality if you do not address their root causes.

See Monitoring events for details.

Column Name	Data Type	Description
`NODE_NAME`	VARCHAR	Name of the node that is reporting the requested information.
`EVENT_CODE`	INTEGER	Numeric identifier that indicates the type of event. See Event Types in Monitoring events for a list of event type codes.
`EVENT_ID`	INTEGER	Unique numeric ID that identifies the specific event.
`EVENT_SEVERITY`	VARCHAR	Severity of the event from highest to lowest. These events are based on standard syslog severity types: `0 – Emergency` `1 – Alert` `2 – Critical` `3 – Error` `4 – Warning` `5 – Notice` `6 – Info` `7 – Debug`
`EVENT_POSTED_TIMESTAMP`	TIMESTAMPTZ	When this event was posted.
`EVENT_CLEARED_TIMESTAMP`	TIMESTAMPTZ	When this event was cleared. Note: You can also query the ACTIVE_EVENTS system table to see events that have not been cleared.
`EVENT_EXPIRATION`	TIMESTAMPTZ	Time at which this event expires. If the same event is posted again prior to its expiration time, this field gets updated to a new expiration time.
`EVENT_CODE_DESCRIPTION`	VARCHAR	Brief description of the event and details pertinent to the specific situation.
`EVENT_PROBLEM_DESCRIPTION`	VARCHAR	Generic description of the event.

Privileges

Superuser

63 - NETWORK_INTERFACES

Provides information about network interfaces on all Vertica nodes.

Column Name	Data Type	Description
NODE_ID	INTEGER	Unique identifier for the node that recorded the row.
NODE_NAME	VARCHAR	Name of the node that is reporting the requested information.
INTERFACE	VARCHAR	Network interface name.
IP_ADDRESS_FAMILY	VARCHAR	Network address family (either 'ipv4' or 'ipv6').
IP_ADDRESS	VARCHAR	IP address for this interface.
SUBNET	VARCHAR	IP subnet for this interface.
MASK	VARCHAR	IP network mask for this interface.
BROADCAST_ADDRESS	VARCHAR	IP broadcast address for this interface.

Privileges

None

Examples

=> \x
Expanded display is on.
=> SELECT * FROM network_interfaces ORDER BY node_name ASC LIMIT 14;
-[ RECORD 1 ]-----+----------------------------------------
node_id           | 45035996273704982
node_name         | v_verticadb_node0001
interface         | lo
ip_address_family | ipv6
ip_address        | ::1
subnet            | ::1
mask              | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff
broadcast_address |
-[ RECORD 2 ]-----+----------------------------------------
node_id           | 45035996273704982
node_name         | v_verticadb_node0001
interface         | ens192
ip_address_family | ipv6
ip_address        | fd9b:1fcc:1dc4:78d3::31
subnet            | fd9b:1fcc:1dc4:78d3::
mask              | ffff:ffff:ffff:ffff::
broadcast_address |
-[ RECORD 3 ]-----+----------------------------------------
node_id           | 45035996273704982
node_name         | v_verticadb_node0001
interface         | lo
ip_address_family | ipv4
ip_address        | 127.0.0.1
subnet            | 127.0.0.0
mask              | 255.0.0.0
broadcast_address | 127.0.0.1
-[ RECORD 4 ]-----+----------------------------------------
node_id           | 45035996273704982
node_name         | v_verticadb_node0001
interface         | ens192
ip_address_family | ipv4
ip_address        | 192.168.111.31
subnet            | 192.168.111.0
mask              | 255.255.255.0
broadcast_address | 192.168.111.255
-[ RECORD 5 ]-----+----------------------------------------
node_id           | 45035996273704982
node_name         | v_verticadb_node0001
interface         | ens32
ip_address_family | ipv4
ip_address        | 10.20.110.21
subnet            | 10.20.110.0
mask              | 255.255.255.0
broadcast_address | 10.20.110.255
-[ RECORD 6 ]-----+----------------------------------------
node_id           | 45035996273704982
node_name         | v_verticadb_node0001
interface         | ens32
ip_address_family | ipv6
ip_address        | fe80::250:56ff:fe8e:61d3
subnet            | fe80::
mask              | ffff:ffff:ffff:ffff::
broadcast_address |
-[ RECORD 7 ]-----+----------------------------------------
node_id           | 45035996273704982
node_name         | v_verticadb_node0001
interface         | ens192
ip_address_family | ipv6
ip_address        | fe80::250:56ff:fe8e:2721
subnet            | fe80::
mask              | ffff:ffff:ffff:ffff::
broadcast_address |
-[ RECORD 8 ]-----+----------------------------------------
node_id           | 45035996273841968
node_name         | v_verticadb_node0002
interface         | lo
ip_address_family | ipv6
ip_address        | ::1
subnet            | ::1
mask              | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff
broadcast_address |
-[ RECORD 9 ]-----+----------------------------------------
node_id           | 45035996273841968
node_name         | v_verticadb_node0002
interface         | ens192
ip_address_family | ipv6
ip_address        | fd9b:1fcc:1dc4:78d3::32
subnet            | fd9b:1fcc:1dc4:78d3::
mask              | ffff:ffff:ffff:ffff::
broadcast_address |
-[ RECORD 10 ]----+----------------------------------------
node_id           | 45035996273841968
node_name         | v_verticadb_node0002
interface         | lo
ip_address_family | ipv4
ip_address        | 127.0.0.1
subnet            | 127.0.0.0
mask              | 255.0.0.0
broadcast_address | 127.0.0.1
-[ RECORD 11 ]----+----------------------------------------
node_id           | 45035996273841968
node_name         | v_verticadb_node0002
interface         | ens192
ip_address_family | ipv4
ip_address        | 192.168.111.32
subnet            | 192.168.111.0
mask              | 255.255.255.0
broadcast_address | 192.168.111.255
-[ RECORD 12 ]----+----------------------------------------
node_id           | 45035996273841968
node_name         | v_verticadb_node0002
interface         | ens32
ip_address_family | ipv4
ip_address        | 10.20.110.22
subnet            | 10.20.110.0
mask              | 255.255.255.0
broadcast_address | 10.20.110.255
-[ RECORD 13 ]----+----------------------------------------
node_id           | 45035996273841968
node_name         | v_verticadb_node0002
interface         | ens32
ip_address_family | ipv6
ip_address        | fe80::250:56ff:fe8e:1787
subnet            | fe80::
mask              | ffff:ffff:ffff:ffff::
broadcast_address |
-[ RECORD 14 ]----+----------------------------------------
node_id           | 45035996273841968
node_name         | v_verticadb_node0002
interface         | ens192
ip_address_family | ipv6
ip_address        | fe80::250:56ff:fe8e:2c9c
subnet            | fe80::
mask              | ffff:ffff:ffff:ffff::
broadcast_address |

64 - NETWORK_USAGE

Provides network bandwidth usage history on the system.

Provides network bandwidth usage history on the system. This is useful for determining if Vertica is using a large percentage of its available network bandwidth.

Column Name	Data Type	Description
`NODE_NAME`	VARCHAR	Name of the node that is reporting the requested information.
`START_TIME`	TIMESTAMP	Beginning of history interval.
`END_TIME`	TIMESTAMP	End of history interval.
`TX_KBYTES_PER_SEC`	FLOAT	Counter history of outgoing (transmitting) usage in kilobytes per second.
`RX_KBYTES_PER_SEC`	FLOAT	Counter history of incoming (receiving) usage in kilobytes per second.

Privileges

Superuser

65 - NODE_EVICTIONS

Monitors node evictions on the system.

Column Name	Data Type	Description
`EVICTION_TIMESTAMP`	TIMESTAMPTZ	Timestamp when the eviction request was made.
`NODE_NAME`	VARCHAR	The node name logging the information.
`EVICTED_NODE_NAME`	VARCHAR	The node name of the evicted node.
`EVICTED_NODE_ID`	INTEGER	The evicted node ID.
`NODE_STATE_BEFORE_EVICTION`	VARCHAR	The previous node state at the time of eviction.

66 - NODE_RESOURCES

Provides a snapshot of the node.

Provides a snapshot of the node. This is useful for regularly polling the node with automated tools or scripts.

Column Name	Data Type	Description
NODE_NAME	VARCHAR	The node name for which information is listed.
HOST_NAME	VARCHAR	The hostname associated with a particular node.
NODE_IDENTIFIER	VARCHAR	A unique identifier for the node.
PROCESS_SIZE_BYTES	INTEGER	The total size of the program.
PROCESS_RESIDENT_SET_SIZE_BYTES	INTEGER	The total number of bytes that the process has in memory.
PROCESS_SHARED_MEMORY_SIZE_BYTES	INTEGER	The amount of shared memory used.
PROCESS_TEXT_MEMORY_SIZE_BYTES	INTEGER	The total number of text bytes that the process has in physical memory. This does not include any shared libraries.
PROCESS_DATA_MEMORY_SIZE_BYTES	INTEGER	The amount of physical memory, in bytes, used for performing processes. This does not include the executable code.
PROCESS_LIBRARY_MEMORY_SIZE_BYTES	INTEGER	The total number of library bytes that the process has in physical memory.
PROCESS_DIRTY_MEMORY_SIZE_BYTES	INTEGER	The number of bytes that have been modified since they were last written to disk.
SPREAD_HOST	VARCHAR	The node name of the spread host.
NODE_PORT	VARCHAR	The port used for intra-cluster communication.
DATA_PORT	VARCHAR	The port used by the Vertica client.
DBCLERK	BOOLEAN	Whether this node is the DB clerk. The DB clerk is responsible for coordinating some administrative tasks in the database.

67 - NODE_STATES

Monitors node recovery state-change history on the system.

Monitors node recovery state-change history on the system. Vertica returns information only on nodes whose state is currently UP. To determine which nodes are not up, query the NODES table.

Column Name	Data Type	Description
`EVENT_TIMESTAMP`	TIMESTAMPTZ	Time when Vertica recorded the event.
`NODE_ID`	INTEGER	Catalog-assigned integer value that uniquely identifies thenode.
`NODE_NAME`	VARCHAR	Name of the node.
`NODE_STATE`	VARCHAR	The node's state, one of the following: UP DOWN READY UNSAFE SHUTDOWN SHUTDOWN_ERROR RECOVERING RECOVER_ERROR RECOVERED INITIALIZING STANDBY NEEDS_CATCHUP

The following flow chart details different node states:

Privileges

None

68 - NOTIFIER_ERRORS

Reports errors encountered by notifiers.

Column Name	Data Type	Description
`ERROR_TIME`	TIMESTAMPTZ	The time that the error occurred.
`NODE_NAME`	VARCHAR	Name of the node that encountered the error.
`NOTIFIER_NAME`	VARCHAR	Name of the notifier that triggered the error.
`DESCRIPTION`	VARCHAR	A description of the error.

Privileges

Superuser

69 - OUTPUT_DEPLOYMENT_STATUS

Contains information about the deployment status of all the projections in your design.

Contains information about the deployment status of all the projections in your design. Each row contains information about a different projection. Vertica populates this table when you deploy the database design by running the function DESIGNER_RUN_POPULATE_DESIGN_AND_DEPLOY.

Column Name	Column Type	Description
`deployment_id`	INTEGER	Unique ID that Database Designer assigned to the deployment.
`design_name`	VARCHAR	Unique name that the user assigned to the design.
`deployment_projection_id`	INTEGER	Unique ID that Database Designer assigned to the output projection.
`deployment_projection_name`	VARCHAR	Name that Database Designer assigned to the output projection or the name of the projection to be dropped.
`deployment_status`	VARCHAR	Status of the deployment: `pending` `complete` `needs_refresh` `in_progress` `error`
`error_message`	VARCHAR	Text of any error that occurred when creating or refreshing the specified projection.

70 - OUTPUT_EVENT_HISTORY

Contains information about each stage that Database Designer performs to design and optimize your database design.

Column Name	Data Type	Description
TIME_STAMP	TIMESTAMP	Date and time of the specified stage.
DESIGN_ID	INTEGER	Unique id that Database Designer assigned to the design.
DESIGN_NAME	VARCHAR	Unique name that the user assigned to the design.
STAGE_TYPE	VARCHAR	Design stage that Database Designer was working on at the time indicated by the `TIME_STAMP` field. Possible values include: `Design in progress` `Analyzing data statistics` `Optimizing query performance` `Optimizing storage footprint` `All done` `Deployment in progress`
ITERATION_NUMBER	INTEGER	Iteration number for the `Optimizing query performance` stage.
TOTAL_QUERY_COUNT	INTEGER	Total number of design queries in the design.
REMAINING_QUERY_COUNT	INTEGER	Number of design queries remaining for Database Designer to process.
MAX_STEP_NUMBER	INTEGER	Number of steps in the current stage.
CURRENT_STEP_NUMBER	INTEGER	Step in the current stage being processed at the time indicated by the `TIME_STAMP` field.
CURRENT_STEP_DESCRIPTION	VARCHAR	Name of the step that Database Designer is performing at that time indicated in the `TIME_STAMP` field. Possible values include: `Design with deployment started` `Design in progress: Analyze statistics phase` `design_table_name` `projection_name` `Design in progress: Query optimization phase` `Extracting interesting columns` `Enumerating sort orders` `Setting up projection candidates` `Assessing projection candidates` `Choosing best projections` `Calculating estimated benefit of best projections` `Complete` `Design in progress: Storage optimization phase` `Design completed successfully` `Setting up deployment metadata` `Identifying projections to be dropped` `Running deployment` `Deployment completed successfully`
TABLE_ID	INTEGER	Unique id that Database Designer assigned to the design table.

Examples

The following example shows the steps that Database Designer performs while optimizing the VMart example database:

=> SELECT DESIGNER_CREATE_DESIGN('VMART_DESIGN');
=> SELECT DESIGNER_ADD_DESIGN_TABLES('VMART_DESIGN','public.*');
=> SELECT DESIGNER_ADD_DESIGN_QUERIES('VMART_DESIGN','/tmp/examples/vmart_queries.sql',);
...
=> \x
Expanded display is on.
=> SELECT * FROM OUTPUT_EVENT_HISTORY;
-[ RECORD 1 ] -----------+----------------------------
time_stamp               | 2013-06-05 11:44:41.588
design_id                | 45035996273705090
design_name              | VMART_DESIGN
stage_type               | Design in progress
iteration_number         |
total_query_count        |
remaining_query_count    |
max_step_number          |
current_step_number      |
current_step_description | Design with deployment started
table id                 |
-[ RECORD 2 ] -----------+----------------------------
time_stamp               | 2013-06-05 11:44:41.611
design_id                | 45035996273705090
design_name              | VMART_DESIGN
stage_type               | Design in progress
iteration_number         |
total_query_count        |
remaining_query_count    |
max_step_number          |
current_step_number      |
current_step_description | Design in progress: Analyze statistics phase
table id                 |
-[ RECORD 3 ] -----------+----------------------------
time_stamp               | 2013-06-05 11:44:42.011
design_id                | 45035996273705090
design_name              | VMART_DESIGN
stage_type               | Analyzing statistics
iteration_number         |
total_query_count        |
remaining_query_count    |
max_step_number          | 15
current_step_number      | 1
current_step_description | public.customer_dimension
table id                 |
...
-[ RECORD 20 ] ----------+----------------------------
time_stamp               | 2013-06-05 11:44:49.324
design_id                | 45035996273705090
design_name              | VMART_DESIGN
stage_type               | Optimizing query performance
iteration_number         | 1
total_query_count        | 9
remaining_query_count    | 9
max_step_number          | 7
current_step_number      | 1
current_step_description | Extracting interesting columns
table id                 |
...
-[ RECORD 62 ] ----------+----------------------------
time_stamp               | 2013-06-05 11:51:23.790
design_id                | 45035996273705090
design_name              | VMART_DESIGN
stage_type               | Deployment in progress
iteration_number         |
total_query_count        |
remaining_query_count    |
max_step_number          |
current_step_number      |
current_step_description | Deployment completed successfully
table id                 |

71 - PARTITION_COLUMNS

For each projection of a partitioned table, shows the following information:.

For each projection of a partitioned table, shows the following information:

Disk space used by each column per node.
Statistics that were collected on partition columns

Disk usage

The column DISK_SPACE_BYTES shows how much disk space the partitioned data uses, including deleted data. So, if you delete rows but do not purge them, the DELETED_ROW_COUNT column changes to show the number of deleted rows in each column; however, DISK_SPACE_BYTES remains unchanged. After deleted rows are purged, Vertica,reclaims the disk space: DISK_SPACE_BYTES changes accordingly, and DELETED_ROW_COUNT is reset to 0.

For grouped partitions, PARTITION_COLUMNS shows the cumulative disk space used for each column per grouped partition. The column GROUPED_PARTITION_KEY, if not null, identifies the partition in which a given column is grouped.

Statistics

STATISTICS_TYPE always shows the most complete type of statistics that are available on a given column, irrespective of timestamp. For example, if you collect statistics for a table on all levels—table, partition, and row, STATISTICS_TYPE is set to FULL (table-level), even if partition- and row-level statistics were collected more recently.

Column Name	Data Type	Description
COLUMN_NAME	VARCHAR	Identifies a named column within the partitioned table.
COLUMN_ID	INTEGER	Unique numeric ID assigned by the Vertica, which identifies the column.
TABLE_NAME	VARCHAR	Name of the partitioned table.
PROJECTION_NAME	VARCHAR	Projection name for which information is listed.
PROJECTION_ID	INTEGER	Unique numeric ID assigned by Vertica, which identifies the projection.
NODE_NAME	VARCHAR	Node that hosts partitioned data.
PARTITION_KEY	VARCHAR	Identifies the table partition.
GROUPED_PARTITION_KEY	VARCHAR	Identifies the grouped partition to which a given column belongs.
ROW_COUNT	INTEGER	The total number of partitioned data rows for each column, including deleted rows.
DELETED_ROW_COUNT	INTEGER	Number of deleted partitioned data rows in each column.
DISK_SPACE_BYTES	INTEGER	Amount of space used by partitioned data.
STATISTICS_TYPE	VARCHAR	Specifies what sort of statistics are used for this column, one of the following listed in order of precedence: FULL: Table-level statistics PARTITION: Partition-level statistics ROWCOUNT: Minimal set of statistics and aggregate row counts
STATISTICS_UPDATED_TIMESTAMP	TIMESTAMPTZ	Specifies when statistics of the type specified in STATISTICS_TYPE were collected for this column.

Privileges

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

Examples

Given the following table definition:

=> CREATE TABLE messages
(
    time_interval timestamp NOT NULL,
    thread_id varchar(32) NOT NULL,
    unique_id varchar(53) NOT NULL,
    msg_id varchar(65),
    ...
    )
PARTITION BY ((messages.time_interval)::date);

a query on partition_columns might return the following (truncated) results:

=> SELECT * FROM partition_columns order by table_name, column_name;
column_name | column_id | table_name | projection_name | projection_id     | node_name        | partition_key | grouped_partition_key  | row_count | deleted_row_count | disk_space_bytes
---------------------------+----------+----------------+-------------------+------------------+---------------+------------------------+-----------+-------------------+------------------
msg_id | 45035996273743190 | messages | messages_super | 45035996273743182 | v_vmart_node0002 | 2010-07-03    |                        | 6147      | 0                 | 41145
msg_id | 45035996273743190 | messages | messages_super | 45035996273743182 | v_vmart_node0002 | 2010-07-15    |                        | 178       | 0                 | 65
msg_id | 45035996273743190 | messages | messages_super | 45035996273743182 | v_vmart_node0003 | 2010-07-03    |                        | 6782      | 0                 | 45107
msg_id | 45035996273743190 | messages | messages_super | 45035996273743182 | v_vmart_node0003 | 2010-07-04    |                        | 866       | 0                 | 5883
...

thread_id | 45035996273743186 | messages | messages_super | 45035996273743182 | v_vmart_node0002 | 2010-07-03 |                        | 6147      | 0                 | 70565
thread_id | 45035996273743186 | messages | messages_super | 45035996273743182 | v_vmart_node0002 | 2010-07-15 |                        | 178       | 0                 | 2429
thread_id | 45035996273743186 | messages | messages_super | 45035996273743182 | v_vmart_node0003 | 2010-07-03 |                        | 6782      | 0                 | 77730
thread_id | 45035996273743186 | messages | messages_super | 45035996273743182 | v_vmart_node0003 | 2010-07-04 |                        | 866       | 0                 | 10317
...

time_interval | 45035996273743184 | messages | messages_super | 45035996273743182 | v_vmart_node0002 | 2010-07-03 |                    | 6147      | 0                 | 6320
time_interval | 45035996273743184 | messages | messages_super | 45035996273743182 | v_vmart_node0002 | 2010-07-15 |                    | 178       | 0                 | 265
time_interval | 45035996273743184 | messages | messages_super | 45035996273743182 | v_vmart_node0003 | 2010-07-03 |                    | 6782      | 0                 | 6967
time_interval | 45035996273743184 | messages | messages_super | 45035996273743182 | v_vmart_node0003 | 2010-07-04 |                    | 866       | 0                 | 892
...

unique_id | 45035996273743188 | messages | messages_super | 45035996273743182 | v_vmart_node0002 | 2010-07-03 |                        | 6147      | 0                 | 70747
unique_id | 45035996273743188 | messages | messages_super | 45035996273743182 | v_vmart_node0002 | 2010-07-15 |                        | 178       | 0                 | 2460
unique_id | 45035996273743188 | messages | messages_super | 45035996273743182 | v_vmart_node0003 | 2010-07-03 |                        | 6782      | 0                 | 77959
unique_id | 45035996273743188 | messages | messages_super | 45035996273743182 | v_vmart_node0003 | 2010-07-04 |                        | 866       | 0                 | 10332
unique_id | 45035996273743188 | messages | messages_super | 45035996273743182 | v_vmart_node0003 | 2010-07-15 |                        | 184       | 0                 | 2549
...

(11747 rows)

72 - PARTITION_REORGANIZE_ERRORS

new column projection_id.

Monitors all background partitioning tasks, and if Vertica encounters an error, creates an entry in this table with the appropriate information. Does not log repartitioning tasks that complete successfully.

Column Name	Data Type	Description
`SESSION_ID`	VARCHAR	Identifier for this session. This identifier is unique within the cluster at any point in time but can be reused when the session closes.
`USER_NAME`	VARCHAR	Name of the user who received the error at the time Vertica recorded the session.
`NODE_NAME`	VARCHAR	Name of the node that is reporting the requested information.
`TABLE_NAME`	VARCHAR	Name of the partitioned table.
`PROJECTION_ID`	INTEGER	Catalog-assigned numeric value that uniquely identifies the projection.
`PROJECTION_NAME`	VARCHAR	Projection name for which information is listed.
`MESSAGE`	VARCHAR	Textual output of the error message.
`HINT`	VARCHAR	Actionable hint about the error.

Privileges

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

73 - PARTITION_STATUS

For each projection of each partitioned table, shows the fraction of its data that is actually partitioned according to the current partition expression.

For each projection of each partitioned table, shows the fraction of its data that is actually partitioned according to the current partition expression. When the partitioning of a table is altered, the value in PARTITION_REORGANIZE_PERCENT for each of its projections drops to zero and goes back up to 100 when all the data is repartitioned.

Column Name	Data Type	Description
`PROJECTION_ID`	INTEGER	Unique numeric ID assigned by the Vertica catalog, which identifies the projection.
`TABLE_SCHEMA`	VARCHAR	Name of the schema that contains the partitioned table.
`TABLE_NAME`	VARCHAR	Table name that is partitioned.
`TABLE_ID`	INTEGER	Unique numeric ID assigned by the Vertica, which identifies the table.
`PROJECTION_SCHEMA`	VARCHAR	Schema containing the projection.
`PROJECTION_NAME`	VARCHAR	Projection name for which information is listed.
`PARTITION_REORGANIZE_PERCENT`	INTEGER	For each projection, drops to zero and goes back up to 100 when all the data is repartitioned after the partitioning of a table has been altered. Ideally all rows will show 100 (%).

Privileges

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

74 - PARTITIONS

Displays partition metadata, one row per partition key, per ROS container.

Column Name	Data Type	Description
`PARTITION_KEY`	VARCHAR	The partition value(s).
`PROJECTION_ID`	INTEGER	Unique numeric ID assigned by the Vertica catalog, which identifies the projection.
`TABLE_SCHEMA`	VARCHAR	The schema name for which information is listed. If the schema belongs to a non-default namespace in an Eon Mode database, the schema name is front-qualified with the name of the schema's namespace. For example, `n1.s` refers to schema `s` in namespace `n1`.
`PROJECTION_NAME`	VARCHAR	The projection name for which information is listed.
`ROS_ID`	VARCHAR	A unique numeric ID assigned by the Vertica catalog, which identifies the ROS container.
`ROS_SIZE_BYTES`	INTEGER	The ROS container size in bytes.
`ROS_ROW_COUNT`	INTEGER	Number of rows in the ROS container.
`NODE_NAME`	VARCHAR	Node where the ROS container resides.
`DELETED_ROW_COUNT`	INTEGER	The number of deleted rows in the partition.
`LOCATION_LABEL`	VARCHAR	The location label of the default storage location.

Notes

A many-to-many relationship exists between partitions and ROS containers. PARTITIONS displays information in a denormalized fashion.
To find the number of ROS containers having data of a specific partition, aggregate PARTITIONS over the partition_key column.
To find the number of partitions stored in a ROS container, aggregate PARTITIONS over the ros_id column.

Examples

See Viewing partition storage data.

75 - PROCESS_SIGNALS

Returns a history of signals that were received and handled by the Vertica process.

Returns a history of signals that were received and handled by the Vertica process. For details about signals, see the Linux documentation.

Column Name	Data Type	Description
`SIGNAL_TIMESTAMP`	TIMESTAMPTZ	Time when Vertica recorded the signal.
`NODE_NAME`	VARCHAR	Name of the node that is reporting the requested information.
`SIGNAL_NUMBER`	INTEGER	Signal number, refers to `POSIX SIGNAL_NUMBER`
`SIGNAL_CODE`	INTEGER	Signal code.
`SIGNAL_PID`	INTEGER	Linux process identifier of the signal.
`SIGNAL_UID`	INTEGER	Process ID of sending process.
`SIGNAL_ADDRESS`	INTEGER	Address at which fault occurred.

Privileges

Superuser

76 - PROJECTION_RECOVERIES

Retains history about projection recoveries.

Retains history about projection recoveries. Because Vertica adds an entry per recovery plan, a projection/node pair might appear multiple times in the output.

Note

You cannot query this or other system tables during cluster recovery; the cluster must be UP to accept connections.

Column Name	Data Type	Description
`NODE_NAME`	VARCHAR	Name of the node that is recovering or has recovered the corresponding projection.
`PROJECTION_ID`	INTEGER	Unique numeric ID assigned by the Vertica catalog, which identifies the projection.
`PROJECTION_NAME`	VARCHAR	Name of the projection that is being or has been recovered on the corresponding node.
`TRANSACTION_ID`	INTEGER	Identifier for the transaction within the session, if any. TRANSACTION_ID initializes as NO_TRANSACTION with a value of 0. Vertica will ignore the recovery query and keep (0) if there's no action to take (no data in the table, etc). When no recovery transaction starts, `ignored` value appears in this table's STATUS column.
`STATEMENT_ID`	INTEGER	Unique numeric ID for the currently-running statement. NULL indicates that no statement is currently being processed. The combination of TRANSACTION_ID, STATEMENT_ID uniquely identifies a statement within a session.
`METHOD`	VARCHAR	Recovery method that Vertica chooses. Possible values are: `incremental` `incremental-replay-delete` `split` `recovery-by-container`
`STATUS`	VARCHAR	Current projection-recovery status on the corresponding node. STATUS can be "queued," which indicates a brief period between the time the query is prepared and when it runs. Possible values are: `queued` `running` `finished` `ignored` `error-retry` `error-fatal`
`PROGRESS`	INTEGER	An estimate (value in the range [0,100]) of percent complete for the recovery task described by this information. Note: The actual amount of time it takes to complete a recovery task depends on a number of factors, including concurrent workloads and characteristics of the data; therefore, accuracy of this estimate can vary. The PROGRESS column value is `NULL` after the task completes.
`DETAIL`	VARCHAR	More detailed information about PROGRESS. The values returned for this column depend on the type of recovery plan: General recovery plans – value displays the estimated progress, as a percent, of the three primary parts of the plan: `Scan`, `Sort,` and `Write`. Recovery-by-container plans – value begins with `CopyStorage:` and is followed by the number of bytes copied over the total number of bytes to copy. Replay delete plans – value begins with `Delete:` and is followed by the number of deletes replayed over an estimate of the total number of deletes to replay. The DETAIL column value becomes `NULL` after the recovery plan completes.
`START_TIME`	TIMESTAMPTZ	Time the recovery task described by this information started.
`END_TIME`	TIMESTAMPTZ	Time the recovery task described by this information ended.
`RUNTIME_PRIORITY`	VARCHAR	Determines the amount of runtime resources (CPU, I/O bandwidth) the Resource Manager should dedicate to running queries in the resource pool. Valid values are: HIGH MEDIUM LOW

Privileges

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

77 - PROJECTION_REFRESHES

Records information about successful and unsuccessful refresh operations. PROJECTION_REFRESHES retains projection refresh data until one of the following events occurs:

Another refresh operation starts on a given projection.
CLEAR_PROJECTION_REFRESHES is called and clears data on all projections.
The table's storage quota is exceeded.

Column Name	Data Type	Description
NODE_NAME	VARCHAR	Node where the refresh was initiated.
PROJECTION_SCHEMA	VARCHAR	Name of the projection schema.
PROJECTION_ID	INTEGER	Catalog-assigned numeric value that uniquely identifies the projection.
PROJECTION_NAME	VARCHAR	Name of the refreshed projection.
ANCHOR_TABLE_NAME	VARCHAR	Name of the projection's anchor table.
REFRESH_STATUS	VARCHAR	Status of refresh operations for this projection, one of the following: Queued : Projection is queued for refresh. Refreshing: Projection refresh is in progress. Refreshed: Projection refresh is complete. Failed: Projection refresh failed.
PERCENT_COMPLETE	VARCHAR	Shows the current percentage of completion for the refresh operation. When the refresh is complete, the column is set to NULL.
REFRESH_PHASE	VARCHAR	Indicates how far the refresh has progressed: Historical: Refresh reached the first phase and is refreshing data from historical data. This refresh phase requires the most amount of time. Current: Refresh reached the final phase and is attempting to refresh data from the current epoch. To complete this phase, refresh must obtain a lock on the table. If the table is locked by another transaction, refresh is blocked until that transaction completes. The LOCKS system table is useful for determining if a refresh is blocked on a table lock. To determine if a refresh has been blocked, locate the term "refresh" in the transaction description. A refresh has been blocked when the scope for the refresh is REQUESTED and other transactions acquired a lock on the table. This field is NULL until the projection starts to refresh and is NULL after the refresh completes.
REFRESH_METHOD	VARCHAR	Method used to refresh the projection: Buddy: Projection refreshed from the contents of a buddy projection. This method maintains historical data, so the projection can be used for historical queries. Scratch: Projection refreshed without using a buddy projection. This method does not generate historical data, so the projection cannot participate in historical queries on data that precedes the refresh. Rebalance: If the projection is segmented, it is refreshed from scratch; if unsegmented, it is refreshed from a buddy projection. Incremental: Projections of a partitioned table were refreshed one partition at a time. For details, see Refreshing projections.
REFRESH_FAILURE_COUNT	INTEGER	Number of times a refresh failed for the projection. REFRESH_FAILURE_COUNT does not indicate whether the projection was eventually refreshed. See REFRESH_STATUS to determine whether the refresh operation is progressing.
SESSION_ID	VARCHAR	Unique numeric ID assigned by the Vertica catalog, which identifies the refresh session.
REFRESH_START	TIMESTAMPTZ	Time the projection refresh started.
REFRESH_DURATION_SEC	INTERVAL SECOND (0)	How many seconds the projection refresh ran.
IS_EXECUTING	BOOLEAN	Differentiates active and completed refresh operations.
RUNTIME_PRIORITY	VARCHAR	Determines how many run-time resources (CPU, I/O bandwidth) the Resource Manager should dedicate to running queries in the resource pool, one of the following: HIGH MEDIUM LOW
TRANSACTION_ID	INTEGER	Identifier for the transaction within the session, if any; otherwise NULL. Note The transaction_id is correlated with the execution plan only when refreshing from scratch. When refreshing from a buddy, multiple sub-transactions are created

Privileges

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

78 - PROJECTION_STORAGE

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

Note

Projections that have no data never have full statistics. Querying this system table lets you see if your projection contains data.

Column Name	Data Type	Description
NODE_NAME	VARCHAR	The node name for which information is listed.
PROJECTION_ID	VARCHAR	Catalog-assigned numeric value that uniquely identifies the projection.
PROJECTION_NAME	VARCHAR	The projection name for which information is listed.
PROJECTION_SCHEMA	VARCHAR	The name of the schema associated with the projection.
PROJECTION_COLUMN_COUNT	INTEGER	The number of columns in the projection.
ROW_COUNT	INTEGER	The number of rows in the table's projections, including any rows marked for deletion.
USED_BYTES	INTEGER	Number of bytes in disk storage used to store the compressed projection data. This value should not be compared to the output of the AUDIT function, which returns the raw data size of database objects.
ROS_COUNT	INTEGER	The number of ROS containers in the projection.
ANCHOR_TABLE_NAME	VARCHAR	The associated table name for which information is listed.
ANCHOR_TABLE_SCHEMA	VARCHAR	The associated table schema for which information is listed.
ANCHOR_TABLE_ID	INTEGER	A unique numeric ID, assigned by the Vertica catalog, which identifies the anchor table.
DISK_SIZE	INTEGER	Size in bytes of the projection's compressed storage.

79 - PROJECTION_USAGE

Records information about projections Vertica used in each processed query.

Column Name	Data Type	Description
`QUERY_START_TIMESTAMP`	TIMESTAMPTZ	Value of query at beginning of history interval.
`NODE_NAME`	VARCHAR	Name of the node that is reporting the requested information.
`USER_NAME`	VARCHAR	Name of the user at the time Vertica recorded the session.
`SESSION_ID`	VARCHAR	Identifier for this session. This identifier is unique within the cluster at any point in time but can be reused when the session closes.
`REQUEST_ID`	INTEGER	Unique identifier of the query request in the user session.
`TRANSACTION_ID`	INTEGER	Identifier for the transaction within the session, if any; otherwise `NULL`.
`STATEMENT_ID`	INTEGER	Unique numeric ID for the currently-running statement. NULL indicates that no statement is currently being processed. The combination of TRANSACTION_ID, STATEMENT_ID, and REQUEST_ID uniquely identifies a statement within a session.
`IO_TYPE`	VARCHAR	Input/output.
`PROJECTION_ID`	INTEGER	Unique numeric ID assigned by the Vertica catalog, which identifies the projection.
`PROJECTION_NAME`	VARCHAR	Projection name for which information is listed.
`ANCHOR_TABLE_ID`	INTEGER	Unique numeric ID assigned by the Vertica, which identifies the anchor table.
`ANCHOR_TABLE_SCHEMA`	VARCHAR	Name of the schema that contains the anchor table.
`ANCHOR_TABLE_NAME`	VARCHAR	Name of the projection's associated anchor table.

Privileges

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

80 - QUERY_CONSUMPTION

Summarizes execution of individual queries.

Summarizes execution of individual queries. Columns STATEMENT_ID and TRANSACTION_ID combine as unique keys to these queries. One exception applies: a query with multiple plans has as many records.

Column Name	Data Type	Description
`START_TIME END_TIME`	TIMESTAMP	Demarcate the start and end of query execution, whether successful or not.
`SESSION_ID`	VARCHAR	Identifies the session where profiling information was captured. This identifier is unique within the cluster at any point in time, but can be reused when the session closes.
`USER_ID`	INTEGER	Unique numeric user identifier assigned by the Vertica catalog.
`USER_NAME`	VARCHAR	User name specified by this query profile.
`TRANSACTION_ID`	INTEGER	Identifies the transaction in which the query ran.
`STATEMENT_ID`	INTEGER	Numeric identifier of this query, unique within the query transaction.
`CPU_CYCLES_US`	INTEGER	Sum, in microseconds, of CPU cycles spent by all threads to process this query.
`NETWORK_BYTES_SENT NETWORK_BYTES_RECEIVED`	INTEGER	Total amount of data sent/received over the network by execution engine operators.
`DATA_BYTES_READ DATA_BYTES_WRITTEN`	INTEGER	Total amount of data read/written by storage operators from and to disk, includes all locations: local, HDFS, S3.
`DATA_BYTES_LOADED`	INTEGER	Total amount of data loaded from external sources: COPY, external tables, and data load.
`BYTES_SPILLED`	INTEGER	Total amount of data spilled to disk—for example, by SortManager, Join, and NetworkSend operators.
`INPUT_ROWS`	INTEGER	Number of unfiltered input rows from DataSource and Load operators. INPUT_ROWS shows the number of input rows that the query plan worked with, but excludes intermediate processing. For example, INPUT_ROWS excludes how many times SortManager spilled and read the same row.
`INPUT_ROWS_PROCESSED`	INTEGER	Value of INPUT_ROWS minus what was filtered by applying query predicates (valindex) and SIPs, and rows rejected by COPY.
`PEAK_MEMORY_KB`	INTEGER	Peak memory reserved by the resource manager for this query.
`THREAD_COUNT`	INTEGER	Maximum number of threads opened to process this query.
`DURATION_MS`	INTEGER	Total wall clock time, in milliseconds, spent to process this query.
`RESOURCE_POOL`	VARCHAR	Name of the resource pool where the query was executed.
`OUTPUT_ROWS`	INTEGER	Number of rows output to the client.
`REQUEST_TYPE`	VARCHAR	Type of query—for example, QUERY or DDL.
`LABEL`	VARCHAR	Label included as a LABEL hint in this query.
`IS_RETRY`	BOOLEAN	This query was tried earlier.
`SUCCESS`	BOOLEAN	This query executed successfully.

81 - QUERY_EVENTS

Returns information about query planning, optimization, and execution events.

Column Name	Data Type	Description
EVENT_TIMESTAMP	TIMESTAMPTZ	Time when Vertica recorded the event.
NODE_NAME	VARCHAR	Name of the node that is reporting the requested information.
USER_ID	INTEGER	Identifier of the user for the query event.
USER_NAME	VARCHAR	Name of the user for which Vertica lists query information at the time it recorded the session.
SESSION_ID	VARCHAR	Identifier for this session. This identifier is unique within the cluster at any point in time but can be reused when the session closes.
REQUEST_ID*	INTEGER	Unique identifier of the query request in the user session.
TRANSACTION_ID*	INTEGER	Identifier for the transaction within the session, if any; otherwise `NULL`.
STATEMENT_ID*	INTEGER	Unique numeric ID for the currently-running statement. NULL indicates that no statement is currently being processed.
EVENT_CATEGORY	VARCHAR	Category of event: OPTIMIZATION or EXECUTION.
EVENT_TYPE	VARCHAR	Type of event. For details on each type, see the following sections: Informational Event Types Event Types for Corrective Action Critical Event Types
EVENT_DESCRIPTION	VARCHAR	Generic description of the event.
OPERATOR_NAME	VARCHAR	Name of the Execution Engine component that generated the event, if applicable; for example, NetworkSend. Values from the `OPERATOR_NAME` and `PATH_ID` columns let you tie a query event back to a particular operator in the query plan. If the event did not come from a specific operator, the OPERATOR_NAME column is NULL.
PATH_ID	INTEGER	Unique identifier that Vertica assigns to a query operation or path in a query plan, NULL if the event did not come from a specific operaton. For more information, see EXECUTION_ENGINE_PROFILES.
OBJECT_ID	INTEGER	Object identifier such as projection or table to which the event refers.
EVENT_DETAILS	VARCHAR	Free-form text describing the specific event.
EVENT_SEVERITY	VARCHAR	Indicates severity of the event with one of the following values: Informational: No action required Warning: Remedial action recommended as specified in `SUGGESTED_ACTION` Critical: Remedial action required, as specified by `SUGGESTED_ACTION`
SUGGESTED_ACTION	VARCHAR	Specifies remedial action, recommended or required as indicated by `EVENT_SEVERITY`.

Informational event types

CSE ANALYSIS: The optimizer performed Common subexpressions analysis
CSE ANALYSIS STATS: Time spent on Common subexpressions analysis (msec)
EXPRESSION_EVAL_ERROR: An exception occurred during evaluation of an expression
EXTERNAL_DATA_FILES_PRUNED: Vertica skipped external data files (for example, Iceberg files) that could not satisfy predicates in the query. The event details include the number and paths of pruned files.
EXTERNAL_PREDICATE_PUSHDOWN_NOT_SUPPORTED: Predicate pushdown for older Hive versions may not be supported. For more information, see Querying external tables.
FLATTENED SUBQUERIES: Subqueries flattened in FROM clause
GROUP_BY_PREPASS_FALLBACK: Vertica could not run an optimization. In-memory prepass is disabled. The projection may not be optimal.
GROUPBY PUSHDOWN: Internal to Vertica
LibHDFS++ FAILOVER RETRY: Vertica attempted to contact a NameNode on an HDFS cluster that uses High Availability NameNode and did not receive a response. Vertica retried with a different NameNode.
LibHDFS++ MANUAL FALLBACK: Vertica accessed HDFS using the hdfs URL scheme but HDFSUseWebHDFS is set. Vertica fell back to WebHDFS.
LibHDFS++ UNSUPPORTED OPERATION: Vertica accessed HDFS using the hdfs URL scheme, but the HDFS cluster uses an unsupported feature such as wire encryption or HTTPS_ONLY or the Vertica session uses delegation tokens. Vertica fell back to WebHDFS.
MERGE_CONVERTED_TO_UNION: Vertica has converted a merge operator to a union operator due to the sort order of the multi-threaded storage access stream.
NO GROUPBY PUSHDOWN: Internal to Vertica
NODE PRUNING: Vertica performed node pruning, which is similar to partition pruning, but at the node level.
ORC_FILE_INFO: A query of ORC files encountered missing information (such as time zone) or an unrecognized ORC version. For missing information, Vertica uses a default value (such as the local time zone).
ORC_SOURCE_PRUNED: An entire ORC file was pruned during predicate pushdown.
ORC_STRIPES_PRUNED: The identified stripes were pruned during predicate pushdown. If an entire ORC file was pruned, it is instead recorded with an ORC_SOURCE_PRUNED event.
OUTER OVERRIDE NOT USED: Vertica found swapping inner/outer tables in a join unnecessary because the inner/outer tables were in good order. (For example, a smaller table was used in an inner join.)
OUTER OVERRIDE USED: For efficiency and optimization, Vertica has swapped the inner/outer tables in a join. Vertica used the smaller table as the inner table.
PARQUET_ROWGROUPS_PRUNED: The identified row groups were pruned during predicate pushdown.
PARTITION_PATH_PRUNED: A path (reported in event details) was pruned.
PARTITION_PATH_REJECTED: Could not evaluate partition column predicate on a path from source list. Path will be rejected.
PARTITION_PRUNING: COPY pruned partitions. The event reports how many paths were pruned, and PARTITION_PATH_PRUNED events record more details.
PREDICATES_DISCARDED_FROM_SCAN: Some predicates have been discarded from this scan because expression analysis shows they are not needed.
REJECT_ROWNUMS_HIT_BUFFER_LIMIT: Buffering row numbers during rejection hit buffer limit
SEQUENCE CACHE REFILLED: Vertica has refilled sequence cache.
SIP_FALLBACK: This optimization did not apply to this query type.
SMALL_MERGE_REPLACED: Vertica has chosen a more efficient way to access the data by replacing a merge.
STORAGE_CONTAINERS_ELIMINATED: Vertica has performed partition pruning for the purpose of optimization.
TRANSITIVE PREDICATE: Vertica has optimized by adding predicates to joins where it makes logical sense to do so.
For example, for the statement, SELECT * FROM A, B WHERE A.a = B.a AND A.a = 1;Vertica may add a predicate B a = 1 as a filter for better storage access of table B.
TYPE_MISMATCH_COLUMNS_PARQUETPARSER: The Parquet parser used loose schema matching to load data, and could not coerce values in the Parquet data to the types defined for the table. By default the parser rejects the row. For more information, see PARQUET.
UNMATCHED_TABLE_COLUMNS_PARQUETPARSER: The Parquet parser used loose schema matching to load data, and columns in the table had no corresponding columns in the data. The columns were given values of NULL.
VALUE_TRUNCATED: A character value is too long.
WEBHDFS FAILOVER RETRY: Vertica attempted to contact a NameNode on an HDFS cluster that uses High Availability NameNode and did not receive a response. Vertica retried with a different NameNode.

Warning event types

AUTO_PROJECTION_USED: The optimizer used an auto-projection to process this query.
Recommendation: Create a projection that is appropriate for this query and others like it; consider using Database Designer to generate query-specific projections.
GROUP_BY_SPILLED: This event type is typically related to a specific type of query, which you might need to adjust.
Recommendation: Identify the type of query and make adjustments accordingly. You might need to adjust resource pools, projections, or the amount of RAM available. Try running the query on a cluster with no additional workload.
INVALID COST: When creating a query plan, the optimizer calculated an invalid cost for a path: not-a-number (NaN) value, infinity value, or negative value. The path cost was set to its default value.
No action available to users.
PATTERN_MATCH_NMEE: More than one pattern event is true for a single row
Recommendation: Modify event expressions to ensure that only one event can be true for any row. Alternatively, modify the query using a MATCH clause with ROWS MATCH FIRST EVENT.
PREDICATE OUTSIDE HISTOGRAM: A predicate value you are trying to match does not exist in a set of possible values for a specific column. For example, you try to match a VARCHAR value WHERE mystring = "ABC<newline>". In this case, the newline character throws off the predicate matching optimizations.
Recommendation: Run ANALYZE_STATISTICS on the column.
RESEGMENTED_MANY_ROWS: This event type is typically related to a specific type of query, which you might need to adjust.
Recommendation: Do projections need to be segmented in a different way to allow for join locality? Can you rewrite the query to filter out more rows at storage access time? (Typically, Vertica does so automatically through predicate pushdown.) Review your explain plan.
RLE_OVERRIDDEN: The average run counts are not large enough for Run Length Encoding (RLE). This event occurs with queries where the filtered results for certain columns do not work with RLE because cardinality is less than 10.
Recommendation: Review and rewrite your query, if necessary.

Critical event types

DELETE WITH NON OPTIMIZED PROJECTION

One or more projections do not have your delete filter column in their sort order, causing Vertica difficulty identifying ros to mark as deleted.

To resolve: Add the delete filter column to the end of every projection sort order for your target delete table.

JOIN_SPILLED

Vertica has spilled a join to disk. A join spill event slows down the subject query and all other queries as it consumes resources while using disk as virtual memory.

To resolve: Try the following:

Review the explain plan. The query might be too ambitious, for example, cross joining two large tables.
Consider adding the query to a lower priority pool to reduce impact on other queries.
Create projections that allow for a merge join instead of a hash join.
Adjust the PLANNEDCONCURRENCYresource pool so that queries have more memory to execute.

MEMORY LIMIT HIT

Indicates query complexity or, possibly, lack of available system memory.

To resolve: Consider adjusting the MAXMEMORYSIZE and PLANNEDCONCURRENCY resource pools so that the optimizer has sufficient memory. On a heavily used system, this event may occur more frequently.

NO HISTOGRAM

Indicates a table does not have an updated column histogram.

To resolve: Running the function ANALYZE_STATISTICS most often corrects this issue.

Privileges

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

82 - QUERY_METRICS

Monitors the sessions and queries running on each node.

Note

Totals in this table are reset each time the database restarts.

Column Name	Data Type	Description
`NODE_NAME`	VARCHAR	The node name for which information is listed.
`ACTIVE_USER_SESSION_COUNT`	INTEGER	The number of active user sessions (connections).
`ACTIVE_SYSTEM_SESSION_COUNT`	INTEGER	The number of active system sessions.
`TOTAL_USER_SESSION_COUNT`	INTEGER	The total number of user sessions.
`TOTAL_SYSTEM_SESSION_COUNT`	INTEGER	The total number of system sessions.
`TOTAL_ACTIVE_SESSION_COUNT`	INTEGER	The total number of active user and system sessions.
`TOTAL_SESSION_COUNT`	INTEGER	The total number of user and system sessions.
`RUNNING_QUERY_COUNT`	INTEGER	The number of queries currently running.
`EXECUTED_QUERY_COUNT`	INTEGER	The total number of queries that ran.

83 - QUERY_PLAN_PROFILES

Provides detailed execution status for queries that are currently running in the system.

Provides detailed execution status for queries that are currently running in the system. Output from the table shows the real-time flow of data and the time and resources consumed for each path in each query plan.

Column Name	Data Type	Description
`TRANSACTION_ID`	INTEGER	An identifier for the transaction within the session if any; otherwise `NULL`.
`STATEMENT_ID`	INTEGER	Unique numeric ID for the currently-running statement. NULL indicates that no statement is currently being processed. The combination of TRANSACTION_ID and STATEMENT_ID uniquely identifies a statement within a session; these columns are useful for creating joins with other system tables.
`PATH_ID`	INTEGER	Unique identifier that Vertica assigns to a query operation or path in a query plan. Textual representation for this path is output in the PATH_LINE column.
`PATH_LINE_INDEX`	INTEGER	Each plan path in QUERY_PLAN_PROFILES could be represented with multiple rows. PATH_LINE_INDEX returns the relative line order. You should include the PATH_LINE_INDEX column in the QUERY_PLAN_PROFILES ... ORDER BY clause so rows in the result set appear as they do in EXPLAIN-generated query plans.
`PATH_IS_EXECUTING`	BOOLEAN	Status of a path in the query plan. True (t) if the path has started running, otherwise false.
`PATH_IS_COMPLETE`	BOOLEAN	Status of a path in the query plan. True (t) if the path has finished running, otherwise false.
`IS_EXECUTING`	BOOLEAN	Status of a running query. True if the query is currently active (t), otherwise false (f).
`RUNNING_TIME`	INTERVAL	The amount of elapsed time the query path took to execute.
`MEMORY_ALLOCATED_BYTES`	INTEGER	The amount of memory the path used, in bytes.
`READ_FROM_DISK_BYTES`	INTEGER	The number of bytes the path read from disk (or the disk cache).
`RECEIVED_BYTES`	INTEGER	The number of bytes received over the network.
`SENT_BYTES`	INTEGER	Size of data sent over the network by the path.
`PATH_LINE`	VARCHAR	The query plan text string for the path, associated with the PATH ID and PATH_LINE_INDEX columns.

Privileges

Non-superusers see only the records of tables they have permissions to view.

Best practices

Table results can be very wide. For best results when you query QUERY_PLAN_PROFILES, sort on these columns:

TRANSACTION_ID
STATEMENT_ID
PATH_ID
PATH_LINE_INDEX

For example:

=> SELECT ... FROM query_plan_profiles
     WHERE ...
    ORDER BY transaction_id, statement_id, path_id, path_line_index;

Examples

See Profiling query plans

84 - QUERY_PROFILES

Provides information about executed queries.

Column Name	Data Type	Description
`SESSION_ID`	`VARCHAR`	The identification of the session for which profiling information is captured. This identifier is unique within the cluster at any point in time but can be reused when the session closes.
`TRANSACTION_ID`	`INTEGER`	An identifier for the transaction within the session if any; otherwise `NULL`.
`STATEMENT_ID`	`INTEGER`	Unique numeric ID for the currently-running statement. NULL indicates that no statement is currently being processed. The combination of `TRANSACTION_ID`, `STATEMENT_ID` uniquely identifies a statement within a session.
`IDENTIFIER`	`VARCHAR`	A string to identify the query in system tables. Note: You can query the `IDENTIFIER` column to quickly identify queries you have labeled for profiling and debugging. See Labeling statements for details.
`NODE_NAME`	`VARCHAR`	The node name for which information is listed.
`QUERY`	`VARCHAR`	The query string used for the query.
`QUERY_SEARCH_PATH`	`VARCHAR`	A list of schemas in which to look for tables.
`SCHEMA_NAME`	`VARCHAR`	The schema name in which the query is being profiled, set only for load operations.
`TABLE_NAME`	`VARCHAR`	The table name in the query being profiled, set only for load operations.
`QUERY_DURATION_US`	`NUMERIC(18,0)`	The duration of the query in microseconds.
`QUERY_START_EPOCH`	`INTEGER`	The epoch number at the start of the given query.
`QUERY_START`	`VARCHAR`	The Linux system time of query execution in a format that can be used as a `DATE/TIME` expression.
`QUERY_TYPE`	`VARCHAR`	Is one of `INSERT`, `SELECT`, `UPDATE`, `DELETE`, `UTILITY`, or `UNKNOWN`.
`ERROR_CODE`	`INTEGER`	The return error code for the query.
`USER_NAME`	`VARCHAR`	The name of the user who ran the query.
`PROCESSED_ROW_COUNT`	`INTEGER`	The number of rows returned by the query.
`RESERVED_EXTRA_MEMORY_B`	`INTEGER`	Shows how much unused memory (in bytes) remains that is reserved for a given query but is unassigned to a specific operator. This is the memory from which unbounded operators pull first. The `MEMORY_INUSE_KB` column in system table `RESOURCE_ACQUISITIONS` shows how much total memory was acquired for each query. If operators acquire all memory acquired for the query, the plan must request more memory from the Vertica resource manager.
`IS_EXECUTING`	`BOOLEAN`	Displays information about actively running queries, regardless of whether profiling is enabled.

85 - QUERY_REQUESTS

Returns information about user-issued query requests.

Returns information about user-issued query requests. A statement can include more than one query request; for example, adding data to a table could include a constraint check in addition to a load or insert request.

For a consolidated view with one row per statement, see STATEMENT_OUTCOMES.

Column Name	Data Type	Description
NODE_NAME	VARCHAR	Name of the node that reported the requested information.
USER_NAME	VARCHAR	Name of the user who issued the query at the time Vertica recorded the session.
SESSION_ID	VARCHAR	Identifier for this session. This identifier is unique within the cluster at any point in time but can be reused after the session ends.
REQUEST_ID	INTEGER	Unique identifier of the query request in the user session.
TRANSACTION_ID	INTEGER	Identifier for the transaction within the session, if any, otherwise NULL.
STATEMENT_ID	INTEGER	Unique identifier for the currently-running statement. NULL indicates that no statement is currently being processed. The combination of TRANSACTION_ID, STATEMENT_ID, and REQUEST_ID uniquely identifies a statement within a session.
REQUEST_TYPE	VARCHAR	Type of the query request. Examples include, but are not limited to: QUERY DDL LOAD UTILITY TRANSACTION PREPARE EXECUTE SET SHOW
REQUEST	VARCHAR	Query statement.
REQUEST_LABEL	VARCHAR	Label of the query, if any.
SEARCH_PATH	VARCHAR	Contents of the search path.
MEMORY_ACQUIRED_MB	FLOAT	Memory acquired by this query request in megabytes.
SUCCESS	BOOLEAN	Whether the query request succeeded.
ERROR_COUNT	INTEGER	Number of errors encountered in this query request (logged in ERROR_MESSAGES).
START_TIMESTAMP	TIMESTAMPTZ	Beginning of history interval.
END_TIMESTAMP	TIMESTAMPTZ	End of history interval.
REQUEST_DURATION	TIMESTAMPTZ	Query request execution time in days, hours, minutes, seconds, and milliseconds.
REQUEST_DURATION_MS	INTEGER	Query request execution time in milliseconds.
IS_EXECUTING	BOOLEAN	Whether the query request is currently running.

Privileges

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

86 - REBALANCE_OPERATIONS

Contains information on historic and ongoing rebalance operations.

Column Name	Data Type	Description
OBJECT_TYPE	VARCHAR	The type of the rebalanced object: Projection DFSfile
OBJECT_ID	INTEGER	The ID of the rebalanced object.
OBJECT_NAME	VARCHAR	The name of the rebalanced object. Objects can be tables, projections, or other Vertica objects.
PATH_NAME	VARCHAR	The DFS path for unstructured data being rebalanced.
TABLE_NAME	VARCHAR	The name of the rebalanced table. This value is NULL for DFS files.
TABLE_SCHEMA	VARCHAR	The schema of the rebalanced table. This value is NULL for DFS files. If the schema belongs to a non-default namespace in an Eon Mode database, the schema name is front-qualified with the name of the schema's namespace. For example, `n1.s` refers to schema `s` in namespace `n1`.
TRANSACTION_ID	INTEGER	The identifier for the transaction within the session.
STATEMENT_ID	INTEGER	The unique numeric ID for the currently-running statement.
NODE_NAME	VARCHAR	Name of the rebalancing node.
OPERATION_NAME	VARCHAR	Identifies the specific rebalance operation being performed, one of: Refresh projection, update `temporary projection name` and ID to `master projection name` Drop unsegmented replicas Replicate DFS File Refresh projection Drop replaced or replacement projection, rename `temporary projection name` to `original projection name` Update temp table segments Prepare : separate Move storage containers
OPERATION_STATUS	VARCHAR	Specifies status of the rebalance operation, one of the followin: START COMPLETE ABORT
IS_EXECUTING	BOOLEAN	TRUE: the operation is currently running.
REBALANCE_METHOD	VARCHAR	The method that Vertica is using to perform the rebalance, one of the following: `REFRESH:` New projections are created according to the new segmentation definition. Data is copied via a refresh plan from projections with the previous segmentation to the new segments. This method is used only if START_REFRESH is called, a configuration parameter is set, or K-safety changes. `REPLICATE:` Unsegmented projection data is copied to new nodes and removed from ephemeral nodes. `ELASTIC_CLUSTER:` The segmentation of existing segmented projections is altered to adjust to a new cluster topology and data is redistributed accordingly.
SESSION_ID	VARCHAR	Identifier for this session. This identifier is unique within the cluster at any point in time but can be reused when the session closes.
OPERATION_START_TIMESTAMP	TIMESTAMPTZ	The time that the rebalance began.
OPERATION_END_TIMESTAMP	TIMESTAMPTZ	The time that the rebalance ended. If the rebalance is ongoing, this value is NULL.
ELASTIC_CLUSTER_VERSION	INTEGER	The Elastic Cluster has a version. Each time the cluster topology changes, this version increments.
IS_LATEST	BOOLEAN	True if this row pertains to the most recent rebalance activity.

Privileges

Superuser

87 - REBALANCE_PROJECTION_STATUS

Maintain history on rebalance progress for relevant projections.

Column Name	Data Type	Description
`PROJECTION_ID`	INTEGER	Identifier of the projection to rebalance.
`PROJECTION_SCHEMA`	VARCHAR	Schema of the projection to rebalance. If the schema belongs to a non-default namespace in an Eon Mode database, the schema name is front-qualified with the name of the schema's namespace. For example, `n1.s` refers to schema `s` in namespace `n1`.
`PROJECTION_NAME`	VARCHAR	Name of the projection to rebalance.
`ANCHOR_TABLE_ID`	INTEGER	Anchor table identifier of the projection to rebalance.
`ANCHOR_TABLE_NAME`	VARCHAR	Anchor table name of the projection to rebalance.
`REBALANCE_METHOD`	VARCHAR	Method used to rebalance the projection, one of the following: `REFRESH`: New projections are created according to the new segmentation definition. Data is copied via a refresh plan from projections with the previous segmentation to the new segments. This method is used only if START_REFRESH is called, a configuration parameter is set, or K-safety changes. `REPLICATE`: Unsegmented projection data is copied to new nodes and removed from ephemeral nodes. `ELASTIC_CLUSTER`: The segmentation of existing segmented projections is altered to adjust to a new cluster topology and data is redistributed accordingly.
`DURATION_SEC`	INTERVAL SEC	Deprecated, set to NULL.
`SEPARATED_PERCENT`	NUMERIC(5,2)	Percent of storage that has been separated for this projection.
`TRANSFERRED_PERCENT`	NUMERIC(5,2)	Percent of storage that has been transferred, for this projection.
`SEPARATED_BYTES`	INTEGER	Number of bytes, separated by the corresponding rebalance operation, for this projection.
`TO_SEPARATE_BYTES`	INTEGER	Number of bytes that remain to be separated by the corresponding rebalance operation for this projection.
`TRANSFERRED_BYTES`	INTEGER	Number of bytes transferred by the corresponding rebalance operation for this projection.
`TO_TRANSFER_BYTES`	INTEGER	Number of bytes that remain to be transferred by the corresponding rebalance operation for this projection.
`IS_LATEST`	BOOLEAN	True if this row pertains to the most recent rebalance activity, where `elastic_cluster_version = (SELECT version FROM v_catalog.elastic_cluster);`
`ELASTIC_CLUSTER_VERSION`	INTEGER	The elastic cluster has a version, and each time the cluster topology changes, this version is incremented. This column reflects the version to which this row of information pertains. The `TO_` fields (TO_SEPARATE_ and TO_TRANSFER_*) are only valid for the current version. To view only rows from the current, latest or upcoming rebalance operation, use: `WHERE elastic_cluster_version = (SELECT version FROM v_catalog.elastic_cluster);`

Privileges

Superuser

88 - REBALANCE_TABLE_STATUS

Maintain history on rebalance progress for relevant tables.

Column Name	Data Type	Description
`TABLE_ID`	INTEGER	Identifier of the table that will be, was, or is being rebalanced.
`TABLE_SCHEMA`	VARCHAR	Schema of the table that will be, was, or is being rebalanced.
`TABLE_NAME`	VARCHAR	Name of the table that will be, was, or is being rebalanced.
`REBALANCE_METHOD`	VARCHAR	Method that will be, is, or was used to rebalance the projections of this table. Possible values are: `REFRESH` `REPLICATE` `ELASTIC_CLUSTER`
`DURATION_SEC`	INTERVAL SEC	Deprecated - populated by NULL. Aggregate, by table_id, rebalance_method, and elastic_cluster_version, of the same in REBALANCE_PROJECTION_STATUS.
`SEPARATED_PERCENT`	NUMERIC(5,2)	Aggregate, by table_id, rebalance_method, and elastic_cluster_version, of the same in REBALANCE_PROJECTION_STATUS.
`TRANSFERRED_PERCENT`	NUMERIC(5,2)	Aggregate, by table_id, rebalance_method, and elastic_cluster_version, of the same in REBALANCE_PROJECTION_STATUS.
`SEPARATED_BYTES`	INTEGER	Aggregate, by table_id, rebalance_method, and elastic_cluster_version, of the same in REBALANCE_PROJECTION_STATUS.
`TO_SEPARATE_BYTES`	INTEGER	Aggregate, by table_id, rebalance_method, and elastic_cluster_version, of the same in REBALANCE_PROJECTION_STATUS.
`TRANSFERRED_BYTES`	INTEGER	Aggregate, by table_id, rebalance_method, and elastic_cluster_version, of the same in REBALANCE_PROJECTION_STATUS.
`TO_TRANSFER_BYTES`	INTEGER	Aggregate, by table_id, rebalance_method, and elastic_cluster_version, of the same in REBALANCE_PROJECTION_STATUS.
`IS_LATEST`	BOOLEAN	True if this row pertains to the most recent rebalance activity, where `elastic_cluster_version = (SELECT version FROM v_catalog.elastic_cluster;)`
`ELASTIC_CLUSTER_VERSION`	INTEGER	The Elastic Cluster has a version, and each time the cluster topology changes, this version is incremented. This column reflects the version to which this row of information pertains. The `TO_` fields (TO_SEPARATE_ and TO_TRANSFER_*) are only valid for the current version. To view only rows from the current, latest or upcoming rebalance operation, use: `WHERE elastic_cluster_version = (SELECT version FROM v_catalog.elastic_cluster;)`
`start_timestamp`	TIMESTAMPTZ	The time that the rebalance began.
`end_timestamp`	TIMESTAMPTZ	The time that the rebalance ended.

Privileges

Superuser

89 - RECOVERY_STATUS

Provides the status of recovery operations, returning one row for each node.

Note

You cannot query this or other system tables table during cluster recovery; the cluster must be UP to accept connections.

Column Name	Data Type	Description
`NODE_NAME`	VARCHAR	Name of the node that is reporting the requested information.
`RECOVER_EPOCH`	INTEGER	Epoch the recovery operation is trying to catch up to.
`RECOVERY_PHASE`	VARCHAR	Current stage in the recovery process. Can be one of the following: `NULL` `current` `historical pass` X, where X is the iteration count
`SPLITS_COMPLETED`	INTEGER	Number of independent recovery SPLITS queries that have run and need to run.
`SPLITS_TOTAL`	INTEGER	Total number of SPLITS queries that ran. Each query corresponds to one row in the PROJECTION_RECOVERIES table. If SPLITS_TOTAL = 2, then there should be 2 rows added to PROJECTION_RECOVERIES, showing query details.
`HISTORICAL_COMPLETED`	INTEGER	Number of independent recovery HISTORICAL queries that have run and need to run.
`HISTORICAL_TOTAL`	INTEGER	Total number of HISTORICAL queries that ran. Each query corresponds to one row in the PROJECTION_RECOVERIES table. If HISTORICAL_TOTAL = 2, then there should be 2 rows added to PROJECTION_RECOVERIES, showing query details.
`CURRENT_COMPLETED`	INTEGER	Number of independent recovery CURRENT queries that have run and need to run.
`CURRENT_TOTAL`	INTEGER	Total number of CURRENT queries that ran. Each query corresponds to one row in the PROJECTION_RECOVERIES table. If CURRENT_TOTAL = 2, then there should be 2 rows added to PROJECTION_RECOVERIES, showing query details.
`IS_RUNNING`	BOOLEAN	True (t) if the node is still running recovery; otherwise false (f).

Privileges

None

90 - REMOTE_REPLICATION_STATUS

Provides the status of replication tasks to alternate clusters.

Column Name	Data Type	Description
CURRENT_EPOCH	INTEGER
EPOCH	INTEGER
LAST_REPLICATED_TIME	TIMESTAMPTZ
OBJECTS	VARCHAR
REPLICATED_EPOCH	INTEGER
REPLICATION_POINT	VARCHAR
SNAPSHOT_NAME	VARCHAR

Privileges

None

91 - REPARENTED_ON_DROP

Lists re-parenting events of objects that were dropped from their original owner but still remain in Vertica.

Lists re-parenting events of objects that were dropped from their original owner but still remain in Vertica. For example, a user may leave the organization and need to be removed from the database. When the database administrator drops the user from the database, that user's objects are re-parented to another user.

In some cases, a Vertica user's objects are reassigned based on the GlobalHeirUsername parameter. In this case, a user's objects are re-parented to the user indicated by this parameter.

Column Name	Data Type	Description
`REPARENT_TIMESTAMP`	TIMESTAMP	The time the re-parenting event occurred.
`NODE_NAME`	VARCHAR	The name of the node or nodes on which the re-parenting occurred.
`SESSION_ID`	VARCHAR	The identification number of the re-parenting event.
`USER_ID`	INTEGER	The unique, system-generated user identification number.
`USER_NAME`	VARCHAR	The name of the user that caused the re-parenting event. For example, a dbadmin user may have dropped a user thus re-parenting that user's objects.
`TRANSACTION_ID`	INTEGER	The system-generated transaction identification number. Is NULL if a transaction id does not exist.
`OLD_OWNER_NAME`	VARCHAR	The the name of the dropped user who used to own the re-parented object.
`OLD_OWNER_OID`	INTEGER	The unique identification number of the user who used to own the re-parented object.
`NEW_OWNER_NAME`	VARCHAR	The name of the user who now owns the re-parented objects.
`NEW_OWNER_OID`	INTEGER	The unique identification number of the user who now owns the re-parented objects.
`OBJ_NAME`	VARCHAR	The name of the object being re-parented.
`OBJ_OID`	INTEGER	The unique identification number of the object being re-parented.
`SCHEMA_NAME`	VARCHAR	The name of the schema in which the object resides.
`SCHEMA_OID`	INTEGER	The unique identification number of the schema in which the re-parented object resides.

92 - REPLICATION_STATUS

Monitors the status of server-based data replication.

Shows the status of current and past server-based replication of data from another Eon Mode database to this database. Each row records node-specific information about a given replication. For more information about server-based replication, see Server-based replication.

Note

If you replicate data between the main cluster and a sandboxed cluster, the SENT_BYTES and TOTAL_BYTES_TO_SEND columns will both have a value of zero. Because the clusters use the same communal storage location, no data is actually copied between the two clusters.

Column name	Data type	Description
NODE_NAME	VARCHAR(128)	Name of the node participating in the replication.
TRANSACTION_ID	INT	Transaction in which the replication took place.
OPERATION_NAME	VARCHAR	Name of the replication operation for which information is listed, one of the following: `load_snapshot_prep`: Catalog files from the source database are copied to `NODE_NAME` and analyzed to determine which storage files need to be copied to the target database. `data_transfer`: Storage files from the source database are copied to the target database's communal storage. `load_snapshot`: Catalog objects loaded during `load_snapshot_prep` are installed on the node.
STATUS	VARCHAR(8192)	State of the `OPERATION_NAME` operation, one of the following: `failed`: The operation did not complete successfully. `completed`: The operation has completed. `started`: The operation is in progress on the node.
SENT_BYTES	INT	Number of bytes that the node has retrieved from the source database's communal storage.
TOTAL_BYTES_TO_SEND	INT	Total number of bytes the node has to retrieve from the source database's communal storage for this replication.
START_TIME	TIMESTAMPTZ	Start time of the replication.
END_TIME	TIMESTAMPTZ	Time at which the node finished transferring data for the replication.

Example

=> SELECT node_name, opeartion_name, status FROM REPLICATION_STATUS 
   ORDER BY node_name;
       node_name       |   operation_name   |  status         
-----------------------+--------------------+-----------
 v_target_db_node0001  | load_snapshot_prep | completed        
 v_target_db_node0001  | data_transfer      | completed         
 v_target_db_node0001  | load_snapshot      | completed        
 v_target_db_node0002  | load_snapshot_prep | completed        
 v_target_db_node0002  | data_transfer      | completed         
 v_target_db_node0002  | load_snapshot      | completed         
 v_target_db_node0003  | load_snapshot_prep | completed
 v_target_db_node0003  | data_transfer      | completed
 v_target_db_node0003  | load_snapshot      | completed
(9 rows)

93 - RESHARDING_EVENTS

Monitors historic and ongoing resharding operations.

Column Name	Data Type	Description
EVENT_TIME_STAMP	TIMESTAMP	Date and time of the resharding event.
NODE_NAME	VARCHAR	Node name for which resharding information is listed.
SESSION_ID	VARCHAR	Unique numeric ID assigned by the Vertica catalog that identifies the session for which resharding information is captured. This identifier is unique within the cluster at any point in time but can be reused when the session closes.
USER_ID	INTEGER	Numeric ID of the user who ran the resharding operation.
USER_NAME	VARCHAR	Name of the user who ran the resharding operation.
TRANSACTION_ID	INTEGER	Numeric ID of the specified rehsarding transaction within the session.
RUNNING_STATUS	VARCHAR	Current status of the resharding operation, one of the following strings: START: The resharding operation has begun on all nodes. RUNNING: The shard named OLD_SHARD_NAME is currently being resharded on the node. RESHARDED: The resharding operation on the node is complete for the shard named OLD_SHARD_NAME. ABORT: The resharding operation was aborted on all nodes. COMPLETE: The resharding operation has completed for all nodes in the database. Note Only the RESHARDED and RUNNING statuses are logged for each node. All other statuses are logged only on the initiator node.
OLD_SHARD_NAME	VARCHAR	Name of the shard to which the node was subscribed previous to the resharding operation. You can query the SHARDS system table for information about the new shard configuration.
OLD_SHARD_OID	INTEGER	Numeric ID of the shard to which the node was subscribed previous to the resharding operation.
OLD_SHARD_LOWER_BOUND	INTEGER	Lower bound of the shard to which the node was subscribed prior to the resharding operation. This value is set only if the resharding operation is complete for the shard specified by OLD_SHARD_OID.
OLD_SHARD_UPPER_BOUND	INTEGER	Upper bound of the shard to which the node was subscribed prior to the resharding operation. This value is set only if the resharding operation is complete for the shard specified by OLD_SHARD_OID.
CATALOG_SIZE	INTEGER	Catalog size (in bytes) on the node for the shard specified by OLD_SHARD_NAME. This value is provided only when the RUNNING_STATUS of the node is RUNNING or RESHARDED.

Privileges

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

94 - RESOURCE_ACQUISITIONS

Retains information about resources (memory, open file handles, threads) acquired by each running request.

Retains information about resources (memory, open file handles, threads) acquired by each running request. Each request is uniquely identified by its transaction and statement IDs within a given session.

Important

If a request cascades to one or more resource pools beyond the original pool, this table contains multiple records for the same request—one record for each resource pool. The following values are specific to each resource pool:

Timestamp values: QUEUE_ENTRY_TIMESTAMP, ACQUISITION_TIMESTAMP, and RELEASE_TIMESTAMP
DURATION_MS
IS_EXECUTING

You can trace the history of cascade events by querying system table RESOURCE_POOL_MOVE.

Column Name	Data Type	Description
`NODE_NAME`	`VARCHAR`	Node name for which information is listed.
`TRANSACTION_ID`	`INTEGER`	Transaction identifier for this request.
`STATEMENT_ID`	`INTEGER`	Unique numeric ID for each statement within a transaction. NULL indicates that no statement is currently being processed.
`REQUEST_TYPE`	`VARCHAR`	Type of request issued to a resource pool. End users always see this column set to Reserve, to indicate that the request is query-specific.
`POOL_ID` / `POOL_NAME`	`INTEGER` / `VARCHAR`	Each resource pool that participated in handling this request: `POOL_ID`: A unique numeric ID assigned by the Vertica catalog that uniquely identifies the resource pool. `POOL_NAME`: Name of the resource pool.
`THREAD_COUNT`	`INTEGER`	Number of threads in use by this request.
`OPEN_FILE_HANDLE_COUNT`	`INTEGER`	Number of open file handles in use by this request.
`MEMORY_INUSE_KB`	`INTEGER`	Total amount of memory in kilobytes acquired by this query. Column `RESERVED_EXTRA_MEMORY`_B in system table `QUERY_PROFILES` shows how much unused memory (in bytes) remains that is reserved for a given query but is unassigned to a specific operator. If operators for a query acquire all memory specified by `MEMORY_INUSE_KB`, the plan must request more memory from the Vertica Resource Manager.
`QUEUE_ENTRY_TIMESTAMP`	`TIMESTAMPTZ`	Timestamp when the request was queued in this resource pool.
`ACQUISITION_TIMESTAMP`	`TIMESTAMPTZ`	Timestamp when the request was admitted to run.
`RELEASE_TIMESTAMP`	`TIMESTAMPTZ`	Time when Vertica released this resource acquisition.
`DURATION_MS`	`INTEGER`	Duration in milliseconds of request execution. If the request cascaded across multiple resource pools, `DURATION_MS` applies only to this resource pool.
`IS_EXECUTING`	`BOOLEAN`	Set to true if the resource pool is still executing this request. A value of false can indicate one of the following: The request was completed or denied. The request cascaded to another resource pool.

Privileges

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

Queue wait time

You can calculate how long a resource pool queues a given request before it begins execution by subtracting QUEUE_ENTRY_TIMESTAMP from ACQUISITION_TIMESTAMP. For example:

 => SELECT pool_name, queue_entry_timestamp, acquisition_timestamp,
    (acquisition_timestamp-queue_entry_timestamp) AS 'queue wait'
    FROM V_MONITOR.RESOURCE_ACQUISITIONS WHERE node_name ILIKE '%node0001';

95 - RESOURCE_POOL_MOVE

Displays the cascade event information on each node.

Column Name	Data Type	Description
`NODE_NAME`	VARCHAR	Node name for which information is listed.
`MOVE_TIMESTAMP`	TIMESTAMPTZ	Time when the query attempted to move to the target pool.
`SESSION_ID`	VARCHAR	Identifier for this session. This identifier is unique within the cluster at any point in time but can be reused when the session closes.
`USER_ID`	INTEGER	Identifies the query event user.
`USER_NAME`	VARCHAR	Name of the user for which Vertica lists query information at the time it records the session.
`TRANSACTION_ID`	INTEGER	Transaction identifier for the request.
`STATEMENT_ID`	INTEGER	Unique numeric ID for the statement.
`SOURCE_POOL_NAME`	VARCHAR	Name of the resource pool where the query was executing when Vertica attempted the move.
`TARGET_POOL_NAME`	VARCHAR	Name of resource pool where the query attempted to move.
`MOVE_CAUSE`	VARCHAR	Denotes why the query attempted to move. Valid values: MOVE RESOURCE POOL COMMAND RUNTIMECAP EXCEEDED
`SOURCE_CAP`	INTEGER	Effective `RUNTIMECAP` value for the source pool. The value represents the lowest of these three values: session `RUNTIMECAP` user `RUNTIMECAP` source pool `RUNTIMECAP`
`TARGET_CAP`	INTEGER	Effective `RUNTIMECAP` value for the target pool. The value represents the lowest of these three values: session `RUNTIMECAP` user `RUNTIMECAP` target pool `RUNTIMECAP`
`SUCCESS`	BOOLEAN	True, if the query successfully moved to the target pool.
`RESULT_REASON`	VARCHAR	States reason for success or failure of the move.

96 - RESOURCE_POOL_STATUS

Provides current state of built-in and user-defined resource pools on each node.

Provides current state of built-in and user-defined resource pools on each node. Information includes:

Current memory usage
Resources requested and acquired by various requests
Number of queries executing

For general information about resource pools, see Resource pool architecture.

Column Name	Data Type	Description
NODE_NAME	VARCHAR	Name of the node for which information is provided.
POOL_OID	INTEGER	Unique numeric ID that identifies the pool and is assigned by the Vertica catalog.
POOL_NAME	VARCHAR	Name of the resource pool.
IS_INTERNAL	BOOLEAN	Denotes whether a pool is built-in.
MEMORY_SIZE_KB	INTEGER	Value of `MEMORYSIZE` setting of the pool in kilobytes.
MEMORY_SIZE_ACTUAL_KB	INTEGER	Current amount of memory, in kilobytes, allocated to the pool by the resource manager. The actual size can be less than specified in the DDL, if both the following conditions exist: The pool has been recently altered in a running system. The request to shuffle memory is pending.
MEMORY_INUSE_KB	INTEGER	Amount of memory, in kilobytes, acquired by requests running against this pool.
GENERAL_MEMORY_BORROWED_KB	INTEGER	Amount of memory, in kilobytes, borrowed from the `GENERAL` pool by requests running against this pool. The sum of `MEMORY_INUSE_KB` and `GENERAL_MEMORY_BORROWED_KB` should be less than `MAX_MEMORY_SIZE_KB`.
QUEUEING_THRESHOLD_KB	INTEGER	Calculated as `MAX_MEMORY_SIZE_KB * 0.95`. When the amount of memory used by all requests against this resource pool exceeds the `QUEUEING_THRESHOLD_KB`, new requests against the pool are queued until memory becomes available.
MAX_MEMORY_SIZE_KB	INTEGER	Value, in kilobytes, of the `MAXMEMORYSIZE` parameter as defined for the pool. After this threshold is reached, new requests against this pool are rejected or queued until memory becomes available. Note `MAX_MEMORY_SIZE_KB` might not reflect the set `MAXMEMORYSIZE` parameter value if the specified value cannot be reached. For example, if `MAXMEMORYSIZE` = 10G but less than 2G is available, `MAX_MEMORY_SIZE_KB` will not reflect the original value in KB. Instead, it will display only 2G in KB, as that is the highest value available to it.
MAX_QUERY_MEMORY_SIZE_KB	INTEGER	Value, in kilobytes, of the `MAXQUERYMEMORYSIZE` parameter as defined for the pool. The resource pool limits this amount of memory to all queries that execute in it.
RUNNING_QUERY_COUNT	INTEGER	Number of queries currently executing in this pool.
PLANNED_CONCURRENCY	INTEGER	Value of `PLANNEDCONCURRENCY` parameter as defined for the pool.
MAX_CONCURRENCY	INTEGER	Value of `MAXCONCURRENCY` parameter as defined for the pool.
IS_STANDALONE	BOOLEAN	If the pool is configured to have `MEMORYSIZE` equal to `MAXMEMORYSIZE`, the pool is considered standalone because it does not borrow any memory from the General pool.
QUEUE_TIMEOUT	INTERVAL	The interval that the request waits for resources to become available before being rejected. If you set this value to NONE, Vertica displays it as NULL.
QUEUE_TIMEOUT_IN_SECONDS	INTEGER	Value of `QUEUETIMEOUT` parameter as defined for the pool. If `QUEUETIMEOUT` is set to NONE, Vertica displays this value as NULL.
EXECUTION_PARALLELISM	INTEGER	Limits the number of threads used to process any single query issued in this resource pool.
PRIORITY	INTEGER	Value of `PRIORITY` parameter as defined for the pool. When set to `HOLD`, Vertica sets a pool's priority to `-999` so the query remains queued until `QUEUETIMEOUT` is reached.
RUNTIMECAP_IN_SECONDS	INTEGER	Defined for this pool by parameter RUNTIMECAP, specifies in seconds the maximum time a query in the pool can execute. If a query exceeds this setting, it tries to cascade to a secondary pool.
RUNTIME_PRIORITY	VARCHAR	Defined for this pool by parameter RUNTIMEPRIORITY, determines how the resource manager should prioritize dedication of run-time resources (CPU, I/O bandwidth) to queries already running in this resource pool.
RUNTIME_PRIORITY_THRESHOLD	INTEGER	Defined for this pool by parameter RUNTIMEPRIORITYTHRESHOLD, specifies in seconds a time limit in which a query must finish before the resource manager assigns to it the resource pool's RUNTIME_PRIORITY setting.
SINGLE_INITIATOR	BOOLEAN	Set for backward compatibility.
QUERY_BUDGET_KB	INTEGER	The current amount of memory that queries are tuned to use. The calculation that Vertica uses to determine this value is described inQuery budgeting. Note The calculated value can change when one or more running queries needs more than the budgeted amount to run. For a detailed example of query budget calculations, see Do You Need to Put Your Query on a Budget? in the Vertica User Community.
CPU_AFFINITY_SET	VARCHAR	The set of CPUs on which queries associated with this pool are executed. Can be: A percentage of CPUs on the system A zero-based list of CPUs (a four-CPU system c of CPUs 0, 1, 2, and 3).
CPU_AFFINITY_MASK	VARCHAR	The bit mask of CPUs available for use in this pool, read from right to left. See Examples below.
CPU_AFFINITY_MODE	VARCHAR	The mode for the CPU affinity, one of the following: ANY EXCLUSIVE SHARED

Examples

The following query returns bit masks that show CPU assignments for three user-defined resource pools. Resource pool bigqueries runs queries on CPU 0, ceo_pool on CPU 1, and testrp on CPUs 0 and 1:

 => SELECT pool_name, node_name, cpu_affinity_set, cpu_affinity_mode,
      TO_BITSTRING(CPU_AFFINITY_MASK::VARBINARY) "CPU Affinity Mask"
      FROM resource_pool_status WHERE IS_INTERNAL = 'false' order by pool_name, node_name;
 pool_name  |    node_name     | cpu_affinity_set | cpu_affinity_mode | CPU Affinity Mask
------------+------------------+------------------+-------------------+-------------------
 bigqueries | v_vmart_node0001 | 0                | SHARED            | 00110001
 bigqueries | v_vmart_node0002 | 0                | SHARED            | 00110001
 bigqueries | v_vmart_node0003 | 0                | SHARED            | 00110001
 ceo_pool   | v_vmart_node0001 | 1                | SHARED            | 00110010
 ceo_pool   | v_vmart_node0002 | 1                | SHARED            | 00110010
 ceo_pool   | v_vmart_node0003 | 1                | SHARED            | 00110010
 testrp     | v_vmart_node0001 | 0-1              | SHARED            | 00110011
 testrp     | v_vmart_node0002 | 0-1              | SHARED            | 00110011
 testrp     | v_vmart_node0003 | 0-1              | SHARED            | 00110011
(9 rows)

97 - RESOURCE_QUEUES

Provides information about requests pending for various resource pools.

Column Name	Data Type	Description
`NODE_NAME`	VARCHAR	The name of the node for which information is listed.
`TRANSACTION_ID`	INTEGER	Transaction identifier for this request
`STATEMENT_ID`	INTEGER	Unique numeric ID for the currently-running statement. NULL indicates that no statement is currently being processed. The combination of TRANSACTION_ID, STATEMENT_ID uniquely identifies a statement within a session.
`POOL_NAME`	VARCHAR	The name of the resource pool
`MEMORY_REQUESTED_KB`	INTEGER	Amount of memory in kilobytes requested by this request
`PRIORITY`	INTEGER	Value of PRIORITY parameter specified when defining the pool.
`POSITION_IN_QUEUE`	INTEGER	Position of this request within the pool’s queue
`QUEUE_ENTRY_TIMESTAMP`	TIMESTAMP	Timestamp when the request was queued

98 - RESOURCE_REJECTION_DETAILS

Records an entry for each resource request that Vertica denies.

Records an entry for each resource request that Vertica denies. This is useful for determining if there are resource space issues, as well as which users/pools encounter problems.

Column Name	Data Type	Description
`REJECTED_TIMESTAMP`	TIMESTAMPTZ	Time when Vertica rejected the resource.
`NODE_NAME`	VARCHAR	Name of the node that is reporting the requested information.
`USER_NAME`	VARCHAR	Name of the user at the time Vertica recorded the session.
`SESSION_ID`	VARCHAR	Identifier for this session. This identifier is unique within the cluster at any point in time but can be reused when the session closes.
`REQUEST_ID`	INTEGER	Unique identifier of the query request in the user session.
`TRANSACTION_ID`	INTEGER	Identifier for the transaction within the session, if any; otherwise `NULL`.
`STATEMENT_ID`	INTEGER	Unique numeric ID for the currently-running statement. NULL indicates that no statement is currently being processed. The combination of TRANSACTION_ID, STATEMENT_ID, and REQUEST_ID uniquely identifies a statement within a session.
`POOL_ID`	INTEGER	Catalog-assigned integer value that uniquely identifies theresource pool.
`POOL_NAME`	VARCHAR	Name of the resource pool
`REASON`	VARCHAR	Reason for rejecting this request; for example: Usage of single request exceeds high limit Timed out waiting for resource reservation Canceled waiting for resource reservation
`RESOURCE_TYPE`	VARCHAR	Memory, threads, file handles or execution slots. The following list shows the resources that are limited by the resource manager. A query might need some amount of each resource, and if the amount needed is not available, the query is queued and could eventually time out of the queue and be rejected. Number of running plans Number of running plans on initiator node (local) Number of requested threads Number of requested file handles Number of requested KB of memory Number of requested KB of address space Note: Execution slots are determined by MAXCONCURRENCY parameter.
`REJECTED_VALUE`	INTEGER	Amount of the specific resource requested by the last rejection

Privileges

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

99 - RESOURCE_REJECTIONS

Monitors requests for resources that are rejected by the.

Monitors requests for resources that are rejected by the Resource manager. Information is valid only as long as the node is up and the counters reset to 0 upon node restart.

Column Name	Data Type	Description
`NODE_NAME`	VARCHAR	The node name for which information is listed.
`POOL_ID`	INTEGER	Catalog-assigned integer value that uniquely identifies theresource pool.
`POOL_NAME`	VARCHAR	Name of the resource pool.
`REASON`	VARCHAR	Reason for rejecting this request, for example: Usage of single request exceeds high limit Timed out waiting for resource reservation Canceled waiting for resource reservation
`RESOURCE_TYPE`	VARCHAR	Memory, threads, file handles or execution slots. The following list shows the resources that are limited by the resource manager. A query might need some amount of each resource, and if the amount needed is not available, the query is queued and could eventually time out of the queue and be rejected. Number of running plans Number of running plans on initiator node (local) Number of requested threads Number of requested file handles Number of requested KB of memory Number of requested KB of address space Note Execution slots are determined by MAXCONCURRENCY parameter.
`REJECTION_COUNT`	INTEGER	Number of requests rejected due to specified reason and `RESOURCE_TYPE`.
`FIRST_REJECTED_TIMESTAMP`	TIMESTAMPTZ	Time of the first rejection for this pool.
`LAST_REJECTED_TIMESTAMP`	TIMESTAMPTZ	Time of the last rejection for this pool.
`LAST_REJECTED_VALUE`	INTEGER	Amount of the specific resource requested by the last rejection.

Examples

=> SELECT node_name, pool_name, reason, resource_type, rejection_count AS count, last_rejected_value AS value FROM resource_rejections;
    node_name     | pool_name |           reason            | resource_type | count |  value
------------------+-----------+-----------------------------+---------------+-------+---------
 v_vmart_node0001 | sysquery  | Request exceeded high limit | Memory(KB)    |     1 | 8248449
(1 row)

100 - RESOURCE_USAGE

Monitors system resource management on each node.

Column Name	Data Type	Description
NODE_NAME	VARCHAR	The node name for which information is listed.
REQUEST_COUNT	INTEGER	The cumulative number of requests for threads, file handles, and memory (in kilobytes).
LOCAL_REQUEST_COUNT	INTEGER	The cumulative number of local requests.
REQUEST_QUEUE_DEPTH	INTEGER	The current request queue depth.
ACTIVE_THREAD_COUNT	INTEGER	The current number of active threads.
OPEN_FILE_HANDLE_COUNT	INTEGER	The current number of open file handles.
MEMORY_REQUESTED_KB	INTEGER	The memory requested in kilobytes.
ADDRESS_SPACE_REQUESTED_KB	INTEGER	The address space requested in kilobytes.
ROS_USED_BYTES	INTEGER	The size of the ROS in bytes.
ROS_ROW_COUNT	INTEGER	The number of rows in the ROS.
RESOURCE_REQUEST_REJECT_COUNT	INTEGER	The number of rejected plan requests.
RESOURCE_REQUEST_TIMEOUT_COUNT	INTEGER	The number of resource request timeouts.
RESOURCE_REQUEST_CANCEL_COUNT	INTEGER	The number of resource request cancelations.
DISK_SPACE_REQUEST_REJECT_COUNT	INTEGER	The number of rejected disk write requests.
FAILED_VOLUME_REJECT_COUNT	INTEGER	The number of rejections due to a failed volume.
TOKENS_USED	INTEGER	For internal use only.
TOKENS_AVAILABLE	INTEGER	For internal use only.

101 - SESSION_MARS_STORE

Shows Multiple Active Result Sets (MARS) storage information.

Column Name	Data Type	Description
`NODE_NAME`	VARCHAR	Name of the node that is reporting the requested information.
`SESSION_ID`	VARCHAR	Identifier of the Vertica session. This identifier is unique within the cluster for the current session but can be reused in a subsequent session.
`USER_NAME`	VARCHAR	The username used to create the connection.
`RESULTSET_ID`	INTEGER	Identifier assigned to the result set.
`ROW_COUNT`	INTEGER	Number of rows requested by the query.
`REMAINING_ROW_COUNT`	INTEGER	Number of rows that still need to be returned.
`BYTES_USED`	INTEGER	The number of bytes requested.

102 - SESSION_PARAMETERS

Provides information about user-defined parameters (UDPARAMETERS) set for the current session.

Column Name	Data Type	Description
`SESSION_ID`	VARCHAR	The unique identifier for the session.
`SCHEMA_NAME`	VARCHAR	The name of the schema on which the session is running.
`LIB_NAME`	VARCHAR	The name of the user library running the UDx, if necessary.
`LIB_OID`	VARCHAR	The object ID of the library containing the function, if one is running.
`PARAMETER_NAME`	VARCHAR	The name of the session parameter.
`CURRENT_VALUE`	VARCHAR	The value of the session parameter.

103 - SESSION_PROFILES

Provides basic session parameters and lock time out data.

Provides basic session parameters and lock time out data. To obtain information about sessions, see Profiling database performance.

Column Name	Data Type	Description
`NODE_NAME`	VARCHAR	The node name for which information is listed.
`USER_NAME`	VARCHAR	The name used to log in to the database or NULL if the session is internal.
`CLIENT_HOSTNAME`	VARCHAR	The host name and port of the TCP socket from which the client connection was made; NULL if the session is internal.
`LOGIN_TIMESTAMP`	TIMESTAMP	The date and time the user logged into the database or when the internal session was created. This field is useful for identifying sessions that have been left open for a period of time and could be idle.
`LOGOUT_TIMESTAMP`	TIMESTAMP	The date and time the user logged out of the database or when the internal session was closed.
`SESSION_ID`	VARCHAR	A unique numeric ID assigned by the Vertica catalog, which identifies the session for which profiling information is captured. This identifier is unique within the cluster at any point in time but can be reused when the session closes.
`EXECUTED_STATEMENT_SUCCESS_COUNT`	INTEGER	The number of successfully run statements.
`EXECUTED_STATEMENT_FAILURE_COUNT`	INTEGER	The number of unsuccessfully run statements.
`LOCK_GRANT_COUNT`	INTEGER	The number of locks granted during the session.
`DEADLOCK_COUNT`	INTEGER	The number of deadlocks encountered during the session.
`LOCK_TIMEOUT_COUNT`	INTEGER	The number of times a lock timed out during the session.
`LOCK_CANCELLATION_COUNT`	INTEGER	The number of times a lock was canceled during the session.
`LOCK_REJECTION_COUNT`	INTEGER	The number of times a lock was rejected during a session.
`LOCK_ERROR_COUNT`	INTEGER	The number of lock errors encountered during the session.
`CLIENT_TYPE`	VARCHAR	The type of client from which the connection was made. Possible client type values: ADO.NET Driver ODBC Driver JDBC Driver vsql
`CLIENT_VERSION`	VARCHAR	Returns the client version.
`CLIENT_OS`	VARCHAR	Returns the client operating system.
`CLIENT_OS_USER_NAME`	VARCHAR	The name of the user that logged into, or attempted to log into, the database. This is logged even when the login attempt is unsuccessful.

104 - SESSIONS

Monitors external sessions.

Monitors external sessions. Use this table to perform the following tasks:

Identify users who are running lengthy queries.
Identify users who hold locks because of an idle but uncommitted transaction.
Determine the details of the database security used for a particular session, either Secure Socket Layer (SSL) or client authentication.
Identify client-specific information, such as client version.

Note

During session initialization and termination, you might see sessions running only on nodes other than the node on which you ran the virtual table query. This is a temporary situation that corrects itself when session initialization and termination complete.

Column Name	Data Type	Description
`NODE_NAME`	`VARCHAR`	The node name for which information is listed.
`USER_NAME`	`VARCHAR`	The name used to log in to the database or NULL if the session is internal.
`CLIENT_OS_HOSTNAME`	`VARCHAR`	The hostname of the client as reported by their operating system.
`CLIENT_HOSTNAME`	`VARCHAR`	The IP address and port of the TCP socket from which the client connection was made; NULL if the session is internal. Vertica accepts either IPv4 or IPv6 connections from a client machine. If the client machine contains mappings for both IPv4 and IPv6, the server randomly chooses one IP address family to make a connection. This can cause the CLIENT_HOSTNAME column to display either IPv4 or IPv6 values, based on which address family the server chooses.
`CLIENT_PID`	`INTEGER`	The process identifier of the client process that issued this connection. Remember that the client process could be on a different machine than the server.
`LOGIN_TIMESTAMP`	`TIMESTAMP`	The date and time the user logged into the database or when the internal session was created. This field can help you identify sessions that have been left open for a period of time and could be idle.
`SESSION_ID`	`VARCHAR`	The identifier required to close or interrupt a session. This identifier is unique within the cluster at any point in time but can be reused when the session closes.
`IDLE_SESSION_TIMEOUT`	`VARCHAR`	Specifies how long this session can remain idle before timing out, set by `SET SESSION IDLESESSIONTIMEOUT`.
`GRACE_PERIOD`	`VARCHAR`	Specifies how long a session socket remains blocked while awaiting client input or output for a given query, set by `SET SESSION GRACEPERIOD`. If the socket is blocked for a continuous period that exceeds the grace period setting, the server shuts down the socket and throws a fatal error. The session is then terminated.
`CLIENT_LABEL`	`VARCHAR`	A user-specified label for the client connection that can be set when using ODBC. See `Label` in ODBC DSN connection properties. An `MC` output value means there are is a client connection to an MC-managed database for that `USER_NAME`
`TRANSACTION_START`	`DATE`	The date/time the current transaction started or NULL if no transaction is running.
`TRANSACTION_ID`	`INTEGER`	A string containing the hexadecimal representation of the transaction ID, if any; otherwise, NULL.
`TRANSACTION _DESCRIPTION`	`VARCHAR`	Description of the current transaction.
`STATEMENT_START`	`TIMESTAMP`	The timestamp the current statement started execution, or NULL if no statement is running.
`STATEMENT_ID`	`INTEGER`	A unique numeric ID assigned by the Vertica catalog, which identifies the currently-executing statement. A value of NULL indicates that no statement is currently being processed.
`LAST_STATEMENT_DURATION_US`	`INTEGER`	The duration of the last completed statement in microseconds.
`RUNTIME_PRIORITY`	`VARCHAR`	Specifies how many run-time resources (CPU, I/O bandwidth) are allocated to queries that are running in the resource pool.
`CURRENT_STATEMENT`	`VARCHAR`	The currently executing statement, if any. NULL indicates that no statement is currently being processed.
`LAST_STATEMENT`	`VARCHAR`	NULL if the user has just logged in; otherwise the currently running statement or the most recently completed statement.
`SSL_STATE`	`VARCHAR`	Indicates if Vertica used Secure Socket Layer (SSL) for a particular session. Possible values are: None—Vertica did not use SSL. Server—Server authentication was used, so the client could authenticate the server. Mutual—Both the server and the client authenticated one another through mutual authentication. See Security and authentication and TLS protocol.
`AUTHENTICATION_ METHOD`	`VARCHAR`	The type of client authentication used for a particular session, if known. Possible values are: `Unknown` `Trust` `Reject` `Hash` `Ident` `LDAP` `GSS` `TLS` See Security and authentication and Configuring client authentication.
`CLIENT_TYPE`	`VARCHAR`	The type of client from which the connection was made. Possible client type values: ADO.NET Driver ODBC Driver JDBC Driver vsql
`CLIENT_VERSION`	`VARCHAR`	Client version.
`CLIENT_OS`	`VARCHAR`	Client operating system.
`CLIENT_OS_USER _NAME`	`VARCHAR`	The name of the user that logged into, or attempted to log into, the database. This is logged even when the login attempt is unsuccessful.
`CLIENT_AUTHENTICATION_NAME`	`VARCHAR`	User-assigned name of the authentication method.
`CLIENT_ AUTHENTICATION`	`INTEGER`	Object identifier of the client authentication method.
`REQUESTED_PROTOCOL`	`INTEGER`	The requested Vertica client server protocol to be used when connecting.
`EFFECTIVE_PROTOCOL`	`INTEGER`	The requested Vertica client server protocol used when connecting.
`EXTERNAL_MEMORY_KB`	`INTEGER`	Amount of memory consumed by the Java Virtual Machines associated with the session.

Privileges

A superuser has unrestricted access to all session information. Users can view information only about their own, current sessions.

105 - SPREAD_STATE

Lists daemon settings for all nodes in the cluster.

Lists Spread daemon settings for all nodes in the cluster.

Column Name	Data Type	Description
NODE_NAME	VARCHAR	Which node the settings are for.
TOKEN_TIMEOUT	INTEGER	The timeout period in milliseconds before spread considers a node to be down due to lack of response to a message.

Examples

=> SELECT * FROM V_MONITOR.SPREAD_STATE;
    node_name     | token_timeout
------------------+---------------
 v_vmart_node0003 |          8000
 v_vmart_node0001 |          8000
 v_vmart_node0002 |          8000
(3 rows)

106 - SQS_LISTENER_ERROR_HISTORY

Lists errors and rejections in reading from SQS.

Lists errors and rejections that occur when reading from SQS, such as malformed SQS messages and authorization problems. These errors occur when Vertica reads from SQS as part of automatic data load.

The DATA_LOADER_EVENTS table records errors that occur during the load (COPY) step but does not include pre-load errors from SQS.

Column Name	Column Type	Description
`REPORT_TIME`	TIMESTAMPTZ	Time when Vertica recorded the error or rejection.
`NODE_NAME`	VARCHAR	Name of the node where the error occurred.
`ERROR_SOURCE`	VARCHAR	Component that produced the error, such as "SQSTrigger" or "SQSListener".
`MESSAGE`	VARCHAR	More details about the error.
`QUEUE_URL`	VARCHAR	SQS queue.

107 - STATEMENT_OUTCOMES

...

Records consolidated information about session-level statements, which can consist of more than one operation and might include retries. For example, inserting into a table with a column constraint includes an INSERT query and a constraint check. STATEMENT_OUTCOMES has one row for the statement, and the related QUERY_REQUESTS table has two rows.

This table tracks only statements that are executed within transactions. Many meta-functions do not automatically open transactions.

Column Name	Data Type	Description
NODE_NAME	VARCHAR	Name of the node that reported the requested information.
USER_NAME	VARCHAR	Name of the user who issued the query at the time Vertica recorded the session.
SESSION_ID	VARCHAR	Identifier for this session. This identifier is unique within the cluster at any point in time but can be reused after the session ends.
TRANSACTION_ID	INTEGER	Identifier for the transaction within the session. Statements that are not executed within transactions are not recorded in this table.
STATEMENT_ID	INTEGER	Unique identifier for the statement. The combination of TRANSACTION_ID and STATEMENT_ID uniquely identifies a user query within a session.
REQUEST_TYPE	VARCHAR	Type of the query request. Examples include, but are not limited to: QUERY DDL LOAD UTILITY TRANSACTION PREPARE EXECUTE SET SHOW
REQUEST	VARCHAR	Query statement.
REQUEST_LABEL	VARCHAR	Label of the query, if any.
SEARCH_PATH	VARCHAR	Contents of the search path.
SUCCESS	BOOLEAN	Whether the statement succeeded.
ERROR	VARCHAR	If the statement did not succeed, the error message.
START_TIMESTAMP	TIMESTAMPTZ	Beginning of history interval.
END_TIMESTAMP	TIMESTAMPTZ	End of history interval.
REQUEST_DURATION	TIMESTAMPTZ	Statement execution time in days, hours, minutes, seconds, and milliseconds. This time includes all operations that make up the statement, including any retries.
REQUEST_DURATION_MS	INTEGER	Statement execution time in milliseconds. This time includes all operations that make up the statement, including any retries.
IS_EXECUTING	BOOLEAN	Whether the statement is currently running.

Privileges

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

108 - STORAGE_BUNDLE_INFO_STATISTICS

Indicates which projections have storage containers with invalid bundle metadata in the database catalog.

Indicates which projections have storage containers with invalid bundle metadata in the database catalog. If any ROS or DV container has invalid bundle metadata fields, Vertica increments the corresponding column (ros_without_bundle_info_count or dv_ros_without_bundle_info_count) by one.

To update the catalog with valid bundle metadata, call UPDATE_STORAGE_CATALOG, as an argument to Vertica meta-function DO_TM_TASK. For details, see Writing bundle metadata to the catalog.

Column Name	Data Type	Description
`node_name`	VARCHAR	Name of this projection's node
`projection_oid`	INTEGER	Projection's unique catalog identifier
`projection_name`	VARCHAR	Projection name
`projection_schema`	VARCHAR	Projection schema name
`total_ros_count`	INTEGER	Total number of ROS containers for this projection
`ros_without_bundle_info_count`	INTEGER	Number of ROS containers for this projection with invalid bundle metadata
`total_dv_ros_count`	INTEGER	Total number of DV (delete vector) containers for this projection
`dv_ros_without_bundle_info_count`	INTEGER	Number of DV containers for this projection with invalid bundle metadata

109 - STORAGE_BUNDLES

Monitors information about ROS storage bundles.

Column Name	Data Type	Description
NODE_NAME	VARCHAR	Node name for which information is listed.
STORAGE_ID	INTEGER	Numeric ID of the storage container. The same OID can appear on more than one node.
SAL_STORAGE_ID	VARCHAR	Unique hexadecimal numeric ID assigned by the Vertica catalog, which identifies the storage.
PROJECTION_ID	INTEGER	Unique identifier of the projection the bundle belongs to.
BUNDLE_SIZE	INTEGER	Size in bytes of the compressed data.
DATA_SIZE	INTEGER	Number of bytes in the storage container used to store the compressed projection data.
INDEX_SIZE	INTEGER	Size in bytes of the bundle's metadata.

Privileges

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

110 - STORAGE_CONTAINERS

Monitors information about Vertica storage containers.

Column Name	Data Type	Description
NODE_NAME*	VARCHAR	Node name for which information is listed.
NAMESPACE_NAME*	VARCHAR	For Eon Mode databases, namespace for which information is listed.
SCHEMA_NAME*	VARCHAR	Schema name for which information is listed. If the schema belongs to a non-default namespace in an Eon Mode database, the schema name is front-qualified with the name of the schema's namespace. For example, `n1.s` refers to schema `s` in namespace `n1`.
PROJECTION_ID*	INTEGER	Unique numeric ID assigned by the Vertica catalog, which identifies the projection.
PROJECTION_NAME*	VARCHAR	Projection name for which information is listed on that node.
STORAGE_OID*	INTEGER	Numeric ID assigned by the Vertica catalog, which identifies the storage. The same OID can appear on more than one node.
SAL_STORAGE_ID	VARCHAR	Unique hexadecimal numeric ID assigned by the Vertica catalog, which identifies the storage.
TOTAL_ROW_COUNT*	VARCHAR	Total rows in the storage container listed for that projection. If the database has been resharded, this value will be inaccurate until the Tuple mover realigns the storage containers to the new shard layout.
DELETED_ROW_COUNT*	INTEGER	Total rows in the storage container deleted for that projection. If the database has been resharded, this value will be inaccurate until the Tuple mover realigns the storage containers to the new shard layout.
USED_BYTES*	INTEGER	Number of bytes in the storage container used to store the compressed projection data. This value should not be compared to the output of the AUDIT function, which returns the raw data size of database objects. If the database has been resharded, this value will be inaccurate until the Tuple mover realigns the storage containers to the new shard layout.
START_EPOCH*	INTEGER	Number of the start epoch in the storage container for which information is listed.
END_EPOCH*	INTEGER	Number of the end epoch in the storage container for which information is listed.
GROUPING	VARCHAR	The group by which columns are stored: `ALL`: All columns are grouped `PROJECTION`: Columns grouped according to projection definition `NONE`: No columns grouped, despite grouping in the projection definition `OTHER`: Some grouping but neither all nor according to projection (e.g., results from add column)
SEGMENT_LOWER_BOUND	INTEGER	Lower bound of the segment range spanned by the storage container or `NULL` if the corresponding projection is not elastic.
SEGMENT_UPPER_BOUND	INTEGER	Upper bound of the segment range spanned by the storage container or `NULL` if the corresponding projection is not elastic.
LOCATION_LABEL	VARCHAR (128)	The location label (if any) for the storage container is stored.
DELETE_VECTOR_COUNT	INTEGER	The number of delete vectors in the storage container. If the database has been resharded, this value will be inaccurate until the Tuple mover realigns the storage containers to the new shard layout.
SHARD_ID	INTEGER	Set only for an Eon Mode database, ID of the shard that this container belongs to.
SHARD_NAME	VARCHAR(128)	Set only for an Eon Mode database, name of the shard that this container belongs to.
ORIGINAL_SEGMENT_LOWER_BOUND	INTEGER	The lower bound of a storage container before database re-sharding. This value is set only if the database has been re-sharded and the storage containers have not been realigned with current shard definitions. For details, see RESHARD_DATABASE.
ORIGINAL_SEGMENT_UPPER_BOUND	INTEGER	The upper bound of a storage container before database re-sharding. This value is set only if the database has been re-sharded and the storage container has not been realigned with current shard definitions. For details, see RESHARD_DATABASE.
DISK_SIZE	INTEGER	Size in bytes of the compressed data in the storage container.

* Column values cached for faster query performance

Privileges

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

Examples

The following query identifies any storage containers that have not yet been realigned to the new shard segmentation bounds after running RESHARD_DATABASE:

=> SELECT COUNT(*) FROM storage_containers WHERE original_segment_lower_bound IS NOT NULL AND original_segment_upper_bound IS NOT NULL;

111 - STORAGE_POLICIES

Monitors the current storage policies in effect for one or more database objects.

Column Name	Data Type	Description
`NAMESPACE_NAME`	VARCHAR	For Eon Mode databases, namespace for which information is listed.
`SCHEMA_NAME`	VARCHAR	Schema name for which information is listed. If the schema belongs to a non-default namespace in an Eon Mode database, the schema name is front-qualified with the name of the schema's namespace. For example, `n1.s` refers to schema `s` in namespace `n1`.
`OBJECT_NAME`	VARCHAR	The name of the database object associated through the storage policy.
`POLICY_DETAILS`	VARCHAR	The object type of the storage policy.
`LOCATION_LABEL`	VARCHAR (128)	The label for this storage location.

Privileges

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

112 - STORAGE_TIERS

Provides information about all storage locations with the same label across all cluster nodes.

Provides information about all storage locations with the same label across all cluster nodes. This table lists data totals for all same-name labeled locations.

The system table shows what labeled locations exist on the cluster, as well as other cluster-wide data about the locations.

Column Name	Data Type	Description
`LOCATION_LABEL`	VARCHAR	The label associated with a specific storage location. The `storage_tiers` system table includes data totals for unlabeled locations, which are considered labeled with empty strings (`''`).
`NODE_COUNT`	INTEGER	The total number of nodes that include a storage location named `location_label`.
`LOCATION_COUNT`	INTEGER	The total number of storage locations named `location_label`. This value can differ from `node_count` if you create labeled locations with the same name at different paths on different nodes. For example: v_vmart_node0001: Create one labeled location, `FAST` V_vmart_node0002: Create two labeled locations, `FAST`, at different directory paths In this case, `node_count` value = 2, while `location_count` value = 3.
`ROS_CONTAINER_COUNT`	INTEGER	The total number of ROS containers stored across all cluster nodes for `location_label`.
`TOTAL_OCCUPIED_SIZE`	INTEGER	The total number of bytes that all ROS containers for `location_label` occupy across all cluster nodes.

Privileges

None

113 - STORAGE_USAGE

Provides information about file system storage usage.

Provides information about file system storage usage. This is useful for determining disk space usage trends.

Column Name	Data Type	Description
`POLL_TIMESTAMP`	TIMESTAMPTZ	Time when Vertica recorded the row.
`NODE_NAME`	VARCHAR	Name of the node that is reporting the requested information.
`PATH`	VARCHAR	Path where the storage location is mounted.
`DEVICE`	VARCHAR	Device on which the storage location is mounted.
`FILESYSTEM`	VARCHAR	File system on which the storage location is mounted.
`USED_BYTES`	INTEGER	Counter history of number of used bytes.
`FREE_BYTES`	INTEGER	Counter history of number of free bytes.
`USAGE_PERCENT`	FLOAT	Percent of storage in use.

Privileges

Superuser

114 - STRATA

Contains internal details of how the combines ROS containers in each projection, broken down by stratum and classifies the ROS containers by size and partition.

Contains internal details of how the Tuple Mover combines ROS containers in each projection, broken down by stratum and classifies the ROS containers by size and partition. The related STRATA_STRUCTURES table provides a summary of the strata values.

Mergeout describes how the Tuple Mover combines ROS containers.

Column Name	Data Type	Description
`NODE_NAME`	VARCHAR	The node name for which information is listed.
`SCHEMA_NAME`	VARCHAR	The schema name for which information is listed. If the schema belongs to a non-default namespace in an Eon Mode database, the schema name is front-qualified with the name of the schema's namespace. For example, `n1.s` refers to schema `s` in namespace `n1`.
`PROJECTION_ID`	INTEGER	Catalog-assigned numeric value that uniquely identifies the projection.
`PROJECTION_NAME`	VARCHAR	The projection name for which information is listed on that node.
`STRATUM_KEY`	VARCHAR	References the partition or partition group for which information is listed.
`STRATA_COUNT`	INTEGER	The total number of strata for this projection partition.
`MERGING_STRATA_COUNT`	INTEGER	The number of strata the Tuple Mover can merge out.
`STRATUM_CAPACITY`	INTEGER	The maximum number of ROS containers for the stratum before they must be merged.
`STRATUM_HEIGHT`	FLOAT	The size ratio between the smallest and largest ROS container in this stratum.
`STRATUM_NO`	INTEGER	The stratum number. Strata are numbered starting at 0, for the stratum containing the smallest ROS containers.
`STRATUM_LOWER_SIZE`	VARCHAR	The smallest ROS container size allowed in this stratum.
`STRATUM_UPPER_SIZE`	VARCHAR	The largest ROS container size allowed in this stratum.
`ROS_CONTAINER_COUNT`	INTEGER	The current number of ROS containers in the projection partition.

115 - STRATA_STRUCTURES

This table provides an overview of internal details.

This table provides an overview of Tuple Mover internal details. It summarizes how the ROS containers are classified by size. A more detailed view can be found in the STRATA virtual table.

Column Name	Data Type	Description
`NODE_NAME`	VARCHAR	The node name for which information is listed.
`SCHEMA_NAME`	VARCHAR	The schema name for which information is listed.
`PROJECTION_NAME`	VARCHAR	The projection name for which information is listed on that node.
`PROJECTION_ID`	INTEGER	Catalog-assigned numeric value that uniquely identifies the projection.
`STRATUM_KEY`	VARCHAR	References the partition or partition group for which information is listed.
`STRATA_COUNT`	INTEGER	The total number of strata for this projection partition.
`MERGING_STRATA_COUNT`	INTEGER	In certain hardware configurations, a high strata could contain more ROS containers than the Tuple Mover can merge out; output from this column denotes the number of strata the Tuple Mover can merge out.
`STRATUM_CAPACITY`	INTEGER	The maximum number of ROS containers that the strata can contained before it must merge them.
`STRATUM_HEIGHT`	FLOAT	The size ratio between the smallest and largest ROS container in a stratum.
`ACTIVE_STRATA_COUNT`	INTEGER	The total number of strata that have ROS containers in them.

Examples


=> \pset expanded
Expanded display is on.
=> SELECT node_name, schema_name, projection_name, strata_count,
            stratum_capacity, stratum_height, active_strata_count
            FROM strata_structures WHERE stratum_capacity > 60;
-[ RECORD 1 ]-------+--------------------------------------------------------
node_name           | v_vmartdb_node0001
schema_name         | public
projection_name     | shipping_dimension_DBD_22_seg_vmart_design_vmart_design
strata_count        | 4
stratum_capacity    | 62
stratum_height      | 25.6511590887058
active_strata_count | 1
-[ RECORD 2 ]-------+--------------------------------------------------------
node_name           | v_vmartdb_node0001
schema_name         | public
projection_name     | shipping_dimension_DBD_23_seg_vmart_design_vmart_design
strata_count        | 4
stratum_capacity    | 62
stratum_height      | 25.6511590887058
active_strata_count | 1
-[ RECORD 3 ]-------+--------------------------------------------------------
node_name           | v_vmartdb_node0002
schema_name         | public
projection_name     | shipping_dimension_DBD_22_seg_vmart_design_vmart_design
strata_count        | 4
stratum_capacity    | 62
stratum_height      | 25.6511590887058
active_strata_count | 1
-[ RECORD 4 ]-------+--------------------------------------------------------
node_name           | v_vmartdb_node0002
schema_name         | public
projection_name     | shipping_dimension_DBD_23_seg_vmart_design_vmart_design
strata_count        | 4
stratum_capacity    | 62
stratum_height      | 25.6511590887058
active_strata_count | 1
-[ RECORD 5 ]-------+--------------------------------------------------------
node_name           | v_vmartdb_node0003
schema_name         | public
projection_name     | shipping_dimension_DBD_22_seg_vmart_design_vmart_design
strata_count        | 4
stratum_capacity    | 62
stratum_height      | 25.6511590887058
active_strata_count | 1
-[ RECORD 6 ]-------+--------------------------------------------------------
node_name           | v_vmartdb_node0003
schema_name         | public
projection_name     | shipping_dimension_DBD_23_seg_vmart_design_vmart_design
strata_count        | 4
stratum_capacity    | 62
stratum_height      | 25.6511590887058
active_strata_count | 1
-[ RECORD 7 ]-------+--------------------------------------------------------
node_name           | v_vmartdb_node0004
schema_name         | public
projection_name     | shipping_dimension_DBD_22_seg_vmart_design_vmart_design
strata_count        | 4
stratum_capacity    | 62
stratum_height      | 25.6511590887058
active_strata_count | 1
-[ RECORD 8 ]-------+--------------------------------------------------------
node_name           | v_vmartdb_node0004
schema_name         | public
projection_name     | shipping_dimension_DBD_23_seg_vmart_design_vmart_design
strata_count        | 4
stratum_capacity    | 62
stratum_height      | 25.6511590887058
active_strata_count | 1

116 - SYSTEM

Monitors the overall state of the database.

Column Name	Data Type	Description
`CURRENT_EPOCH`	`INTEGER`	The current epoch number.
`AHM_EPOCH`	`INTEGER`	The AHM epoch number.
`LAST_GOOD_EPOCH`	`INTEGER`	The smallest (min) of all the checkpoint epochs on the cluster.
`REFRESH_EPOCH`	`INTEGER`	Deprecated, always set to -1.
`DESIGNED_FAULT_TOLERANCE`	`INTEGER`	The designed or intended K-safety level.
`NODE_COUNT`	`INTEGER`	The number of nodes in the cluster.
`NODE_DOWN_COUNT`	`INTEGER`	The number of nodes in the cluster that are currently down.
`CURRENT_FAULT_TOLERANCE`	`INTEGER`	The number of node failures the cluster can tolerate before it shuts down automatically. This is the current K-safety level.
`CATALOG_REVISION_NUMBER`	`INTEGER`	The catalog version number.
`ROS_USED_BYTES`	`INTEGER`	The ROS size in bytes (cluster-wide).
`ROS_ROW_COUNT`	`INTEGER`	The number of rows in ROS (cluster-wide).
`TOTAL_USED_BYTES`	`INTEGER`	The total storage in bytes across the database cluster.
`TOTAL_ROW_COUNT`	`INTEGER`	The total number of rows across the database cluster.

117 - SYSTEM_RESOURCE_USAGE

Provides history about system resources, such as memory, CPU, network, disk, I/O.

Column Name	Data Type	Description
`NODE_NAME`	VARCHAR	Name of the node that is reporting the requested information.
`END_TIME`	TIMESTAMP	End time of the history interval.
`AVERAGE_MEMORY_USAGE_PERCENT`	FLOAT	Average memory usage in percent of total memory (0-100) during the history interval.
`AVERAGE_CPU_USAGE_PERCENT`	FLOAT	Average CPU usage in percent of total CPU time (0-100) during the history interval.
`NET_RX_KBYTES_PER_SECOND`	FLOAT	Average number of kilobytes received from network (incoming) per second during the history interval.
`NET_TX_KBYTES_PER_SECOND`	FLOAT	Average number of kilobytes transmitting to network (outgoing) per second during the history interval.
`IO_READ_KBYTES_PER_SECOND`	FLOAT	Disk I/O average number of kilobytes read from disk per second during the history interval.
`IO_WRITTEN_KBYTES_PER_SECOND`	FLOAT	Average number of kilobytes written to disk per second during the history interval.

Privileges

Superuser

118 - SYSTEM_SERVICES

Provides information about background system services that Workload Analyzer monitors.

Column Name	Data Type	Description
`NODE_NAME`	VARCHAR	Name of the node that is reporting the requested information.
`SERVICE_TYPE`	VARCHAR	Type of service; can be one of: SYSTEM TUPLE MOVER
`SERVICE_GROUP`	VARCHAR	Group name, if there are multiple services of the same type.
`SERVICE_NAME`	VARCHAR	Name of the service.
`SERVICE_INTERVAL_SEC`	INTEGER	How often the service is executed (in seconds) during the history interval.
`IS_ENABLED`	BOOLEAN	Denotes if the service is enabled.
`LAST_RUN_START`	TIMESTAMPTZ	Denotes when the service was started last time.
`LAST_RUN_END`	TIMESTAMPTZ	Denotes when the service was completed last time.

Privileges

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

119 - SYSTEM_SESSIONS

Provides information about system internal session history by system task.

Column Name	Data Type	Description
`NODE_NAME`	`VARCHAR`	Name of the node that is reporting the requested information.
`USER_NAME`	`VARCHAR`	Name of the user at the time Vertica recorded the session.
`SESSION_ID`	`INTEGER`	Identifier for this session. This identifier is unique within the cluster at any point in time but can be reused when the session closes.
`TRANSACTION_ID`	`INTEGER`	Identifier for the transaction within the session, if any. If a session is active but no transaction has begun, `TRANSACTION_ID` returns NULL.
`STATEMENT_ID`	`VARCHAR`	Unique numeric ID for the currently-running statement. NULL indicates that no statement is currently being processed. The combination of `TRANSACTION_ID` and `STATEMENT_ID` uniquely identifies a statement within a session.
`SESSION_TYPE`	`VARCHAR`	Session type, one of: `CLIENT` `DBD` `MERGEOUT` `REBALANCE_CLUSTER` `RECOVERY` `REFRESH` `TIMER_SERVICE` `CONNECTION` `SUBSESSION` `REPARTITION_TABLE` `LICENSE_AUDIT` `STARTUP` `SHUTDOWN` `VSPREAD`
`RUNTIME_PRIORITY`	`VARCHAR`	Specifies how many run-time resources (CPU, I/O bandwidth) are allocated to queries that are running in the resource pool.
`DESCRIPTION`	`VARCHAR`	Transaction description in this session.
`SESSION_START_TIMESTAMP`	`TIMESTAMPTZ`	Value of session at beginning of history interval.
`SESSION_END_TIMESTAMP`	`TIMESTAMPTZ`	Value of session at end of history interval.
`IS_ACTIVE`	`BOOLEAN`	Denotes if the session is still running.
`SESSION_DURATION_MS`	`INTEGER`	Duration of the session in milliseconds.
`CLIENT_TYPE`	`VARCHAR`	Columns not used in SYSTEM_SESSIONS system table. To view values for these columns, see the V_MONITOR schema system tables SESSIONS, USER_SESSIONS, CURRENT_SESSION, and SESSION_PROFILES.
`CLIENT_VERSION`	`VARCHAR`
`CLIENT_OS`	`VARCHAR`
`CLIENT_OS_USER_NAME`	`VARCHAR`	The name of the user that logged into, or attempted to log into, the database. This is logged even when the login attempt is unsuccessful.

Privileges

Superuser

120 - TABLE_RECOVERIES

Provides detailed information about recovered and recovering tables during a recovery by table.

Column Name	Data Type	Description
`NODE_NAME`	VARCHAR	Name of the node that is performing the recovery.
`TABLE_NAME`	VARCHAR	The name of the table being recovered.
`TABLE_OID`	INTEGER	The object ID of the table being recovered.
`STATUS`	VARCHAR	The status of the table. Tables can have the following status: recovered: The table is fully recovered recovering: The table is in the process of recovery error_retry: Vertica has attempted to recover the table, but the recovery failed. Tables that have not yet begun the recovery process do not have a status.
`PHASE`	VARCHAR	The phase of the recovery.
`THREAD_ID`	VARCHAR	The ID of the thread that performed the recovery.
`START_TIME`	TIMESTAMPTZ	The date and time that the table began recovery.
`END_TIME`	TIMESTAMPTZ	The date and time that the table completed recovery.
`RECOVER_PRIORITY`	INTEGER	The recovery priority of the table being recovered.
`RECOVER_ERROR`	VARCHAR	Error that caused the recovery to fail.
`IS_HISTORICAL`	BOOLEAN	If `f`, the record contains recovery information for the current process.

Privileges

None

Examples

=> SELECT * FROM TABLE_RECOVERIES;
-[RECORD 1]----------------------------------
node_name        | node04
table_oid        | 45035996273708000
table_name       | public.t
status           | recovered
phase            | current replay delete
thread_id        | 7f7a817fd700
start_time       | 2017-12-13 08:47:28.825085-05
end_time         | 2017-12-13 08:47:29.216571-05
recover_priority | -9223372036854775807
recover_error    | Event apply failed
is_historical    | t
-[RECORD 2]--------------------------------------
node_name        | v_test_parquet_ha_node0011
table_oid        | 45035996273937680
table_name       | public.t2_impala230_uncompre_multi_file_libhdfs_1
status           | error-retry
phase            | historical
thread_id        | 7f89a574f700
start_time       | 2018-02-24 11:30:59.008831-05
end_time         | 2018-02-24 11:33:09.780798-05
recover_priority | -9223372036854775807
recover_error    | Could not stop all dirty transactions[txnId = 45035996273718426; ]
is_historical    | t

121 - TABLE_RECOVERY_STATUS

Provides node recovery information during a Recovery By Table.

Column Name	Data Type	Description
`NODE_NAME`	VARCHAR	Name of the node that is performing the recovery.
`NODE_RECOVERY_START_TIME`	TIMESTAMPTZ	The timestamp for when the node began recovering.
`RECOVER_EPOCH`	INTEGER	The epoch that the recovery operation is trying to recover to.
`RECOVERING_TABLE_NAME`	VARCHAR	The name of the table currently recovering.
`TABLES_REMAIN`	INTEGER	The total number of tables on the node.
`IS_RUNNING`	BOOLEAN	Indicates if the recovery process is still running.

Privileges

None

Examples

=> SELECT * FROM TABLE_RECOVERY_STATUS;
-[ RECORD 1 ]------------+-----------------
node_name                | v_vmart_node0001
node_recovery_start_time |
recover_epoch            |
recovering_table_name    |
tables_remain            | 0
is_running               | f
-[ RECORD 2 ]------------+-----------------
node_name                | v_vmart_node0002
node_recovery_start_time |
recover_epoch            |
recovering_table_name    |
tables_remain            | 0
is_running               | f
-[ RECORD 3 ]------------+-----------------
node_name                | v_vmart_node0003
node_recovery_start_time | 2017-12-13 08:47:28.282377-05
recover_epoch            | 23
recovering_table_name    | user_table
tables_remain            | 5
is_running               | y

122 - TABLE_STATISTICS

Displays statistics that have been collected for tables and their respective partitions.

Column Name	Data Type	Description
LOGICAL_STATS_OID	INTEGER	Uniquely identifies a collection of statistics for a given table.
TABLE_NAME	VARCHAR	Name of an existing database table.
MIN_PARTITION_KEY, MAX_PARTITION_KEY	VARCHAR	Statistics for a range of partition keys collected by ANALYZE_STATISTICS_PARTITION, empty if statistics were collected by ANALYZE_STATISTICS.
ROW_COUNT	INTEGER	The number of rows analyzed for each statistics collection.
STAT_COLLECTION_TIME	TIMESTAMPTZ	The timestamp of each statistics collection.

123 - TLS_CONFIGURATIONS

Lists settings for TLS Configuration objects for the server, LDAP, etc.

Column Name	Data Type	Description
NAME	VARCHAR	Name of the TLS Configuration. Vertica includes the following TLS Configurations by default: `server` `LDAPLink` `LDAPAuth` `data_channel`
OWNER	VARCHAR	Owner of the TLS Configuration object.
CERTIFICATE	VARCHAR	The certificate associated with the TLS Configuration object.
CA_CERTIFICATES	VARCHAR	The CA certificate(s) used to verify client certificates. In cases where a TLS Configuration uses more than one CA, each CA will have its own row in the table.
CIPHER_SUITES	VARCHAR	The cipher suites to used to secure the connection.
MODE	VARCHAR	How Vertica establishes TLS connections with another host, one of the following, in order of ascending security: `DISABLE`: Disables TLS. All other options for this parameter enable TLS. `ENABLE`: Enables TLS. Vertica does not check client certificates. `TRY_VERIFY`: Establishes a TLS connection if one of the following is true: the other host presents a valid certificate the other host doesn't present a certificate If the other host presents an invalid certificate, the connection will use plaintext. `VERIFY_CA`: Connection succeeds if Vertica verifies that the other host's certificate is from a trusted CA. If the other host does not present a certificate, the connection uses plaintext. `VERIFY_FULL`: Connection succeeds if Vertica verifies that the other host's certificate is from a trusted CA and the certificate's `cn` (Common Name) or `subjectAltName` attribute matches the hostname or IP address of the other host. Note that for client certificates, `cn` is used for the username, so `subjectAltName` must match the hostname or IP address of the other host. `VERIFY_FULL` is unsupported for client-server TLS (the connection type handled by ServerTLSConfig) and behaves as `VERIFY_CA`.

Examples

In this example, the LDAPAuth TLS Configuration uses two CA certificates:

=> SELECT * FROM tls_configurations WHERE name='LDAPAuth';
    name   |  owner  | certificate | ca_certificate | cipher_suites |  mode
----------+---------+-------------+----------------+---------------+---------
 LDAPAuth | dbadmin | server_cert | ca             |               | DISABLE
 LDAPAuth | dbadmin | server_cert | ica            |               | DISABLE
(2 rows)

To make more clear the relationship between a TLS Configuration and its CA certificates, you can format the query with LISTAGG:

=> SELECT name, owner, certificate, LISTAGG(ca_certificate) AS ca_certificates, cipher_suites, mode
FROM tls_configurations
WHERE name='LDAPAuth'
GROUP BY name, owner, certificate, cipher_suites, mode
ORDER BY 1;
   name   |  owner  | certificate | ca_certificates | cipher_suites |  mode
----------+---------+-------------+-----------------+---------------+---------
 LDAPAuth | dbadmin | server_cert | ca,ica          |               | DISABLE
(1 row)

124 - TRANSACTIONS

Records the details of each transaction.

Column Name	Data Type	Description
`START_TIMESTAMP`	TIMESTAMPTZ	Beginning of history interval.
`END_TIMESTAMP`	TIMESTAMPTZ	End of history interval.
`NODE_NAME`	VARCHAR	Name of the node that is reporting the requested information.
`USER_ID`	INTEGER	Unique numeric ID assigned by the Vertica catalog, which identifies the user.
`USER_NAME`	VARCHAR	Name of the user for which transaction information is listed.
`SESSION_ID`	VARCHAR	Identifier for this session. This identifier is unique within the cluster at any point in time but can be reused when the session closes.
`TRANSACTION_ID`	INTEGER	Identifier for the transaction within the session, if any; otherwise `NULL`.
`DESCRIPTION`	VARCHAR	Textual description of the transaction.
`START_EPOCH`	INTEGER	Number of the start epoch for the transaction.
`END_EPOCH`	INTEGER	Number of the end epoch for the transaction
`NUMBER_OF_STATEMENTS`	INTEGER	Number of query statements executed in this transaction.
`ISOLATION`	VARCHAR	Denotes the transaction mode as "READ COMMITTED" or "SERIALIZABLE".
`IS_READ_ONLY`	BOOLEAN	Denotes "READ ONLY" transaction mode.
`IS_COMMITTED`	BOOLEAN	Determines if the transaction was committed. False means ROLLBACK.
`IS_LOCAL`	BOOLEAN	Denotes transaction is local (non-distributed).
`IS_INITIATOR`	BOOLEAN	Denotes if the transaction occurred on this node (t).
`IS_DDL`	BOOLEAN	Distinguishes between a DDL transaction (t) and non-DDL transaction (f).

Privileges

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

125 - TRUNCATED_SCHEMATA

Lists the original names of restored schemas that were truncated due to name lengths exceeding 128 characters.

Column Name	Data Type	Description
`RESTORE_TIME`	TIMESTAMPTZ	The time that the table was restored.
`SESSION_ID`	VARCHAR	Identifier for the restoring session. This identifier is unique within the cluster at any point in time but can be reused when the session closes.
`USER_ID`	INTEGER	Identifier of the user for the restore event.
`USER_NAME`	VARCHAR	Name of the user for which Vertica lists restore information at the time it recorded the session.
`TRANSACTION_ID`	INTEGER	Identifier for the transaction within the session, if any; otherwise NULL.
`ORIGINAL_SCHEMA_NAME`	VARCHAR	The original name of the schema prior to the restore.
`NEW_SCHEMA_NAME`	VARCHAR	The name of the schema after it was truncated.

Privileges

None

126 - TUNING_RECOMMENDATIONS

Returns tuning recommendation results from the last call to ANALYZE_WORKLOAD.

Returns tuning recommendation results from the last call to ANALYZE_WORKLOAD. This information is useful for building filters on the Workload Analyzer result set.

Column Name	Data Type	Description
OBSERVATION_COUNT	INTEGER	Integer for the total number of events observed for this tuning recommendation. For example, if you see a return value of 1, Workload Analyzer is making its first tuning recommendation for the event in 'scope'.
FIRST_OBSERVATION_TIME	TIMESTAMPTZ	Timestamp when the event first occurred. If this column returns a null value, the tuning recommendation is from the current status of the system instead of from any prior event.
LAST_OBSERVATION_TIME	TIMESTAMPTZ	Timestamp when the event last occurred. If this column returns a null value, the tuning recommendation is from the current status of the system instead of from any prior event.
TUNING_PARAMETER	VARCHAR	Objects on which to perform a tuning action. For example, a return value of: `public.t` informs the DBA to run Database Designer on table `t` in the public schema `bsmith` notifies a DBA to set a password for user `bsmith`
TUNING_DESCRIPTION	VARCHAR	Textual description of the tuning recommendation to perform on the `tuning_parameter` object. For example: Run database designer on table schema.table Create replicated projection for table schema.table Consider incremental design on query Re-segment projection projection-name on high-cardinality column(s) Drop the projection projection-name Alter a table's partition expression Reorganize data in partitioned table Decrease the MoveOutInterval configuration parameter setting
TUNING_COMMAND	VARCHAR	Command string if tuning action is a SQL command. For example: Update statistics on a particular schema's table.column: `SELECT ANALYZE_STATISTICS('public.table.column');` Resolve mismatched configuration parameter `LockTimeout`: `SELECT * FROM CONFIGURATION_PARAMETERS WHERE parameter_name = 'LockTimeout';` Set the password for user `bsmith`: `ALTER USER (bsmith) IDENTIFIED BY ('new_password');`
TUNING_COST	VARCHAR	Cost is based on the type of tuning recommendation and is one of: `LOW`: minimal impact on resources from running the tuning command `MEDIUM`: moderate impact on resources from running the tuning command `HIGH`: maximum impact on resources from running the tuning command Depending on the size of your database or table, consider running high-cost operations after hours instead of during peak load times.

Privileges

Superuser

Examples

See ANALYZE_WORKLOAD.

127 - TUPLE_MOVER_OPERATIONS

Monitors the status of operations on each node.

Monitors the status of Tuple Mover operations on each node.

Column Name	Data Type	Description
OPERATION_START_TIMESTAMP	TIMESTAMP	Start time of a Tuple Mover operation.
NODE_NAME	VARCHAR	Node name for which information is listed.
OPERATION_NAME	VARCHAR	One of the following: Analyze Statistics DVMergeout Mergeout Partitioning Rebalance Recovery Replay Delete
OPERATION_STATUS	VARCHAR	Returns the status of each operation, one of the following: Empty string: not running Start Running Complete Update Abort Change plan type to Replay Delete
TABLE_SCHEMA	VARCHAR	Schema name for the specified projection. If the schema belongs to a non-default namespace in an Eon Mode database, the schema name is front-qualified with the name of the schema's namespace. For example, `n1.s` refers to schema `s` in namespace `n1`.
TABLE_NAME	VARCHAR	Table name for the specified projection.
PROJECTION_NAME	VARCHAR	Name of the projection being processed.
PROJECTION_ID	INTEGER	Unique numeric ID assigned by the Vertica catalog, which identifies the projection.
COLUMN_ID	INTEGER	Identifier for the column for the associated projection being processed.
EARLIEST_CONTAINER_START_EPOCH	INTEGER	Populated for mergeout and purge operations only. For an automatically-invoked mergeout, for example, the returned value represents the lowest epoch of containers involved in the mergeout.
LATEST_CONTAINER_END_EPOCH	INTEGER	Populated for mergeout and purge_partitions operations. For an automatically-invoked mergeout, for example, the returned value represents the highest epoch of containers involved in the mergeout.
ROS_COUNT	INTEGER	Number of ROS containers.
TOTAL_ROS_USED_BYTES	INTEGER	Size in bytes of all ROS containers in the mergeout operation. (Not applicable for other operations.)
PLAN_TYPE	VARCHAR	One of the following: Analyze Statistics DVMergeout Mergeout Partitioning Rebalance Recovery Replay Delete Replay Delete
SESSION_ID	VARCHAR	Identifier for this session. This identifier is unique within the cluster at any point in time but can be reused when the session closes.
TRANSACTION_ID	INTEGER	Identifier for the transaction within the session, if any. If a session is active but no transaction has begun, `TRANSACTION_ID` returns NULL.
IS_EXECUTING	BOOLEAN	Distinguishes between actively-running (t) and completed (f) tuple mover operations.
RUNTIME_PRIORITY	VARCHAR	Determines how many run-time resources (CPU, I/O bandwidth) the Resource Manager should dedicate to running queries in the resource pool, one of the following: HIGH MEDIUM LOW

Privileges

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

Examples

=> SELECT node_name, operation_status, projection_name, plan_type
    FROM TUPLE_MOVER_OPERATIONS;
 node_name         | operation_status | projection_name  | plan_type
-------------------+------------------+------------------+-----------
 v_vmart_node0001  | Running          | p1_b2            | Mergeout
 v_vmart_node0002  | Running          | p1               | Mergeout
 v_vmart_node0001  | Running          | p1_b2            | Replay Delete
 v_vmart_node0001  | Running          | p1_b2            | Mergeout
 v_vmart_node0002  | Running          | p1_b2            | Mergeout
 v_vmart_node0001  | Running          | p1_b2            | Replay Delete
 v_vmart_node0002  | Running          | p1               | Mergeout
 v_vmart_node0003  | Running          | p1_b2            | Replay Delete
 v_vmart_node0001  | Running          | p1               | Mergeout
 v_vmart_node0002  | Running          | p1_b1            | Mergeout

128 - UDFS_EVENTS

Records information about events involving the S3, HDFS, GCS, and Azure file systems.

Events are logged if they take longer to complete than the threshold specified by UDFSSlowRequestThreshold (default 1000ms).

HTTP requests are logged if they take longer to complete than the threshold specified by HTTPSlowRequestThreshold (default 500ms), or if they produce errors or are retries. HTTP requests are logged only for S3.

Column Name	Data Type	Description
START_TIME	TIMESTAMPTZ	Event start time
END_TIME	TIMESTAMPTZ	Event end time
NODE_NAME	VARCHAR	Name of the node that reported the event
SESSION_ID	VARCHAR	Identifies the event session, unique within the cluster at any point in time but can be reused when the session closes.
USER_ID	INTEGER	Identifies the event user.
TRANSACTION_ID*	INTEGER	Identifies the event transaction within the SESSION_ID-specified session, if any; otherwise NULL.
STATEMENT_ID*	INTEGER	Uniquely identifies the current statement, if any; otherwise NULL.
REQUEST_ID*	INTEGER	Uniquely identifies the event request in the user session.
FILESYSTEM	VARCHAR	Name of the file system, such as S3
PATH	VARCHAR	Complete file path
EVENT	VARCHAR	An event type, such as `HttpRequestAttempt`, or a function call, such as `virtual size_t SAL::S3FileOperator::read(void*, size_t)`
STATUS	VARCHAR	Status of the event: OK, CANCEL, or FAIL
DESCRIPTION	VARCHAR	Other event details. Depending on the event type, this column can be plain text or JSON.
ACTIVITY	VARCHAR	Points to the component that was active and logged the event, for internal use only.
PLAN_ID	VARCHAR	Uniquely identifies the node-specific Optimizer plan for this event.
OPERATOR_ID	INTEGER	Identifier assigned by the Execution Engine operator instance that performs the work

* In combination, TRANSACTION_ID, STATEMENT_ID, and REQUEST_ID uniquely identify an event within a given session.

Privileges

Superuser

129 - UDFS_OPS_PER_HOUR

This table summarizes the S3 file system statistics for each hour.

Column Name	Data Type	Description
NODE_NAME	VARCHAR	Name of the node the file system is on.
FILESYSTEM	VARCHAR	Name of the file system, such as S3.
START_TIME	TIMESTAMP	Start time for statistics gathering.
END_TIME	TIMESTAMP	Stop time for statistics gathering.
AVG_OPERATIONS_PER_SECOND	INTEGER	Average number of operations per second during the specified hour.
AVG_ERRORS_PER_SECOND	INTEGER	Average number of errors per second during the specified hour.
RETRIES	INTEGER	Number of retry events during the specified hour.
METADATA_READS	INTEGER	Number of requests to write metadata during the specified hour. For example, S3 POST and DELETE requests are metadata writes.
METADATA_WRITES	INTEGER	Number of requests to write metadata during the specified hour. For example, S3 POST and DELETE requests are metadata writes.
DATA_READS	INTEGER	Number of read operations, such as S3 GET requests to download files, during the specified hour.
DATA_WRITES	INTEGER	Number of write operations, such as S3 PUT requests to upload files, during the specified hour.
UPSTREAM_BYTES	INTEGER	Number of bytes received during the specified hour.
DOWNSTREAM_BYTES	INTEGER	Number of bytes sent during the specified hour.

Examples

=> \x
Expanded display is on.
=> SELECT * FROM UDFS_OPS_PER_HOUR;
-[ RECORD 1 ]-------------+--------------------
node_name                 | e1
filesystem                | S3
start_time                | 2018-04-06 04:00:00
end_time                  | 2018-04-06 04:00:00
avg_operations_per_second | 0
avg_errors_per_second     | 0
retries                   | 0
metadata_reads            | 0
metadata_writes           | 0
data_reads                | 0
data_writes               | 0
upstream_bytes            | 0
downstream_bytes          | 0
...

130 - UDFS_OPS_PER_MINUTE

This table summarizes the S3 file system statistics for each minute.

Column Name	Data Type	Description
NODE_NAME	VARCHAR	Name of the node the file system is on.
FILESYSTEM	VARCHAR	Name of the file system, such as S3.
START_TIME	TIMESTAMP	Start time for statistics gathering.
END_TIME	TIMESTAMP	Stop time for statistics gathering.
AVG_OPERATIONS_PER_SECOND	INTEGER	Average number of operations per second during the specified minute.
AVG_ERRORS_PER_SECOND	INTEGER	Average number of errors per second during the specified minute.
RETRIES	INTEGER	Number of retry events during the specified minute.
METADATA_READS	INTEGER	Number of requests to write metadata during the specified minute. For example, S3 POST and DELETE requests are metadata writes.
METADATA_WRITES	INTEGER	Number of requests to write metadata during the specified minute. For example, S3 POST and DELETE requests are metadata writes.
DATA_READS	INTEGER	Number of read operations, such as S3 GET requests to download files, during the specified minute.
DATA_WRITES	INTEGER	Number of write operations, such as S3 PUT requests to upload files, during the specified minute.
UPSTREAM_BYTES	INTEGER	Number of bytes received during the specified minute.
DOWNSTREAM_BYTES	INTEGER	Number of bytes sent during the specified minute.

Examples

=> \x
Expanded display is on.
=> SELECT * FROM UDFS_OPS_PER_MINUTE;
-[ RECORD 1 ]-------------+--------------------
node_name                 | e1
filesystem                | S3
start_time                | 2018-04-06 04:17:00
end_time                  | 2018-04-06 04:18:00
avg_operations_per_second | 0
avg_errors_per_second     | 0
retries                   | 0
metadata_reads            | 0
metadata_writes           | 0
data_reads                | 0
data_writes               | 0
upstream_bytes            | 0
downstream_bytes          | 0
...

131 - UDFS_STATISTICS

Records aggregate information about operations on file systems and object-stores.

Records aggregate information about operations on file systems and object stores. For access through LibHDFS++, the table records information about metadata but not data.

An operation can be made up of many individual read, write, or retry requests. SUCCESSFUL_OPERATIONS and FAILED_OPERATIONS count operations; the other counters count individual requests. When an operation finishes, one of the OPERATIONS counters is incremented once, but several other counters could be incremented several times each.

Column Name	Data Type	Description
FILESYSTEM	VARCHAR	Name of the file system, such as S3 or Libhdfs++.
SUCCESSFUL_OPERATIONS	INTEGER	Number of successful operations.
FAILED_OPERATIONS	INTEGER	Number of failed operations.
RETRIES	INTEGER	Number of retry events.
METADATA_READS	INTEGER	Number of requests to read metadata. For example, S3 list bucket and HEAD requests are metadata reads.
METADATA_WRITES	INTEGER	Number of requests to write metadata. For example, S3 POST and DELETE requests are metadata writes.
DATA_READS	INTEGER	Number of read operations, such as S3 GET requests to download files.
DATA_WRITES	INTEGER	Number of write operations, such as S3 PUT requests to upload files.
DOWNSTREAM_BYTES	INTEGER	Number of bytes received.
UPSTREAM_BYTES	INTEGER	Number of bytes sent.
OPEN_FILES	INTEGER	Number of files that are currently open.
MAPPED_FILES	INTEGER	Number of files that are currently mapped. On S3 file systems, this is the number of streaming connections for reading data. On other file systems, this value is 0.
READING	INTEGER	The number of read operations that are currently running.
WRITING	INTEGER	The number of write operations that are currently running.
TOTAL_REQUEST_DURATION_MS	INTEGER	Accumulated HTTP request duration, in milliseconds. This information is recorded only for S3 and GCS.
OVERALL_AVERAGE_LATENCY_MS	INTEGER	Average HTTP request duration, in milliseconds. This value is TOTAL_REQUEST_DURATION_MS divided by the sum of SUCCESSFUL_OPERATIONS and FAILED_OPERATIONS (rounded). This value is set only for S3 and GCS.
OVERALL_DOWNSTREAM_THROUGHPUT_MB_S	FLOAT	HTTP request downstream throughput in megabytes per second (MB/s). This value is DOWNSTREAM_BYTES divided by TOTAL_REQUEST_DURATION_MS. This value is set only for S3 and GCS.
OVERALL_UPSTREAM_THROUGHPUT_MB_S	FLOAT	HTTP request upstream throughput in megabytes per second (MB/s). This value is UPSTREAM_BYTES divided by TOTAL_REQUEST_DURATION_MS. This value is set only for S3 and GCS.

Examples

The following query gets the total number of metadata RPCs for Libhdfs++ operations:

=> SELECT SUM(metadata_reads) FROM UDFS_STATISTICS WHERE filesystem = 'Libhdfs++';

132 - UDX_EVENTS

Records information about events raised from the execution of user-defined extensions.

A UDx populates the __RAW__ column using ServerInterface::logEvent() (C++ only). VMap support is provided by Flex Tables, which must not be disabled.

In combination, TRANSACTION_ID, STATEMENT_ID, and REQUEST_ID uniquely identify an event within a given session.

Column Name	Data Type	Description
REPORT_TIME	TIMESTAMPTZ	Time the event occurred.
NODE_NAME	VARCHAR	Name of the node that reported the event
SESSION_ID	VARCHAR	Identifies the event session, unique within the cluster at any point in time but can be reused when the session closes.
USER_ID	INTEGER	Identifies the user running the UDx.
USER_NAME	VARCHAR	Identifies the user running the UDx.
TRANSACTION_ID	INTEGER	Identifies the event transaction within the SESSION_ID-specified session, if any; otherwise NULL.
STATEMENT_ID	INTEGER	Uniquely identifies the current statement, if any; otherwise NULL.
REQUEST_ID	INTEGER	Uniquely identifies the event request in the user session.
UDX_NAME	VARCHAR	Name of the UDx, as specified in the corresponding CREATE FUNCTION statement.
__RAW__	VARBINARY	VMap containing UDx-specific values. See the documentation of individual UDXs for supported fields.

133 - UDX_FENCED_PROCESSES

Provides information about processes Vertica uses to run user-defined extensions in fenced mode.

Column Name	Data Type	Description
`NODE_NAME`	VARCHAR	Name of the node that is reporting the requested information.
`PROCESS_TYPE`	VARCHAR	Indicates what kind of side process this row is for and can be one of the following values: `UDxZygoteProcess` — Master process that creates worker side processes, as needed, for queries. There will be, at most, 1 UP UDxZygoteProcess for each Vertica instance. `UDxSideProcess` — Indicates that the process is a worker side process. There could be many UDxSideProcesses, depending on how many sessions there are, how many queries, and so on.
`SESSION_ID`	VARCHAR	Identifier for this session. This identifier is unique within the cluster at any point in time but can be reused when the session closes.
`LANGUAGE`	VARCHAR	The language of the UDx. For example '`R`' or '`C++`';
`MAX_MEMORY_JAVA_KB`	INTEGER	The maximum amount of memory in KB that can be used for the Java heap file on the node.
`PID`	INTEGER	Linux process identifier of the side process (`UDxSideProcess`).
`PORT`	VARCHAR	For Vertica internal use. The TCP port that the side process is listening on.
`STATUS`	VARCHAR	Set to UP or DOWN, depending on whether the process is alive or not. After a process fails, Vertica restarts it only on demand. So after a process failure, there might be periods of time when no side processes run.

Privileges

None

134 - USER_LIBRARIES

Lists the user libraries that are currently loaded.

Lists the user libraries that are currently loaded. These libraries contain user-defined extensions (UDxs) that provide additional analytic functions.

Column Name	Data Type	Description
`NAMESPACE_NAME`	VARCHAR	For Eon Mode databases, name of the namespace that contains the library.
`SCHEMA_NAME`	VARCHAR(8192)	The name of the schema containing the library. If the schema belongs to a non-default namespace in an Eon Mode database, the schema name is front-qualified with the name of the schema's namespace. For example, `n1.s` refers to schema `s` in namespace `n1`.
`LIB_NAME`	VARCHAR(8192)	The name of the library.
`LIB_OID`	INTEGER	The object ID of the library.
`AUTHOR`	VARCHAR(8192)	The creator of the library file.
`OWNER_ID`	INTEGER	The object ID of the library's owner.
`LIB_FILE_NAME`	VARCHAR(8192)	The name of the shared library file.
`MD5_SUM`	VARCHAR(8192)	The MD5 checksum of the library file, used to verify that the file was correctly copied to each node. Note This use of MD5 is not for cryptographic or authentication purposes. For information on authenticating with MD5 see Hash authentication.
`SDK_VERSION`	VARCHAR(8192)	The version of the Vertica SDK used to compile the library.
`REVISION`	VARCHAR(8192)	The revision of the Vertica SDK used to compile the library.
`LIB_BUILD_TAG`	VARCHAR(8192)	Internal information set by library developer to track the when the library was compiled.
`LIB_VERSION`	VARCHAR(8192)	The version of the library.
`LIB_SDK_VERSION`	VARCHAR(8192)	The version of the Vertica SDK intended for use with the library. The developer sets this value manually. This value may differ from the values in the SDK_VERSION and REVISION, which are set automatically during compilation.
`SOURCE_URL`	VARCHAR(8192)	A URL that contains information about the library.
`DESCRIPTION`	VARCHAR(8192)	A description of the library.
`LICENSES_REQUIRED`	VARCHAR(8192)	The licenses required to use the library.
`SIGNATURE`	VARCHAR(8192)	The signature used to sign the library for validation.
`DEPENDENCIES`	VARCHAR (8192)	External libraries on which this library depends. These libraries are maintained by Vertica, just like the user libraries themselves.

135 - USER_LIBRARY_MANIFEST

Lists user-defined functions contained in all loaded user libraries.

Column Name	Data Type	Description
`NAMESPACE_NAME`	VARCHAR	For Eon Mode databases, name of the namespace that contains the function.
`SCHEMA_NAME`	VARCHAR	Name of the schema that contains the function. If the schema belongs to a non-default namespace in an Eon Mode database, the schema name is front-qualified with the name of the schema's namespace. For example, `n1.s` refers to schema `s` in namespace `n1`.
`LIB_NAME`	VARCHAR	The name of the library containing the UDF.
`LIB_OID`	INTEGER	The object ID of the library containing the function.
`OBJ_NAME`	VARCHAR	The name of the constructor class in the library for a function.
`OBJ_TYPE`	VARCHAR	The type of user defined function (scalar function, transform function)
`ARG_TYPES`	VARCHAR	A comma-delimited list of data types of the function's parameters.
`RETURN_TYPE`	VARCHAR	A comma-delimited list of data types of the function's return values.

Privileges

None

136 - USER_SESSIONS

Returns user session history on the system.

Column Name	Data Type	Description
`NODE_NAME`	VARCHAR	Name of the node that is reporting the requested information.
`USER_NAME`	VARCHAR	Name of the user at the time Vertica recorded the session.
`SESSION_ID`	VARCHAR	Identifier for this session. This identifier is unique within the cluster at any point in time but can be reused when the session closes.
`TRANSACTION_ID`	VARCHAR	Identifier for the transaction within the session, if any. If a session is active but no transaction has begun, `TRANSACTION_ID` returns NULL.
`STATEMENT_ID`	VARCHAR	Unique numeric ID for the currently-running statement. NULL indicates that no statement is currently being processed. The combination of `TRANSACTION_ID` and `STATEMENT_ID` uniquely identifies a statement within a session.
`RUNTIME_PRIORITY`	VARCHAR	Determines the amount of run-time resources (CPU, I/O bandwidth) the Resource Manager should dedicate to queries already running in the resource pool. Valid values are: `HIGH` `MEDIUM` `LOW` Queries with a `HIGH` run-time priority are given more CPU and I/O resources than those with a `MEDIUM` or `LOW` run-time priority.
`SESSION_START_TIMESTAMP`	TIMESTAMPTZ	Value of session at beginning of history interval.
`SESSION_END_TIMESTAMP`	TIMESTAMPTZ	Value of session at end of history interval.
`IS_ACTIVE`	BOOLEAN	Denotes if the operation is executing.
`CLIENT_OS_HOSTNAME`	`VARCHAR`	The hostname of the client as reported by their operating system.
`CLIENT_HOSTNAME`	`VARCHAR`	The IP address and port of the TCP socket from which the client connection was made; NULL if the session is internal. Vertica accepts either IPv4 or IPv6 connections from a client machine. If the client machine contains mappings for both IPv4 and IPv6, the server randomly chooses one IP address family to make a connection. This can cause the CLIENT_HOSTNAME column to display either IPv4 or IPv6 values, based on which address family the server chooses.
`CLIENT_PID`	INTEGER	Linux process identifier of the client process that issued this connection. Note: The client process could be on a different machine from the server.
`CLIENT_LABEL`	VARCHAR	User-specified label for the client connection that can be set when using ODBC. See Label in DSN Parameters.
`SSL_STATE`	VARCHAR	Indicates if Vertica used Secure Socket Layer (SSL) for a particular session. Possible values are: None – Vertica did not use SSL. Server – Sever authentication was used, so the client could authenticate the server. Mutual – Both the server and the client authenticated one another through mutual authentication. See Implementing Security and TLS protocol.
`AUTHENTICATION_METHOD`	VARCHAR	Type of client authentication used for a particular session, if known. Possible values are: Unknown Trust Reject Kerberos Password MD5 LDAP Kerberos-GSS Ident See Security and authentication and Configuring client authentication.
`CLIENT_TYPE`	VARCHAR	The type of client from which the connection was made. Possible client type values: ADO.NET Driver ODBC Driver JDBC Driver vsql
`CLIENT_VERSION`	VARCHAR	Returns the client version.
`CLIENT_OS`	VARCHAR	Returns the client operating system.
`CLIENT_OS_USER_NAME`	VARCHAR	The name of the user that logged into, or attempted to log into, the database. This is logged even when the login attempt is unsuccessful.

Privileges

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