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

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(dot)klaver(at)aklaver(dot)com