Re: NEW in after insert trugger contained incorrect data - Mailing list pgsql-general

Albe Laurenz wrote on 11/14/2014 01:28 PM:
>
> You should post the table definition and the whole trigger; the error
> message seems to refer to things you omitted in your quote.
>
> Yours,
> Laurenz Albe
>

Table with original data trassa.cpu_load:
CREATE TABLE trassa.cpu_load
(
   id serial NOT NULL,
   device integer NOT NULL,
   created timestamp without time zone NOT NULL DEFAULT now(),
   device_timestamp timestamp without time zone NOT NULL,
   cpu smallint NOT NULL,
   value smallint NOT NULL,
   CONSTRAINT cpu_load_pk PRIMARY KEY (id),
   CONSTRAINT cpu_load_device FOREIGN KEY (device)
       REFERENCES trassa.devices (id) MATCH SIMPLE
       ON UPDATE NO ACTION ON DELETE NO ACTION,
   CONSTRAINT cpu_load_val CHECK (value >= 0 AND value <= 100)
)
WITH (
   OIDS=FALSE
);

Function for save values in table trassa.cpu_Load:
CREATE OR REPLACE FUNCTION trassa.update_cpu_load_list(device_id
integer, device_timestamp integer, device_cpu smallint[],
device_cpu_load smallint[])
   RETURNS boolean AS
$BODY$
DECLARE
val_len SMALLINT DEFAULT array_length($3, 1);
cmd TEXT DEFAULT 'INSERT INTO trassa.cpu_load (device, device_timestamp,
cpu, value) VALUES';
result SMALLINT;
ts TIMESTAMP DEFAULT to_timestamp($2);
BEGIN
    IF val_len = array_length($4, 1) THEN
        FOR i IN 1..val_len LOOP
            cmd = cmd || '(' ||
                $1::text ||
                ',''' || ts::text || ''',' ||
                $3[i]::text || ',' ||
                $4[i]::text || ')';
            IF i != val_len THEN
                cmd = cmd || ',';
            END IF;
        END LOOP;
        EXECUTE cmd;
        GET DIAGNOSTICS result = ROW_COUNT;
        IF result = val_len THEN
            RETURN TRUE;
        ELSE
            RETURN FALSE;
        END IF;
    ELSE
        RETURN FALSE;
    END IF;
END;$BODY$
   LANGUAGE plpgsql VOLATILE SECURITY DEFINER
   COST 100;

Table for save statistic trassa.cpu_load_stat:
CREATE TABLE trassa.cpu_load_stat
(
   id serial NOT NULL,
   device integer NOT NULL,
   cpu smallint NOT NULL,
   min_value smallint NOT NULL,
   min_device_timestamp timestamp without time zone NOT NULL,
   min_timestamp timestamp without time zone,
   avg_value smallint NOT NULL,
   avg_timestamp timestamp without time zone NOT NULL,
   max_value smallint NOT NULL,
   max_device_timestamp timestamp without time zone NOT NULL,
   max_timestamp timestamp without time zone,
   total_value bigint NOT NULL,
   total_count integer NOT NULL,
   CONSTRAINT cpu_load_stat_pk PRIMARY KEY (id),
   CONSTRAINT cpu_load_stat_device_fk FOREIGN KEY (device)
       REFERENCES trassa.devices (id) MATCH SIMPLE
       ON UPDATE NO ACTION ON DELETE NO ACTION,
   CONSTRAINT cpu_load_stat_avg_value_check CHECK (avg_value >= 0 AND
avg_value <= 100),
   CONSTRAINT cpu_load_stat_max_value_check CHECK (max_value >= 0 AND
max_value <= 100),
   CONSTRAINT cpu_load_stat_min_value_check CHECK (min_value >= 0 AND
min_value <= 100)
)
WITH (
   OIDS=FALSE
);

Trigger for update trassa.cpu_load_stat, values from trassa.cpu_Load:
CREATE OR REPLACE FUNCTION trassa.update_cpu_load_stat()
   RETURNS trigger AS
$BODY$
DECLARE
line_id INTEGER DEFAULT 0;
cpu_min_value SMALLINT DEFAULT 0;
cpu_min_created_timestamp TIMESTAMP;
cpu_min_device_timestamp TIMESTAMP;
cpu_max_value SMALLINT DEFAULT 0;
cpu_max_created_timestamp TIMESTAMP;
cpu_max_device_timestamp TIMESTAMP;
BEGIN
    SELECT id INTO line_id FROM trassa.cpu_load_stat
    WHERE device = NEW.device AND cpu = NEW.cpu;
    RAISE NOTICE USING MESSAGE = '*** START ***: ' || NEW;
    IF FOUND THEN
        RAISE NOTICE USING MESSAGE = '*** UPDATE ***: ID ' || line_id || ',
data ' || NEW;
        SELECT created, device_timestamp, value
        INTO cpu_min_created_timestamp, cpu_min_device_timestamp,
            cpu_min_value
        FROM trassa.cpu_load
        WHERE trassa.cpu_load.device = NEW.device
            AND trassa.cpu_load.cpu = NEW.cpu
        ORDER BY value, created
        LIMIT 1;

        SELECT created, device_timestamp, value
        INTO cpu_max_created_timestamp, cpu_max_device_timestamp,
            cpu_max_value
        FROM trassa.cpu_load
        WHERE trassa.cpu_load.device = NEW.device
            AND trassa.cpu_load.cpu = NEW.cpu
        ORDER BY value DESC, created
        LIMIT 1;

        UPDATE trassa.cpu_load_stat
        SET min_value = cpu_min_value,
            min_device_timestamp = cpu_min_device_timestamp,
            min_timestamp = cpu_min_created_timestamp,
            avg_value = CEIL((total_value + NEW.value) /
                    (total_count + 1)),
            avg_timestamp = NOW(),
            max_value = cpu_max_value,
            max_device_timestamp = cpu_max_device_timestamp,
            max_timestamp = cpu_max_created_timestamp,
            total_value = (total_value + NEW.value),
            total_count = (total_count + 1)
        WHERE id = line_id;
        RAISE NOTICE '*** END UPDATE ***';
    ELSE
        RAISE NOTICE USING MESSAGE = '*** INSERT ***: ' || NEW;
        INSERT INTO trassa.cpu_load_stat
            (device, cpu,
            min_value, min_device_timestamp, min_timestamp,
            avg_value, avg_timestamp,
            max_value, max_device_timestamp, max_timestamp,
            total_value, total_count)
        VALUES (NEW.device, NEW.cpu,
            NEW.value, NEW.device_timestamp, NOW(),
            NEW.value, NOW(),
            NEW.value, NEW.device_timestamp, NOW(),
            NEW.value, 1);
        RAISE NOTICE '*** END INSERT ***';
    END IF;
    RAISE NOTICE USING MESSAGE = '*** END ***: ' || TG_NAME;
    RETURN NULL;
END;
$BODY$
   LANGUAGE plpgsql VOLATILE SECURITY DEFINER
   COST 100;

Trigger update_cpu_load_stat added to table trassa.cpu_load:
CREATE TRIGGER update_cpu_load_stat_trigger
   AFTER INSERT
   ON trassa.cpu_load_stat
   FOR EACH ROW
   EXECUTE PROCEDURE trassa.update_cpu_load_stat();

Thank you and excuse my big message.

--
Best regards,
Brilliantov Kirill Vladimirovich