Specifying CASCADE When Dropping a Table: Quick Tip

Posted January 14, 2019 by Jim Knicely, Big Data Solutions Architect, Vertica

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 projection manually or via the Database Designer (non-auto projections), you will need to drop the table specifying the CASCADE option. 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/latest/HTML/Content/Authoring/SQLReferenceManual/Statements/DROPTABLE.htm

https://www.vertica.com/docs/latest/HTML/Content/Authoring/AdministratorsGuide/Projections/AutoProjections.htm

Have fun!