Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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.