Skip site navigation (1) Skip section navigation (2)

pg6.4.2 eating records...

From: Matthew Hagerty <matthew(at)venux(dot)net>
To: pgsql-hackers(at)postgresql(dot)org
Subject: pg6.4.2 eating records...
Date: 2000-06-01 03:29:14
Message-ID: 4.3.1.2.20000531231333.00b19ba0@127.0.0.1 (view raw or flat)
Thread:
Lists: 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


Responses

pgsql-hackers by date

Next:From: Charlie HornbergerDate: 2000-06-01 03:34:36
Subject: full-text indexing, locales, triggers, SPI & more fun
Previous:From: Tatsuo IshiiDate: 2000-06-01 03:11:15
Subject: Re: Back online

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group