How to savely drop a column involved in a projection

joergschaber

Hi,
I want to drop a column that is part of a primary key and involved in a projection. Clearly, I have to drop the constraints first and define a new primary key. But when I try to drop the projection I get an error: "No up-to-date super projection left on the anchor table of projection X_super".
Also also tried something like

ALTER TABLE X DROP CONSTRAINT pk;
CREATE PROJECTION X_p1  as select * FROM X UNSEGMENTED ALL NODES;
SELECT START_REFRESH();
SELECT MAKE_AHM_NOW();  
ALTER TABLE X DROP COLUMN col;
ALTER TABLE X ADD CONSTRAINT pk PRIMARY KEY (other_col) ENABLED;

That works sometime, but not always.

Is there a robsut way to do this?

Jim_Knicely

Hi,

You just have to be sure that their always exists atleast one super projection available to service queries, where that super projection does not use the column that you are going to drop in the ORDER BY or SEGMENTED BY clauses.

Example:

dbadmin=> CREATE TABLE t1 (c1 INT NOT NULL, c2 INT NOT NULL, c3 VARCHAR) ORDER BY c1, c2 SEGMENTED BY HASH(c1, c2) ALL NODES;
CREATE TABLE

dbadmin=> ALTER TABLE t1 ADD CONSTRAINT t1_pk PRIMARY KEY(c1, c2) ENABLED;
ALTER TABLE

dbadmin=> INSERT INTO t1 SELECT 1, 1, 'Data1';
 OUTPUT
--------
      1
(1 row)

dbadmin=> INSERT INTO t1 SELECT 1, 2, 'Data2';
 OUTPUT
--------
      1
(1 row)

dbadmin=> COMMIT;
COMMIT

dbadmin=> CREATE PROJECTION t1_new_super AS SELECT * FROM t1 ORDER BY c2 SEGMENTED BY HASH(c2) ALL NODES;
WARNING 4468:  Projection <public.t1_new_super> is not available for query processing. Execute the select start_refresh() function to copy data into this projection.
          The projection must have a sufficient number of buddy projections and all nodes must be up before starting a refresh
CREATE PROJECTION

dbadmin=> SELECT refresh('t1');
                                                                                                                                            refresh                                                                                             
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Refresh completed with the following outcomes:
Projection Name: [Anchor Table] [Status] [Refresh Method] [Error Count] [Duration (sec)]
----------------------------------------------------------------------------------------
"public"."t1_new_super": [t1] [refreshed] [scratch] [0] [0]

(1 row)

dbadmin=> ALTER TABLE t1 DROP CONSTRAINT t1_pk;
ALTER TABLE

dbadmin=> ALTER TABLE t1 DROP COLUMN c1;
ALTER TABLE

dbadmin=> ALTER TABLE t1 ADD CONSTRAINT t1_pk PRIMARY KEY(c2) ENABLED;
ALTER TABLE

dbadmin=> SELECT export_objects('', 't1', FALSE);
                                                                                                                                      export_objects                                                                                            
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


CREATE TABLE public.t1
(
    c2 int NOT NULL,
    c3 varchar(80),
    CONSTRAINT t1_pk PRIMARY KEY (c2) ENABLED
);


CREATE PROJECTION public.t1_new_super
(
 c2,
 c3
)
AS
 SELECT t1.c2,
        t1.c3
 FROM public.t1
 ORDER BY t1.c2
SEGMENTED BY hash(t1.c2) ALL NODES OFFSET 1;



(1 row)

marcothesane

Can you try a SELECT MAKE_AHM_NOW(); , before you drop the old super projection? You can't indeed drop a projection if there are still delete vectors pointing to it. These disappear with MAKE_AHM_NOW() .

marcothesane

In a nutshell: dropping the primary key is not enough. A column that is part of the ORDER BY clause of any projection definition, or is part of the SEGMENTED BY clause of a projection definition can't be dropped.

As @Jim_Knicely did it:

• Create a new super projection (any projection involving all columns is a superprojection) ordered and segmented without using the column you want to drop;
• refresh;
• drop the offending super projection.
• finally drop the column

joergschaber

Thanks for clarification. Howver, that apprach does not work es expected for me, because the old super projection still exisits. When I try to

DROP PROJECTION projection X_super;

I get [Code: 4470, SQL State: 55006] [Vertica]VJDBC ROLLBACK: Projection cannot be dropped because history after AHM would be lost
[Vertica][VJDBC]Detail: Current AHM epoch = 4949076

How can I get rif of the old projection?

Jim_Knicely

@joergschaber - Try running the MAKE_AHM_NOW() function!

It sets the Ancient History Mark (AHM) to the greatest allowable value.

Note: After running this function, you cannot query historical data that precedes the current epoch. Only database administrators should use this function.

marcothesane

@Jim_Knicely and me ... Here you see how shared-nothing MPP works ... :-]]

Jim_Knicely

@marcothesane - Ha! Massively Parallel People (MPP)!

@joergschaber - Fyi - The Vertica Analytics Platform is based on a massively parallel processing (MPP), shared-nothing architecture, in which the query processing workload is divided among all nodes of the Vertica database.

joergschaber

Yes, using SELECT MAKE_AHM_NOW(), it works. Thanks!