Postgres Pro
Downloads
Home   >   mailing lists

Thread: After insert trigger not work

After insert trigger not work

From
Brilliantov Kirill Vladimirovich
Date:
Hello!
I use postgre-9.3.5 on windows7 x64.
Trigger should update data in table:
CREATE TABLE trassa.ram_free_stat
(
   id serial NOT NULL,
   device integer NOT NULL,
   min_value integer NOT NULL,
   avg_value integer NOT NULL DEFAULT 0,
   max_value integer NOT NULL,
   last_update timestamp without time zone NOT NULL DEFAULT now(),
   CONSTRAINT ram_free_stat_pk PRIMARY KEY (id),
   CONSTRAINT ram_free_stat_device_fk FOREIGN KEY (device)
       REFERENCES trassa.devices (id) MATCH SIMPLE
       ON UPDATE NO ACTION ON DELETE NO ACTION,
   CONSTRAINT ram_free_stat_max_fk FOREIGN KEY (max_value)
       REFERENCES trassa.ram (id) MATCH SIMPLE
       ON UPDATE NO ACTION ON DELETE NO ACTION,
   CONSTRAINT ram_free_stat_min_fk FOREIGN KEY (min_value)
       REFERENCES trassa.ram (id) MATCH SIMPLE
       ON UPDATE NO ACTION ON DELETE NO ACTION
)

My trigger:
CREATE OR REPLACE FUNCTION trassa.update_ram_free_stat()
   RETURNS trigger AS
$BODY$
DECLARE
device_id INTEGER DEFAULT 0;
min_id INTEGER DEFAULT 0;
avg_val INTEGER DEFAULT 0;
max_id INTEGER DEFAULT 0;
BEGIN
    SELECT id INTO device_id FROM trassa.ram_free_stat
        WHERE device = NEW.device;
    SELECT free_ram, id INTO min_id FROM trassa.ram
        WHERE device = NEW.device
        ORDER BY free_ram LIMIT 1;
    SELECT free_ram, id INTO max_id FROM trassa.ram
        WHERE device = NEW.device
        ORDER BY free_ram DESC LIMIT 1;
    SELECT CEIL(AVG(free_ram)) INTO avg_val
        FROM trassa.ram WHERE device = NEW.device;
    IF device_id > 0 THEN
        UPDATE trassa.ram_free_stat
            SET min_value = min_id,
                avg_value = avg_val,
                max_value = max_id
            WHERE id = device_id;
    ELSE
        INSERT INTO trassa.ram_free_stat
            (device, min_value,
                avg_value, max_value)
            VALUES(NEW.device, min_id,
                avg_val, max_id);
    END IF;
    RETURN NULL;
END;
$BODY$
   LANGUAGE plpgsql VOLATILE SECURITY DEFINER
   COST 100;

I add this trigger on another table:
CREATE TRIGGER update_ram_free_stat_trigger
   AFTER INSERT
   ON trassa.ram
   FOR EACH ROW
   EXECUTE PROCEDURE trassa.update_ram_free_stat();

All executed without any error, but data in trassa.ram_free_stat not
updated.
Can you help me solve this problem?
Thank you and excuse me for my bad english.

--
Best regards,
Brilliantov Kirill Vladimirovich

Re: After insert trigger not work

From
Brilliantov Kirill Vladimirovich
Date:
Melvin Davidson wrote on 11/13/2014 05:29 PM:
> s for
> I suspect your problem is because you have 6 columns that are NOT NULL, but
> on INSERT you are only supplying values for 4 columns plus the id(serial).
> Therefore, the INSERT will fail. Perhaps if you supplied a value for
> last_update you it will work a lot better.

Hello, Melvin!
Why this is a problem is last_update column created with default value?

>> Trigger should update data in table:
>> CREATE TABLE trassa.ram_free_stat
>> (
>>    id serial NOT NULL,
>>    device integer NOT NULL,
>>    min_value integer NOT NULL,
>>    avg_value integer NOT NULL DEFAULT 0,
>>    max_value integer NOT NULL,
>>    last_update timestamp without time zone NOT NULL DEFAULT now(),
>>    CONSTRAINT ram_free_stat_pk PRIMARY KEY (id),
>>    CONSTRAINT ram_free_stat_device_fk FOREIGN KEY (device)
>>        REFERENCES trassa.devices (id) MATCH SIMPLE
>>        ON UPDATE NO ACTION ON DELETE NO ACTION,
>>    CONSTRAINT ram_free_stat_max_fk FOREIGN KEY (max_value)
>>        REFERENCES trassa.ram (id) MATCH SIMPLE
>>        ON UPDATE NO ACTION ON DELETE NO ACTION,
>>    CONSTRAINT ram_free_stat_min_fk FOREIGN KEY (min_value)
>>        REFERENCES trassa.ram (id) MATCH SIMPLE
>>        ON UPDATE NO ACTION ON DELETE NO ACTION
>> )
>>



