Viewing Internal Statements Executed by Vertica

Jim_Knicely

The DC_INTERNAL_STATEMENTS Data Collector table tracks internal statements that are executed by Vertica.

Example:

dbadmin=> SELECT component, description
dbadmin->   FROM data_collector
dbadmin->  WHERE table_name = 'dc_internal_statements';
     component      |            description
--------------------+------------------------------------
InternalStatements | Internal statements kicked started
(1 row)

dbadmin=> CREATE TABLE a_test (c1 INT, c2 VARCHAR(10));
CREATE TABLE

dbadmin=> INSERT INTO a_test SELECT 1, 'A';
OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT projection_name
dbadmin=> SELECT projection_name, is_super_projection, create_type
dbadmin->   FROM projections
dbadmin->  WHERE anchor_table_name = 'a_test';
projection_name | is_super_projection |   create_type
-----------------+---------------------+------------------
a_test_super    | t                   | DELAYED CREATION
(1 row)

But I didn’t create that projection, Vertica did!

That is, Vertica created an “Auto-projection” on my behalf because I loaded data into a table that had no previously defined projections.

I can view the SQL statement that was executed by Vertica internally via the DC_INTERNAL_STATEMENTS Data Collector table:

dbadmin=> SELECT statement
dbadmin->   FROM dc_internal_statements
dbadmin->  WHERE session_id = CURRENT_SESSION();
                                                          statement
-----------------------------------------------------------------------------------------------------------------------------
CREATE PROJECTION public.a_test AS  SELECT * FROM public.a_test ORDER BY  c1,c2 SEGMENTED BY hash( c1,c2) ALL NODES  KSAFE;
(1 row) 

Helpful Links:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/Projections/AutoProjections.htm
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/Glossary/DataCollector.htm

Have fun!