Greetings,
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). :(
Every night I run the following:
<sql to drop all indexes>
vacuum;
<sql to create all indexes>
vacuum analyze;
The problem I am having is that somewhere in that process records are being
lost. There are two tables that have a one-to-one relationship and records
are always missing from one of the tables, the appnotes:
create table applicants ( app_id int4 . . .
);
create table appnotes ( note_id int4, personalnotes text, technotes text, relonote text, follownote
text, profile text
);
The rest of the applicant data is stored in the applicant table. I don't
really know why the text fields were broken out into their own table, but
they are, and for every record in the applicant table there is supposed to
be a record in the appnotes table.
I added the following query before and after the normal nightly sql and
this is what I got:
-----------
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
------
(0 rows)
<sql to drop all indexes>
vacuum;
<sql to create all indexes>
vacuum analyze;
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)
------------
What happened? Did vacuum eat them or something? The records are always
just missing out of the appnotes table.
Any insight would be greatly appreciated.
Thank you,
Matthew
