I created a table in AWS Redshift (4-node dc1 cluster) in the following manner.
CREATE TABLE events
(
event_id int,
event_date int ,
event_month_year int ,
event_year int ,
event_fraction_date numeric(10,4) ,
event_actor1_code varchar(100) ,
event_actor1_name varchar(100) ,
event_actor1_country_code varchar(100) ,
event_actor1_known_group_code varchar(100) ,
event_actor1_ethnic_code varchar(100) ,
event_actor1_religion1_code varchar(100) ,
event_actor1_religion2_code varchar(100) ,
event_actor1_type1_code varchar(100) ,
event_actor1_type2_code varchar(100) ,
event_actor1_type3_code varchar(100) ,
event_actor2_code varchar(100) ,
event_actor2_name varchar(100) ,
event_actor2_country_code varchar(100) ,
event_actor2_known_group_code varchar(100) ,
event_actor2_ethnic_code varchar(100) ,
event_actor2_religion1_code varchar(100) ,
event_actor2_religion2_code varchar(100) ,
event_actor2_type1_code varchar(100) ,
event_actor2_type2_code varchar(100) ,
event_actor2_type3_code varchar(100),
is_root_event varchar(100) ,
event_code varchar(100) ,
event_base_code varchar(100) ,
event_root_code varchar(100) ,
event_quad_class int ,
event_goldstein_scale float ,
event_num_mentions int ,
event_num_sources int ,
event_num_articles int ,
event_avg_tone float ,
event_actor1_geo_type int ,
event_Actor1_Geo_Full_Name varchar(500) encode lzo,
event_Actor1_Geo_Country_Code varchar(100) ,
event_Actor1_Geo_ADM1_Code varchar(100) ,
event_Actor1_Geo_Lat float ,
event_Actor1_Geo_Long float ,
event_Actor1_Geo_FeatureID varchar(100) ,
event_Actor2_Geo_Type int ,
event_Actor2_Geo_Full_Name varchar(500) encode lzo,
event_Actor2_Geo_Country_Code varchar(100) ,
event_Actor2_Geo_ADM1_Code varchar(100) ,
event_Actor2_Geo_Lat float ,
event_Actor2_Geo_Long float ,
event_Actor2_Geo_FeatureID varchar(100) ,
event_Action_Geo_Type int ,
event_Action_Geo_Full_Name varchar(500) encode lzo,
event_Action_Geo_Country_Code varchar(100) ,
event_Action_Geo_ADM1_Code varchar(100) ,
event_Action_Geo_Lat float ,
event_Action_Geo_Long float ,
event_Action_Geo_FeatureID varchar(100) ,
event_date_added int ,
event_source_url varchar(500) encode lzo
)
diststyle key
distkey(event_id);
I performed a COPY of around 60k records.
Post that, I performed a VACUUM and ANALYZE. It fails at the ANALYZE command with the following error:
[Amazon](500310) Invalid operation: index "pg_toast_16408_index" is not a btree;
I tried dropping and re-creating. But I get the same error. But once I change the diststyle to ALL, then the error disappears.
Any help on this would be very much appreciated.
Best Answer
A restore of the snapshot database resolved the error. Looks like the index was corrupted.