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' DIRECT; 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:
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:
One exception applies: Vertica uses the following convention to name live aggregate projections:
|
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