--
Best regards,
Brilliantov Kirill Vladimirovich

Re: After insert trigger not work

From
Adrian Klaver
Date:
On 11/13/2014 04:27 AM, Brilliantov Kirill Vladimirovich wrote:
> Hello!
> I use postgre-9.3.5 on windows7 x64.
> Trigger should update data in table:
> CREATE TABLE trassa.ram_free_stat
> (
>    id serial NOT NULL,
>    device integer NOT NULL,
>    min_value integer NOT NULL,
>    avg_value integer NOT NULL DEFAULT 0,
>    max_value integer NOT NULL,
>    last_update timestamp without time zone NOT NULL DEFAULT now(),
>    CONSTRAINT ram_free_stat_pk PRIMARY KEY (id),
>    CONSTRAINT ram_free_stat_device_fk FOREIGN KEY (device)
>        REFERENCES trassa.devices (id) MATCH SIMPLE
>        ON UPDATE NO ACTION ON DELETE NO ACTION,
>    CONSTRAINT ram_free_stat_max_fk FOREIGN KEY (max_value)
>        REFERENCES trassa.ram (id) MATCH SIMPLE
>        ON UPDATE NO ACTION ON DELETE NO ACTION,
>    CONSTRAINT ram_free_stat_min_fk FOREIGN KEY (min_value)
>        REFERENCES trassa.ram (id) MATCH SIMPLE
>        ON UPDATE NO ACTION ON DELETE NO ACTION
> )
>
> My trigger:
> CREATE OR REPLACE FUNCTION trassa.update_ram_free_stat()
>    RETURNS trigger AS
> $BODY$
> DECLARE
> device_id INTEGER DEFAULT 0;
> min_id INTEGER DEFAULT 0;
> avg_val INTEGER DEFAULT 0;
> max_id INTEGER DEFAULT 0;
> BEGIN
>      SELECT id INTO device_id FROM trassa.ram_free_stat
>          WHERE device = NEW.device;
>      SELECT free_ram, id INTO min_id FROM trassa.ram
>          WHERE device = NEW.device
>          ORDER BY free_ram LIMIT 1;

In above and below you are selecting two column values into one integer
variable, you may not be getting what you think you are:

test=> DO $$
DECLARE
     var_1 integer DEFAULT 0;
BEGIN
     SELECT 10, 1 INTO var_1;
     RAISE NOTICE '%', var_1;
END;
$$ LANGUAGE plpgsql
;

NOTICE:  10
DO

Or maybe you are, it is not clear what the variables are supposed to
hold. From the name I would say the device id, from how they are used
below I would say the free ram values.


>      SELECT free_ram, id INTO max_id FROM trassa.ram
>          WHERE device = NEW.device
>          ORDER BY free_ram DESC LIMIT 1;

>      SELECT CEIL(AVG(free_ram)) INTO avg_val
>          FROM trassa.ram WHERE device = NEW.device;
>      IF device_id > 0 THEN
>          UPDATE trassa.ram_free_stat
>              SET min_value = min_id,
>                  avg_value = avg_val,
>                  max_value = max_id
>              WHERE id = device_id;
>      ELSE
>          INSERT INTO trassa.ram_free_stat
>              (device, min_value,
>                  avg_value, max_value)
>              VALUES(NEW.device, min_id,
>                  avg_val, max_id);
>      END IF;
>      RETURN NULL;
> END;
> $BODY$
>    LANGUAGE plpgsql VOLATILE SECURITY DEFINER
>    COST 100;
>
> I add this trigger on another table:
> CREATE TRIGGER update_ram_free_stat_trigger
>    AFTER INSERT
>    ON trassa.ram
>    FOR EACH ROW
>    EXECUTE PROCEDURE trassa.update_ram_free_stat();
>
> All executed without any error, but data in trassa.ram_free_stat not
> updated.


> Can you help me solve this problem?

You might to put some RAISE NOTICEs in your function to track what is
going on:

http://www.postgresql.org/docs/9.3/interactive/plpgsql-errors-and-messages.html

> Thank you and excuse me for my bad english.
>


--
Adrian Klaver
adrian.klaver@aklaver.com