A Haunted Database

I know that PostgreSQL does not support logging of all transactions in a
separate file. Unfortunately, I need this feature. As we all know, the
documentation is rather incomplete and unorganized (can't blame anybody, I
prefer programming to documenting as well =:-) ). That's why I hope to get
some help here:

What would be the best way to implement it?
- triggers (if yes, how efficiently?) ???

I would be most grateful for a short example.

Regards
Horst

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:

Robert Cleveland wrote:

... 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.
... Does anyone know what inhuman entity
might be causing this to occur?

Hmmm...hard not to yell, "It's GREMLINS!!!"...

I'd put a trigger on the table to log the date/time of any changes to the
server log. That might tell you if it's getting corrupted via SQL or via
file system access. Or you might just shutdown the server and see if it
still gets corrupted (verifying your assertion that nobody is touching the
db).

Regards,
Ed Loehr

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

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.

How small is the "chunk"? If you have an index on it, it must be much
smaller than 8k. I recommend lower than 2k.

P.S. Can you give me the dump file so that I could dig into the
problem. I think pg_dump -t the_table_you_have_problems is probably
enough.
--
Tatsuo Ishii

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: