Thread: Need help to identify stray row in a table

Need help to identify stray row in a table

From
சிவகுமார் மா
Date:
1. We have a production system tracking value added to a batch through
series of stages. Value table is updated through triggers on  data
tables.

2. These trigger functions have been tested and validated for over 1.5
years with more than 100,000 records.

3. We found a difference in the calculation while verifying March 2010
records. Rechecked functions and data. Identified the source of
difference as a row in value table which could not be explained.

This table is filled by a trigger function, not touched by application code.

4. Tried looking at oid of the rows.

select oid, * from transaction_value where transaction_id in (633509,
633507, 633505) and cost_type_id=1;
    oid    | transaction_id | source_id | cost_type_id | section_id |    value
-----------+----------------+-----------+--------------+------------+-------------
    570938 |         633505 |           |            1 |            |
614078.0250
 292333023 |         633509 |    629483 |            1 |            |
12284.9411
 292332829 |         633505 |    629483 |            1 |            |
115701.8092
 292332944 |         633507 |    629483 |            1 |            |
85101.1377

Three rows starting with 292333--- are expected ones. The one with
oid=570938 is the unexplained one.

Does this indicate any thing? Or should we look elsewhere?

5.  Running PostgreSQL 8.3.5 on i686-pc-linux-gnu, compiled by GCC gcc
(GCC) 4.2.1 (SUSE Linux)
Have not done upgrades for quite some time. Will do so over the week end.

Thanks for any help.

Best regards,

Ma Sivakumar

மா சிவகுமார்
எல்லோரும் எல்லாமும் பெற வேண்டும்
http://masivakumar.blogspot.com

Re: Need help to identify stray row in a table

From
Merlin Moncure
Date:
2010/4/23 சிவகுமார் மா <masivakumar@gmail.com>:
> 1. We have a production system tracking value added to a batch through
> series of stages. Value table is updated through triggers on  data
> tables.
>
> 2. These trigger functions have been tested and validated for over 1.5
> years with more than 100,000 records.
>
> 3. We found a difference in the calculation while verifying March 2010
> records. Rechecked functions and data. Identified the source of
> difference as a row in value table which could not be explained.
>
> This table is filled by a trigger function, not touched by application code.
>
> 4. Tried looking at oid of the rows.
>
> select oid, * from transaction_value where transaction_id in (633509,
> 633507, 633505) and cost_type_id=1;
>    oid    | transaction_id | source_id | cost_type_id | section_id |    value
> -----------+----------------+-----------+--------------+------------+-------------
>    570938 |         633505 |           |            1 |            |
> 614078.0250
>  292333023 |         633509 |    629483 |            1 |            |
> 12284.9411
>  292332829 |         633505 |    629483 |            1 |            |
> 115701.8092
>  292332944 |         633507 |    629483 |            1 |            |
> 85101.1377
>
> Three rows starting with 292333--- are expected ones. The one with
> oid=570938 is the unexplained one.
>
> Does this indicate any thing? Or should we look elsewhere?

You haven't given enough information to make any sort of reasonable
diagnosis.  Most people are going to assume the problem is on your end
but it's possible to know for sure without having the trigger function
at the very least.

merlin

Re: Need help to identify stray row in a table

From
சிவகுமார் மா
Date:
2010/4/23 Merlin Moncure <mmoncure@gmail.com>:
>
> You haven't given enough information to make any sort of reasonable
> diagnosis.  Most people are going to assume the problem is on your end
> but it's possible to know for sure without having the trigger function
> at the very least.
>

Thanks merlin for the reply. There are two functions,

1. for inserts on stock transaction table, calculating value and
inserting in transaction_value table.

2. the other is on transaction_value table itself, to update values of
child transactions of row being inserted/updated/deleted.

The second function is more than 200 lines.  I have attached a text
file containing trigger and function code.

Thanks for any insights you can provide.

Best regards,

Ma Sivakumar

மா சிவகுமார்
எல்லோரும் எல்லாமும் பெற வேண்டும்
http://masivakumar.blogspot.com


> merlin
>

Attachment

Re: Need help to identify stray row in a table

From
Merlin Moncure
Date:
2010/4/23 சிவகுமார் மா <masivakumar@gmail.com>:
> 2010/4/23 Merlin Moncure <mmoncure@gmail.com>:
>>
>> You haven't given enough information to make any sort of reasonable
>> diagnosis.  Most people are going to assume the problem is on your end
>> but it's possible to know for sure without having the trigger function
>> at the very least.
>>
>
> Thanks merlin for the reply. There are two functions,
>
> 1. for inserts on stock transaction table, calculating value and
> inserting in transaction_value table.
>
> 2. the other is on transaction_value table itself, to update values of
> child transactions of row being inserted/updated/deleted.
>
> The second function is more than 200 lines.  I have attached a text
> file containing trigger and function code.
>
> Thanks for any insights you can provide.

There's way too much logic going on there for me to test all the
different cases.

I suspect this is your problem: you triggered a case somehow which is
not handled properly via your labyrinth of switches and loops.  I
highly doubt this is a case of database corruption.  My advice here
would be to not rely on procedural code to guard against something
which can and should be enforced by a constraint.   If something is
wrong (source_id being null), declare it to be wrong -- that way the
next time this happens the constraint will bounce the transaction and
you can catch the problem when it happens as opposed to reverse
engineering it.

merlin

Re: Need help to identify stray row in a table

From
சிவகுமார் மா
Date:
2010/4/23 Merlin Moncure <mmoncure@gmail.com>:
>
> There's way too much logic going on there for me to test all the
> different cases.
>
> I suspect this is your problem: you triggered a case somehow which is
> not handled properly via your labyrinth of switches and loops.  I
> highly doubt this is a case of database corruption.  My advice here
> would be to not rely on procedural code to guard against something
> which can and should be enforced by a constraint.   If something is
> wrong (source_id being null), declare it to be wrong -- that way the
> next time this happens the constraint will bounce the transaction and
> you can catch the problem when it happens as opposed to reverse
> engineering it.
>
Thanks for the suggestion and help. Will work on changing the function
and constraints for enforcing data accuracy.

Thanks and regards,

Ma Sivakumar

மா சிவகுமார்
எல்லோரும் எல்லாமும் பெற வேண்டும்
http://masivakumar.blogspot.com