BUG #13938: CAST error on Index "function must be immutable"

kurt@kwnet.at writes:

CREATE TABLE ud_data (
val TEXT,
);

CREATE INDEX ud_data_FLOAT ON ud_data(CAST(val AS FLOAT)) WHERE i_type=3;
--accepted as valid.

CREATE INDEX ud_data_TIME ON ud_data(CAST(val AS INTERVAL)) WHERE
i_type=10;
--results in error "functions in index expression must be marked IMMUTABLE"

This is not a bug. The cast from text to interval isn't immutable because
its results may vary depending on the IntervalStyle setting.

--the same error when casting to TIMESTAMP (with or without time zone)

Same, although it's DateStyle and/or TimeZone that affect this.

regards, tom lane

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

but the workaround is running well and get's rated as "IMMUTABLE" though
returning timestamp and interval...
So maybe the allowness for setting the function in the workaround to
immutable will be the bug?

--WORKAROUND START:

CREATE OR REPLACE FUNCTION ud_data_timestamp1(val TEXT) RETURNS
TIMESTAMP WITH TIME ZONE AS $$
BEGIN
RETURN CAST($1 AS TIMESTAMP WITH TIME ZONE);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION ud_data_timestamp2(val TEXT) RETURNS
TIMESTAMP WITHOUT TIME ZONE AS $$
BEGIN
RETURN CAST($1 AS TIMESTAMP WITHOUT TIME ZONE);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION ud_data_interval(val TEXT) RETURNS INTERVAL AS $$
BEGIN
RETURN CAST($1 AS INTERVAL);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE INDEX ud_data_DT_ZONE ON ud_data(ud_data_timestamp1(val)) WHERE
i_type IN(5,6) AND (i_param & 4)=0;
CREATE INDEX ud_data_DT_GMT ON ud_data(ud_data_timestamp2(val)) WHERE
i_type IN(5,6) AND (i_param & 4)=4;
CREATE INDEX ud_data_TIME ON ud_data(ud_data_interval(val)) WHERE i_type=10;

--WORKAROUND END

Am 09.02.2016 um 18:15 schrieb Tom Lane:

kurt@kwnet.at writes:

CREATE TABLE ud_data (
val TEXT,
);
CREATE INDEX ud_data_FLOAT ON ud_data(CAST(val AS FLOAT)) WHERE i_type=3;
--accepted as valid.
CREATE INDEX ud_data_TIME ON ud_data(CAST(val AS INTERVAL)) WHERE
i_type=10;
--results in error "functions in index expression must be marked IMMUTABLE"

This is not a bug. The cast from text to interval isn't immutable because
its results may vary depending on the IntervalStyle setting.

--the same error when casting to TIMESTAMP (with or without time zone)

Same, although it's DateStyle and/or TimeZone that affect this.

regards, tom lane

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

On Sat, Feb 13, 2016 at 1:58 AM, Kurt Weiß <kurt@kwnet.at> wrote:

but the workaround is running well and get's rated as "IMMUTABLE" though
returning timestamp and interval...
So maybe the allowness for setting the function in the workaround to
immutable will be the bug?

​That may be the case but teaching PostgreSQL to understand functions to
that degree is extremely challenging and of marginal benefit. If we ever
did get that far the user-specification of volatility would just go away -
but as things stand now you need to be truthful and help the system
understand what level of volatility your function requires.

David J.
​