On Sun, Aug 15, 2021 at 1:24 AM Pól Ua Laoínecháin <linehanp@tcd.ie> wrote:
ERROR: window functions are not allowed in UPDATE LINE 2: SET sort_order = activity_id - FIRST_VALUE(activity_id)
Why are window functions now allowed in UPDATEs
You can get it to work via a subquery/FROM clause computation. That it doesn't work directly in the SET clause I don't know off-hand, but most likely the development and runtime cost of making it work isn't worth the benefit.
Why aren't window functions allowed in GENERATED columns?
Because the expressions allowed in GENERATED can only immutably reference other columns in the same row. The underlying rationale is probably quite similar to the UPDATE comment above.
INSERT INTO t1 (user_id, activity_id, sort_order) VALUES (NEW.user_id, NEW.activity_id, (SELECT so FROM cte));
Yes, an insert trigger that performs a literal insert into the same table is fundamentally broken due to exhibiting infinite loop behavior. Same goes for update/delete - during trigger execution you are already in the middle of doing the required thing.
If triggers aren't the ideal solution, I'm open to other avenues
Off the top of my head - I'd have a second table that is dedicated to dealing with ordering. It would have a one-to-one relationship with the main table. Upon executing DML against the main table, ideally for a single user_id at a time, remove all of the records from the ordering table for that user_id and then insert them anew. I would probably do this within functions and disallow direct access to the main and ordering tables generally - but triggers can probably be made to work.
Or just discard the idea of pre-computing this data and wrap the ordering logic in a view.