PostgreSQL 11 returns null instead of exception if previous CTE does access relation or error string does not contain parameter

Question

In Postgres 11.4, I have a simple function for throwing exceptions. The purpose of this function is to enable me to throw an exception from within a vanilla SQL SELECT (if this is the most elegant solution is a different matter)

CREATE OR REPLACE FUNCTION public.throw_error_wrapper("errorText" text)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
BEGIN
    RAISE EXCEPTION '%',$1;
END;
$function$

This function is called from within another function which (simplified) looks like this:

CREATE OR REPLACE FUNCTION public.my_function("myParam" integer)
 RETURNS void
 LANGUAGE sql
AS $function$

WITH my_cte AS (
  SELECT 'foo'
)
SELECT 
    throw_error_wrapper('my_function throws an error when called with parameter: ' || $1 || ' Please try again.')
FROM my_cte     

$function$

So, it should always throw an error message which includes the value of the parameter $1. Now if I call that function like SELECT my_function(42); everything works as expected. I get the expected error

my_function throws an error when called with parameter: 42 Please try again. CONTEXT: PL/pgSQL function throw_error_wrapper(text) line 3 at RAISE SQL function "my_function" statement 1

Now let's create a dummy table with one column:

CREATE TABLE IF NOT EXISTS my_relation (my_column text);

And then replace the dummy CTE SELECT 'foo' with SELECT my_column FROM my_relation so that my_function now looks like this:

CREATE OR REPLACE FUNCTION public.my_function("myParam" integer)
 RETURNS void
 LANGUAGE sql
AS $function$

WITH my_cte AS (
  SELECT my_column FROM my_relation
)
SELECT 
    throw_error_wrapper('my_function throws an error when called with parameter: ' || $1 || ' Please try again.')
FROM my_cte     

$function$

Still, I expect to get an error when now again doing SELECT my_function(42); However, I do not get an error, but simply an empty result.

Now if I remove the parameter $1 from the error message so that my_function consists now of the code below

CREATE OR REPLACE FUNCTION public.my_function("myParam" integer)
 RETURNS void
 LANGUAGE sql
AS $function$

WITH my_cte AS (
  SELECT my_column FROM my_relation
)
SELECT 
    throw_error_wrapper('my_function throws an error when called with parameter: ' || ' Please try again.')
FROM my_cte     

$function$

I get again the expected error (this time, of course, without the value of $1).

That really confuses me. Why do the CTE and the parameter concatenation prevent the function from working as expected? Why is no error thrown when it should?

Your calling function is not IMMUTABLE it queries the database. PostgreSQL might be skipping calling the query because it can see you aren't doing anything with the result. Mark your function as STABLE if it only reads or VOLATILE if it can change between repeated calls or updates the database. — Richard Huxton
– Richard Huxton, Commented Jul 23, 2019 at 9:23
@Richard Huxton You are right, that defining my_function as IMMUTABLE is not correct. However, trying the values to STABLE or VOLATILE did not change anything for me. — cis
– cis, Commented Jul 23, 2019 at 9:26

Łukasz Kamiński · Accepted Answer · 2019-07-23 11:30:14Z

It is just my guess, will delete if someone gives better explanation. I also think that this question fits better on https://dba.stackexchange.com/

PostgreSQL does some kind of optimization when it sees IMMUTABLE function used within query with constant parameter and evaluates it before executing query and then treats its result as immutable value. Something that can be useful when for example you are using CHECK constraint on old school inherit partitions and want to filter out partitions by some value that could be result of function - it will only work if that function is immutable, most likely meaning that it is being executed during planning, so that PostgreSQL knows what partitions to search before it actually does searching. That is why you are getting exception thrown even tho there are no rows returned and thus that function should have never executed.

Try to EXPLAIN that query throwing exception, it should just output query plan, but instead it will throw exception - because it decided to execute that function regardless just to get that plan.

