[postgis-users] Releasing a lock on a view/database

[Andy Colson]

> -----Original Message-----
> From: Andy Colson [mailto:andy at squeakycode.net]
> Sent: 01 March 2012 14:53
> To: PostGIS Users Discussion
> Cc: Stromberg Chris
> Subject: Re: [postgis-users] Releasing a lock on a view/database
>
> On 3/1/2012 4:05 AM, Chris Stromberg wrote:
>> I'm trying to edit a view that is not currently in active use, but has
> been
>> used previously.
>>
>> when submitting the change to the view, the SQL window just says
> 'query is
>> running' and it'll sit there until i stop it.
>>
>> If i restart the postgres service, obviousely, all locks are released,
> so
>> when i run the update view statement, it works immediately. But this
> is not
>> practical when other tables/views are in use.
>>
>> Any one know how i can amend a view when the rest of the database is
> in use?
>>
>> thanks
>>
>
>
> Sounds like you have transactions left open, which can keep things
> locked.  check ps ax|grep postgres for things that say "idle in
> transaction"
>
> Also, you can query pg_locks and it can tell you what is using it.
>
> Also, check pg_stat_activity so see what's currently running.
>
> -Andy
>
>
> The information in this email is confidential and may be legally privileged. It is intended solely for the addressee. If you receive this email by mistake please  notify the sender and delete it immediately. Opinions expressed are those of the individual and do not necessarily  represent the opinion of Cambridgeshire County Council. All sent and received email from Cambridgeshire County Council is automatically scanned for the presence of computer viruses and security issues.
>
> Visit www.cambridgeshire.gov.uk
>


On 03/02/2012 04:46 AM, Stromberg Chris wrote:
> Andy,
>
> Sorry to contact you directly, but i can't see your reply against the
> thread, so can't add to it!!! (don't think it's been accepted yet...)
>
> I'm a bit of a novice with postgis, and am using PG Admin to manage it
> within a windows environment.
>
> You mentioned pg_locks and pg_stat_activity. Where do it see them (i
> can't find them under any of the data structure)? and can i remove the
> relevant locks on that view/table in question?
>
> many thanks
>
> Chris
>
>


No problem.  It's an email list, so just cc postgis-users at postgis.refractions.net and everyone else should see it too.


In a sql window run:

select * from pg_stat_activity

I don't use PgAdmin, so I'm not sure how it looks.  The pg_* tables are system tables, so they probably wont show under a public group.  Is there a system group?  You don't really need to see it in the explorer tree, just run the sql.

No, you cannot delete from pg_locks.  And that table only has ID's that you have to look up in other tables, so it's  not real simple.  Lets start with the activity table and see if we can find anything interesting in there.

-Andy