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

Answers

• SruthiA Administrator

  March 2021

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

  0
• Jim_Knicely - Select Field - Administrator

  March 2021 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...

  0
• kfruchtman Vertica Customer ✭

  March 2021

  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

  0