POSTGRESQL pl/pgsql trigger function returns null on insert

Question

I have a pg table like this:

CREATE TABLE order_status_history (
order_id integer NOT NULL,
status character varying NOT NULL,
sequence integer DEFAULT 1 NOT NULL,
date_status timestamp with time zone DEFAULT now() NOT NULL,
record_state character varying(12) DEFAULT 'ACTIVE'::character varying NOT NULL
);

with composite PK on order_id and sequence. Essentially the status column is an auto-increment value starting at 1, for each individual order_id. So, if order_id 2 already has two rows, the sequences would be 1 and 2, and for a new row, the sequence should be 3. I'm trying to use a trigger to implement this behaviour before insert, but when I try to insert the first row for a new order_id (i.e. trigger doesn't have to change the row prior to insert), I'm getting an error for PG. saying that it cannot insert NULL into sequence. I can't see how my trigger function would be returning NULL, but my pl/sql is not great so i'm sure it's something simple... Trigger function below, thanks.

DECLARE
    seq_no INTEGER;

BEGIN
    SELECT INTO seq_no MAX(sequence) FROM rar.order_status_history WHERE order_id = NEW.order_id;
    IF FOUND THEN 
        NEW.sequence := seq_no + 1;
    END IF;

    RETURN NEW;
END;

Using a `SELECT max()` to get a new id is a very, very bad idea. It is not transaction safe and your solution will create wrong sequence numbers in a multi-user environment. — , Jan 27 '12 at 11:38
@a_horse_with_no_name, good point thank you, is there a better way to achieve this? — Alpaus, Jan 27 '12 at 13:38
if you want to make that transaction safe, you will have to exclusively lock the table. — , Jan 27 '12 at 14:05
For those who came here in search of another thing: that you have NULL values for any NEW.[column] that is to be inserted by a trigger, see [Postgres trigger after insert accessing NEW](https://stackoverflow.com/questions/11001118/postgres-trigger-after-insert-accessing-new) and [Postgresql insert trigger to set value](https://stackoverflow.com/questions/16102188/postgresql-insert-trigger-to-set-value). — questionto42, Dec 16 '22 at 09:13

score 3 · Accepted Answer · answered Jan 27 '12 at 12:14

3

If there is nothing in order_status_history for that order_id, then MAX(sequence) will be null. Use COALESCE(MAX(sequence),0) to make it default to 1.

You could simply write:

NEW.sequence := (SELECT COALESCE(MAX(sequence),0) FROM /* etc.. */) + 1;

You should really be locking the order row in exclusive mode before you do this as well, to allow this to work with multiple transactions concurrently inserting into the history for the same order. That is:

SELECT 1 FROM orders WHERE orders.order_id = NEW.order_id FOR UPDATE;

answered Jan 27 '12 at 12:14

araqnid

127,052
24
157
134

Thank you, makes more sense. Do I need to release the lock or is that implicit once the trigger function ends? Are there any good web resources for me to get a better handle on writing these functions? I'm finding a good, comprehensive tutorial hard to come by and piecing it together from what I do find. – Alpaus Jan 27 '12 at 21:47
the lock will automatically be released at the end of the transaction. releasing it sooner doesn't make sense as other transactions wouldn't be able to see the change that was guarded by the lock yet. – araqnid Jan 28 '12 at 14:50

score 2 · Answer 2 · answered Jan 27 '12 at 11:30

2

The max(sequence) is always FOUND, but it may be NULL if there is no data. In which case your NEW.sequence := seq_no + 1 makes it still NULL. IF seq_no IS NOT NULL sounds like a more proper condition.

answered Jan 27 '12 at 11:30

Michael Krelin - hacker

138,757
24
193
173

POSTGRESQL pl/pgsql trigger function returns null on insert

2 Answers2