Why am I not able to drop a column in Vertica

Question

I created a table in vertica

select * from my_faithful_test;
 id | eruptions
----+-----------
  1 |       3.4

I want to drop eruptions column but when I run this command, I get error

alter table my_faithful_test drop eruptions cascade;
ROLLBACK 4122:  No up-to-date super projection left on the anchor table of projection my_faithful_test_super
HINT:  Use DROP TABLE ... CASCADE to drop the anchor table and its last projection, or create a replacement super projection instead

I don't want to drop the entire table. What is the way to drop a column or rename it?

Answer 1

If you don't take precautions on how your table's first super projection is created, Vertica will use the first 8 columns for both ORDER BY and SEGMENTED BY HASH().

See here:

CREATE TABLE my_faithful_test(id,eruptions) AS
          SELECT 1, 3.2
UNION ALL SELECT 2, 4.3
;
-- out CREATE TABLE
-- out Time: First fetch (0 rows): 40.831 ms. All rows formatted: 41.480 ms

SELECT EXPORT_OBJECTS('','my_faithful_test',FALSE);
-- out EXPORT_OBJECTS
-- out ---------------
-- out 
-- out CREATE TABLE dbadmin.my_faithful_test
-- out (
-- out     id int,
-- out     eruptions numeric(2,1)
-- out );
-- out 
-- out 
-- out CREATE PROJECTION dbadmin.my_faithful_test_super /*+basename(my_faithful_test),createtype(A)*/ 
-- out (
-- out  id,
-- out  eruptions
-- out )
-- out AS
-- out  SELECT my_faithful_test.id,
-- out         my_faithful_test.eruptions
-- out  FROM dbadmin.my_faithful_test
-- out  ORDER BY my_faithful_test.id,
-- out           my_faithful_test.eruptions
-- out SEGMENTED BY hash(my_faithful_test.id, my_faithful_test.eruptions) ALL NODES OFFSET 0;                                                                                                             

If you really want to keep the table, you have these choices:

• rename the column you want to get rid of, to park it
• create a new super projection without the offending column used for ordering or segmenting; refresh the table; SELECT MAKE_AHM_NOW() for safety, to remove any remaining delete vector ROS containers; drop the offending super projection (with the offending column in an "important" role; finally, remove the unwanted column.

Here's how I do it:

ALTER PROJECTION dbadmin.my_faithful_test_super 
RENAME TO my_faithful_test_dropme;
-- out ALTER PROJECTION
CREATE PROJECTION dbadmin.my_faithful_test_super
AS SELECT * FROM dbadmin.my_faithful_test
ORDER BY id UNSEGMENTED ALL NODES;
-- out WARNING 4468:  Projection <dbadmin.my_faithful_test_super> is not available for query processing. Execute the select start_refresh() function to copy data into this projection.
-- out           The projection must have a sufficient number of buddy projections and all nodes must be up before starting a refresh
-- out CREATE PROJECTION
SELECT REFRESH('dbadmin.my_faithful_test');
-- out REFRESH 
-- out --------
-- out  Refresh completed with the following outcomes:
-- out Projection Name: [Anchor Table] [Status] [Refresh Method] [Error Count] [Duration (sec)]
-- out ----------------------------------------------------------------------------------------
-- out "dbadmin"."my_faithful_test_super": [my_faithful_test] [refreshed] [scratch] [0] [0]
SELECT MAKE_AHM_NOW();
-- out           MAKE_AHM_NOW          
-- out --------------------------------
-- out  AHM set (New AHM Epoch: 37482)
DROP PROJECTION dbadmin.my_faithful_test_dropme;
-- out DROP PROJECTION
ALTER TABLE my_faithful_test DROP COLUMN eruptions;
-- out ALTER TABLE