NEW in after insert trugger contained incorrect data

Brilliantov Kirill Vladimirovich wrote:

I use Postgre-9.3.5 on windows7 x64.
I use simple trigger for store some statistic data, it code:
SELECT field IN variable FROM table WHERE ...;
IF FOUND THEN
UPDATE table SET field = ...;
ELSE
INSERT INTO table (field) VALUES(value);
END IF;
RETURN NULL;

This trigger added as FOR EACH ROW on table2.
In table2 I insert multiple data on one insert, e.g. INSERT INTO
table2(field) VALUES(value0),(value1),(value2).

Unfortunately trigger exit with error: ERROR: record "new" has no field
"value";

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Brilliantov Kirill Vladimirovich wrote:

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,

[...]

)

Trigger:
CREATE OR REPLACE FUNCTION trassa.update_cpu_load_stat()
RETURNS trigger AS
$BODY$

[...]

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;

I'd say that the error message refers to this or the following query:

There is no field "value" in the table, and "NEW" represents a row in the table,
so the expression "NEW.value" does not make sense.

Yours,
Laurenz Albe

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 11/14/2014 03:24 AM, Albe Laurenz wrote:

Brilliantov Kirill Vladimirovich wrote:

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,

[...]

)

Trigger:
CREATE OR REPLACE FUNCTION trassa.update_cpu_load_stat()
RETURNS trigger AS
$BODY$

[...]

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;

I'd say that the error message refers to this or the following query:

There is no field "value" in the table, and "NEW" represents a row in the table,
so the expression "NEW.value" does not make sense.

Actually I think there is in the table the trigger is on. From the
original post:

