Specifying CASCADE When Dropping a Table
Jim_Knicely
Administrator
The DROP TABLE command removes a table and its projections.
Example:
dbadmin=> CREATE TABLE test1 (c INT); CREATE TABLE dbadmin=> INSERT INTO test1 SELECT 1; OUTPUT -------- 1 (1 row) dbadmin=> SELECT projection_name, create_type FROM projections WHERE anchor_table_name = 'test1'; projection_name | create_type -----------------+------------------ test1_super | DELAYED CREATION (1 row) dbadmin=> DROP TABLE test1; DROP TABLE
A projection with a create type of “DELAYED CREATION” is an auto projection. Auto-projections are superprojections that Vertica automatically generates for tables.
If you create a non-auto projection, either manually or via Database Designer, then you will need to drop the table specifying the CASCADE option.
dbadmin=> CREATE TABLE test1 (c INT); CREATE TABLE dbadmin=> CREATE PROJECTION test1_pr AS SELECT c FROM test1 ORDER BY c UNSEGMENTED ALL NODES; CREATE PROJECTION dbadmin=> SELECT projection_name, create_type FROM projections WHERE anchor_table_name = 'test1'; projection_name | create_type -----------------+------------------- test1_pr | CREATE PROJECTION (1 row) dbadmin=> DROP TABLE test1; NOTICE 4927: The Projection test1_pr depends on Table test1 ROLLBACK 3128: DROP failed due to dependencies DETAIL: Cannot drop Table test1 because other objects depend on it HINT: Use DROP ... CASCADE to drop the dependent objects too dbadmin=> DROP TABLE test1 CASCADE; DROP TABLE
Helpful Links:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Statements/DROPTABLE.htm
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/Projections/AutoProjections.htm
Have fun!
0