Re: Reliabilty, was [GENERAL] Future of PostgreSQL

Thomas Reinke wrote:

> 1) Up front, I'll state that we use 6.3, so a number of
> the technical glitches may have been solved since...

6.3 is unbelievably old. Perhaps you weren't getting responses since most
people don't use versions of PostgreSQL that old? I know I tend not to respond
to posts about versions that old. Perhaps that's wrong...

>
> 4) We could never get any answers to reliability related
> questions answered by any of the development team or
> by anyone else on the various postgres discussion groups. We
> would ask the question, post the relevant error log
> message, describe the scenario we thought cause the
> problem, and it's as if the question disappeared into
> a black hole.
>
> Believe it or not, it's actually item #4 that annoys us
> the most. Work arounds are a pain, but at least they
> accomplish something - the problem no longer occurs.
> But when you bang your head against a problem, and no
> one seems to have heard of the issue ever, or even
> acknowledges the post in question, it definitely
> detracts from the value of the product.
>
> Case in point: a long time ago we found a problem affecting
> insertions into the database - doing many inserts (I believe
> where the record already existed) caused a memory leak when
> the insert was rejected due to duplicate index entries.
> This forced us to inject a drop/reconnect sequence into the
> code to avoid using up all of our memory. We asked about
> the problem - no response; we posted the bug in the PR
> database - no response; 6 months later, we saw someone
> else ask the exact same question (not sure of release,
> i thought he was on 6.4, but don't hold me on that one).
>
> It's that kind of non-responsiveness that in our mind makes
> the db reliability an issue.
>

The following are fixes relating to problems you described since 6.3:

Bug Fixes
---------
Fix for a tiny memory leak in PQsetdb/PQfinish(Bryan)
Fix for buffer leaks in large object calls(Pascal)
Fix memory leak in libpgtcl's pg_select(Constantin)
libpq memory overrun fix
pg_dump fixes for memory leak, inheritance constraints, layout change
Fix memory overruns(Tatsuo)
Memory overrun cleanups(Tatsuo)
Drop buffers before destroying database files(Bruce)
Fix for memory leak in executor with fjIsNull
Fix for aggregate memory leaks(Erik Riedel)
Fix for memory leak in failed queries(Tom)
Fix vacuum's memory consumption(Hiroshi,Tatsuo)
Reduce the total memory consumption of vacuum(Tom)
This is to re-use space on index pages freed by vacuum(Vadim)
Repair incorrect cleanup of heap memory allocation during transaction
abort(Tom)

These are just the ones I pulled from the change logs on www.postgresql.org
related to memory. There are hundreds of other fixes listed as well. I realize
that the answer of "upgrade" to problems you are experiencing is not a
definitive solution, since the bugs might very well be present in current
releases. For example, I can guess its still going to take quite a while for
vacuum to remove 600,000 rows from your database. Some have suggested (and I
agree) that vacuum ought to:

1) Estimate the number of rows to be removed
2) If over a certain threshold:
A. drop all indexes on tables
B. vacuum away dead tuples
C. rebuild indexes

Having said that, I must say that my general impression has been that the
major code developers took over code which was probably 50% bug-ridden garbage
and worked away at it with each release performing MAJOR bug fixes. Just read
Bruce Momjian's HISTORY document to get an idea of the monumental tasks they
have undertaken. I normally don't upgrade other software at each minor release
-- but I do with PostgreSQL. You can tell that they've made huge advances
against the otherwise, uncharted, bug-ridden pieces of 1980's Berkley code...
They're getting closer and closer to what one might call "robustness" at an
accelerated pace, so keep the faith! :-)

Merry Christmas,

Mike Mascari

P.S.: We've been running 6.5beta in a production envirnoment with similar
record counts as the ones you've described and only run into trouble twice.
Once was due to aborting a transaction which contained DDL statements, and the
other was, what I believe, a problem with the 2.0.36 Linux kernel. I hope you
can read into this that our eagerness to get to 6.5 meant using 6.5 beta in
production over using 6.4.2....