insertion with trigger failed unexpectedly

From: Anton(dot)Nikiforov(at)loteco(dot)ru
To: pgsql-general(at)postgresql(dot)org
Subject: insertion with trigger failed unexpectedly
Date: 2004-01-12 05:57:25
Message-ID: 1047118776.20040112085725@loteco.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Dear all,
i have a problem with insertion data and running post insert trigger
on it.
Preambula:
there is a table named raw:
ipsrc | cidr
ipdst | cidr
bytes | bigint
time | timestamp
Triggers:
daily_trigger AFTER INSERT OR UPDATE ON raw FOR EACH ROW EXECUTE PROCEDURE daily_func()
and the table daily:
ip | cidr
bytesin | bigint
bytesout | bigint
local_traffic | boolean
time | date

The matter of this code is to store raw traffic counters that i'm
getting from my cisco or FreeBSD routers and calculate daily traffic
in daily table dividing it into internal and external by internal ip.

The problem:
>From the very beginning everything was fine and all records that i
was getting from routers were calculated just right. I spent a weeks
monitoring and testing my software.
Now i have 10.000.000 records in raw table and when i'm inserting data
alot of records are missing in raw and daily. Sometimes i got UPDATE
failed errors, sometimes INSERT failed, but in general i'm getting
this messages twice a day but not only two records are missing -
hundreds of them. Currently i have no idea where to go and what to
check. I did my tests mainly on FreeBSD platform and now i did tests
on RedHat Linux and the result is the same - some records just did not
reach the database (trigger has logger that is saying that everything
was inserted, but it is not true, the tool that inerting records has
logger too). When i'm doing tests - everything is going well, but in
production when multiple records being inserted losses happend.
The insertion tool is very simple C program that (looks like) have no
place to store bugs :) The trigger is very simple too. If you need the
code - i could post it here, but i do not think that it will be
useful.
Could you give me an idea where to go and what to check in this case?

Regards,
Anton

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Merrall, Graeme 2004-01-12 06:23:34 Re: Hierarchical queries
Previous Message ezra epstein 2004-01-12 05:18:58 Precedence of a TRIGGER vs. a CHECK on a column