Thread: insertion with trigger failed unexpectedly
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
On Monday 12 January 2004 05:57, Anton.Nikiforov@loteco.ru wrote: > Dear all, > i have a problem with insertion data and running post insert trigger > on it. > When i'm doing tests - everything is going well, but in > production when multiple records being inserted losses happend. OK - there must be something different happening with the live situation. Better post the CREATE TABLE, trigger code and a sample INSERT. PS - is the problem: 1. INSERT fails to raw table, and daily is not updated 2. INSERT fails to raw table, but daily is updated 3. INSERT works on raw table but daily is not updated -- Richard Huxton Archonet Ltd
Dear Anton Nikiforov, >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. > I am suggesting something but may be its wrong in eithercase we would require your PostgreSQL version number etc Also Checkout the disk space if you have run out of diskspace > 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 > > This is a real problem its seems no disk space probelm in this case would you please show us the code >reach the database (trigger has logger that is saying that everything > Regards, Vishal Kashyap
Richard Huxton <dev@archonet.com> writes:
> On Monday 12 January 2004 05:57, Anton.Nikiforov@loteco.ru wrote:
>> i have a problem with insertion data and running post insert trigger
>> on it.
> Better post the CREATE TABLE, trigger code and a sample INSERT.
And the specific error messages you're getting, and the PG version
number. Also, you say it happens "twice a day" --- do you mean at
specific times of day, like noon and midnight?
regards, tom lane
Hello Tom,
Here is a function code (i changed only my real IPs, just in case) :)
They were in place of 10.0.0.0/24 networks, all the rest including
192.168.0/16 is true values.
I have checked alot of times and looks like testing and live
environment are the same, the only thing that differ that insertion
tool (that is getting plain values from stdin and inserting them into
the table) is being run from cron daemon.
All the rest is the same.
Update Failed error happening not in exacxt time, but randomly.
Currently i removed a trigger from the table definition and all
records being inserted just fine with no looses.
The additional problem is that the TRIGGER defined as AFTER INSERT
that means that the record should appear in the raw table anyway, but
it is not happened.
Best regards,
Anton Nikiforov
=============================================
CREATE TABLE raw (
ipsrc cidr NOT NULL,
ipdst cidr NOT NULL,
bytes bigint NOT NULL,
"time" timestamp without time zone DEFAULT now() NOT NULL
);
CREATE TABLE daily (
ip cidr NOT NULL,
bytesin bigint NOT NULL,
bytesout bigint NOT NULL,
local_traffic boolean DEFAULT true NOT NULL,
"time" date DEFAULT now() NOT NULL
);
CREATE FUNCTION test_func() RETURNS "trigger"
AS '
DECLARE
checked_record INTEGER :=0;
checked_ipsrc TEXT;
checked_ipdst TEXT;
checked_ip RECORD;
traffic_is_local BOOLEAN;
BEGIN
-- resetting values
traffic_is_local := ''f'';
-- logging a message about the beginning
-- INSERT INTO logtable (logtext) values (''new record begins'');
-- IF TG_OP = ''INSERT'' THEN
IF NEW.ipsrc ISNULL THEN
INSERT INTO logtable (logtext) values (''ipsrc cannot be NULL value'');
RETURN NEW;
END IF;
IF NEW.ipdst ISNULL THEN
INSERT INTO logtable (logtext) values (''ipdst cannot be NULL value'');
RETURN NEW;
END IF;
IF NEW.bytes ISNULL THEN
INSERT INTO logtable (logtext) values (''bytes cannot be NULL value'');
RETURN NEW;
END IF;
IF NEW.time ISNULL THEN
INSERT INTO logtable (logtext) values (''time cannot be NULL value'');
RETURN NEW;
END IF;
--
--
-- Checking if traffic is local
SELECT INTO checked_ipsrc NEW.ipsrc <<= ''192.168.0.0/16''::cidr OR NEW.ipsrc <<= ''10.0.0.0/24''::cidr
asexpr;
SELECT INTO checked_ipdst NEW.ipdst <<= ''192.168.0.0/16''::cidr OR NEW.ipdst <<= ''10.0.0.0/24''::cidr
asexpr;
-- Logging error in case that bouth ips are external
IF checked_ipsrc = ''f'' AND checked_ipdst = ''f'' THEN
INSERT INTO logtable (logtext) values (''ERROR::Bouth IPs are remote'');
RAISE NOTICE ''bouth ips are external'';
RETURN NEW;
ELSIF checked_ipsrc = ''t'' AND checked_ipdst = ''t'' THEN
traffic_is_local := ''t'';
END IF;
IF checked_ipdst = ''t'' THEN
SELECT INTO checked_record sum(1) FROM test_daily
WHERE ip = NEW.ipdst
AND local_traffic = traffic_is_local
AND time = date(NEW.time);
-- RAISE NOTICE ''checked_record "%"'',checked_record;
IF checked_record IS NULL THEN
INSERT INTO test_daily (ip, bytesin, bytesout, local_traffic, time)
VALUES (NEW.ipdst, NEW.bytes, 0, traffic_is_local, date(NEW.time));
ELSE
UPDATE test_daily SET bytesin = bytesin + NEW.bytes
WHERE time = date(NEW.time)
AND ip = NEW.ipdst
AND local_traffic = traffic_is_local;
END IF;
END IF;
-- Checking if traffic is outgoing
IF checked_ipsrc = ''t'' THEN
SELECT INTO checked_record sum(1) FROM test_daily
WHERE ip = NEW.ipsrc
AND local_traffic = traffic_is_local
AND time = date(NEW.time);
-- RAISE NOTICE ''checked_record = "%"'', checked_record;
IF checked_record IS NULL THEN
INSERT INTO test_daily (ip, bytesin, bytesout, local_traffic, time)
VALUES (NEW.ipsrc, 0, NEW.bytes, traffic_is_local, date(NEW.time));
ELSE
UPDATE test_daily SET bytesout = bytesout + NEW.bytes
WHERE time = date(NEW.time)
AND ip = NEW.ipsrc
AND local_traffic = traffic_is_local;
END IF;
END IF;
-- END IF;
-- INSERT INTO logtable (logtext) values (''new record ended'');
RETURN NEW;
END; '
LANGUAGE plpgsql;
CREATE TRIGGER test_trigger
AFTER INSERT OR UPDATE ON raw
FOR EACH ROW
EXECUTE PROCEDURE test_func();
TL> Richard Huxton <dev@archonet.com> writes:
>> On Monday 12 January 2004 05:57, Anton.Nikiforov@loteco.ru wrote:
>>> i have a problem with insertion data and running post insert trigger
>>> on it.
>> Better post the CREATE TABLE, trigger code and a sample INSERT.
TL> And the specific error messages you're getting, and the PG version
TL> number. Also, you say it happens "twice a day" --- do you mean at
TL> specific times of day, like noon and midnight?
TL> regards, tom lane
TL> ---------------------------(end of broadcast)---------------------------
TL> TIP 2: you can get off all lists at once with the unregister command
TL> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
On Tuesday 13 January 2004 10:36, Anton.Nikiforov@loteco.ru wrote: > Hello Tom, > Here is a function code (i changed only my real IPs, just in case) :) > They were in place of 10.0.0.0/24 networks, all the rest including > 192.168.0/16 is true values. > I have checked alot of times and looks like testing and live > environment are the same, the only thing that differ that insertion > tool (that is getting plain values from stdin and inserting them into > the table) is being run from cron daemon. > All the rest is the same. > Update Failed error happening not in exacxt time, but randomly. > Currently i removed a trigger from the table definition and all > records being inserted just fine with no looses. > The additional problem is that the TRIGGER defined as AFTER INSERT > that means that the record should appear in the raw table anyway, but > it is not happened. So - the error is that occasionally, some values aren't entered in the raw table nor summarised in the daily table? Does your live insertion code check the result-code from PG? If the insert is failing, it should say so. I'd suspect some concurrency issues, but I can't see anything obvious. Testing for this is a pain, but it produces exactly these kind of problems. Try running 5 test processes at once, all inserting simultaneously and see if that reproduces your problem. If no-one else has a bright idea, I'll try running it with some test data on my end this afternoon (London time) if I get a chance. -- Richard Huxton Archonet Ltd
RH> So - the error is that occasionally, some values aren't entered in the raw RH> table nor summarised in the daily table? Yes. Exactly. After reacing the number of records in the row above 8.000.000 (before i did not have a problems and in my tests 2million records everything was just fine) RH> Does your live insertion code check the result-code from PG? If the insert is RH> failing, it should say so. It is saying. That couple UPDATE/Insert failures are coming from insertion tool diagnostics. RH> I'd suspect some concurrency issues, but I can't see anything obvious. Testing RH> for this is a pain, but it produces exactly these kind of problems. Try RH> running 5 test processes at once, all inserting simultaneously and see if RH> that reproduces your problem. The matter is that there is no cuncurrency in real life. Insertion tool trying to insert next value after getting return code from PG that the previous one is done. Statistics being downloaded from the routers each 5 minutes and the procedure to upload it into PG takes no more than 2 minutes, so cuncurrency is impossible. I do not know exactly, but maybe PG returning from insert before trigger is done? I was also testing inserts without a trigger. With my insertion tool everything was fine, but with inserting from text file via psql utility there were looses if the number of INSERTs was largger than 100 items. For information the same happened on my 7.3.2, then 7.3.4 and now 7.4. Best regards, Anton RH> If no-one else has a bright idea, I'll try running it with some test data on RH> my end this afternoon (London time) if I get a chance. Ñ óâàæåíèåì, IT Äèðåêòîð ÎÎÎ "Ëîòýêî" Àíòîí Íèêèôîðîâ Òåë.: +7 095 7814200 Ôàêñ: +7 095 7814201 Mail: Anton.Nikiforov@loteco.ru Web: www.loteco.ru
On Tuesday 13 January 2004 11:48, Anton.Nikiforov@loteco.ru wrote: > RH> Does your live insertion code check the result-code from PG? If the > insert is RH> failing, it should say so. > It is saying. That couple UPDATE/Insert failures are coming from > insertion tool diagnostics. And the error message is? > I was also testing inserts without a trigger. With my insertion tool > everything was fine, but with inserting from text file via psql > utility there were looses if the number of INSERTs was largger than > 100 items. > For information the same happened on my 7.3.2, then 7.3.4 and now 7.4. There must be an error message, surely? -- Richard Huxton Archonet Ltd
RH> And the error message is? UPDATE Failed... that is it.... I'll try to rewrite my logger to get more diagnostics... As soon as i'll have some statistics - i'll post it here But i still cannot understand why it was working fine with a fresh database (initdb just done) for 1.5 years and when the size become huge it starts to loose data. I was not modifying tables (only now() when were migrating to 7.4) nor trigger. Everything is the same as a year ago. And when i cleaned some space by removing previous statistic and vacuuming db - it did not help. But now, when a dropped a trigger - raw table started to fill normaly. >> I was also testing inserts without a trigger. With my insertion tool >> everything was fine, but with inserting from text file via psql >> utility there were looses if the number of INSERTs was largger than >> 100 items. >> For information the same happened on my 7.3.2, then 7.3.4 and now 7.4. RH> There must be an error message, surely? No message. I know it is strange, but belive me, i'm woorking with postgresql since 6.5 or something like this and this traffic database coming from that time. Maybe i do not know something where to get additional error codes, but looks like i read documentation 20 times at least. Regards, Anton
On Tue, 2004-01-13 at 08:05, Anton.Nikiforov@loteco.ru wrote:
> RH> And the error message is?
> UPDATE Failed... that is it....
> I'll try to rewrite my logger to get more diagnostics...
> As soon as i'll have some statistics - i'll post it here
I can seem to gleen if you have cranked up your postgresql.conf logging
yet... set the error log level to at least warning, and set it to log
the statement at that level as well, then check your postgresql logs and
see what it says.
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
Anton.Nikiforov@loteco.ru writes:
> RH> There must be an error message, surely?
> No message.
You mean your code is failing to report the message. Try looking in the
postmaster log.
regards, tom lane