public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tom Lane <[email protected]>
To: Ayush Vatsa <[email protected]>
Cc: [email protected]
Subject: Re: Query regarding functions of postgres
Date: Sun, 07 Apr 2024 11:18:13 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <CACX+KaM2bePqt6Oz=t2pA=B=+i2Amc9CsdR6nmbmR84M9Xs7mg@mail.gmail.com>
References: <CACX+KaM2bePqt6Oz=t2pA=B=+i2Amc9CsdR6nmbmR84M9Xs7mg@mail.gmail.com>

Ayush Vatsa <[email protected]> writes:
> Recently I was reading about functions Immutability and security definer
> but got confused
> Whether the below two functions can be marked immutable or not
> 1. If a function has constant Raise notice inside it. Eg.

> CREATE OR REPLACE FUNCTION text_equals(text, text) RETURNS boolean AS $$BEGIN
>     RAISE NOTICE 'Comparing two texts';
>     RETURN $1 = $2;END;
> $$ LANGUAGE plpgsql;

> 2. If a function has Raise notice but extracting current user inside
> notice, although its output purely depends on its input arguments eg.

> CREATE OR REPLACE FUNCTION text_equals(text, text) RETURNS boolean AS $$BEGIN
>     RAISE NOTICE 'Current user: %', current_user;
>     RETURN $1 = $2;END;
> $$ LANGUAGE plpgsql;

The definition of "immutable" includes a statement that immutable
functions shouldn't have side-effects, so really this comes down to
whether you consider the emission of a NOTICE to be an interesting
side-effect.  It has no further effect on the computation, so you
could probably get away with considering it insignificant if you
choose to.  If you do, you are likely to observe the NOTICE coming
out at unexpected times --- for instance, it might appear once
during the planner's constant-folding phase, even though naive
interpretation of the query suggests that it should appear many
times or not at all.  But beyond that possible POLA violation,
neither of these functions will bother Postgres any.

			regards, tom lane

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Query regarding functions of postgres
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox