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.
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 |