Thread: Need help to identify stray row in a table
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
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
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
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
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