cannot drop text index in Vertica

rajatpaliwal86

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

marcothesane

You might notice that it does not say that "Object already exists".
An object could be a table, a projection, a view, a sequence.
Find any object with that name, and DROP TABLE, DROP VIEW, DROP PROJECTION, DROP SEQUENCE accordingly - or rename with , for example ALTER TABLE network_events_text_index RENAME TO network_events_text_index_old;.
Then, no object with the same name will exist, and it will work.

rajatpaliwal86

@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);