Postgres Pro
Downloads
Home   >   mailing lists

Unexpected behaviour of a RAISE statement in an IMMUTABLE function - Mailing list pgsql-bugs

From Joel Mukuthu
Subject Unexpected behaviour of a RAISE statement in an IMMUTABLE function
Date
Msg-id CAAT35tGXUYgjjViNZ5+9nFkrOcmgE4ce+VvekjgKDy_C38RT2g@mail.gmail.com
Whole thread Raw
Responses Re: Unexpected behaviour of a RAISE statement in an IMMUTABLE function
Re: Unexpected behaviour of a RAISE statement in an IMMUTABLE function
List pgsql-bugs
Tree view
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.

pgsql-bugs by date:

Previous
From: Tom Lane
Date:
Subject: Re: BUG #17233: Incorrect behavior of DELETE command with bad subquery in WHERE clause
Next
From: PG Bug reporting form
Date:
Subject: BUG #17693: Slow performance: Much slower queries on pg_stat_all_tables since 13.4