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.klaver@aklaver.com