Thread: 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