ERROR: Too many data partitions

mdobryakov

I have the following table:

CREATE TABLE rosing_epg_program_events
(
    id  IDENTITY ,
    uid varchar(512) NOT NULL,
    name varchar(1024) NOT NULL,
    program_uid varchar(512) NOT NULL,
    ….
)
PARTITION BY (rosing_epg_program_events.program_uid);

ALTER TABLE rosing_epg_program_events ADD CONSTRAINT C_PRIMARY PRIMARY KEY (id);

As you can see it partitioned by program_uid field. This field has a lot of values:

SELECT count(DISTINCT program_uid)
FROM rosing_epg_program_events

Result: 7089

Seems like I choose invalid field for partitioning (see Best Practices section here).

select table_schema, projection_name, count(1)
from v_monitor.partitions
where projection_name = 'rosing_epg_program_events_super'
group by table_schema, projection_name
order by table_schema, projection_name;

Result:

table_schema         | projection_name                 | count
---------------------------------------------------------------
spbtv_russia_staging | rosing_epg_program_events_super | 1017

So, I want to remove partitioning from this table:

ALTER TABLE rosing_epg_program_events REMOVE PARTITIONING

but this SQL cause the following error:

Severity: ROLLBACK, Message: Too many data partitions, 
Sqlstate: 54000, 
Hint: Verify that the table partitioning expression is correct, 
Routine: handlePartitionKey, 
File: /scratch_a/release/16125/vbuild/vertica/EE/Operators/DataTarget.cpp, 
Line: 1478, 
SQL: "ALTER TABLE rosing_epg_program_events REMOVE PARTITIONING"

So, how to fix this problem and remove partitioning from this table?

sKwa

Hi!

 

I see 2 options:

1. rebuild a table
2. try to merge partitions and then alter table.