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@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
