Thread: yet another db corruption issue (more info)

Responding to myself...

I just noticed something else.  I realized that "attribute 19" means the
19th field defined for the fraction table, so it might be interesting to
see the dable definitions.  There should be 19 attributes, this is the
SQL used to create the table:

CREATE TABLE fraction (
sampleid         INT4 NOT NULL UNIQUE
             REFERENCES sample (sampleid),
date             DATE,
opeid            INT4 REFERENCES people(peopleid),
frac_method      INT2,
frac_protocol    INT2,
methoddesc       TEXT,
pfssampleid      INT4 REFERENCES sample (sampleid),
pfsdesc          TEXT,
rt1              FLOAT4,
rt2              FLOAT4,
frac_number      INT2,
notes            TEXT,
active           BOOLEAN NOT NULL DEFAULT 't',
status           INT2 NOT NULL,
remove           BOOLEAN DEFAULT 'f',
init             VARCHAR(20) NOT NULL,
initdate         DATE NOT NULL,
edit             VARCHAR(20) NOT NULL,
editdate         DATE NOT NULL

);

But, this is the result of a \d on fraction, sure enough, attribute 19
(editdate) is missing!

main_v0_8=# select * from fraction ;
ERROR:  cannot find attribute 19 of relation fraction
main_v0_8=# \d fraction
                        Table "fraction"
    Attribute   |         Type          |       Modifier
---------------+-----------------------+----------------------
  sampleid      | integer               | not null
  date          | date                  |
  opeid         | integer               |
  frac_method   | smallint              |
  frac_protocol | smallint              |
  methoddesc    | text                  |
  pfssampleid   | integer               |
  pfsdesc       | text                  |
  rt1           | real                  |
  rt2           | real                  |
  frac_number   | smallint              |
  notes         | text                  |
  active        | boolean               | not null default 't'
  status        | smallint              | not null
  remove        | boolean               | default 'f'
  init          | character varying(20) | not null
  initdate      | date                  | not null
  edit          | character varying(20) | not null
Index: fraction_sampleid_key



--

-**-*-*---*-*---*-*---*-----*-*-----*---*-*---*-----*-----*-*-----*---
  Jon Lapham
  Extracta Moléculas Naturais, Rio de Janeiro, Brasil
  email: lapham@extracta.com.br      web: http://www.extracta.com.br/
***-*--*----*-------*------------*--------------------*---------------

Indeed, the problem has been tracked down to hardware.  Bad RAM.  I was (with
the kind help of some list members) able to rebuild the database without any
data loss.

Basically, what I had to do was update 'pg_class->relnatts' from 19 to 18.
This single change allowed me to actually work with the fraction table, but
without the ability to access the missing 'editdate' attribute.

Then, I tried to add a new column to 'fraction' using "ALTER TABLE fraction
ADD editdate ...", this barfed saying that the "editdate" colun already exists.

So, I then ran a "ALTER TABLE fraction RENAME editdate TO editdate2". This
command suceeeded, but I still couldn't see the missing attribute in \d
fraction.  So, I re-updated pg_class and set relnatts back to 19.  Now I could
see and access the 'editdate2' attribute!  A simple rename command put
everything back together again.

Now I can sleep.


--
-**-*-*---*-*---*-*---*-----*-*-----*---*-*---*-----*-----*-*-----*---
 Jon Lapham
 Extracta Mol�culas Naturais, Rio de Janeiro, Brasil
 email: lapham@extracta.com.br      web: http://www.extracta.com.br/
***-*--*----*-------*------------*--------------------*---------------