Strange issue with RPC function returning the same result after calling it more than 5 times (instead of random entry)

[lukey78]

I have defined an RPC function in Postgres:

CREATE OR REPLACE FUNCTION get_random_entry() RETURNS peak AS $$
      select * from entries tablesample bernoulli(1) order by random() limit 1;
$$ LANGUAGE SQL IMMUTABLE;

When I call this function inside psql, I can repeatedly call it without problems:

select * from get_random_entry();

But when I call this function through Postgrest, it works 5 times in a row (getting a random entry), and then it always returns the SAME result:

https://mypostgrest.instance.com/rpc/get_random_entry

Is there some mechanism in Postgrest where this could happen, e.g. a result cache or something? If I wait for one or two minutes, the random response works again.

Thanks for a hint into the right direction.

[monacoremo]

The IMMUTABLE is probably the issue here, as it leads Postgres to make wrong optimizations. From https://www.postgresql.org/docs/13/sql-createfunction.html:

IMMUTABLE indicates that the function cannot modify the database and always returns the same result when given the same argument values

Try VOLATILE?

[lukey78]

VOLATILE works perfectly.

THANK YOU for the quick response, very helpful.