Thread: Creating index on concatenated char columns fails is Postgres 9 (regression)
Steps to reproduce:
Run commands
create temp table test (kuupaev date, kellaaeg char(5) ) on commit drop;
create index test on test ((kuupaev||kellaaeg));
in
"PostgreSQL 9.3.4, compiled by Visual C++ build 1600, 32-bit"
Observed result:
ERROR: functions in index expression must be marked IMMUTABLE
In
"PostgreSQL 8.4.4, compiled by Visual C++ build 1400, 32-bit"
those commands work OK.
I need this index to speed up query
SELECT
max( kuupaev||kellaaeg )
from ALGSA
where laonr=?nlaonr and kuupaev <=?prmLOPP and kuupaev||kellaaeg <= ?someparam
How to fix or other way to speed this query?
Posted also in http://stackoverflow.com/questions/26161561/how-to-create-composite-index-in-postgres-9
Andrus.
Re: Creating index on concatenated char columns fails is Postgres 9 (regression)
From
Adrian Klaver
Date:
On 10/02/2014 01:49 PM, Andrus wrote: > Steps to reproduce: > Run commands > create temp table test (kuupaev date, kellaaeg char(5) ) on commit > drop; > create index test on test ((kuupaev||kellaaeg)); > in > "PostgreSQL 9.3.4, compiled by Visual C++ build 1600, 32-bit" > Observed result: > ERROR: functions in index expression must be marked IMMUTABLE > In > "PostgreSQL 8.4.4, compiled by Visual C++ build 1400, 32-bit" > those commands work OK. > I need this index to speed up query > SELECT > max( kuupaev||kellaaeg ) > from ALGSA > where laonr=?nlaonr and kuupaev <=?prmLOPP and kuupaev||kellaaeg <= > ?someparam > How to fix or other way to speed this query? My guess is you are seeing the result of this commit: http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5a86e5e1930d95f495a134000512d6ca22064338 which was back ported to 8.x but after 8.4.4. As I recall there has been a general tightening up of checks for VOLATILE vs IMMUTABLE. For a possible solution see here: http://dba.stackexchange.com/questions/71133/creating-unique-constraint-to-be-validated-from-input > Posted also in > http://stackoverflow.com/questions/26161561/how-to-create-composite-index-in-postgres-9 > Andrus. -- Adrian Klaver adrian.klaver@aklaver.com