From: | Marti Raudsepp <marti(at)juffo(dot)org> |
---|---|
To: | Vojtěch Rylko <vojta(dot)rylko(at)seznam(dot)cz> |
Cc: | Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Drop big index |
Date: | 2012-02-16 08:53:59 |
Message-ID: | CABRT9RDu3uK0BMR7vLWnmH2m4mn4aQfJvPCeMGfhPU4SnUpjUA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
2012/2/15 Vojtěch Rylko <vojta(dot)rylko(at)seznam(dot)cz>:
> this query performed so long and blocked table so I had to interrupt it. Is
> there any way how to drop large indexes in non-blocking or /faster/ way?
Usually the problem is not with the size of the index -- but some
other running transactions that hold a read lock on the table, and
preventing the DROP INDEX from getting an exclusive lock. If the
exclusive lock is granted, the drop index is usually very fast.
Run 'select * from pg_stat_activity' and see if there are any "<IDLE>
in transaction" connections. It's normal to have these for a second or
few, but longer idle transactions usually indicate an application bug
-- it started a transaction, but "forgot" to rollback or commit. These
are problematic for this exact reason -- locks can't be released until
the transaction finishes.
Regards,
Marti
From | Date | Subject | |
---|---|---|---|
Next Message | sujayr06 | 2012-02-16 09:02:59 | Re: Unable to execute \copy from Client Application |
Previous Message | Albe Laurenz | 2012-02-16 08:41:50 | RE: [GENERAL] conexão no windows 7 |