phoenix.kiula@gmail.com (Phoenix Kiula) writes:
> On Wed, Mar 4, 2009 at 12:55 AM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:
>> I guess my question is, how should I remove all pending locks on a
>> table so that I can get on with the rest of the stuff?
>>
>> I mean, even if I can now find an offending RULE on the table, I
>> cannot replace or remove it. '
>
> Any ideas? I think I have identified the offending RULE that was newly
> created on the table. It may have missed the WHERE condition.
>
> But even if CREATE OR REPLACE this rule, the command doesn't go
> through. So I'm stuck in a vicious loop.
>
> How can I get rid of all open locks? When I go through pg_locks there
> are about 1041 of them right now. I am looking for a mechanism of
> deleting them all (can I just delete all rows in pg_locks?) instead of
> manually going through 1041 on a live database.
>
> Thanks!
You don't "delete locks" - the requestors need to relinquish them.
pg_locks is NOT a table - it is a view that draws in data from a set
returning function, so attempting to delete them won't work. See?
mydatabase=# delete from pg_locks;
ERROR: cannot delete from a view
HINT: You need an unconditional ON DELETE DO INSTEAD rule.
The hint isn't particularly relevant here; it's not meaningful to try
to "delete" the locks.
Locks are taken out as a result of connections doing their work. In
order for them to relinquish the locks, one of two things must happen:
a) The transaction (held by a connection) needs to finish, or
b) You might terminate the connection to *force* termination of the
transaction.
Thus, what you *might* do would be to look at the processes involved
with those 1041 locks, and terminate the PIDs. I doubt that there are
1041 unique PIDs involved; it is much more likely that a few
connections have claimed most of those locks.
Of course, terminating those connections might have some negative
side-effects. It would probably, for instance, terminate the
connection that you WANT to have working on alterations, so you might
want to exclude *that* connection.
--
(reverse (concatenate 'string "gro.mca" "@" "enworbbc"))
http://cbbrowne.com/info/lsf.html
Rules of the Evil Overlord #154. "I will instruct my Legions of Terror
in proper search techniques. In particular, if they are searching for
escapees and someone shouts, "Quick! They went that way!", they must
first ascertain the identity of this helpful informant before dashing
off in hot pursuit." <http://www.eviloverlord.com/>
