More details for the immutable functions error message

[YAmikep]

Hi

This project looks promising, I wanted to try it but I got the following error.

ERROR:  mutable function is not supported on incrementally maintainable materialized view
HINT:  functions must be marked IMMUTABLE 

SQL state: 0A000

I then marked ALL of my functions as IMMUTABLE but the error is still happening.

It would be helpful to have the name of the function that is causing the problem in the error message.

Thank you

[yugo-n]

Thank you for your suggestion! I'll fix it so that the message include the name of the causing function.

[yugo-n]

After some investigation, I found this is not easer than I though initially... We use a public function provied in PostgreSQL core code for this check, but this just returns whether given expression contains IMMUTABLE function or not, but does not return the function name. Actually, PostgreSQL doesn't report the function name in similar error messages; for example, ERROR: functions in index predicate must be marked IMMUTABLE

Therefore, we need make a special function for pg_ivm to meet your request. That is possible, but seems a bit redundant. I'll try to find a better way, but it may take a time.

[YAmikep]

@yugo-n Thanks for looking into this.

What PostgreSQL function do you use to check this?

I found the following

select proname, pronamespace::regnamespace, provolatile
from pg_proc

where proname is the function name and provolatile will equal i for immutable.

[yugo-n]

What PostgreSQL function do you use to check this?

We use contain_mutable_functions that recursively searches for mutable functions within a clause and returns true if any mutable function (or operator implemented by a mutable function) is found.

[YAmikep]

We use contain_mutable_functions that recursively searches for mutable functions within a clause and returns true if any mutable function (or operator implemented by a mutable function) is found.

I see. Interesting. Thanks for the insights.

Any chance you know of a workaround to identify the part in my view that is the problem? I made the couple functions I wrote immutable so now I really don't know what it could be. 🤔

[yugo-n]

I wonder you can try to make an IMMV using a query that contains only part of the your original view definition and check which part is problematic.

[illes]

@YAmikep the usual suspects would be current date/time functions (CURRENT_TIMESTAMP, NOW(), ...) and RAND()