Projection naming

Vertica identifies projections according to the following conventions, where proj‑basename is the name assigned to this projection by CREATE PROJECTION.

Vertica identifies projections according to the following conventions, where proj-basename is the name assigned to this projection by CREATE PROJECTION.

Unsegmented projections

Unsegmented projections conform to the following naming conventions:

proj-basename_super

Identifies the auto projection that Vertica creates when data is loaded for the first time into a new unsegmented table. Vertica uses the anchor table name to create the projection base name proj-basename and appends the string _super. The auto projection is always a superprojection.

For example:

=> CREATE TABLE store.store_dimension
    store_key int NOT NULL,
    store_name varchar(64),
    ...
) UNSEGMENTED ALL NODES;
CREATE TABLE
=> COPY store.store_dim FROM '/home/dbadmin/store_dimension_data.txt';
50
=> SELECT anchor_table_name, projection_basename, projection_name FROM projections WHERE anchor_table_name = 'store_dimension';
 anchor_table_name | projection_basename |    projection_name
-------------------+---------------------+-----------------------
 store_dimension   | store_dimension     | store_dimension_super
 store_dimension   | store_dimension     | store_dimension_super
 store_dimension   | store_dimension     | store_dimension_super
(3 rows)
proj-basename_unseg

Identifies an unsegmented projection, where proj-basename and the anchor table name are identical. If no other projection was previously created with this base name (including an auto projection), Vertica appends the string _unseg to the projection name. If the projection is copied on all nodes, this projection name maps to all instances.

For example:

=> CREATE TABLE store.store_dimension(
    store_key int NOT NULL,   
    store_name varchar(64),
    ...
);
CREATE TABLE
=> CREATE PROJECTION store_dimension AS SELECT * FROM store.store_dimension UNSEGMENTED ALL NODES;
WARNING 6922:  Projection name was changed to store_dimension_unseg because it conflicts with the basename of the table store_dimension
CREATE PROJECTION
=> SELECT anchor_table_name, projection_basename, projection_name FROM projections WHERE anchor_table_name = 'store_dimension';
 anchor_table_name | projection_basename |    projection_name
-------------------+---------------------+-----------------------
 store_dimension   | store_dimension     | store_dimension_unseg
 store_dimension   | store_dimension     | store_dimension_unseg
 store_dimension   | store_dimension     | store_dimension_unseg
(3 rows)

Segmented projections

Segmented projections conform to the following naming convention:

Enterprise mode
proj-basename_boffset

Identifies buddy projections for a segmented projection, where offset identifies the projection's node location relative to all other buddy projections. All buddy projections share the same project base name. For example:

=> SELECT projection_basename, projection_name FROM projections WHERE anchor_table_name = 'store_orders';
 projection_basename | projection_name
---------------------+-----------------
 store_orders        | store_orders_b0
 store_orders        | store_orders_b1
(2 rows)

One exception applies: Vertica uses the following convention to name live aggregate projections:

  • proj-basename

  • proj-basename_b1

  • ...

Eon Mode
proj-basename

Identifies projections for a segmented projection.

Projections of renamed and copied tables

Vertica uses the same logic to rename existing projections in two cases:

In both cases, Vertica uses the following algorithm to rename projections:

  1. Iterate over all projections anchored on the renamed or new table, and check whether their names are prefixed by the original table name:

    • No: Retain projection name

    • Yes: Rename projection

  2. If yes, compare the original table name and projection base name:

    If projection base name is... Then...
    Same as original table name Replace base name with the new table name, generate projection names with new base name.
    Prefixed by original table name 
    1. Replace the prefix with the new table name.

1. Remove any version strings that were appended to the old base name—for example, *`old-basename`*`_v1`.

1. Generate projection names with new base name.

  3. Check whether the new projection names already exist. If not, save them. Otherwise, resolve name conflicts by appending version numbers as needed to the new base name—new-basename_v1, new-basename_v2, and so on.

Example

The following example creates segmented table testRenameSeg and populates it with data:

=> CREATE TABLE testRenameSeg (a int, b int);
CREATE TABLE
dbadmin=> INSERT INTO testRenameSeg VALUES (1,2);
 OUTPUT
--------
  1
(1 row)

dbadmin=> COMMIT;
COMMIT

Vertica automatically creates two buddy superprojections for this table:

=> \dj testRename*
                      List of projections
 Schema |     Name          |  Owner  |       Node       | Comment
--------+-----------------------+---------+------------------+---------
 public | testRenameSeg_b0  | dbadmin |                  |
 public | testRenameSeg_b1  | dbadmin |                  |

The following CREATE PROJECTION statements explicitly create additional projections for the table:


=> CREATE PROJECTION nameTestRenameSeg_p AS SELECT * FROM testRenameSeg;
=> CREATE PROJECTION testRenameSeg_p AS SELECT * FROM testRenameSeg;
=> CREATE PROJECTION testRenameSeg_pLap AS SELECT b, MAX(a) a FROM testRenameSeg GROUP BY b;
=> CREATE PROJECTION newTestRenameSeg AS SELECT * FROM testRenameSeg;
=> \dj *testRenameSeg*
                List of projections
 Schema |      Name          |  Owner  | Node | Comment
--------+------------------------+---------+------+---------
 public | nameTestRenameSeg_p_b0 | dbadmin |  |
 public | nameTestRenameSeg_p_b1 | dbadmin |  |
 public | newTestRenameSeg_b0| dbadmin |      |
 public | newTestRenameSeg_b1| dbadmin |      |
 public | testRenameSeg_b0   | dbadmin |      |
 public | testRenameSeg_b1   | dbadmin |      |
 public | testRenameSeg_pLap | dbadmin |      |
 public | testRenameSeg_pLap_b1  | dbadmin |  |
 public | testRenameSeg_p_b0 | dbadmin |      |
 public | testRenameSeg_p_b1 | dbadmin |      |
(10 rows)

If you rename anchor table testRenameSeg, Vertica also renames its projections as follows:

=> ALTER TABLE testRenameSeg RENAME TO newTestRenameSeg;
ALTER TABLEn=> \dj *testRenameSeg*
                 List of projections
 Schema |       Name           |  Owner  | Node | Comment
--------+--------------------------+---------+------+---------
 public | nameTestRenameSeg_p_b0   | dbadmin |  |
 public | nameTestRenameSeg_p_b1   | dbadmin |  |
 public | newTestRenameSeg_b0  | dbadmin |      |
 public | newTestRenameSeg_b1  | dbadmin |      |
 public | newTestRenameSeg_pLap_b0 | dbadmin |  |
 public | newTestRenameSeg_pLap_b1 | dbadmin |  |
 public | newTestRenameSeg_p_b0| dbadmin |      |
 public | newTestRenameSeg_p_b1| dbadmin |      |
 public | newTestRenameSeg_v1_b0   | dbadmin |  |
 public | newTestRenameSeg_v1_b1   | dbadmin |  |
(10 rows)

Two sets of buddy projections are not renamed, as their names are not prefixed by the original table name:

  • nameTestRenameSeg_p_b0

  • nameTestRenameSeg_p_b1

  • newTestRenameSeg_b0

  • newTestRenameSeg_b1

When renaming the other projections, Vertica identified a potential conflict between the table's superprojection—originally testRenameSeg—and existing projection newTestRenameSeg. It resolved this conflict by appending version numbers _v1 and _v2 to the superprojection's new name:

  • newTestRenameSeg_v1_b0

  • newTestRenameSeg_v1_b1