Re: insertion with trigger failed unexpectedly - Mailing list pgsql-general

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)