Обсуждение: Clearing locks

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

Excerpts from Matthew Excell's message of lun jun 07 15:33:27 -0400 2010:
> 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

uh.  Check pg_prepared_transactions (or was it pg_prepared_xacts?) and
do a ROLLBACK PREPARED (or COMMIT PREPARED) if there's anything that
shouldn't be there.

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

That did it. Thanks!

Matthew Excell