Thread: NEW in after insert trugger contained incorrect data

NEW in after insert trugger contained incorrect data

From

Brilliantov Kirill Vladimirovich

Date:

14 November 2014, 12:53:22

Hello!
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";
Ok, for debug I add messages and what I see:
NOTICE:  *** START ***: (9994,5,"2014-11-14 13:38:04.94","1970-01-02
06:02:38",0 ,6)
// insert in table2
CONTEXT:  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)"
PL/pgSQL function
update_cpu_load_list(integer,integer,smallint[],smallint[]) line 19 at
EXECUTE statement
// insert in table
NOTICE:  *** INSERT ***: (9994,5,"2014-11-14 13:38:04.94","1970-01-02
06:02:38", 0,6)
CONTEXT:  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)"
PL/pgSQL function
update_cpu_load_list(integer,integer,smallint[],smallint[]) line 19 at
EXECUTE statement
NOTICE:  *** START ***: (38,5,0,6,"1970-01-02 06:02:38","2014-11-14
13:38:04.94" ,6,"2014-11-14 13:38:04.94",6,"1970-01-02
06:02:38","2014-11-14 13:38:04.94",6,1)

Last START incorrect because NEW contained data from previews INSERT.
Why and how can I solve this problem?
Thank you and excuse me for my bad english.

--
Best regards,
Brilliantov Kirill Vladimirovich

Re: NEW in after insert trugger contained incorrect data

From

Albe Laurenz

Date:

14 November 2014, 13:28:22

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

Re: NEW in after insert trugger contained incorrect data

From

Brilliantov Kirill Vladimirovich

Date:

14 November 2014, 13:54:39

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

Re: NEW in after insert trugger contained incorrect data

From

Albe Laurenz

Date:

14 November 2014, 14:24:21

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

Re: NEW in after insert trugger contained incorrect data

From

Adrian Klaver

Date:

14 November 2014, 17:15:42

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

Re: NEW in after insert trugger contained incorrect data

From

Brilliantov Kirill Vladimirovich

Date:

14 November 2014, 18:23:56

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

Re: NEW in after insert trugger contained incorrect data

From

Adrian Klaver

Date:

14 November 2014, 18:27:47

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

Re: NEW in after insert trugger contained incorrect data

From

Brilliantov Kirill Vladimirovich

Date:

14 November 2014, 18:32:30

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

Re: NEW in after insert trugger contained incorrect data

From

Adrian Klaver

Date:

14 November 2014, 19:01:01

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

Re: NEW in after insert trugger contained incorrect data

From

Adrian Klaver

Date:

14 November 2014, 19:09:58

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

Re: NEW in after insert trugger contained incorrect data

From

Jim Nasby

Date:

15 November 2014, 07:57:50

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-2at 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

Re: NEW in after insert trugger contained incorrect data

From

Brilliantov Kirill Vladimirovich

Date:

17 November 2014, 12:04:13

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

Re: NEW in after insert trugger contained incorrect data

From

Brilliantov Kirill Vladimirovich

Date:

17 November 2014, 16:13:06

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