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