pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedUnexpected behaviour of a RAISE statement in an IMMUTABLE function
4+ messages / 3 participants
[nested] [flat]
* Unexpected behaviour of a RAISE statement in an IMMUTABLE function
@ 2022-11-23 14:12 Joel Mukuthu <jom@upright.co>
2022-11-23 16:14 ` Re: Unexpected behaviour of a RAISE statement in an IMMUTABLE function David G. Johnston <david.g.johnston@gmail.com>
2022-11-23 16:20 ` Re: Unexpected behaviour of a RAISE statement in an IMMUTABLE function Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 4+ messages in thread
From: Joel Mukuthu @ 2022-11-23 14:12 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Unexpected behaviour of a RAISE statement in an IMMUTABLE function
2022-11-23 14:12 Unexpected behaviour of a RAISE statement in an IMMUTABLE function Joel Mukuthu <jom@upright.co>
@ 2022-11-23 16:14 ` David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: David G. Johnston @ 2022-11-23 16:14 UTC (permalink / raw)
To: Joel Mukuthu <jom@upright.co>; +Cc: pgsql-bugs@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Unexpected behaviour of a RAISE statement in an IMMUTABLE function
2022-11-23 14:12 Unexpected behaviour of a RAISE statement in an IMMUTABLE function Joel Mukuthu <jom@upright.co>
@ 2022-11-23 16:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-11-25 09:56 ` Re: Unexpected behaviour of a RAISE statement in an IMMUTABLE function Joel Mukuthu <jom@upright.co>
1 sibling, 1 reply; 4+ messages in thread
From: Tom Lane @ 2022-11-23 16:20 UTC (permalink / raw)
To: Joel Mukuthu <jom@upright.co>; +Cc: pgsql-bugs@lists.postgresql.org
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
> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Unexpected behaviour of a RAISE statement in an IMMUTABLE function
2022-11-23 14:12 Unexpected behaviour of a RAISE statement in an IMMUTABLE function Joel Mukuthu <jom@upright.co>
2022-11-23 16:20 ` Re: Unexpected behaviour of a RAISE statement in an IMMUTABLE function Tom Lane <tgl@sss.pgh.pa.us>
@ 2022-11-25 09:56 ` Joel Mukuthu <jom@upright.co>
0 siblings, 0 replies; 4+ messages in thread
From: Joel Mukuthu @ 2022-11-25 09:56 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org
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
> > 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
>
--
Joel Mukuthu
Upright Solutions
jom@upright.co
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2022-11-25 09:56 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-11-23 14:12 Unexpected behaviour of a RAISE statement in an IMMUTABLE function Joel Mukuthu <jom@upright.co>
2022-11-23 16:14 ` David G. Johnston <david.g.johnston@gmail.com>
2022-11-23 16:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-11-25 09:56 ` Joel Mukuthu <jom@upright.co>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox