Clearing locks

Matthew Excell [ Mo, 07 Juni 2010 21:33 ] [ ID #2042760 ]

Re: Clearing locks

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) 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.
>
> When I execute a query to get lock info:
>
> 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;
>
> (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.

--
=C3=81lvaro Herrera <alvherre [at] commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

alvherre [ Mo, 07 Juni 2010 21:46 ] [ ID #2042761 ]

Re: Clearing locks

--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
<alvherre [at] 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,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 <alvherre [at] commandprompt.com>
> 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 
 <div class=3D"gmail_quote">On Mon, Jun 7, 2010 at 1:46 PM, Alvaro H=
errera <<a href=3D"mailto:alvherre [at] commandprompt.com">=
alvherre [at] commandprompt.com</a>> wrote: <blockquote class=3D"gm=
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: 
<div class=3D"im">> 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 
 
</div>uh. =A0Check pg_prepared_transactions (or was it pg_prepared_xacts?) =
and 
do a ROLLBACK PREPARED (or COMMIT PREPARED) if there's anything that<br=
>
shouldn't be there. 
 
-- 
=C1lvaro Herrera <<a href=3D"mailto:alvherre [at] commandprompt.com">alvherre=
[at] commandprompt.com</a>> 
The PostgreSQL Company - Command Prompt, Inc. 
PostgreSQL Replication, Consulting, Custom Development, 24x7 support 
</blockquote></div> 

--002215048dfb89f1cd0488761c33--

Matthew Excell [ Mo, 07 Juni 2010 21:59 ] [ ID #2042762 ]