Re: Using a TRIGGER with window functions.

On Sun, Aug 15, 2021 at 1:24 AM Pól Ua Laoínecháin <linehanp(at)tcd(dot)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.

David J.