Creating index on concatenated char columns fails is Postgres 9 (regression)

[Andrus]

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.

[Adrian Klaver]

Post by Andrus
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"
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

Post by Andrus
Posted also in
http://stackoverflow.com/questions/26161561/how-to-create-composite-index-in-postgres-9
Andrus.