Обсуждение: BUG #13938: CAST error on Index "function must be immutable"

The following bug has been logged on the website:

Bug reference:      13938
Logged by:          Kurt Weiß
Email address:      kurt@kwnet.at
PostgreSQL version: 9.1.14
Operating system:   linux
Description:

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"

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

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

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

On Sat, Feb 13, 2016 at 1:58 AM, Kurt Wei=C3=9F <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?
>

=E2=80=8BThat may be the case but teaching PostgreSQL to understand functio=
ns 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.
=E2=80=8B