"ERROR: generation expression is not immutable" why is the alter table for new generated column is immutable?

Question

Why is the below code not valid. I have only used immutable columns. Below code is supposed to add a new column to the users table. There are few conditions that needs to meet for the is_valid column to be true else it will be false. The email and legacy_password columns must not be null or empty. And expiry_date must not be passed.

ALTER TABLE IF EXISTS vnext.users
ADD COLUMN is_valid boolean GENERATED ALWAYS AS
 ((
    Case when (((trim(coalesce(email,'')) <> '') IS NOT False) 
    AND ((trim(coalesce(legacy_password,'')) <> '') IS NOT False)
    AND (Case WHEN expiry_date is null Then True ELSE date_trunc('day', 
    expiry_date)>date_trunc('day', current_timestamp) END)) THEN TRUE ELSE FALSE END
  )) STORED;

You can’t use current _timestamp, it’s value changes every microsecond, it’s not immutable — Frank Heikens, Mar 11 '23 at 14:49
Is there a way to check if `expiry_date` is passed without breaking immutability? — LordDraagon, Mar 11 '23 at 14:51
Which date? The value of `current_timestamp` when you wrote the question is a different value then when I wrote this comment. — , Mar 11 '23 at 14:51

score 1 · Answer 1 · answered Mar 11 '23 at 14:49

In this case, the use of the current_timestamp in the query made it mutable, and got the above error. Removing the current_timestamp should work for now.

Not sure if there's a way to check if the expiry_date is expired without being mutable.

ALTER TABLE IF EXISTS vnext.users
ADD COLUMN is_valid boolean GENERATED ALWAYS AS
 ((
    Case when (((trim(coalesce(email,'')) <> '') IS NOT False) 
    AND ((trim(coalesce(legacy_password,'')) <> '') IS NOT False))
    THEN TRUE ELSE FALSE END
  )) STORED;

You can simplify that expression to `( trim(coalesce(email,'')) <> '' and trim(coalesce(legacy_password,'')) <> '')` — , Mar 11 '23 at 14:51

score 1 · Answer 2 · answered Mar 11 '23 at 14:56

1

current_timestamp is obviously not immutable.

Also, date_trunc when the 2nd parameter is a timestamptz is only stable, because the result it gives depends on your session's timezone setting.

answered Mar 11 '23 at 14:56

jjanes

37,812
5
27
34

score 1 · Answer 3 · answered Mar 12 '23 at 12:56

One way you can overcome the problem of current_timestamp not being IMMUTABLE is to use a BEFORE INSERT OR UPDATE trigger instead of a generated column. The trigger function can simply assign the value to the boolean column, and you are not restricted to IMMUTABLE expressions.

The difference is that the trigger only runs at a certain point in time, while a generated column guarantees that the generated value will always be equal to the result of the generation expression. Hence the requirement for immutability.

"ERROR: generation expression is not immutable" why is the alter table for new generated column is immutable?

3 Answers3