It is weird that the index name needs quotation around it. Below command worked absolutely fine and dropped the index as well
drop index my_schema."users_dept_idx"
It is weird that the index name needs quotation around it. Below command worked absolutely fine and dropped the index as well
drop index my_schema."users_dept_idx"
I had a similar problem. It turns out that when you create an index it is created in the same schema as the underlying table. In this case, you don't have to use the "schema" part of the name. When you drop it you have to use the full name of the index:
create index if not exists ah_login_minute on api.acc_history(login,updated_minute);
drop index if exists ah_login_minute; -- this fails, you have to use full name
drop index if exists api.ah_login_minute; -- this works
Hi all,
for my query, i create multiple indices and then drop them after the query execution..
CREATE INDEX IF NOT EXISTS idx_a_id on tbl_a(a_id)
then
DROP INDEX CONCURRENTLY IF EXISTS idx_a_id;
my problem is that sometimes it hangs on the drop index command for hours so I am supposed to break execution..
Maybe someone will be able to give me some tips on what to do in this situation.
Thanks in advise..
You could try DROP INDEX [ CONCURRENTLY ] name
CONCURRENTLY
Drop the index without locking out concurrent selects, inserts, updates, and deletes on the index's table. A normal DROP INDEX acquires 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.
Here's the documentation for postgres 9.2: http://www.postgresql.org/docs/9.2/static/sql-dropindex.html
Note: This feature is not available before postgres 9.2.
Here's the documentation for postgres 9.1: http://www.postgresql.org/docs/9.1/static/sql-dropindex.html
This is similar to
ONLINE = ONoption in SQL Server: http://technet.microsoft.com/en-us/library/ms176118.aspx
Somehow maintainance_work_mem is not considered as maintenance operations and it does not have any implication to speed up the drop index process.
Dropping the index concurrently seems to be the only option but I won't suggest to directly run it from Pgadmin or Psql terminal. Because if the session gets terminated or due to timeout, it may leave index inconsistent.
Hope this helps. Aj
http://www.postgresql-blog.com/