How to create a composite index in Postgres 9
I tried
create temp table test (kuupaev date, kellaaeg char(5) ) on commit drop;
create index on test ((kuupaev||kellaaeg));
but got an error
ERROR: functions in index expression must be marked IMMUTABLE
How is this index in Postgres 9?
In Postgres 8, it worked.
Update
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"
cause an error
ERROR: functions in index expression must be marked IMMUTABLE
but in
"PostgreSQL 8.4.4, compiled by Visual C++ build 1400, 32-bit"
they work fine
source to share
The query pg_operator
shows that the corresponding function is implementing the operator anytextcat
.
regress=> select l.typname, r.typname, o.oprcode
from pg_operator o
inner join pg_type l on (o.oprleft = l.oid)
inner join pg_type r on (o.oprright = r.oid)
where oprname = '||';
typname | typname | oprcode
-------------+-------------+-----------------
anyarray | anyelement | array_append
anyelement | anyarray | array_prepend
anyarray | anyarray | array_cat
text | text | textcat
varbit | varbit | bitcat
bytea | bytea | byteacat
text | anynonarray | textanycat
anynonarray | text | anytextcat
tsvector | tsvector | tsvector_concat
tsquery | tsquery | tsquery_or
(10 rows)
\df+ anytextcat
shows that it is stable
(I am committing 9.4beta2 + git changes, but it is the same as in 9.2).
Pulling up the relevant line pg_proc.h
and git blame
, the last commit was cd30728f, but a look git show
shows that it doesn't matter. So I'll skip it with git blame cd30728f^ -- ./src/include/catalog/pg_proc.h
. Working backwards, I can see that the changes were:
cd30728f - Allow LEAKPROOF functions for better performance of security views. (9.2)
3db6524f - Mark some more I/O-conversion-invoking functions as stable not volatile. (9.2)
8f9fe6ed - Add notion of a "transform function" that can simplify function calls. (9.2)
c82d931d - Fix the volatility marking of textanycat() and anytextcat() (9.0)
Of these, only c82d931d is really suitable. 3db6524f changes it from volatile to stable, but that won't help you.
git show c82d931d
describes the commit that probably caused this behavior to change:
commit c82d931dd180965a9a0c06acc764404f91de8170 Author: Tom Lane <tgl@sss.pgh.pa.us> Date: Thu May 27 16:20:11 2010 +0000 Fix the volatility marking of textanycat() and anytextcat(): they were marked immutable, but that is wrong in general because the cast from the polymorphic argument to text could be stable or even volatile. Mark them volatile for safety. In the typical case where the cast isn't volatile, the planner will deduce the correct expression volatility after inlining the function, so performance is not lost. The just-committed fix in CREATE INDEX also ensures this won't break any indexing cases that ought to be allowed. Per discussion, I'm not bumping catversion for this change, as it doesn't seem critical enough to force an initdb on beta testers.
In accordance with:
git branch --contains c82d931d
it was introduced in the PostgreSQL 9.0.0 release.
The commit message suggests that it shouldn't cause any issues with safe indexes, and a subsequent change there shouldn't make any difference.
Looking closer, however, you are not using text
or varchar
. You are using the awful old type char
that bpchar
PostgreSQL has internally .
The problem here would be that there is subtle char
locale-dependent behavior with , or that oversight has caused this change to not handle the case for char
. I will need to dig a lot deeper into the sources than I have time for tonight to be sure, and frankly for the type char(n)
I don't really care about.
You should probably just use varchar(5)
. If you need to fill in a gap, think about it with lpad
.
If not, please report the issue to pgsql-bugs, but make sure you show the exact versions from SELECT version()
.
source to share