Обсуждение: Fixing stale locks

Hi,

I am a new postresql user, but have done a lock of work with Oracle. I am
having a familiar problem with stale locks. I try to drop a table in psql
but the transaction hangs. I can abort the transaction with Ctl-C, but it
will never succeed.

My first instinct was a stale lock so I look in pg_locks and sure enough a
lock exists. Now the question is, how do I free this lock? This is just a
single machine installation and there are no processes running that could
hold the lock. I suspect it is from a process that crashed. Under Oracle we
used to just kill the pid that held the lock and the DB cleaned everything
up. When I tried that under postgresql my database crashed. Obviously the
PID column in the pg_locks table is for the server process, not the client
process :-(.

How do people normally deal with this type of problem?

Thanks,

Chris.

--
chris.waters@networkchemistry.com
www.networkchemistry.com

"Chris Waters" <chris.waters@networkchemistry.com> writes:
> My first instinct was a stale lock so I look in pg_locks and sure enough a
> lock exists. Now the question is, how do I free this lock? This is just a
> single machine installation and there are no processes running that could
> hold the lock.

Oh?  Exactly what process does pg_locks show is holding the lock then?

> How do people normally deal with this type of problem?

Either you're misdescribing it, or it's not a normal type of problem.

            regards, tom lane