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 |
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. |
proj-basename _unseg |
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. |
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
Note
Eon Mode uses shards in communal storage to segment table data, which are functionally equivalent to Enterprise Mode buddy projections. For details, see Namespaces and shards.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 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.
-
-
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