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. — user330315
– user330315, Commented Jan 27, 2012 at 11:38
@a_horse_with_no_name, good point thank you, is there a better way to achieve this? — Alpaus
– Alpaus, Commented Jan 27, 2012 at 13:38
if you want to make that transaction safe, you will have to exclusively lock the table. — user330315
– user330315, Commented Jan 27, 2012 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 and Postgresql insert trigger to set value. — questionto42
– questionto42, Commented Dec 16, 2022 at 9:13

araqnid · Accepted Answer · 2012-01-27 12:14:44Z

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, 2012 at 12:14

araqnid

135k25 gold badges163 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alpaus Over a year ago

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.

araqnid Over a year ago

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.

Michael Krelin - hacker · Accepted Answer · 2012-01-27 11:30:21Z

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, 2012 at 11:30

Michael Krelin - hacker

144k25 gold badges200 silver badges177 bronze badges

Collectives™ on Stack Overflow

POSTGRESQL pl/pgsql trigger function returns null on insert

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related