-----Original Message-----
From: Andy Colson [mailto:a...@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@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
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Reply via email to