Обсуждение: pg6.4.2 eating records...

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

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

At 12:51 AM 6/1/00 -0400, Tom Lane wrote:
>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 ;-)

I am, believe me, I am! :)

> > 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.

Well, I do it that way based on your feedback, Tom. ;)  You said once that 
you should drop the indexes prior to running vacuum, then another time you 
said vacuum analyze should be run with indexes in place.  So I do both.  Is 
this bad?


> > 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?

Actually it does not fix itself, I add new records everyday to the appnotes 
table so the application does not break.  I know I know.


>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...

I guess I'm not really looking for a fix, I was just wondering if this was 
a known problem with 6.4.2 and/or if there was maybe a patch that fixed it 
or something.  I need to tell my project manager something intelligent so 
he can communicate to the client and get them to spend the money to have 
the database upgraded to 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

Thanks Tom!

Matthew

Matthew Hagerty <matthew@venux.net> writes:
>>>> <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.

> Well, I do it that way based on your feedback, Tom. ;)  You said once that 
> you should drop the indexes prior to running vacuum, then another time you 
> said vacuum analyze should be run with indexes in place.

I did?  That must have been long ago and far away, because I am now well
aware that vacuum analyze doesn't do anything special with indexes...

> So I do both.  Is this bad?

Well, other than causing wear and tear on your disk drives, it probably
isn't harmful as long as you've got the cycles to spare.  But it's
certainly a waste of time.

>> 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...

> I guess I'm not really looking for a fix, I was just wondering if this was 
> a known problem with 6.4.2 and/or if there was maybe a patch that fixed it 
> or something.

Dunno.  We've fixed a heckuva lot of bugs since 6.4, but I don't have
enough information to tell if this is one of them.  If it remains
unfixed, we'll sure do our best to fix it ... but we'd really like to
see a report against 7.0 before we spend a lot of effort investigating.
        regards, tom lane