pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Clearing locks
3+ messages / 2 participants
[nested] [flat]

* Clearing locks
@ 2010-06-07 19:33  Matthew Excell <mexcell@ucentive.net>
  0 siblings, 1 reply; 3+ messages in thread

From: Matthew Excell @ 2010-06-07 19:33 UTC (permalink / raw)
  To: pgsql-admin

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

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Clearing locks
@ 2010-06-07 19:46  Alvaro Herrera <alvherre@commandprompt.com>
  parent: Matthew Excell <mexcell@ucentive.net>
  0 siblings, 1 reply; 3+ messages in thread

From: Alvaro Herrera @ 2010-06-07 19:46 UTC (permalink / raw)
  To: Matthew Excell <mexcell@ucentive.net>; +Cc: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Clearing locks
@ 2010-06-07 19:59  Matthew Excell <mexcell@ucentive.net>
  parent: Alvaro Herrera <alvherre@commandprompt.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Matthew Excell @ 2010-06-07 19:59 UTC (permalink / raw)
  To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: pgsql-admin

That did it. Thanks!

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.
> >
> > 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
>

^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2010-06-07 19:59 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-06-07 19:33 Clearing locks Matthew Excell <mexcell@ucentive.net>
2010-06-07 19:46 ` Alvaro Herrera <alvherre@commandprompt.com>
2010-06-07 19:59   ` Matthew Excell <mexcell@ucentive.net>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox