I am running PostgreSQL 7.3.3 on OS X Server 10.2

The database has been running just fine for quite some time now, but 
this morning it began pitching the error:
	ERROR:  cannot read block 176 of tfxtrade_details: Numerical result 
out of range
any time the table tfxtrade_details is accessed.

A description of the table is at the end of this email

I have a backup from last night, so I haven't lost much data (if any), 
but I am curious if there is a way to recover from this (beyond 
restoring from backup) and how I would go about figuring out what 
caused it to prevent it from happening again.

I will keep a copy of the data directory if anyone wants me to do any 
analysis on it (I will need instructions).

Any insights would be appreciated.

Thanks

Jason Essington
jaessing@greenrivercomputing.com


hedgehog=# \d tfxtrade_details
            Table "public.tfxtrade_details"
     Column     |           Type           | Modifiers
---------------+--------------------------+-----------
  rid           | integer                  | not null
  clientid      | integer                  |
  tradeid       | integer                  |
  rollid        | integer                  |
  rollpct       | numeric(10,8)            |
  expdetailid   | integer                  |
  expid         | integer                  |
  contractpct   | numeric(10,8)            |
  contractamt   | numeric(18,2)            |
  origpct       | numeric(10,8)            |
  origamt       | numeric(18,2)            |
  acctgperiod   | integer                  |
  acctgperiodid | integer                  |
  editdate      | timestamp with time zone |
  edituserid    | character varying(48)    |
  parentid      | integer                  |
  entityid      | integer                  |
  tradedate     | date                     |
  maturitydate  | date                     |
  strategyid    | integer                  |
  currencyid    | integer                  |
Indexes: tfxtrade_details_pkey primary key btree (rid),
          tfxlinks_entityid_index btree (entityid),
          tfxlinks_expdetailid_index btree (expdetailid),
          tfxlinks_expid_index btree (expid),
          tfxlinks_mdate_index btree (maturitydate),
          tfxlinks_parentid_index btree (parentid),
          tfxlinks_strategy_index btree (strategyid),
          tfxlinks_tradeid_index btree (tradeid)
Triggers: RI_ConstraintTrigger_30891,
           RI_ConstraintTrigger_30894,
           tfxdetail_delete_trigger


Sat, 14 Feb 2004 12:04:38 -0700
Jason Essington <jasone@GreenRiverComputing.com>
Cannot read block error.
Hello,

When was the last time you ran a reindex? Or a vacuum / vacuum full?

Sincerely,

Joshua D. Drake

On Sat, 14 Feb 2004, Jason Essington wrote:

> I am running PostgreSQL 7.3.3 on OS X Server 10.2
> 
> The database has been running just fine for quite some time now, but 
> this morning it began pitching the error:
> 	ERROR:  cannot read block 176 of tfxtrade_details: Numerical result 
> out of range
> any time the table tfxtrade_details is accessed.
> 
> A description of the table is at the end of this email
> 
> I have a backup from last night, so I haven't lost much data (if any), 
> but I am curious if there is a way to recover from this (beyond 
> restoring from backup) and how I would go about figuring out what 
> caused it to prevent it from happening again.
> 
> I will keep a copy of the data directory if anyone wants me to do any 
> analysis on it (I will need instructions).
> 
> Any insights would be appreciated.
> 
> Thanks
> 
> Jason Essington
> jaessing@greenrivercomputing.com
> 
> 
> hedgehog=# \d tfxtrade_details
>             Table "public.tfxtrade_details"
>      Column     |           Type           | Modifiers
> ---------------+--------------------------+-----------
>   rid           | integer                  | not null
>   clientid      | integer                  |
>   tradeid       | integer                  |
>   rollid        | integer                  |
>   rollpct       | numeric(10,8)            |
>   expdetailid   | integer                  |
>   expid         | integer                  |
>   contractpct   | numeric(10,8)            |
>   contractamt   | numeric(18,2)            |
>   origpct       | numeric(10,8)            |
>   origamt       | numeric(18,2)            |
>   acctgperiod   | integer                  |
>   acctgperiodid | integer                  |
>   editdate      | timestamp with time zone |
>   edituserid    | character varying(48)    |
>   parentid      | integer                  |
>   entityid      | integer                  |
>   tradedate     | date                     |
>   maturitydate  | date                     |
>   strategyid    | integer                  |
>   currencyid    | integer                  |
> Indexes: tfxtrade_details_pkey primary key btree (rid),
>           tfxlinks_entityid_index btree (entityid),
>           tfxlinks_expdetailid_index btree (expdetailid),
>           tfxlinks_expid_index btree (expid),
>           tfxlinks_mdate_index btree (maturitydate),
>           tfxlinks_parentid_index btree (parentid),
>           tfxlinks_strategy_index btree (strategyid),
>           tfxlinks_tradeid_index btree (tradeid)
> Triggers: RI_ConstraintTrigger_30891,
>            RI_ConstraintTrigger_30894,
>            tfxdetail_delete_trigger
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faqs/FAQ.html
> 

-- 
Co-Founder
Command Prompt, Inc.
The wheel's spinning but the hamster's dead


Sat, 14 Feb 2004 13:29:18 -0800 (PST)
"Joshua D. Drake" <jd@commandprompt.com>
Re: Cannot read block error.
Both vacuum [full] and reindex fail with that same error.

vacuum is run regularly via a cron job.

-jason
On Feb 14, 2004, at 2:29 PM, Joshua D. Drake wrote:

> Hello,
>
> When was the last time you ran a reindex? Or a vacuum / vacuum full?
>
> Sincerely,
>
> Joshua D. Drake
>
> On Sat, 14 Feb 2004, Jason Essington wrote:
>
>> I am running PostgreSQL 7.3.3 on OS X Server 10.2
>>
>> The database has been running just fine for quite some time now, but
>> this morning it began pitching the error:
>> 	ERROR:  cannot read block 176 of tfxtrade_details: Numerical result
>> out of range
>> any time the table tfxtrade_details is accessed.
>>
>> A description of the table is at the end of this email
>>
>> I have a backup from last night, so I haven't lost much data (if any),
>> but I am curious if there is a way to recover from this (beyond
>> restoring from backup) and how I would go about figuring out what
>> caused it to prevent it from happening again.
>>
>> I will keep a copy of the data directory if anyone wants me to do any
>> analysis on it (I will need instructions).
>>
>> Any insights would be appreciated.
>>
>> Thanks
>>
>> Jason Essington
>> jaessing@greenrivercomputing.com
>>
>>
>> hedgehog=# \d tfxtrade_details
>>             Table "public.tfxtrade_details"
>>      Column     |           Type           | Modifiers
>> ---------------+--------------------------+-----------
>>   rid           | integer                  | not null
>>   clientid      | integer                  |
>>   tradeid       | integer                  |
>>   rollid        | integer                  |
>>   rollpct       | numeric(10,8)            |
>>   expdetailid   | integer                  |
>>   expid         | integer                  |
>>   contractpct   | numeric(10,8)            |
>>   contractamt   | numeric(18,2)            |
>>   origpct       | numeric(10,8)            |
>>   origamt       | numeric(18,2)            |
>>   acctgperiod   | integer                  |
>>   acctgperiodid | integer                  |
>>   editdate      | timestamp with time zone |
>>   edituserid    | character varying(48)    |
>>   parentid      | integer                  |
>>   entityid      | integer                  |
>>   tradedate     | date                     |
>>   maturitydate  | date                     |
>>   strategyid    | integer                  |
>>   currencyid    | integer                  |
>> Indexes: tfxtrade_details_pkey primary key btree (rid),
>>           tfxlinks_entityid_index btree (entityid),
>>           tfxlinks_expdetailid_index btree (expdetailid),
>>           tfxlinks_expid_index btree (expid),
>>           tfxlinks_mdate_index btree (maturitydate),
>>           tfxlinks_parentid_index btree (parentid),
>>           tfxlinks_strategy_index btree (strategyid),
>>           tfxlinks_tradeid_index btree (tradeid)
>> Triggers: RI_ConstraintTrigger_30891,
>>            RI_ConstraintTrigger_30894,
>>            tfxdetail_delete_trigger
>>
>>
>> ---------------------------(end of 
>> broadcast)---------------------------
>> TIP 5: Have you checked our extensive FAQ?
>>
>>                http://www.postgresql.org/docs/faqs/FAQ.html
>>
>
> -- 
> Co-Founder
> Command Prompt, Inc.
> The wheel's spinning but the hamster's dead
>


Sat, 14 Feb 2004 15:22:01 -0700
Jason Essington <jasone@GreenRiverComputing.com>
Re: Cannot read block error.
Hello,

There are a couple of things it could be. I would suggest that you take 
down the database, start it up with -P? (I think it is -o '-P' it might 
be -p '-O' I don't recall) and try and reindex the database itself.

You can also do a vacuuum verbose and see if you get some more errors you 
may have a corrupt system index that needs to be reindexed.

Sincerely,

Johsua D. Drake
 

On Sat, 14 Feb 2004, Jason Essington wrote:

> Both vacuum [full] and reindex fail with that same error.
> 
> vacuum is run regularly via a cron job.
> 
> -jason
> On Feb 14, 2004, at 2:29 PM, Joshua D. Drake wrote:
> 
> > Hello,
> >
> > When was the last time you ran a reindex? Or a vacuum / vacuum full?
> >
> > Sincerely,
> >
> > Joshua D. Drake
> >
> > On Sat, 14 Feb 2004, Jason Essington wrote:
> >
> >> I am running PostgreSQL 7.3.3 on OS X Server 10.2
> >>
> >> The database has been running just fine for quite some time now, but
> >> this morning it began pitching the error:
> >> 	ERROR:  cannot read block 176 of tfxtrade_details: Numerical result
> >> out of range
> >> any time the table tfxtrade_details is accessed.
> >>
> >> A description of the table is at the end of this email
> >>
> >> I have a backup from last night, so I haven't lost much data (if any),
> >> but I am curious if there is a way to recover from this (beyond
> >> restoring from backup) and how I would go about figuring out what
> >> caused it to prevent it from happening again.
> >>
> >> I will keep a copy of the data directory if anyone wants me to do any
> >> analysis on it (I will need instructions).
> >>
> >> Any insights would be appreciated.
> >>
> >> Thanks
> >>
> >> Jason Essington
> >> jaessing@greenrivercomputing.com
> >>
> >>
> >> hedgehog=# \d tfxtrade_details
> >>             Table "public.tfxtrade_details"
> >>      Column     |           Type           | Modifiers
> >> ---------------+--------------------------+-----------
> >>   rid           | integer                  | not null
> >>   clientid      | integer                  |
> >>   tradeid       | integer                  |
> >>   rollid        | integer                  |
> >>   rollpct       | numeric(10,8)            |
> >>   expdetailid   | integer                  |
> >>   expid         | integer                  |
> >>   contractpct   | numeric(10,8)            |
> >>   contractamt   | numeric(18,2)            |
> >>   origpct       | numeric(10,8)            |
> >>   origamt       | numeric(18,2)            |
> >>   acctgperiod   | integer                  |
> >>   acctgperiodid | integer                  |
> >>   editdate      | timestamp with time zone |
> >>   edituserid    | character varying(48)    |
> >>   parentid      | integer                  |
> >>   entityid      | integer                  |
> >>   tradedate     | date                     |
> >>   maturitydate  | date                     |
> >>   strategyid    | integer                  |
> >>   currencyid    | integer                  |
> >> Indexes: tfxtrade_details_pkey primary key btree (rid),
> >>           tfxlinks_entityid_index btree (entityid),
> >>           tfxlinks_expdetailid_index btree (expdetailid),
> >>           tfxlinks_expid_index btree (expid),
> >>           tfxlinks_mdate_index btree (maturitydate),
> >>           tfxlinks_parentid_index btree (parentid),
> >>           tfxlinks_strategy_index btree (strategyid),
> >>           tfxlinks_tradeid_index btree (tradeid)
> >> Triggers: RI_ConstraintTrigger_30891,
> >>            RI_ConstraintTrigger_30894,
> >>            tfxdetail_delete_trigger
> >>
> >>
> >> ---------------------------(end of 
> >> broadcast)---------------------------
> >> TIP 5: Have you checked our extensive FAQ?
> >>
> >>                http://www.postgresql.org/docs/faqs/FAQ.html
> >>
> >
> > -- 
> > Co-Founder
> > Command Prompt, Inc.
> > The wheel's spinning but the hamster's dead
> >
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 

-- 
Co-Founder
Command Prompt, Inc.
The wheel's spinning but the hamster's dead


Sat, 14 Feb 2004 16:01:09 -0800 (PST)
"Joshua D. Drake" <jd@commandprompt.com>
Re: Cannot read block error.
Starting in single user mode and reindexing the database didn't fix the 
error, although it seemed to run just fine.

Vacuum verbose ran until it hit the tfxtrade_details table and then it 
died with that same error. it didn't whine about any other problems 
prior to dying.

INFO:  --Relation public.tfxtrade_details--
ERROR:  cannot read block 176 of tfxtrade_details: Numerical result out 
of range

Guess there is just something really munged in this one. I'll just try 
to restore it from the backup.

Interesting, when I went to copy my data directory out of the way, I 
received this from cp:

cp: data/base/16976/17840: Result too large

might be a clue

-jason

On Feb 14, 2004, at 5:01 PM, Joshua D. Drake wrote:

> Hello,
>
> There are a couple of things it could be. I would suggest that you take
> down the database, start it up with -P? (I think it is -o '-P' it might
> be -p '-O' I don't recall) and try and reindex the database itself.
>
> You can also do a vacuuum verbose and see if you get some more errors 
> you
> may have a corrupt system index that needs to be reindexed.
>
> Sincerely,
>
> Johsua D. Drake
>
>
> On Sat, 14 Feb 2004, Jason Essington wrote:
>
>> Both vacuum [full] and reindex fail with that same error.
>>
>> vacuum is run regularly via a cron job.
>>
>> -jason
>> On Feb 14, 2004, at 2:29 PM, Joshua D. Drake wrote:
>>
>>> Hello,
>>>
>>> When was the last time you ran a reindex? Or a vacuum / vacuum full?
>>>
>>> Sincerely,
>>>
>>> Joshua D. Drake
>>>
>>> On Sat, 14 Feb 2004, Jason Essington wrote:
>>>
>>>> I am running PostgreSQL 7.3.3 on OS X Server 10.2
>>>>
>>>> The database has been running just fine for quite some time now, but
>>>> this morning it began pitching the error:
>>>> 	ERROR:  cannot read block 176 of tfxtrade_details: Numerical result
>>>> out of range
>>>> any time the table tfxtrade_details is accessed.
>>>>
>>>> A description of the table is at the end of this email
>>>>
>>>> I have a backup from last night, so I haven't lost much data (if 
>>>> any),
>>>> but I am curious if there is a way to recover from this (beyond
>>>> restoring from backup) and how I would go about figuring out what
>>>> caused it to prevent it from happening again.
>>>>
>>>> I will keep a copy of the data directory if anyone wants me to do 
>>>> any
>>>> analysis on it (I will need instructions).
>>>>
>>>> Any insights would be appreciated.
>>>>
>>>> Thanks
>>>>
>>>> Jason Essington
>>>> jaessing@greenrivercomputing.com
>>>>
>>>>
>>>> hedgehog=# \d tfxtrade_details
>>>>             Table "public.tfxtrade_details"
>>>>      Column     |           Type           | Modifiers
>>>> ---------------+--------------------------+-----------
>>>>   rid           | integer                  | not null
>>>>   clientid      | integer                  |
>>>>   tradeid       | integer                  |
>>>>   rollid        | integer                  |
>>>>   rollpct       | numeric(10,8)            |
>>>>   expdetailid   | integer                  |
>>>>   expid         | integer                  |
>>>>   contractpct   | numeric(10,8)            |
>>>>   contractamt   | numeric(18,2)            |
>>>>   origpct       | numeric(10,8)            |
>>>>   origamt       | numeric(18,2)            |
>>>>   acctgperiod   | integer                  |
>>>>   acctgperiodid | integer                  |
>>>>   editdate      | timestamp with time zone |
>>>>   edituserid    | character varying(48)    |
>>>>   parentid      | integer                  |
>>>>   entityid      | integer                  |
>>>>   tradedate     | date                     |
>>>>   maturitydate  | date                     |
>>>>   strategyid    | integer                  |
>>>>   currencyid    | integer                  |
>>>> Indexes: tfxtrade_details_pkey primary key btree (rid),
>>>>           tfxlinks_entityid_index btree (entityid),
>>>>           tfxlinks_expdetailid_index btree (expdetailid),
>>>>           tfxlinks_expid_index btree (expid),
>>>>           tfxlinks_mdate_index btree (maturitydate),
>>>>           tfxlinks_parentid_index btree (parentid),
>>>>           tfxlinks_strategy_index btree (strategyid),
>>>>           tfxlinks_tradeid_index btree (tradeid)
>>>> Triggers: RI_ConstraintTrigger_30891,
>>>>            RI_ConstraintTrigger_30894,
>>>>            tfxdetail_delete_trigger
>>>>
>>>>
>>>> ---------------------------(end of
>>>> broadcast)---------------------------
>>>> TIP 5: Have you checked our extensive FAQ?
>>>>
>>>>                http://www.postgresql.org/docs/faqs/FAQ.html
>>>>
>>>
>>> -- 
>>> Co-Founder
>>> Command Prompt, Inc.
>>> The wheel's spinning but the hamster's dead
>>>
>>
>>
>> ---------------------------(end of 
>> broadcast)---------------------------
>> TIP 4: Don't 'kill -9' the postmaster
>>
>
> -- 
> Co-Founder
> Command Prompt, Inc.
> The wheel's spinning but the hamster's dead
>


Sat, 14 Feb 2004 18:04:21 -0700
Jason Essington <jasone@GreenRiverComputing.com>
Re: Cannot read block error.