By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,780 Members | 1,549 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,780 IT Pros & Developers. It's quick & easy.

insertion with trigger failed unexpectedly

P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #1
Share this Question
Share on Google+
10 Replies


P: n/a
On Monday 12 January 2004 05:57, An*************@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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 12 '05 #2

P: n/a
Dear Anton Nikiforov,
The problem:
From the very beginning everything was fine and all records that iwas 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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #3

P: n/a
Richard Huxton <de*@archonet.com> writes:
On Monday 12 January 2004 05:57, An*************@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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 12 '05 #4

P: n/a
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 as expr;
SELECT INTO checked_ipdst NEW.ipdst <<= ''192.168.0.0/16''::cidr OR NEW.ipdst <<= ''10.0.0.0/24''::cidr as expr;
-- 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 <de*@archonet.com> writes:
On Monday 12 January 2004 05:57, An*************@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 ma*******@postgresql.org)
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 22 '05 #5

P: n/a
On Tuesday 13 January 2004 10:36, An*************@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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 22 '05 #6

P: n/a
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: An*************@loteco.ru
Web: www.loteco.ru
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 22 '05 #7

P: n/a
On Tuesday 13 January 2004 11:48, An*************@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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 22 '05 #8

P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 22 '05 #9

P: n/a
On Tue, 2004-01-13 at 08:05, An*************@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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 22 '05 #10

P: n/a
An*************@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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 22 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.