alter column with projections

tdiprimatdiprima Vertica Customer

It looks like Vertica adds a projection to a newly created table, after at least 1 record has been inserted.
That's being said, suppose that I want to alter the table's column, by changing the datatype. With these auto-created projections, the ALTER TABLE statement will fail.
I tried the following, but "all projections" seems to only apply to adding a column.
Any ideas?
ALTER TABLE a_table ALTER COLUMN a_column SET DATA TYPE INT ALL PROJECTIONS ;

Best Answers

  • moshegmosheg Vertica Employee Administrator
    Answer ✓

    You cannot alter type of a column when it is referenced in the segmentation expression of that table projection.
    The following will work.

    CREATE TABLE my_test(f1 number(10), f2 int);
    CREATE TABLE
    CREATE PROJECTION public.my_test_b0
    ( f1,f2) AS
     SELECT my_test.f1,  my_test.f2
     FROM public.my_test
     ORDER BY my_test.f2
    SEGMENTED BY hash(my_test.f2) ALL NODES offset 0;
    CREATE PROJECTION
    SELECT MARK_DESIGN_KSAFE(1);
      MARK_DESIGN_KSAFE
    ----------------------
     Marked design 1-safe
    (1 row)
    
    insert into my_test values(1);
     OUTPUT
    --------
          1
    (1 row)
    
    commit;
    COMMIT
    ALTER TABLE my_test ALTER COLUMN f1 SET DATA TYPE int;
    ALTER TABLE
    

    The following will not work because by default the projection was segmented by both f1 and f2 as shown below.

    DROP TABLE my_test cascade;
    DROP TABLE
    CREATE TABLE my_test(f1 number(10), f2 int);
    CREATE TABLE
    insert into my_test values(1);
     OUTPUT
    --------
          1
    (1 row)
    
    commit;
    COMMIT
    ALTER TABLE my_test ALTER COLUMN f1 SET DATA TYPE int;
    vsql:3test.sql:15: ROLLBACK 2353:  Cannot alter type of column "f1" since it is referenced in the segmentation expression of projection "my_test_b0"
    SELECT EXPORT_OBJECTS('','public.my_test','true');
      CREATE TABLE public.my_test (f1 numeric(10,0), f2 int);
      CREATE PROJECTION public.my_test_b0 /*+basename(my_test),createtype(L)*/
      ( f1, f2 ) AS
     SELECT my_test.f1, my_test.f2
     FROM public.my_test
     ORDER BY my_test.f1, my_test.f2
    SEGMENTED BY hash(my_test.f1, my_test.f2) ALL NODES;
    
  • marcothesanemarcothesane - Select Field - Administrator
    Answer ✓

    No .
    SELECT mark_design_ksafe(1); is a dbadmin command that configures the database to use KSAFE 1 implicitly, by default, when creating any projection.
    You don't need to fire that command if you create a projection on a table that you, as an ordinary user, have created.
    It's just as @mosheg has said it:
    You cannot alter the data type of a column that is used in the segmentation condition of any projection in a table, because there is no guarantee that the resulting HASH() value will be the same before and after the type change. If the HASH() value for a given row is different, that specific row will have to go to a different node than it currently is .

Answers

  • tdiprimatdiprima Vertica Customer

    Hi @mosheg. Thank you for this thorough answer.
    If I'm understanding it correctly, it will only work if you run SELECT mark_design_ksafe(1);. Which means you have to be a superuser.
    Otherwise, then the mark_design_ksafe fails, and subsequently the INSERT fails, and then the ALTER after that.
    Have I got it right? :)
    Thanks again.

Leave a Comment

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