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

kfruchtman

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

SruthiA

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

Jim_Knicely

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

kfruchtman

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