Re: NEW in after insert trugger contained incorrect data - Mailing list 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