Re: insertion with trigger failed unexpectedly

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 <dev(at)archonet(dot)com> writes:
>> On Monday 12 January 2004 05:57, Anton(dot)Nikiforov(at)loteco(dot)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(at)postgresql(dot)org)