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.
>

Table with statistic:
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)
)

Trigger:
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;
-- value BIGINT DEFAULT 0;
-- number INTEGER DEFAULT 1;
BEGIN
--    RAISE NOTICE 'Device %', NEW.device;
--    RAISE NOTICE 'Device timestamp %', NEW.device_timestamp;
--    RAISE NOTICE 'CPU %', NEW.cpu;
--    RAISE NOTICE 'Value %', NEW.value;

    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;

--        SELECT total_value, total_count
--        INTO value, number
--        FROM trassa.cpu_load_stat
--        WHERE device = id;
--        value = value + NEW.value;
--        number = number + 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;



--
Best regards,
Brilliantov Kirill Vladimirovich