test=# EXPLAIN
test-# WITH my_cte AS (
test(#   SELECT my_column FROM my_relation
test(# )
test-# SELECT
test-#     throw_error_wrapper('my_function throws an error when called with parameter: ' || 42 || ' Please try again.')
test-# FROM my_cte;
ERROR:  my_function throws an error when called with parameter: 42 Please try again.
CONTEXT:  PL/pgSQL function throw_error_wrapper(text) line 3 at RAISE

I'm claiming optimization, because if you change function public.throw_error_wrapper("errorText" text) from IMMUTABLE to STABLE or VOLATILE it will stop throwing errors when no rows are returned.

Shouldn't function with constant parameter behave the same way? PostgreSQL should know that it will always execute public.throw_error_wrapper(42), so it should optimize it in the same way. And this is true for PL/pgSQL language, but not so much in case of SQL. It can be illustrated with use of partitions and foreign tables. In example below you will see partion created in a way that it cannot be accessed for 2 reasons: no user mapping defined and that foreign server does not exists. It will always fail if access is attempted.

CREATE SERVER test_srv FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (dbname 'test', host 'fake_host');

CREATE TABLE IF NOT EXISTS my_relation (dat date, my_column text);

CREATE FOREIGN TABLE my_relation_partition_1
(constraint dat_chk check(dat between '2019-06-01' and '2019-06-30'))
INHERITS (my_relation) SERVER test_srv;

Did not scan foreign table:

test=# explain SELECT * FROM my_relation WHERE dat = '2019-05-04';
                            QUERY PLAN
------------------------------------------------------------------
 Append  (cost=0.00..0.01 rows=1 width=36)
   ->  Seq Scan on my_relation  (cost=0.00..0.00 rows=1 width=36)
         Filter: (dat = '2019-05-04'::date)
(3 rows)

test=# SELECT * FROM my_relation WHERE dat = '2019-05-04';
 dat | my_column
-----+-----------
(0 rows)

Attempted scan of foreign table:

test=# explain SELECT * FROM my_relation WHERE dat = '2019-06-04';
                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Append  (cost=0.00..127.24 rows=8 width=36)
   ->  Seq Scan on my_relation  (cost=0.00..0.00 rows=1 width=36)
         Filter: (dat = '2019-06-04'::date)
   ->  Foreign Scan on my_relation_partition_1  (cost=100.00..127.20 rows=7 width=36)
(4 rows)

test=# SELECT * FROM my_relation WHERE dat = '2019-06-04';
ERROR:  user mapping not found for "postgres"

If you pass that date in a way that is not IMMUTABLE, for example as a result of non immutable function, then it will not be used to filter out partitions by CHECK constraint. So here, despite us knowing that it should not touch June 2019 partition, it still does, because result of date_trunc(..) is not immutable/constant.

test=# explain SELECT * FROM my_relation WHERE dat = date_trunc('month', '2019-05-04'::date);
                                            QUERY PLAN
---------------------------------------------------------------------------------------------------
 Append  (cost=0.00..161.23 rows=8 width=36)
   ->  Seq Scan on my_relation  (cost=0.00..0.00 rows=1 width=36)
         Filter: (dat = date_trunc('month'::text, ('2019-05-04'::date)::timestamp with time zone))
   ->  Foreign Scan on my_relation_partition_1  (cost=100.00..161.19 rows=7 width=36)
         Filter: (dat = date_trunc('month'::text, ('2019-05-04'::date)::timestamp with time zone))
(5 rows)

Okay, so now your query with concatenation using immutable value:

test=# WITH my_cte AS (
test(#   SELECT my_column FROM my_relation WHERE dat = '2019-05-04'
test(# )
test-# SELECT
test-#     throw_error_wrapper('my_function throws an error when called with parameter: ' || random()::int2 || ' Please try again.')
test-# FROM my_cte;
 throw_error_wrapper
---------------------
(0 rows)

No exception thrown. Now that we have this, lets see how function will behave.

CREATE OR REPLACE FUNCTION public.my_function(adat date, "myParam" integer)
 RETURNS void
 LANGUAGE sql
 SECURITY DEFINER
AS $function$
WITH my_cte AS (
  SELECT my_column FROM my_relation where dat = $1
)
SELECT 
    throw_error_wrapper('my_function throws an error when called with parameter: ' || $2 || ' Please try again.')
FROM my_cte     
$function$;

test=# SELECT * FROM public.my_function('2019-05-03', 42);
ERROR:  user mapping not found for "postgres"
CONTEXT:  SQL function "my_function" statement 1

As suspected, it did not find function parameter to be immutable and attempted to access foreign table. Just like that function throwing exception did not get (in the eyes of PostgreSQL planner) immutable value in your attempts.

Now this is something I discovered some time ago encountering this problem with partitions and function slow down due to accessing too many tables - if you change function from language SQL to plpgsql it will suddenly treat function parameters as immutable.

Change definition slightly:

CREATE OR REPLACE FUNCTION public.my_function2(adat date, "myParam" integer)
 RETURNS table(t text)
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$
begin
WITH my_cte AS (
  SELECT my_column FROM my_relation where dat = $1
)
SELECT 
    throw_error_wrapper('my_function throws an error when called with parameter: ' || $2 || ' Please try again.')
FROM my_cte;
END;
$function$;

test=# SELECT * FROM public.my_function2('2019-05-03', 42);
ERROR:  my_function throws an error when called with parameter: 42 Please try again.
CONTEXT:  PL/pgSQL function throw_error_wrapper(text) line 3 at RAISE
SQL statement "WITH my_cte AS (
  SELECT my_column FROM my_relation where dat = $1
)
SELECT
    throw_error_wrapper('my_function throws an error when called with parameter: ' || $2 || ' Please try again.')
FROM my_cte"
PL/pgSQL function my_function2(date,integer) line 3 at SQL statement

And what do you know, it not only ignored that foreign table/partion, it also passed 42 as immutable value to your exception throwing function.

As far as functions and language SQL go I think that implementation of that is most likely limited. Not that long time ago you couldn't even use parameter names and only placeholders $1, $2, $3 [..] were available, so maybe there is some bug in there with those parameters or it is like that so that query planner can more easily integrate contents of those functions into query that executes them.

Collectives™ on Stack Overflow

PostgreSQL 11 returns null instead of exception if previous CTE does access relation or error string does not contain parameter

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related