How to create a composite index in Postgres 9

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()

.