How postgresql 'remember' result of function?-CodePudding

I faced with strange behavior of postgresql, please, could you clarify it for me?

I created function, which return constants from constants table.

CREATE TABLE constants (   key varchar PRIMARY KEY , value varchar );

CREATE OR REPLACE FUNCTION get_constant(_key varchar) RETURNS varchar
AS   $$ SELECT value FROM constants WHERE key = _key; $$ LANGUAGE sql
IMMUTABLE;

Then I added a constant to the table.

insert into constants(key, value)
values('const', '1')
;

Then if I change the value of the constant and call the function:

select get_constant('const');

Then result is CORRECT.

BUT! If I call function in other procedure, for example:

create or REPLACE PROCEDURE etl.test()
LANGUAGE plpgsql
AS $$
declare  
begin
   raise notice '%', etl.get_constant('const');
END $$;

Then it rememer first result of calling, and don't change result of raise notice, even if I change constant in table.

But if I recompile procedure - then new const-value printing correct.

I tried to find documentation about it, tried google: 'cache results of postgre SQL procedure', and ect., but found nothing.

Could you clarify it and attach link to documentation this issue?

CodePudding user response：

The documentation for CREATE TABLE says this about the IMMUTABLE keyword:

IMMUTABLE indicates that the function cannot modify the database and always returns the same result when given the same argument values; that is, it does not do database lookups or otherwise use information not directly present in its argument list. If this option is given, any call of the function with all-constant arguments can be immediately replaced with the function value.

So by declaring etl.get_constant with that keyword, you're telling Postgres "the output of this function will always be the same for a given input, forever".

The call etl.get_constant('const') has "all-constant arguments" - the value 'const' won't ever change. Since you've told Postgres that etl.get_constant will always return the same output for the same input, it immediately replaces the function call with the result.

So when you call etl.test() it doesn't run etl.get_constant at all, it just returns the value it got earlier, which you told it would be valid forever.

Compare that with the next paragraph on the same page (emphasis mine):

STABLE indicates that the function cannot modify the database, and that within a single table scan it will consistently return the same result for the same argument values, but that its result could change across SQL statements. This is the appropriate selection for functions whose results depend on database lookups, parameter variables (such as the current time zone), etc.

So if your "constant" is subject to change, but not within the scope of a particular query, you should mark it STABLE, not IMMUTABLE.