sqids-plpgsql icon indicating copy to clipboard operation
sqids-plpgsql copied to clipboard

mark functions as immutable

Open cereum opened this issue 5 months ago • 4 comments

When using the output of squids.encode(...)in a GENERATED column, postgres was complaining that the functions needs to be immutable.

-- Example Table
CREATE TABLE public.order(
  id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  external_id text UNIQUE NOT NULL GENERATED ALWAYS AS (sqids.encode(ARRAY[id, 0], 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', 5)) STORED,
  amount numeric NOT NULL,
  name text,
  created_at timestamp with time zone DEFAULT timezone('utc', now()) NOT NULL,
  updated_at timestamp with time zone DEFAULT timezone('utc', now()) NOT NULL
);

From the postgres documentation:

An IMMUTABLE function cannot modify the database and is guaranteed to return the same results given the same arguments forever. This category allows the optimizer to pre-evaluate the function when a query calls it with constant arguments. For example, a query like SELECT ... WHERE x = 2 + 2 can be simplified on sight to SELECT ... WHERE x = 4, because the function underlying the integer addition operator is marked IMMUTABLE.

TL;DR Functions marked as IMMUTABLE always return the same output for the same input and do not depend on any external state. Non-IMMUTABLE functions may depend on database state or have side effects.

Accordingly, I've marked the following functions as follows:

Function IMMUTABLE
sqids.shuffle Yes
sqids.checkAlphabet Yes
sqids.toId Yes
sqids.toNumber Yes
sqids.encodeNumbers Yes
sqids.decode Yes
sqids.encode (both versions) Yes
sqids.isBlockedId No
sqids.defaultBlocklist No

The tests included with the repo all pass locally on my machine with these changes.

cereum avatar Sep 14 '24 21:09 cereum