Re: pg6.4.2 eating records...

Matthew Hagerty <matthew@venux.net> writes:
> I have a question about pg6.4.2, I know it is old but upgrading is not an 
> option at this point in time (not my decision). :(

Try to persuade your PHB to reconsider ;-)

> Every night I run the following:

> <sql to drop all indexes>
> vacuum;
> <sql to create all indexes>
> vacuum analyze;

This is a little bizarre, to say the least.  It should be

> <sql to drop all indexes>
> vacuum analyze;
> <sql to create all indexes>

There's no point in running two vacuums, and there's even less point
in dropping/recreating indexes around the vacuum only to proceed to
run another vacuum with the indexes in place.

> select a.app_id from applicants as a where a.app_id not in
> (select n.note_id from appnotes as n where n.note_id=a.app_id);
> app_id
> ------
> 27555
> 26446
> 27556
> 1734
> 26502
> 26246
> (6 rows)

Ugh.  It would be interesting to see EXPLAIN of this query run just
before and just after the vacuum sequence.  If it's failing just
after the nightly vacuum, what makes it start working again by the
time of the next one?

In all honesty, you are not likely to attract a whole lot of interest
in fixing 6.4.* bugs at this late date.  My own interest will only
extend as far as making sure the bug is not still there in 7.0...

> What happened?  Did vacuum eat them or something?  The records are always 
> just missing out of the appnotes table.

My guess is the records are still there, but due to some bug the
specific query you are using fails to find them.  Postgres has many
faults, but losing data completely isn't usually one of them.
        regards, tom lane