vertica projections: adding new columns leads to reusing aliases of old columns

36 Views Asked by At

Sometimes after adding new columns to a table we face with problem of strange aliases in projection columns: new columns have aliases of old columns names. Columns were added like alter table DMA.test_migrations add column my_column int

Example of DDL:

CREATE TABLE DMA.test_migrations
(
    launch_id int NOT NULL,
    datamart varchar(128),
    actual_date date,
    time_sla interval,
    kek int,
    lol int,
    lolkek int,
    my_column int,
    my_column2 int,
    my_column3 int,
    my_column4 int,
    my_column5 int,
    my_column6 int,
    my_column7 int
);


CREATE PROJECTION DMA.test_migrations_super /*+basename(test_migrations),createtype(P)*/ 
(
 launch_id,
 datamart,
 actual_date,
 time_sla,
 kek,
 lol,
 lolkek,
 my_column,
 my_column2,
 my_column3,
 my_column4,
 my_column5,
 my_column6,
 my_column7
)
AS
 SELECT test_migrations.launch_id,
        test_migrations.datamart,
        test_migrations.actual_date,
        test_migrations.time_sla,
        test_migrations.kek,
        test_migrations.lol,
        test_migrations.lolkek,
        test_migrations.my_column AS launch_id,
        test_migrations.my_column2 AS datamart,
        test_migrations.my_column3 AS actual_date,
        test_migrations.my_column4 AS time_sla,
        test_migrations.my_column5 AS kek,
        test_migrations.my_column6 AS lol,
        test_migrations.my_column7 AS lolkek
 FROM DMA.test_migrations
 ORDER BY test_migrations.datamart,
          test_migrations.actual_date
UNSEGMENTED ALL NODES;


SELECT MARK_DESIGN_KSAFE(1);

This situation leads to DDL statement interfered with this statement error while select analyze_histogram('DMA.test_migrations') Does anyone face with such a problem?

We did't succeed in finding hypoteses of such behaviour.

The complete sequence of actions that led to the problem:

create table if not exists dma.test_migrations
(
    launch_id int not null,
    datamart varchar(128),
    actual_date date,
    time_sla interval
)
order by datamart, actual_date
unsegmented all nodes;

ALTER TABLE dma.test_migrations ADD COLUMN kek int ;
ALTER TABLE dma.test_migrations ADD COLUMN lol int ;
alter table dma.test_migrations drop column lol;
ALTER TABLE dma.test_migrations ADD COLUMN lol int ;
ALTER TABLE dma.test_migrations ADD COLUMN lolkek int ;
ALTER TABLE dma.test_migrations ADD COLUMN my_column int ;
ALTER TABLE dma.test_migrations ADD COLUMN my_column2 int ;
ALTER TABLE dma.test_migrations ADD COLUMN my_column3 int ;
ALTER TABLE dma.test_migrations ADD COLUMN my_column4 int ;
ALTER TABLE dma.test_migrations ADD COLUMN my_column5 int ;
ALTER TABLE dma.test_migrations ADD COLUMN my_column6 int ;
ALTER TABLE dma.test_migrations ADD COLUMN my_column7 int ;
0

There are 0 best solutions below