Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

How to savely drop a column involved in a projection

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?

Best Answers

  • Jim_KnicelyJim_Knicely Administrator
    edited December 2021 Accepted Answer

    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)
    
  • marcothesanemarcothesane Employee
    Accepted Answer

    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() .

Answers

  • 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
  • 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_KnicelyJim_Knicely Administrator

    @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.

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

  • Jim_KnicelyJim_Knicely Administrator

    @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.

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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.