Projection naming

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:

Segmented projections

Enterprise Mode

In Enterprise Mode, segmented projections use the following naming convention:

proj-basename_boffset

This name identifies buddy projections for a segmented projection, where offset is 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, and so on.

Eon Mode

In Eon Mode, segmented projections use the following naming convention:

proj-basename

Projections of renamed and copied tables

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

• You rename a table with ALTER TABLE...RENAME TABLE.

• You create a table from an existing one with CREATE TABLE LIKE...INCLUDING PROJECTIONS.

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 the new base name is the same as the original table name, then replace the base name with the new table name in the table and projection names.

   • If the new base name is prefixed by the original table name, then replace the prefix with the new table name, remove any version strings that were appended to the old base name (such as old-basename_v1), and generate projection names with the 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.

Examples

An auto projection is always a superprojection:

=> 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)

An unsegmented projection name has the _unseg suffix on all nodes:

=> 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)

The following example creates the 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 the anchor table, Vertica also renames its projections:

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