Specifying CASCADE When Dropping a Table

Jim_Knicely

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!