Clearing locks

Clearing locks

am 07.06.2010 21:33:27 von Matthew Excell

--001636eee59a6d387a048875c09e
Content-Type: text/plain; charset=ISO-8859-1

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,d atabase,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

--001636eee59a6d387a048875c09e
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

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" fo=
rever. (I've waited hours - it still shows as waiting in pg_top.)=A0 I =
have restarted the database - even tried it in single-user mode - but it st=
ill waits.



When I execute a query to get lock info:

select pg_class.relnam=
e,pg_locks.locktype,mode,virtualtransaction,database,relatio n,granted from =
pg_class,pg_locks where pg_class.relfilenode=3Dpg_locks.relation;



(These are all the columns in pg_locks with anything but null in them.)
=

          =A0=
  =A0 relname              =A0 | locktype |=
    =A0 mode       | virtualtransaction | database | re=
lation | granted


--------------------------------------+----------+---------- -------+-------=
-------------+----------+----------+---------
=A0property_key_name_index=
            =A0 | relation | AccessShareLock | -1/2=
2805859      =A0 |  =A0 64197 |   361377 | t


=A0location_network_name_index        =A0 | relation | Acce=
ssShareLock | -1/22805859      =A0 |  =A0 64197 |   925=
488 | t
=A0serial_item_ref_num_idx          =A0= A0=
=A0 | relation | AccessShareLock | -1/22805859      =A0 |  =
=A0 64197 |  =A0 78445 | t


=A0course_locator_id_key               =A0 | rel=
ation | AccessShareLock | -1/22805859      =A0 |  =A0 64197=
|   139543 | t
=A0pg_class_oid_index          =
         | relation | AccessShareLock | 2/35    =A0=
        =A0 |  =A0 64197 |     2662 | t


=A0transit_locator_source_id_index    =A0 | relation | AccessShareL=
ock | -1/22805859      =A0 |  =A0 64197 |  =A0 71026 | =
t
=A0transit_locator_destination_id_index | relation | AccessShareLock |=
-1/22805859      =A0 |  =A0 64197 |  =A0 71025 | t


=A0pg_locks                = A0    =
      =A0 | relation | AccessShareLock | 2/35      =
         |  =A0 64197 |  =A0 10969 | t
=A0pg_cla=
ss_relname_nsp_index           | relation | AccessShare=
Lock | 2/35               |  =A0 64197 |=A0=
  =A0 2663 | t


=A0course_locator_location_id           | relation | Ac=
cessShareLock | -1/22805859      =A0 |  =A0 64197 |   1=
39707 | t
=A0property_key_category_ordering_index | relation | AccessSha=
reLock | -1/22805859      =A0 |  =A0 64197 |   361381 |=
t


=A0serial_item_condition               =A0 | rel=
ation | AccessShareLock | -1/22805859      =A0 |  =A0 64197=
|   139711 | t
=A0user_account_customer_id        =
     | relation | AccessShareLock | -1/22805859      =
=A0 |  =A0 64197 |   404180 | t


=A0customer_locator_unique_index      =A0 | relation | AccessSh=
areLock | -1/22805859      =A0 |  =A0 64197 |   361408 =
| t
=A0web_store_store_id            =A0   =
  =A0 | relation | AccessShareLock | -1/22805859      =A0 |=
  =A0 64197 |   404179 | t


=A0custom_title              =A0     =A0=
     | relation | AccessShareLock | -1/22805859      =
=A0 |  =A0 64197 |  =A0 64244 | t
=A0store      =A0=
                          | =
relation | AccessShareLock | -1/22805859      =A0 |  =A0 64=
197 |  =A0 64588 | t


=A0store_pkey              =A0=A 0    =
       | relation | AccessShareLock | -1/22805859    =
  =A0 |  =A0 64197 |  =A0 70822 | t
=A0custom_title_pkey=A0=
                   | relation | AccessS=
hareLock | -1/22805859      =A0 |  =A0 64197 |  =A0 707=
02 | t


=A0title_product_code_like_index      =A0 | relation | AccessSh=
areLock | -1/22805859      =A0 |  =A0 64197 |   355673 =
| t
=A0title_desc_author_fulltext_index     | relation | AccessS=
hareLock | -1/22805859      =A0 |  =A0 64197 |   357244=
| t


=A0property_value_key_location_index  =A0 | relation | AccessShareLock =
| -1/22805859      =A0 |  =A0 64197 |   361407 | t
=
=A0web_store_store_name_index           | relation | Ac=
cessShareLock | -1/22805859      =A0 |  =A0 64197 |  =
=A0 92414 | t


=A0custom_title_location_id_index       | relation | AccessShar=
eLock | -1/22805859      =A0 |  =A0 64197 |  =A0 70916 =
| t
=A0course_locator_division_id_key       | relation | Acc=
essShareLock | -1/22805859      =A0 |  =A0 64197 |   13=
9892 | t


=A0property_key_lookup_name_index       | relation | AccessShar=
eLock | -1/22805859      =A0 |  =A0 64197 |   404257 | =
t
=A0pg_class                = A0  =
        =A0 | relation | AccessShareLock | 2/35    =
           |  =A0 64197 |     1259 | t


=A0web_store                     =A0=
       | relation | AccessShareLock | -1/22805859    =
  =A0 |  =A0 64197 |  =A0 92411 | t
=A0user_account_username=
_index        =A0 | relation | AccessShareLock | -1/2280585=
9      =A0 |  =A0 64197 |   925492 | t


=A0transit_locator                   =A0=
   | relation | AccessShareLock | -1/22805859      =A0 |=A0=
   64197 |  =A0 64392 | t
=A0inventory_item_locator_id  =A0=
         | relation | AccessShareLock | -1/22805859  =
    =A0 |  =A0 64197 |   925487 | t


=A0inventory_item_sku_index          =A0 =A0 | relation =
| AccessShareLock | -1/22805859      =A0 |  =A0 64197 |=A0=
=A0 925490 | t
=A0transit_locator_pkey          =A0=
    =A0 | relation | AccessShareLock | -1/22805859    =A0=
   |  =A0 64197 |  =A0 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. nt>



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 runn=
ing the query that lists the locks :-)



