cannot drop text index in Vertica

The Vertica is not able to drop a text index. If I try to create a text index - it complaints that text index already exists however If I try to drop that index it says that index doesn't exist. This seems to be weird.

dbadmin=> CREATE TEXT INDEX athenav2.network_events_text_index ON athenav2.f_network_events ( event_id, raw) TOKENIZER public.FlexTokenizer(long varbinary);
ERROR 6158: Can't create an index named "network_events_text_index": Object already exists

dbadmin=> DROP TEXT INDEX athenav2.network_events_text_index;
ROLLBACK 6285: Index "network_events_text_index" does not exist

Best Answer

Answers

  • @marcothesane
    The export_objects signified that a text index implicitly creates a table in Vertica. I had previously run the create text index command and which interrupted due to some reason.
    The drop table command worked for me.

    dbadmin=> select export_objects('','athenav2.network_events_text_index');

    export_objects

    CREATE TABLE athenav2.network_events_text_index
    (
    token varchar(128),
    doc_id int NOT NULL,
    partition int NOT NULL
    )
    PARTITION BY (network_events_text_index.partition);

    ALTER TABLE athenav2.network_events_text_index ADD CONSTRAINT C_FOREIGN FOREIGN KEY (doc_id) references athenav2.f_network_events (event_id);

    CREATE PROJECTION athenav2.network_events_text_index_super /+basename(network_events_text_index),createtype(P)/
    (
    token ENCODING RLE,
    doc_id ENCODING DELTAVAL,
    partition ENCODING RLE
    )
    AS
    SELECT network_events_text_index.token,
    network_events_text_index.doc_id,
    network_events_text_index.partition
    FROM athenav2.network_events_text_index
    ORDER BY network_events_text_index.token,
    network_events_text_index.doc_id
    SEGMENTED BY hash(network_events_text_index.doc_id) ALL NODES OFFSET 0;

    SELECT MARK_DESIGN_KSAFE(0);

Leave a Comment

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.