pg6.4.2 eating records... - Mailing list pgsql-hackers

From Matthew Hagerty
Subject pg6.4.2 eating records...
Date
Msg-id 4.3.1.2.20000531231333.00b19ba0@127.0.0.1
Whole thread Raw
Responses Re: pg6.4.2 eating records...
List pgsql-hackers
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



pgsql-hackers by date:

Previous
From: Tatsuo Ishii
Date:
Subject: Re: Back online
Next
From: Tom Lane
Date:
Subject: Re: pg6.4.2 eating records...