SQL statement "INSERT INTO trassa.cpu_load (device, device_timestamp,
cpu, value) VALUES(5,'1970-01-02 06:02:38',0,6),(5,'1970-01-02
06:02:38',1,0),(5,'1970-01-02 06:02:38',255,3)"

though what is showing up in the error is this:

NOTICE: *** START ***: (9994,5,"2014-11-14 13:38:04.94","1970-01-02
06:02:38",0 ,6)

Not sure where the 9994, and "2014-11-14 13:38:04.94" are coming from,
though I think it is related to this from the error message:

PL/pgSQL function
update_cpu_load_list(integer,integer,smallint[],smallint[]) line 19 at
EXECUTE statement

I am pretty sure the error is coming from a different function then the
one we are being shown. So we would need to see the table the trigger is
being run on as well as any other triggers and associated functions.

Yours,
Laurenz Albe

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Adrian Klaver wrote on 11/14/2014 05:15 PM:

Actually I think there is in the table the trigger is on. From the
original post:

SQL statement "INSERT INTO trassa.cpu_load (device, device_timestamp,
cpu, value) VALUES(5,'1970-01-02 06:02:38',0,6),(5,'1970-01-02
06:02:38',1,0),(5,'1970-01-02 06:02:38',255,3)"

though what is showing up in the error is this:

NOTICE: *** START ***: (9994,5,"2014-11-14 13:38:04.94","1970-01-02
06:02:38",0 ,6)

You can see what original data contained timestamp 1970-01-02 06:02:38,
value 0 and 6, trigger start message also contain this values.
Base on this I think trigger start message is correct and I get it after
first insert, where 9994 is a inserted id.

Not sure where the 9994, and "2014-11-14 13:38:04.94" are coming from,
though I think it is related to this from the error message:

PL/pgSQL function
update_cpu_load_list(integer,integer,smallint[],smallint[]) line 19 at
EXECUTE statement

I am pretty sure the error is coming from a different function then the
one we are being shown. So we would need to see the table the trigger is
being run on as well as any other triggers and associated functions.

Yours,
Laurenz Albe

--
С уважением,
Бриллиантов Кирилл Владимирович
…………………………………………………………………
программист, технический отдел
ООО «БайтЭрг»
Видеокамеры МВК – Эффективность разумных решений
…………………………………………………………………
+7(495)221-66-22
http://www.byterg.ru http://www.bestdvr.ru

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 11/14/2014 07:23 AM, Brilliantov Kirill Vladimirovich wrote:

Adrian Klaver wrote on 11/14/2014 05:15 PM:

Actually I think there is in the table the trigger is on. From the
original post:

SQL statement "INSERT INTO trassa.cpu_load (device, device_timestamp,
cpu, value) VALUES(5,'1970-01-02 06:02:38',0,6),(5,'1970-01-02
06:02:38',1,0),(5,'1970-01-02 06:02:38',255,3)"

though what is showing up in the error is this:

NOTICE: *** START ***: (9994,5,"2014-11-14 13:38:04.94","1970-01-02
06:02:38",0 ,6)

You can see what original data contained timestamp 1970-01-02 06:02:38,
value 0 and 6, trigger start message also contain this values.
Base on this I think trigger start message is correct and I get it after
first insert, where 9994 is a inserted id.

But the error message is coming from this:

PL/pgSQL function
update_cpu_load_list(integer,integer,smallint[],smallint[]) line 19 at
EXECUTE statement

Note different function name and the EXECUTE statement. There is no
EXECUTE in the function you showed us.

Not sure where the 9994, and "2014-11-14 13:38:04.94" are coming from,
though I think it is related to this from the error message:

PL/pgSQL function
update_cpu_load_list(integer,integer,smallint[],smallint[]) line 19 at
EXECUTE statement

I am pretty sure the error is coming from a different function then the
one we are being shown. So we would need to see the table the trigger is
being run on as well as any other triggers and associated functions.

Yours,
Laurenz Albe

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 11/14/2014 07:32 AM, Brilliantov Kirill Vladimirovich wrote:

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

Just approaching caffeine level required to follow this:)

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
);

FYI, in the function below you have declared aliases for the function
arguments e.g. device_id integer. You can use those aliases in the
function instead of $*. It would make things easier to follow.

http://www.postgresql.org/docs/9.3/interactive/plpgsql-declarations.html#PLPGSQL-DECLARATION-PARAMETERS

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;

I have not thought this all the way through, but I see a potential
problem with the test above. It is not clear to me which version of cmd
you are using nor what exactly it returns. You might want to put a
NOTICE in there to see what you are actually building.

Also you might want to take a look at this section of the docs:

http://www.postgresql.org/docs/9.3/interactive/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING

In particular the following forms:

FOR target IN EXECUTE text_expression ...

FOREACH target [ SLICE number ] IN ARRAY expression LOOP

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.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 11/14/2014 07:32 AM, Brilliantov Kirill Vladimirovich wrote:

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

Turns out I was not at a sufficient caffeine level previously:(

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();

Another run through showed that the issue is above. You have declared
the trigger on trassa.cpu_load_stat instead of trassa.cpu_load.
trassa.cpu_load_stat has no value field, hence the error.

Thank you and excuse my big message.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 11/14/14, 10:09 AM, Adrian Klaver wrote:

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();

Another run through showed that the issue is above. You have declared the trigger on trassa.cpu_load_stat instead of trassa.cpu_load. trassa.cpu_load_stat has no value field, hence the error.

Something else to consider: using FOUND to decide whether to INSERT vs UPDATE is a race condition: you can do the SELECT, someone else can then insert or delete, and then you attempt to do the wrong thing.

To handle this correctly, you need an appropriate UNIQUE constraint or primary key, and to follow the pattern in table 40-2 at http://www.postgresql.org/docs/9.3/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

If you don't, and you have concurrent activity you can end up losing data (and in the case of a DELETE after your SELECT, the data loss will be completely silent).
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Jim Nasby wrote on 11/15/2014 07:57 AM:

Something else to consider: using FOUND to decide whether to INSERT vs
UPDATE is a race condition: you can do the SELECT, someone else can then
insert or delete, and then you attempt to do the wrong thing.

To handle this correctly, you need an appropriate UNIQUE constraint or
primary key, and to follow the pattern in table 40-2 at
http://www.postgresql.org/docs/9.3/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

If you don't, and you have concurrent activity you can end up losing
data (and in the case of a DELETE after your SELECT, the data loss will
be completely silent).

Hello, Jim!
Table trassa.cpu_load_stat have field id, it is a primary key.
I rewrite trigger with using loop, unfortunately thi not solve problem.

--
Best regards,
Brilliantov Kirill Vladimirovich

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hello!
After modify trassa.update_cpu_load_list function on inserting one line
per time trigger work fine.

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);
total_insert SMALLINT DEFAULT 0;
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;
*/
EXECUTE 'INSERT INTO trassa.cpu_load (device, device_timestamp, cpu,
value) ' ||
'VALUES(' ||
$1::text ||
',''' || ts::text || ''',' ||
$3[i]::text || ',' ||
$4[i]::text || ')';
GET DIAGNOSTICS result = ROW_COUNT;
total_insert := total_insert + result;
END LOOP;
IF total_insert = val_len THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;

ELSE
RETURN FALSE;
END IF;
END;$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER
COST 100;

--
Best regards,
Brilliantov Kirill Vladimirovich

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general