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

[Stromberg Chris]

Thanks Andy,

Took a look at the pg_stat_activity table, and yes, there are a number
of records in there with 'procpid' and '<IDLE> in transaction' in the
'current_query text field'

Thanks

Chris


-----Original Message-----
From: Andy Colson [mailto:andy at squeakycode.net] 
Sent: 02 March 2012 14:14
To: Stromberg Chris; PostGIS Users Discussion
Subject: Re: [postgis-users] Releasing a lock on a view/database

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

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