It also appears that I can't execute a "drop index" on th=
at table either.=A0 Thoughts? How do I fix this so I can run DDL?

Pl=
ease let me know what further information I can provide.

Thanks! >


Matt

Matthew Excell



--001636eee59a6d387a048875c09e--

Re: Clearing locks

am 07.06.2010 21:46:28 von alvherre

Excerpts from Matthew Excell's message of lun jun 07 15:33:27 -0400 2010:
> I'm on PostgreSQL 8.3.11 on Debian.
>=20
> I have a small piece of DDL (alter table title drop column is_target) t=
hat
> 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.
>=20
> When I execute a query to get lock info:
>=20
> select
> pg_class.relname,pg_locks.locktype,mode,virtualtransaction,d atabase,rel=
ation,granted
> from pg_class,pg_locks where pg_class.relfilenode=3Dpg_locks.relation;
>=20
> (These are all the columns in pg_locks with anything but null in them.)
>=20
> 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.

--=20
Álvaro Herrera
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Clearing locks

am 07.06.2010 21:59:13 von Matthew Excell

--002215048dfb89f1cd0488761c33
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

That did it. Thanks!

Matthew Excell


On Mon, Jun 7, 2010 at 1:46 PM, Alvaro Herrera
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,d atabase,relat=
ion,granted
> > from pg_class,pg_locks where pg_class.relfilenode=3Dpg_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.
>
> --
> =C1lvaro Herrera
> The PostgreSQL Company - Command Prompt, Inc.
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>

--002215048dfb89f1cd0488761c33
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

That did it. Thanks!

Matthew Excell



On Mon, Jun 7, 2010 at 1:46 PM, Alvaro H=
errera <=
alvherre@commandprompt.com> wrote:
ail_quote" style=3D"border-left: 1px solid rgb(204, 204, 204); margin: 0pt =
0pt 0pt 0.8ex; padding-left: 1ex;">

Excerpts from Matthew Excell's message of lun jun 07 15:33:27 -0400 201=
0:

> 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 s=
hows as waiting in

> pg_top.) =A0I have restarted the database - even tried it in single-us=
er mode

> - but it still waits.

>

> When I execute a query to get lock info:

>

> select

> pg_class.relname,pg_locks.locktype,mode,virtualtransaction,d atabase,re=
lation,granted

> from pg_class,pg_locks where pg_class.relfilenode=3Dpg_locks.relation;=


>

> (These are all the columns in pg_locks with anything but null in them.=
)

>

> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0relname =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=
| locktype | =A0 =A0 =A0mode =A0 =A0 =A0 |

> virtualtransaction | database | relation | granted

> --------------------------------------+----------+---------- -------+--=
------------------+----------+----------+---------

> =A0property_key_name_index =A0 =A0 =A0 =A0 =A0 =A0 =A0| relation | Acc=
essShareLock |

> -1/22805859 =A0 =A0 =A0 =A0| =A0 =A064197 | =A0 361377 | t



uh. =A0Check 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.



--

=C1lvaro Herrera <alvherre=
@commandprompt.com>

The PostgreSQL Company - Command Prompt, Inc.

PostgreSQL Replication, Consulting, Custom Development, 24x7 support




--002215048dfb89f1cd0488761c33--