I have an Immutable Function that takes a Table as parameter
CREATE TABLE playground.t_test (
c1 INTEGER,
c2 INTEGER
);
CREATE OR REPLACE FUNCTION playground.f_test (
test playground.t_test
)
RETURNS jsonb LANGUAGE 'sql'
IMMUTABLE
RETURNS NULL ON NULL INPUT
SECURITY INVOKER
LEAKPROOF
PARALLEL SAFE
COST 100
AS
$body$
select
to_jsonb(test);
$body$;
If the metadata of the table “playground.t_test” Changes, for example, if a new column is added. Will the Function regognize this as change and change the result in the next call?
I use PostgreSQL 14
I tried testing it myself, i added a timestamp to the result in the function body like this:
select
to_jsonb(test) || jsonb_build_object('now', current_timestamp); -- I KNOW THIS IS NOT IMMUTABLE
Despite being immutable, the function generates me a new result with the new timestamp, every time i call the function. so i can’t seem to be able to generate a proper testcase. Hence here i am 🙂
In some cases, PostgreSQL can detect if an IMMUTABLE function is not immutable. For example, if you try to run a DML statement in an IMMUTABLE function, PostgreSQL will protest:
CREATE FUNCTION imm(integer) RETURNS void
IMMUTABLE LANGUAGE sql
AS 'INSERT INTO tab VALUES ($1)';
SELECT imm(5);
ERROR: INSERT is not allowed in a non-volatile function
CONTEXT: SQL function "imm" during startup
But in general, PostgreSQL doesn’t invest a lot of effort to ascertain that an IMMUTABLE function really is immutable. If you define a function as IMMUTABLE, PostgreSQL simply believes you. It is the responsibility of the user to declare a function as IMMUTABLE only if it really lives up to the promise.
If you define an IMMUTABLE function that really isn’t, bad things can happen. For example, you can use that function in an index definition. But if the function fails to keep the promise, like your modified function, the index will automatically become corrupted. In that case, you have only yourself to blame for the ensuing problems.
Now, is your original function definition OK? As you correctly observe, the function is not guaranteed to be immutable. If you change the table definition, the function will return different results than before.
Still, it is acceptable to declare such a function IMMUTABLE if you promise never to change the table definition. PostgreSQL has no way to enforce that, it is your responsibility.
Essentially, PostgreSQL allows such definitions because they can be really useful (e.g. for indexes). The benefits outweigh the dangers. It is up to the user to use that feature responsibly.
7