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:
/messages/by-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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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 use case are the following
tables.

[...]

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.

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 little 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general