sql - 如何在 Postgres 9 中创建复合索引

Question

我试过了

create temp table test (kuupaev date, kellaaeg char(5)  ) on commit drop;
create index on test ((kuupaev||kellaaeg));     

但出现错误

ERROR:  functions in index expression must be marked IMMUTABLE

如何在 Postgres 9 中建立这个索引？

在 Postgres 8 中它起作用了。

更新

命令

create temp table test (kuupaev date, kellaaeg char(5)  ) on commit drop;
create index test on test ((kuupaev||kellaaeg));     

在

"PostgreSQL 9.3.4, compiled by Visual C++ build 1600, 32-bit"

导致错误

ERROR:  functions in index expression must be marked IMMUTABLE

但是在

"PostgreSQL 8.4.4, compiled by Visual C++ build 1400, 32-bit"

他们工作正常

Answer 1

查询pg_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 表明它是稳定的(我运行的是 9.4beta2+git 更改，但回到 9.2 也是一样)。

查看 pg_proc.h 和 git blame 的相关行，最后一次提交是 cd30728f，但是查看 git show证明它不相关。所以我用 git blame cd30728f^ -- ./src/include/catalog/pg_proc.h 跳过它。向后工作我看到变化是:

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)

其中，只有 c82d931d 真正相关。 3db6524f 将其从 volatile 更改为稳定，但这对您没有帮助。

git show c82d931d 将可能导致此行为更改的提交描述为:

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.

根据:

git branch --contains c82d931d

它是在 PostgreSQL 9.0.0 版本中引入的。

提交消息表明它应该不会对安全的索引造成任何问题，以后的更改应该不会有任何不同。

但是，仔细观察，您并没有使用 text 或 varchar。您正在使用可怕的旧 char 类型，它在 PostgreSQL 内部是 bpchar。

这里的问题要么是 char 存在依赖于语言环境的微妙行为，要么是疏忽导致此更改无法处理 char 的情况。我需要比今晚有时间更深入地挖掘源代码才能确定，坦率地说，对于 char(n) 类型我并不关心。

您应该只使用 varchar(5)。如果您需要空格填充，请考虑使用 lpad。

否则，请将此问题报告给 pgsql-bugs - 但请确保显示来自 SELECT version() 的准确 版本。