alter column with projections

tdiprima

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 ;

mosheg

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;

marcothesane

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 .

tdiprima

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.