Trigger on self-referenced table

Question

I have an invoices table like this :

CREATE TABLE invoices (
    idinvoice SERIAL PRIMARY KEY
    ,idclient INTEGER REFERENCES clients(idclient)
    -- other fields here
);

and an invoiceitems table :

CREATE TABLE invoiceitems (
    idinvoiceitem SERIAL PRIMARY  KEY
    ,idinvoice INTEGER REFERENCES invoices(idinvoice)
    ,amount NUMERIC(15,2)
    ,sortorder INTEGER DEFAULT 10
);

My problem is with the sortorder field : every time I insert or update an invoiceitem, I want the sortorders of all items of the same invoice to be recalculated with a query like this :

WITH pre AS (
    SELECT idinvoiceitem,10*ROW_NUMBER() OVER (ORDER BY sortorder,idinvoiceitem) AS rownum
    FROM invoiceitems
    WHERE idinvoice=xx
    ORDER BY sortorder,idinvoiceitem
)
UPDATE invoiceitems di
SET sortorder=rownum
FROM pre p
WHERE p.idinvoiceitem=di.idinvoiceitem;

That way, every time a new item is added, it will be numbered 10+last item, and if I decide to change the sort order of the item by manually setting a value, all the sortorders will be recalculated with a 10 increment :

id | sortorder
---+----------
 1 | 10
 2 | 20
 3 | 30
 4 | 40  ← I change this to 25

Then after the query, I will get :

id | sortorder
---+----------
 1 | 10
 2 | 20
 4 | 30
 3 | 40

I would like to have this in a trigger. The problem is that item #4 will be UPDATEd by the trigger (thus calling the trigger again) and I will get some kind of endless recursion.

Is there a way to do this ? For example, set some semaphore that would prevent the trigger from being called when the UPDATE occurs within the trigger itself ? Or maybe an explicit LOCK ?

(using PostgreSQL 11+)

@LaurenzAlbe What do you mean ? The user *wants* to enforce some sort order, hence my demand. Sorting by "insert timestamp" doesn't always work. What would you suggest ? — JC Boggio, May 06 '21 at 16:06
I would suggest generating the `sortorder` on the fly whenever you `SELECT` from the table. — Laurenz Albe, May 06 '21 at 18:27
I mean something like [this answer](https://stackoverflow.com/a/67164998/6464308). — Laurenz Albe, May 06 '21 at 18:36
I see. That was my first idea but it is not very user-friendly (what's the half of 0.125 ? Some people will have a hard time figuring out or will make errors). I could get around this in the interface but I like this simplistic approach. Thanks for the pointer. — JC Boggio, May 06 '21 at 20:30
It is not the end user who has to make that calculation. Your code does that. — Laurenz Albe, May 07 '21 at 02:24

score 1 · Accepted Answer · answered May 06 '21 at 13:44

One thing you could do is to use pg_trigger_depth() function, which is documented here.

Then your trigger should look similar to this:

CREATE TRIGGER trigger_name
AFTER UPDATE OR INSERT ON invoiceitem
FOR EACH ROW
WHEN (pg_trigger_depth() = 0)
EXECUTE PROCEDURE your_procedure();

I'd recommend read some discussions about this approach (1, 2), but in general for not complicated systems this should be fine.

Trigger on self-referenced table

1 Answers1