record "new" is not assigned yet

Question

I'm trying to implement table partition and I have the below code in PostgreSQL (source from https://www.postgresql.org/docs/9.6/ddl-partitioning.html )

CREATE or replace FUNCTION child_tables.func_inventory_movement_insert_trigger()
    RETURNS trigger 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$BEGIN

   IF  ( NEW.report_date >=  '2019-04-01 ' AND
         NEW.report_date <  '2019-05-01 ' ) THEN
        INSERT INTO child_tables.inventory_movement_y2019m03 VALUES (NEW.*);

    ELSIF ( NEW.report_date >=  '2019-06-01 ' AND
            NEW.report_date <  '2019-07-01 ' ) THEN

        INSERT INTO child_tables.inventory_movement_y2019m04 VALUES (NEW.*);

    ELSE
        RAISE EXCEPTION 
        ' out of range exception.  Fix the child_tables.func_inventory_movement_insert_trigger() function. ';
    END IF;
    RETURN NULL;
END;
$BODY$;

Trigger function:

CREATE TRIGGER im_partition_trigger
BEFORE INSERT OR DELETE OR UPDATE 
ON core.inventory_movement
FOR EACH ROW
EXECUTE PROCEDURE child_tables.func_inventory_movement_insert_trigger();

Tried for both after or before in above-mentioned trigger.

ERROR: record "new" is not assigned yet
DETAIL: The tuple structure of a not-yet-assigned record is indeterminate.

FYI, same code is working for the other table.

Any suggestions?

When does your error occur? On ```CREATE TRIGGER``` or on firing the trigger? If on firing, on which type of event? I feel the problem is ```NEW``` in combination with ```DELETE```. — Islingre, Sep 20 '19 at 00:16
Please add this information and the command, that fires the trigger. You say the same code is working on another table. Is the trigger used on the same events and do you fire the same events? — Islingre, Sep 20 '19 at 00:25
yes same event for both. I have a stored procedure I will call that procedure, and it will extract data from other stage tables, and then it will insert into my core table. So before insert into this table trigger will fire automatically. — Rj_N, Sep 20 '19 at 00:34

Erwin Brandstetter · Answer 1 · 2019-09-20T11:40:14.833

3

Your trigger is defined

BEFORE INSERT OR DELETE OR UPDATE

But NEW is not defined for a DELETE . So your trigger function is bound to fail like it did.

Either write separate trigger functions and triggers for INSERT / UPDATE / DELETE (recommended). The displayed trigger function only deals with INSERT. So this trigger would make sense:

CREATE TRIGGER im_partition_trigger
BEFORE INSERT    -- !!
ON core.inventory_movement
FOR EACH ROW EXECUTE PROCEDURE child_tables.func_inventory_movement_insert_trigger();

Or nest all calls to OLD and NEW in acombined trigger function in IF or CASE constructs, checking for TG_OP.

Code examples in many related questions.

That said, to implement table partitioning I would much rather use declarative partitioning in Postgres 10 or later. Ideally, use the upcoming Postgres 12, which brings big improvements for partitioning.

Aside, in Postgres 11 or later, rather use the fixed syntax for triggers:

...
FOR EACH ROW EXECUTE FUNCTION child_tables.func_inventory_movement_insert_trigger();

It's a function, not a "procedure".

edited Sep 20 '19 at 11:40

answered Sep 20 '19 at 02:18

Erwin Brandstetter

605,456
145
1,078
1,228

But, here same trigger is working for another table. As I mentioned, in stored procedure INSERT/UPDATE will performs. Currently we are using 9.6v because of some application dependencies. – Rj_N Sep 20 '19 at 07:34
1

`NEW` is defined for `INSERT` and `UPDATE`, but not for `DELETE`. "Function", btw, not "stored procedure". – Erwin Brandstetter Sep 20 '19 at 11:38
Thank you for sharing your thoughts, I figured out with some trial and errors and succeeded with below syntax, `CREATE TRIGGER im_partition_trigger_before BEFORE INSERT OR DELETE OR UPDATE OF coulmn1,coulmn2,column3 ON core.inventory_movement FOR EACH ROW EXECUTE PROCEDURE child_tables.func_inventory_movement_insert_trigger();` and in 9.6 we have to use `PROCEDURE` not `FUNCTION` – Rj_N Sep 20 '19 at 18:20
1

This is still dangerously wrong. The given function will error out with the first `DELETE` event. Yes, you have to use the keyword `PROCEDURE` in pg 9.6. It's still about a *function*. Hence the syntax change in pg 11. – Erwin Brandstetter Sep 20 '19 at 18:26
Hi @ErwinBrandstetter, It's surprising to me that same trigger function is working after a couple of attempts. may it's a bug or I'm not sure. `CREATE TRIGGER im_partition_trigger_before_i_u_d before INSERT OR UPDATE or delete ON core.inventory_movement FOR EACH ROW EXECUTE PROCEDURE child_tables.func_inventory_movement_insert_trigger();` – Rj_N Sep 23 '19 at 19:49
like I said, delete in there is wrong and bound to raise exceptions. – Erwin Brandstetter Sep 25 '19 at 12:50

record "new" is not assigned yet

1 Answers1