Thread: Immutable functions, Exceptions and the Query Optimizer

Hello all,

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?

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).

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.

Kind regards,

Cochise Ruhulessin

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) wher=
ein 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 exceptio=
n if no entity with book_id
> exists. I'd like this function to be immutable so it can be used as a che=
ck 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

Thanks for your elaborate reply and providing me these insights on the
concept on immutable functions.

Regarding your question about what the CHECK constraint should achieve, I
had abstracted by use case into Books/Book Types, which may have caused
some vagueness. The actual use case are the following tables.

------------------------------------------------------------------------
--  Describes a type of geographical entity.
--
--  Columns:
--      gtype_id: The primary key and identifier code of type.
--      feature_class: A character indicating the feature class.
--      display_name: The display name of the type.
--      description: A description of the type.
--      is_enabled: Indicates if type is globally enabled.
--      is_system: Indicates if type is system and therefor may not
--          be modified.
--      is_deleted: Indicates if type is considered deleted.
------------------------------------------------------------------------
CREATE TABLE gtypes(
    feature_code varchar(64) NOT NULL PRIMARY KEY,
    feature_class varchar(1) NOT NULL,
    display_name varchar(128) NOT NULL,
    --administrative_depth integer NOT NULL CHECK(administrative_depth > 0)
DEFAULT 0,
    description text,
    is_enabled boolean NOT NULL DEFAULT TRUE,
    is_system boolean NOT NULL DEFAULT FALSE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    UNIQUE (feature_code, feature_class)
);


------------------------------------------------------------------------
--  Describes a geographical entity.
--
--  Columns:
--      feature_id: An unsigned long integer specifying the primary
--          key.
--      gtype_id: A string referencing a gtype instance.
--      ascii_name: The entity name as ascii.
--      display_name: A string containing the display name of entity,
--          English preferred.
--      native_name: A string containing the native name of entity.
--      valid_from: A date specifying the valid from date of entity.
--      valid_to: A date specifying the valid to date of entity.
--      primary_datasource: A string indicating the primary datasource
--          of entity.
--      is_deleted: A boolean indicating if entity is considered deleted.
--      created: A timestamp with time zone indicating the date and
--          time entity was inserted.
------------------------------------------------------------------------
CREATE SEQUENCE feature_id_seq START WITH 100000;
CREATE TABLE features(
    feature_id bigint NOT NULL PRIMARY KEY DEFAULT
nextval('feature_id_seq'),
    feature_code varchar(64) NOT NULL -- Immutable
        REFERENCES gtypes (feature_code)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
        DEFERRABLE INITIALLY DEFERRED,
    feature_name varchar(255),
    feature_name_native varchar(255),
    ascii_name varchar(512) NOT NULL,
    display_name varchar(512) NOT NULL,
    native_name varchar(512),
    abbreviation varchar(64),
    timezone varchar(64),
    valid_from date NOT NULL DEFAULT now()::date,
    valid_to date,
    created timestamp with time zone NOT NULL DEFAULT now(),
    modified timestamp with time zone NOT NULL DEFAULT now(),
    primary_datasource varchar(64) NOT NULL, -- Immutable
    is_deleted boolean NOT NULL DEFAULT FALSE,
    CHECK (valid_to >= valid_from),
    UNIQUE (feature_id, feature_code)
);

------------------------------------------------------------------------
-- trigger function to handle immutable fields on the features table.
------------------------------------------------------------------------
CREATE FUNCTION chk_features_immutable()
RETURNS TRIGGER AS
$$
BEGIN
    CASE
        WHEN OLD.feature_code != NEW.feature_code THEN
            RAISE SQLSTATE '23514' USING MESSAGE = 'features.feature_code
is immutable';
        WHEN OLD.primary_datasource != NEW.primary_datasource THEN
            RAISE SQLSTATE '23514' USING MESSAGE =
'features.primary_datasource is immutable';
        ELSE
            RETURN NEW;
    END CASE;
END;
$$
LANGUAGE 'plpgsql';


CREATE TRIGGER tr_chk_features_immutable
    BEFORE UPDATE ON features
    FOR EACH ROW
    EXECUTE PROCEDURE chk_features_immutable();


------------------------------------------------------------------------
--  Returns the feature code of a given feature.
--
--  Args:
--      feature_id bigint: The primary key of a features entity.
--
--  Returns:
--      varchar(64)
------------------------------------------------------------------------
CREATE FUNCTION features_get_feature_code(int8)
RETURNS varchar(64) AS
$$
DECLARE
    fcode varchar(64);
BEGIN
    SELECT feature_code INTO fcode FROM features WHERE feature_id = $1;
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Entity does not exist.';
    END IF;
    RETURN fcode;
END;
$$
LANGUAGE 'plpgsql' IMMUTABLE;


The "features" table contains countries, administrative divisions, cities,
postal codes, landmarks, sights, rivers, mountains; any kind of
geographical feature.


There is also a table called "persons" (irrelevant fields omitted):

CREATE TABLE persons(
    person_id int8 NOT NULL PRIMARY KEY,
    place_of_birth_id int8
        REFERENCES features (feature_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
        INITIALLY IMMEDIATE,
    CHECK (features_get_feature_code(place_of_birth_id) ~ 'PC.*|ADM.*|PP.*')
);


The CHECK constraint should achieve that "persons.place_of_birth_id" is
always a country, or a (first_order) adminitrative division, or a city
(which is defined by "features.gtype_id").

Though this could be done by creating a multi-column foreign key on
("features.feature_id","features.gtype_id"), this would violate the
principles of normalization.

Of course this could also be achieved by a TRIGGER, but that seems a little
redundant to me.

Kind regards,

Cochise Ruhulessin

Cochise Ruhulessin wrote:
> Regarding your question about what the CHECK constraint should achieve, I=
 had abstracted by use case
> into Books/Book Types, which may have caused some vagueness. The actual u=
se case are the following
> tables.

[...]
=20
> CREATE TABLE persons(
>     person_id int8 NOT NULL PRIMARY KEY,
>     place_of_birth_id int8
>         REFERENCES features (feature_id)
>         ON UPDATE CASCADE
>         ON DELETE RESTRICT
>         INITIALLY IMMEDIATE,
>     CHECK (features_get_feature_code(place_of_birth_id) ~ 'PC.*|ADM.*|PP.=
*')
> );
>=20
>=20
> The CHECK constraint should achieve that "persons.place_of_birth_id" is a=
lways a country, or a
> (first_order) adminitrative division, or a city (which is defined by "fea=
tures.gtype_id").
>=20
> Though this could be done by creating a multi-column foreign key on
> ("features.feature_id","features.gtype_id"), this would violate the princ=
iples of normalization.

True; but if you don't mind that, it would be a nice solution
since you already have a unique index on features(feature_id, feature_code)=
.

> Of course this could also be achieved by a TRIGGER, but that seems a litt=
le redundant to me.

I think a trigger is the best solution here.
Why is it more redundant than a CHECK constraint?
Both will do about the same thing, with the advantage
that the trigger solution would be correct and won't
give you any trouble at dump/reload time.

Yours,
Laurenz Albe