Menu
In a legacy PostgreSQL DB I tried to drop an existing index issuing the command:
DROP INDEX testing.idx_testing_data_model_output_data_id;
and see the error:
ERROR: index "<index name>" does not exist
But I can see the index using the \d <table name> command:
leg=# \d testing.data_model
Table "testing.data_model"
Column | Type | Modifiers
----------------+-----------------------------+---------------------------------------------------------------------------
id | bigint | not null default nextval('testing.data_model_id_seq'::regclass)
input_data | text |
output_data_id | bigint |
Indexes:
"pk_testing_data_model" PRIMARY KEY, btree (id)
"idx_testing_data_model_output_data_id" btree (output_data_id)
Ok, when I try to create the index I receive the following error:
ERROR: relation "<index name>" already exists
It seems that somehow the index creation or index dropping was not successfully complete. How can I resolve this issue?
332
DROP TABLE removes tables from the database. Only the table owner, the schema owner, and superuser can drop a table. To empty a table of rows without destroying the table, use DELETE or TRUNCATE . DROP TABLE always removes any indexes, rules, triggers, and constraints that exist for the target table.
As we saw above, running a couple of queries on our posts table reveals that even given an index to use, Postgres will not always choose to use it. The reason why this is the case is that indexes have a cost to create and maintain (on writes) and use (on reads).
Execute a drop index on a table with 11Mil rows takes over 1 hour. It could be that the TSQL to drop the index is being blocked by something else running against the table. Also check to see if you auto shrink set on on the database. Sometimes these settings can cause performance problems.
A normal DROP INDEX acquires an ACCESS EXCLUSIVE lock on the table, blocking other accesses until the index drop can be completed. With this option, the command instead waits until conflicting transactions have completed.
SET search_path = <schema_name>;
Setting search_path worked for me.
136
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
