Re: A Haunted Database - Mailing list pgsql-general

Vacuuming is sort of necessary at the moment, because if you don't vacuum,
postgres won't use your indexes. :(  This is supposedly going to be fixed
in the 7.x series (7.5 I think I heard) but I've never heard of a vacuum
corrupting a normally working database in the 4 or 5 months I've been
reading the GENERAL list (or at least I don't remember it...)

Can you post your vacuum script?  Maybe it's doing something besides the
vacuum and that's what's corrupting your database.  Other than that, the
only thing I can think off is that the vacuum is scanning the fields of
your table and is changing ones that have a specific pattern.  That would
be a VERY bad bug, so you would think it would have cropped up before.

BTW: What version are you using?  We use 6.5.3 here, and haven't had any
problems.


At 11:56 AM 4/9/00, Robert Cleveland wrote:
>Thanks! Turning off the nightly vacuum script did the trick. Now . . . any
>idea why vacuum would be so damaging? It certainly appears, at least for me,
>that the routine is more trouble than it is worth. Is it a malfunction that
>can be overwritten or a bug or something else?
>
>Again many thanks. I can sleep without fear now
>
>Rob
>
>
>
> >Do you have any automated program accessing the database overnight?  IE a
> >malfunctioning backup or vacuum script?  You might also want to do a diff
> >-C1 first_dump second_dump to see what is actually being changed.
> >
> >At 11:40 AM 4/8/00, Robert Cleveland wrote:
> >>Here's a mystery I hope someone can solve for me.
> >>
> >>We are entering blocks of HTML into a table called bodyparts. We use PHP3
>to
> >>break up these blocks into several chunks to keep the length below the
> >>maximum. When the end user calls up the section, the "bodyparts" are
> >>extracted and re-assembled.
> >>
> >>The output pages work fine . . . for a while. We set up the output pages
> >>during the day, check them for accuracy and go to bed thinking we have
>done
> >>a great job. Then , in the middle of the night, something happens and when
> >>we awake, we find the HTML has been scrambled like so many breakfast eggs.
> >>Not all sections are scrambled. In fact it is the same sections every
>single
> >>time. So we re-enter the data, check it, assume we are done, and then the
> >>same thing happens the next day.
> >>
> >>To gather some empirical evidence, I ran pg_dump at 7pm on the offending
> >>table. I check the output pages at midnight the same evening, and they all
> >>were good. When I got back in front of the computer at 9am, the pages were
> >>scrambled again. I ran pg_dump a second time to a separate file. The file
> >>sizes were different (insert scary music here). No one had touched the
> >>database or the pages.
> >>
> >>I reloaded the data and everything is back to normal. But I suspect it
>will
> >>happen again tonight and I am afraid. Does anyone know what inhuman entity
> >>might be causing this to occur?
> >>
> >>