Thread: Question regarding blocking locks

I have a question regarding blocking locks in the pg database. I ran into a
process which terminated abnormally, and to fully clear the locks it left
behind I had to reboot the system (probably restarting postmaster would have
had the same effect). This was a personal development system so this was no
big deal to reboot it. I would like to know what other options I have so if
this was to occur in a production environment in the future I had a less
drastic measure to take to resolve the issue.

I saw the locks in the pg_locks view (the mode was Exclusivelock),

Can someone point me in the right direction to addressing such a problem
should it occur in the future?

Thanks,

Kevin

_________________________________________________________________
Express yourself instantly with MSN Messenger! Download today - it's FREE!
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/

"Kevin Keith" <kevinrkeith@hotmail.com> writes:
> I have a question regarding blocking locks in the pg database. I ran into a
> process which terminated abnormally, and to fully clear the locks it left
> behind I had to reboot the system (probably restarting postmaster would have
> had the same effect).

Define "terminated abnormally".  You really aren't going to get helpful
answers without giving full details of what happened.  (If the
postmaster thought the backend had crashed, it would have forced a
database restart which would have wiped shared memory.  So it's not
clear from your comment what did happen.)

Also, exactly which PG version is this?

            regards, tom lane

The version of Postgres is 7.4.

When I said the process had terminated abnormally - what I meant was a UNIX
process (C program) had opened a database connection, run some updates
without a COMMIT and then exited without closing the connection to the
database or committing the transactions.

From what I see below, could I assume that the best method is to kill the
offending process in UNIX, and then postmaster should recognize there was a
problem and restart the database. Correct?

Thanks,

Kevin

>From: Tom Lane <tgl@sss.pgh.pa.us>
>Subject: Re: [ADMIN] Question regarding blocking locks Date: Thu, 25 Aug
>2005 12:08:11 -0400
>
>"Kevin Keith" <kevinrkeith@hotmail.com> writes:
> > I have a question regarding blocking locks in the pg database. I ran
>into a
> > process which terminated abnormally, and to fully clear the locks it
>left
> > behind I had to reboot the system (probably restarting postmaster would
>have
> > had the same effect).
>
>Define "terminated abnormally".  You really aren't going to get helpful
>answers without giving full details of what happened.  (If the
>postmaster thought the backend had crashed, it would have forced a
>database restart which would have wiped shared memory.  So it's not
>clear from your comment what did happen.)
>
>Also, exactly which PG version is this?
>
>            regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: don't forget to increase your free space map settings

_________________________________________________________________
Is your PC infected? Get a FREE online computer virus scan from McAfee�
Security. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963