Re: Strange behavior: row won't delete

Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)com> writes:

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

You're off on the wrong track. Locks are held by transactions until the
transaction commits. You need to find the transactions which are holding these
locks and either commit or roll them back.

You look in pg_locks to see what locks transactions are holding. In particular
look for rows with "granted" set to "t", especially locks on relations and
especially ExclusiveLocks.

Then you take the pid of those transactions and look in pg_stat_activity to
see what they're up to. If they say "<idle in transaction>" then they're
waiting for the client to do something. If they stay that way for any length
of time while holding locks which block other transactions that's bad.

Alternately if you see a query in pg_stat_transaction which is taking a long
time to run you might check whether you have a bad plan or a bad query running
while holding locks effectively doing the same thing.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's RemoteDBA services!