CODEWITHSUNDEEP
postgresqlplpgsql
koyae
koyae

Reputation: 770

Is it okay to have a STABLE function that can throw exceptions?

Without being more familiar with Postgres, I would be leery of defining a function such that it might be skipped by the planner/optimizer when it would otherwise raise an exception. Should a function's volatility (STABLE, VOLATILE, or IMMUTABLE) be changed if it raises exceptions, or are these cached in the same way that return-values are?

Is there an established convention for how functions-that-can-throw should be defined?

According to the docs:

  • A VOLATILE function can do anything, including modifying the database. It can return different results on successive calls with the same arguments. The optimizer makes no assumptions about the behavior of such functions. [...]

  • A STABLE function cannot modify the database and is guaranteed to return the same results given the same arguments for all rows within a single statement. This category allows the optimizer to optimize multiple calls of the function to a single call. [...]

  • An IMMUTABLE function cannot modify the database and is guaranteed to return the same results given the same arguments forever. This category allows the optimizer to pre-evaluate the function when a query calls it with constant arguments. [...]

This is crystal-clear with regard to how return-values should behave, but exceptions (which occur before RETURN) are not addressed. ...I'm aware that other parts of the function-definition such as using STRICT could cause the planner to step over would-be exceptions, but what about here?

Are there potential edge-cases wherein a STABLE function defined to have high calling cost (which in turn uses other STABLE and/or IMMUTABLE functions to help it) might be skipped when it would ordinarily throw?

Another way of answering this would to be explain how/whether the plpgsql compiler/interpreter treats functions containing RAISE EXCEPTION any differently, noting for example whether volatility status is implicitly altered by such statements appearing in the body of a function.

Upvotes: 1

Views: 274

Answers (1)

Pavel Stehule
Pavel Stehule

Reputation: 45910

Any function (VOLATILE, STABLE, IMMUTABLE) can raise an exception. The exception stops an execution and the rollback is followed.

Currently no result is cached. In some cases, a call of STABLE or IMMUTABLE function can be replaced by their result in planning time. The IMMUTABLE function can be used if functional indexes, and then the function is not called, and value is used from index.

Upvotes: 2

Related Questions