Unexpected behaviour of a RAISE statement in an IMMUTABLE function
Hi,
Given the following two functions, where the only difference between them
is that one is VOLATILE while the other is IMMUTABLE:
CREATE FUNCTION raise_exception_volatile(IN err_message text)
RETURNS void
LANGUAGE 'plpgsql'
VOLATILE
AS $BODY$
BEGIN
RAISE EXCEPTION
USING MESSAGE = err_message;
END;
$BODY$;
CREATE FUNCTION raise_exception_immutable(IN err_message text)
RETURNS void
LANGUAGE 'plpgsql'
IMMUTABLE
AS $BODY$
BEGIN
RAISE EXCEPTION
USING MESSAGE = err_message;
END;
$BODY$;
1. This raises an exception, as expected:
SELECT raise_exception_volatile('foo') WHERE true;
-- ERROR: foo
-- CONTEXT: PL/pgSQL function raise_exception_volatile(text) line 3 at RAISE
2. This does not raises an exception, as expected:
SELECT raise_exception_volatile('foo') WHERE false;
3. This raises an exception, as expected:
SELECT raise_exception_immutable('foo') WHERE true;
-- ERROR: foo
-- CONTEXT: PL/pgSQL function raise_exception_immutable(text) line 3 at
RAISE
4. This raises an exception that was surprising to me:
SELECT raise_exception_immutable('foo') WHERE false;
-- ERROR: foo
-- CONTEXT: PL/pgSQL function raise_exception_immutable(text) line 3 at
RAISE
5. This does not raises an exception, that was also surprising to me:
SELECT raise_exception_immutable(format('foo')) WHERE false;
Tested on a postgres:12.8-alpine docker container.
I couldn't find any notes about this behaviour in the postgres docs (
https://www.postgresql.org/docs/12/sql-createfunction.html and
https://www.postgresql.org/docs/12/plpgsql-errors-and-messages.html) and in
fact, the documentation on CREATE FUNCTION suggests to me that this
function should be marked as IMMUTABLE.
Is this behaviour expected?
Best regards,
Joel Mukuthu.
On Wed, Nov 23, 2022 at 9:01 AM Joel Mukuthu <jom@upright.co> wrote:
Given the following two functions, where the only difference between them
is that one is VOLATILE while the other is IMMUTABLE:
That is a huge difference
Is this behaviour expected?
Yes.
RAISE is a side-effect inducing statement, which disqualifies the function
from being IMMUTABLE.
The immutable marker gives the system permission to execute the function,
given constant arguments, once to find out what the value of that function
is during the execution of the query and make decisions based upon that
result.
David J.
Joel Mukuthu <jom@upright.co> writes:
CREATE FUNCTION raise_exception_immutable(IN err_message text)
RETURNS void
LANGUAGE 'plpgsql'
IMMUTABLE
AS $BODY$
BEGIN
RAISE EXCEPTION
USING MESSAGE = err_message;
END;
$BODY$;
A function with side-effects (like raising an error) isn't
really immutable [1]https://www.postgresql.org/docs/current/xfunc-volatility.html. We do fudge that a bit, since hardly
anything could be marked immutable if there were a strict
rule about it --- but when the primary point of the function
is to cause that side-effect, you can't fudge it.
4. This raises an exception that was surprising to me:
SELECT raise_exception_immutable('foo') WHERE false;
-- ERROR: foo
-- CONTEXT: PL/pgSQL function raise_exception_immutable(text) line 3 at
RAISE
The allegedly-immutable function is evaluated during constant folding.
5. This does not raises an exception, that was also surprising to me:
SELECT raise_exception_immutable(format('foo')) WHERE false;
format() isn't immutable, only stable; so constant-folding can't
reach the error.
regards, tom lane
[1]: https://www.postgresql.org/docs/current/xfunc-volatility.html
Thank you for the responses.
This explains the issue quite clearly to me, but I'm still a bit in the
dark as to what scenarios I should have in mind when I think of statement
side-effects in postgres. Does "side-effects" mean that a statement writes
to tables or files? Or what other scenarios should I have in mind? I'm wary
of making a similar mistake in future.
In this regard, would it make sense to try and warn users of some possible
mistakes in function definitions? I'm aware of discussions such as
https://postgrespro.com/list/thread-id/1752462 and I'm not proposing that,
but perhaps issuing a warning at compile time if there's an INSERT or RAISE
in a non-volatile function. Or would adding some more examples to the docs (
https://www.postgresql.org/docs/current/xfunc-volatility.html) suffice?
Best regards,
Joel Mukuthu
On Wed, Nov 23, 2022 at 5:20 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Joel Mukuthu <jom@upright.co> writes:
CREATE FUNCTION raise_exception_immutable(IN err_message text)
RETURNS void
LANGUAGE 'plpgsql'
IMMUTABLE
AS $BODY$
BEGIN
RAISE EXCEPTION
USING MESSAGE = err_message;
END;
$BODY$;A function with side-effects (like raising an error) isn't
really immutable [1]. We do fudge that a bit, since hardly
anything could be marked immutable if there were a strict
rule about it --- but when the primary point of the function
is to cause that side-effect, you can't fudge it.4. This raises an exception that was surprising to me:
SELECT raise_exception_immutable('foo') WHERE false;
-- ERROR: foo
-- CONTEXT: PL/pgSQL function raise_exception_immutable(text) line 3 at
RAISEThe allegedly-immutable function is evaluated during constant folding.
5. This does not raises an exception, that was also surprising to me:
SELECT raise_exception_immutable(format('foo')) WHERE false;
format() isn't immutable, only stable; so constant-folding can't
reach the error.regards, tom lane
[1] https://www.postgresql.org/docs/current/xfunc-volatility.html
--
Joel Mukuthu
Upright Solutions
jom@upright.co