Re: [GENERAL] Is Pg 7.0.x's Locking Mechanism BROKEN?

Fabrice Scemama wrote:

> Hello;
>
> we've been using Postgres for nearly 2 years now, and have
> followed updgrades, from 6.4.2 to 7.0.2. Our applications
> are mainly shell scripts, and CGI. All is done in perl.
> We have long, heavy, and simultaneous transactions.
>
> With 7.0.x versions, we've come across a problem, new for us:
> very often (2 or 3 times a day), there are on our Pg machine
> a dozen of 'update waiting', and our perl scripts are waiting
> too to begin a transaction. So many postgres are running, than
> no other one can be added; only SELECTs continue to be possible.
> We finally have to restart Postgres; but even if we kill
> postmaster's process, there still are postgres processes runnings,
> and they have to be killed too! Definitely not working.
>
> We've been looking for solutions for a few weeks, and even
> decided to move Pg from a satured PIII 650 to a DEC Alpha 500mhz,
> where Postgres is the only application to run.
> So far, we haven't found any workaround, and the hangings have
> been quite nasty for our project.
>
> Yesterday, I moved back our DB to Pg 6.5.3, and yes, now
> everything works fine again (it's slower, but it works).
> And vacuums don't hang the postmaster any more.
>
> So, I've got 2 questions:
> - Am I the only one here to have such problems with 7.0.2?
> - Is 7.0.2 broken?
>
> Regards
> Fabrice Scemama

I saw this problem just last night, exactly the same, I couldn't vacuum,

I shutdown the server but there
where still postmasters running - I killed them, restarted but the locks

where still in place but, in my
case even if I selected the affected record psql would hang. The only
way I managed to recover was to
shutdown the server completely - pgdump the database - drop it -
createit - pgload it, now its working again.
I too never had this problem with 6.5.3, this was the second time for me

the first time I put it down to a 'c'
function I was playing with but I was not even logged it this time.
It seem that two people accessed the same record that started the
problem !

Regards,
Frank.