Início / dba / Perguntas / 144839
Accepted
andilabs
Asked: 2016-07-26 07:44:16 +0800 CST

CONCAT usado em INDEX causa ERRO: as funções na expressão de índice devem ser marcadas como IMUTÁVEIS

  • 772

Estou enfrentando o seguinte erro:

ERROR:  functions in index expression must be marked IMMUTABLE

Ao tentar criar um índice como este:

CREATE INDEX full_phone_number ON orders_clientphone (concat(area_code, phone));

Por outro lado, ao usar sintaxe alternativa para concatenação:

CREATE INDEX full_phone_number ON orders_clientphone ((area_code || phone));

Postgres está muito bem com isso.
Ambas as colunas são definidas como character varying(256).

postgresql index

3 respostas

  1. Best Answer

    O fator decisivo para Postgres é que a função concat()é definida como estável e não imutável no catálogo do sistema pg_proc:

    SELECT proname, provolatile, proargtypes, proargtypes[0]::regtype AS argtype, prosrc
FROM pg_proc
WHERE proname = 'concat';

proname | provolatile | proargtypes| argtype | prosrc
--------+-------------+------------+---------+-----------
concat  | s           | 2276       | "any"   | text_concat

    O manual sobre pg_proc.provolatile:

    provolatileinforma se o resultado da função depende apenas de seus argumentos de entrada ou é afetado por fatores externos. É ipara funções "imutáveis", que sempre fornecem o mesmo resultado para as mesmas entradas. É spara funções "estáveis", cujos resultados (para entradas fixas) não mudam dentro de uma varredura.

    Também adicionei os tipos de argumento da função ( "any") para conectar às respostas de @dezso e @jjanes, que fornecem a lógica por trás da decisão de tornar essa função apenas estável. E o nome da função interna ( text_concat).

    Aqui está uma questão relacionada para ilustrar por que a imutabilidade das expressões de índice é uma condição sine qua non:

    Quanto ao uso do operador ||:

        SELECT o.oprname, o.oprleft::regtype, o.oprright::regtype, o.oprcode, p.provolatile
    FROM   pg_operator o
    JOIN   pg_proc     p ON p.oid = o.oprcode
    WHERE  oprname = '||';


 oprname |   oprleft   |  oprright   |     oprcode     | provolatile
---------+-------------+-------------+-----------------+-------------
 ||      | anyarray    | anyelement  | array_append    | i
 ||      | anyelement  | anyarray    | array_prepend   | i
 ||      | anyarray    | anyarray    | array_cat       | i
 ||      | text        | text        | textcat         | i
 ||      | bit varying | bit varying | bitcat          | i
 ||      | bytea       | bytea       | byteacat        | i
 ||      | text        | anynonarray | textanycat      | s
 ||      | anynonarray | text        | anytextcat      | s
 ||      | tsvector    | tsvector    | tsvector_concat | i
 ||      | tsquery     | tsquery     | tsquery_or      | i
 ||      | jsonb       | jsonb       | jsonb_concat    | i

    A função a ser usada internamente depende do tipo de dado real dos operandos. A definição de um operador inclui os tipos de dados dos operandos no Postgres. Todas as funções são diferentes e também diferentes das text_concatanteriores. ||também é estável, quando um dos operadores é anynonarray. As coisas não são tão triviais atrás das cortinas.

    character varying(256)(como qualquer varcharvariante) é binário-coercível para textque o padrão de resolução do tipo de funçãotext seja .

    • 9

  2. Para preencher a resposta de dezso, aqui está um exemplo da mesma entrada gerando saídas diferentes, dependendo do estado do banco de dados:

    select concat(1.0000000000003::float8,56);

1.000000000000356

set extra_float_digits TO 3;

select concat(1.0000000000003::float8,56);

1.0000000000002999856
    • 4

  3. Acredito concat, definido na documentação da seguinte forma:

    FUNCTION                                  RETURN TYPE  DESCRIPTION
-----------------------------------------------------------------------------------
concat(str "any" [, str "any" [, ...] ])  text         Concatenate the text repre-
                                                       sentations of all the arguments. 
                                                       NULL arguments are ignored.

    não é imutável porque a representação do texto pode depender das configurações do banco de dados, como data ou carimbo de data/hora .

    • 3

relate perguntas