Re: NEW in after insert trugger contained incorrect data

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