Postgresql – Why isn’t to_char IMMUTABLE, and how can I work around it

I recommend this alternative approach to get an IMMUTABLE unaccent() function:

CREATE OR REPLACE FUNCTION f_unaccent(text)
  RETURNS text AS
$func$
SELECT public.unaccent('public.unaccent', $1)  -- schema-qualify function and dictionary
$func$  LANGUAGE sql IMMUTABLE;

Use that function for the expression index and in all queries. Detailed explanation:

• Does PostgreSQL support “accent insensitive” collations?

And do not alter the original function:

ALTER FUNCTION unaccent(text) IMMUTABLE;

.. which also solves any possible issues with the search_path: The fact that you had to schema-qualify public.unaccent() in your index definition suggests that you might not have public in your search_path.

Then, your index:

CREATE INDEX author_label_hun_gin_trgm ON address 
USING gin (f_unaccent(label_hun) gin_trgm_ops);

I did not include lower(). You can, but trigram indexes support case insensitive patterns out of the box:

• Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

Troubleshooting

If you are running all of this in the same session with the same search_path and one call works while the next reports function ... does not exist, then something is seriously broken in your database.

First make sure you are testing with a valid search_path setting:

SHOW search_path

Details in this related answer

• How to create table inside specific schema by default in Postgres?

If you diagnose breakage, switch into alarm mode and take all the necessary steps to avoid data loss (like drawing a backup before you do anything else).

A broken index for the system table pg_proc might be the cause. To reindex all system tables from the shell:

reindexdb [connection-option...] --system mydb

Normally we'd expect that when postgres was restarted, the crash recovery process would have removed files related to a rollback'ed index from the data directory.

Let's assume that it didn't work, or at least that it has to be checked manually.

The list of files that should be in the datadir can be established with a query like this:

select pg_relation_filenode(oid)
   from pg_class
  where relkind in ('i','r','t','S','m')
    and reltablespace=0
  order by 1;

reltablespace=0 is for the default tablespace. If the problematic index was created in a non-default tablespace, this 0 must be replaced by its OID in pg_tablespace.

i,r,t,S,m in relkind correspond respectively to indexes, tables, toast space, sequences, materialized views. All these objects have their data in files whose names match pg_relation_filenode(oid).

On disk, the data files are below $PGDATA/base/oid/ where oid is the oid of the database obtained by select oid,datname from pg_database. If we're not talking about the default tablespace, base is replaced by PG_version_somelabel instead.

List and sort the files matching relfilenodes in that directory:

ls | grep -E '^[0-9]+$' | sort -n > /tmp/list-of-relations.txt

(that actually keeps only the first segment for relations that are larger than 1Gb. If there are lingering segments not attached to anything they should be considered separately)

and diff that file with the result of the query above.

If there are lingering data files that do not correspond to any object that the db knows about, they should appear in that diff.