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:
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 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 For example:
|
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:
|
Eon Mode | |
proj‑basename |
Identifies projections for a segmented projection. Eon Mode uses shards in communal storage to segment table data, which are functionally equivalent to Enterprise Mode buddy projections. For details, see Shards and Subscriptions. |
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:
- 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
- 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 - Replace the prefix with the new table name.
- Remove any version strings that were appended to the old base name—for example,
old‑basename_v1
. - Generate projection names with new base name.
- 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