I'm on PostgreSQL 8.3.11 on Debian.
I have a small piece of DDL (alter table title drop column is_target) that
hangs "waiting" forever. (I've waited hours - it still shows as waiting in
pg_top.) I have restarted the database - even tried it in single-user mode
- but it still waits.
When I execute a query to get lock info:
select
pg_class.relname,pg_locks.locktype,mode,virtualtransaction,database,relation,granted
from pg_class,pg_locks where pg_class.relfilenode=pg_locks.relation;
(These are all the columns in pg_locks with anything but null in them.)
relname | locktype | mode |
virtualtransaction | database | relation | granted
--------------------------------------+----------+-----------------+--------------------+----------+----------+---------
property_key_name_index | relation | AccessShareLock |
-1/22805859 | 64197 | 361377 | t
location_network_name_index | relation | AccessShareLock |
-1/22805859 | 64197 | 925488 | t
serial_item_ref_num_idx | relation | AccessShareLock |
-1/22805859 | 64197 | 78445 | t
course_locator_id_key | relation | AccessShareLock |
-1/22805859 | 64197 | 139543 | t
pg_class_oid_index | relation | AccessShareLock |
2/35 | 64197 | 2662 | t
transit_locator_source_id_index | relation | AccessShareLock |
-1/22805859 | 64197 | 71026 | t
transit_locator_destination_id_index | relation | AccessShareLock |
-1/22805859 | 64197 | 71025 | t
pg_locks | relation | AccessShareLock |
2/35 | 64197 | 10969 | t
pg_class_relname_nsp_index | relation | AccessShareLock |
2/35 | 64197 | 2663 | t
course_locator_location_id | relation | AccessShareLock |
-1/22805859 | 64197 | 139707 | t
property_key_category_ordering_index | relation | AccessShareLock |
-1/22805859 | 64197 | 361381 | t
serial_item_condition | relation | AccessShareLock |
-1/22805859 | 64197 | 139711 | t
user_account_customer_id | relation | AccessShareLock |
-1/22805859 | 64197 | 404180 | t
customer_locator_unique_index | relation | AccessShareLock |
-1/22805859 | 64197 | 361408 | t
web_store_store_id | relation | AccessShareLock |
-1/22805859 | 64197 | 404179 | t
custom_title | relation | AccessShareLock |
-1/22805859 | 64197 | 64244 | t
store | relation | AccessShareLock |
-1/22805859 | 64197 | 64588 | t
store_pkey | relation | AccessShareLock |
-1/22805859 | 64197 | 70822 | t
custom_title_pkey | relation | AccessShareLock |
-1/22805859 | 64197 | 70702 | t
title_product_code_like_index | relation | AccessShareLock |
-1/22805859 | 64197 | 355673 | t
title_desc_author_fulltext_index | relation | AccessShareLock |
-1/22805859 | 64197 | 357244 | t
property_value_key_location_index | relation | AccessShareLock |
-1/22805859 | 64197 | 361407 | t
web_store_store_name_index | relation | AccessShareLock |
-1/22805859 | 64197 | 92414 | t
custom_title_location_id_index | relation | AccessShareLock |
-1/22805859 | 64197 | 70916 | t
course_locator_division_id_key | relation | AccessShareLock |
-1/22805859 | 64197 | 139892 | t
property_key_lookup_name_index | relation | AccessShareLock |
-1/22805859 | 64197 | 404257 | t
pg_class | relation | AccessShareLock |
2/35 | 64197 | 1259 | t
web_store | relation | AccessShareLock |
-1/22805859 | 64197 | 92411 | t
user_account_username_index | relation | AccessShareLock |
-1/22805859 | 64197 | 925492 | t
transit_locator | relation | AccessShareLock |
-1/22805859 | 64197 | 64392 | t
inventory_item_locator_id | relation | AccessShareLock |
-1/22805859 | 64197 | 925487 | t
inventory_item_sku_index | relation | AccessShareLock |
-1/22805859 | 64197 | 925490 | t
transit_locator_pkey | relation | AccessShareLock |
-1/22805859 | 64197 | 70858 | t
(33 rows)
I'm not seeing anything there with exclusives, but there are several indexes
listed here from that table under the -1/22805859 virtual transaction.
I seem to be able to execute DDL modifications on other tables without
indexes in this list without issues.
Do I need to clear these locks to get this to run? If so, how? WIth the
exception of the 2/35 transactions, they don't belong to a process - and
those belong to the process running the query that lists the locks :-)
It also appears that I can't execute a "drop index" on that table either.
Thoughts? How do I fix this so I can run DDL?
Please let me know what further information I can provide.
Thanks!
Matt
Matthew Excell
