Re: After insert trigger not work - Mailing list pgsql-general

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