Good day, hackers.
I found, than declaration of function as IMMUTABLE/STABLE is not enough to be sure
function doesn't manipulate data.
In fact, SPI checks only direct function kind, but fails to check indirect call.
Attached immutable_not.sql creates 3 functions:
- `immutable_direct` is IMMUTABLE and tries to insert into table directly.
PostgreSQL correctly detects and forbids this action.
- `volatile_direct` is VOLATILE and inserts into table directly.
It is allowed and executed well.
- `immutable_indirect` is IMMUTABLE and calls `volatile_direct`.
PostgreSQL failed to detect and prevent this DML manipulation.
Output:
select immutable_direct('immutable_direct'); psql:immutable_not.sql:28:
ERROR: INSERT is not allowed in a non-volatile function CONTEXT: SQL
statement "insert into xxx values(j)" PL/pgSQL function
immutable_direct(character varying) line 3 at SQL statement select
volatile_direct('volatile_direct'); volatile_direct -----------------
volatile_direct (1 row) select immutable_indirect('immutable_indirect');
immutable_indirect -------------------- immutable_indirect (1 row)
select * from xxx; i -------------------- volatile_direct
immutable_indirect (2 rows) Attached forbid-non-volatile-mutations.diff
add checks readonly function didn't made data manipulations. Output for
patched version: select immutable_indirect('immutable_indirect');
psql:immutable_not.sql:32: ERROR: Damn2! Update were done in a
non-volatile function CONTEXT: SQL statement "SELECT
volatile_direct(j)" PL/pgSQL function immutable_indirect(character
varying) line 3 at PERFORM I doubt check should be done this way. This
check is necessary, but it should be FATAL instead of ERROR. And ERROR
should be generated at same place, when it is generated for
`immutable_direct`, but with check of "read_only" status through whole
call stack instead of just direct function kind. ----- regards, Yura
Sokolov Postgres Professional
