Re: Immutable functions, Exceptions and the Query Optimizer

Cochise Ruhulessin wrote:
> If an immutable function raises an exception, is that exception cached by the query optimizer? Or does
> it only cache in the case that a function actually returns a value?

If an error occurs, query processing is terminated,
so nothing needs to be cached.

PostgreSQL doesn't cache function results, immutable
or not:

CREATE OR REPLACE FUNCTION i(integer) RETURNS integer
LANGUAGE plpgsql IMMUTABLE STRICT AS
$$BEGIN
RAISE NOTICE 'Called for %', $1;
RETURN $1;
END$$;

WITH t(t) AS (VALUES (1), (2), (1))
SELECT i(t) FROM t;

NOTICE: Called for 1
NOTICE: Called for 2
NOTICE: Called for 1
i
---
1
2
1
(3 rows)

The difference is that an immutable function, when applied
to a constant, can be evaluated at query planning time:

WITH t(t) AS (VALUES (1), (2), (1))
SELECT i(42) FROM t;

NOTICE: Called for 42
i
----
42
42
42
(3 rows)

Notice that the function was evaluated only once.

> The use case is a table books(book_id NOT NULL PRIMARY KEY, type_id) wherein type_id is considered
> immutable (enforced with a trigger).

No database object is immutable (note that "immutable"
means something else here than in the case of a function,
so don't mix those up).

You can, for example, drop the table.

Any function that SELECTs from the database cannot
be immutable.

> The function f() must return type_id given book_id, and raise an exception if no entity with book_id
> exists. I'd like this function to be immutable so it can be used as a check constraint.

The documentation says in
http://www.postgresql.org/docs/current/static/sql-createtable.html

Currently, CHECK expressions cannot contain subqueries nor
refer to variables other than columns of the current row.

CHECK constraints are only verified when the value is modified,
so nothing can prevent the constraint from getting violated
after the row has been added.

It might, for example, lead to problems during dump/restore,
as seen here:
http://www.postgresql.org/message-id/29488.1332857456@sss.pgh.pa.us

What should the CHECK constraint achieve?
Maybe it can be expressed with a BEFORE trigger or some
other construct.

Yours,
Laurenz Albe