Content-Type: text/plain; charset=UTF-8
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: Clearing locks
From: Alvaro Herrera <alvherre@commandprompt.com>
To: Matthew Excell <mexcell@ucentive.net>
In-reply-to: <AANLkTikxe6Scg9Y0DjrRILmQE9un2rWQgJi-70oxdkVj@mail.gmail.com>
References: <AANLkTikxe6Scg9Y0DjrRILmQE9un2rWQgJi-70oxdkVj@mail.gmail.com>
Date: Mon, 07 Jun 2010 15:46:28 -0400
Message-Id: <1275939916-sup-6638@alvh.no-ip.org>
User-Agent: Sup/0.11
Content-Transfer-Encoding: 8bit

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