Обсуждение: 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
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
Matthew Excell
On Mon, Jun 7, 2010 at 1:46 PM, Alvaro Herrera <alvherre@commandprompt.com> wrote:
Excerpts from Matthew Excell's message of lun jun 07 15:33:27 -0400 2010:> I'm on PostgreSQL 8.3.11 on Debian.uh. Check pg_prepared_transactions (or was it pg_prepared_xacts?) and
>
> 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
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