Unexpected behaviour of a RAISE statement in an IMMUTABLE function

From: Joel Mukuthu <jom(at)upright(dot)co>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Unexpected behaviour of a RAISE statement in an IMMUTABLE function
Date: 2022-11-23 14:12:43
Message-ID: CAAT35tGXUYgjjViNZ5+9nFkrOcmgE4ce+VvekjgKDy_C38RT2g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message PG Bug reporting form 2022-11-23 14:46:47 BUG #17693: Slow performance: Much slower queries on pg_stat_all_tables since 13.4
Previous Message Tom Lane 2022-11-22 23:49:50 Re: BUG #17233: Incorrect behavior of DELETE command with bad subquery in WHERE clause