Help... ROLLBACK 7976: Add column with default subquery is not supported for unsegmented projection

kfruchtmankfruchtman Vertica Customer

I have done the same as on this post:
https://forum.vertica.com/discussion/241554/how-to-add-a-new-column-to-a-table-efficiently
and when I alter the micky table on my db and giving this command:
ALTER TABLE mickey
ADD months_worked INTEGER DEFAULT(SELECT months_worked FROM helper WHERE id=mickey.id);

This is what I get:
ROLLBACK 7976: Add column with default subquery is not supported for unsegmented projections
DETAIL: Projection "spins_DBD_15_rep_test_ittai_3" is unsegmented and is either super projection or specified in the projection list

how can I workaround that?
I am working on Vertica 9.3 version.
Please help me!
Keren

Answers

  • SruthiASruthiA Vertica Employee Administrator

    Can you share us with the export objects of the table mickey?

  • Jim_KnicelyJim_Knicely - Select Field - Administrator
    edited March 2021

    @kfruchtman - This is a bug. I opened a Jira to get it fixed and will update this thread with the hotfix when it is avaiable.

    Note that you can use SET USING...

    Example:

    dbadmin=> CREATE TABLE mickey (id INT);
    CREATE TABLE
    
    dbadmin=> CREATE PROJECTION mickey_rep AS SELECT * FROM mickey UNSEGMENTED ALL NODES;
    CREATE PROJECTION
    
    dbadmin=> INSERT INTO mickey SELECT 1;
     OUTPUT
    --------
          1
    (1 row)
    
    dbadmin=> CREATE TABLE helper (id INT, months_worked INT);
    CREATE TABLE
    
    dbadmin=> INSERT INTO helper SELECT 1, 12;
     OUTPUT
    --------
          1
    (1 row)
    
    dbadmin=> ALTER TABLE mickey ADD months_worked INTEGER DEFAULT(SELECT months_worked FROM helper WHERE id=mickey.id);
    ROLLBACK 7976:  Add column with default subquery is not supported for unsegmented projections
    DETAIL:  Projection "mickey_rep" is unsegmented and is either super projection or specified in the projection list
    
    dbadmin=> ALTER TABLE mickey ADD months_worked INTEGER SET USING (SELECT months_worked FROM helper WHERE id=mickey.id);
    ALTER TABLE
    
    dbadmin=> SELECT refresh_columns('mickey', 'months_worked');
          refresh_columns
    ---------------------------
     refresh_columns completed
    (1 row)
    
    dbadmin=> SELECT * FROM mickey;
     id | months_worked
    ----+---------------
      1 |            12
    (1 row)
    

    Although not ideal, you can also just have segemented projections (for now) on the anchor table mickey...

  • kfruchtmankfruchtman Vertica Customer

    That is a superb workaround!
    Can I ask you another question? is there a way to automate this whole procedure? lets say table micky was updated from an outside source without the new column populated and everyday I need to update the helper table and then update the micky with the new value for the new column.Is it possible somehow?
    many thanks
    Keren

Leave a Comment

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