Postgres full text search error when using setweight()

Question

I have PostgreSQL 9.6.8 running on Fedora 27 64bit. When i execute this query:

UPDATE tbl SET textsearchable_index_col = 
setweight(to_tsvector('french', coalesce("col1",'')), 'D') || 
setweight(to_tsvector('french', coalesce("col2",'')), 'D');

I get this error:

ERROR:  cache lookup failed for function 3625

********** Error **********

ERROR: cache lookup failed for function 3625
SQL state: XX000

but when I execute either:

UPDATE tbl SET textsearchable_index_col = 
setweight(to_tsvector('french', coalesce("col1",'')), 'D');

or

UPDATE tbl SET textsearchable_index_col = 
setweight(to_tsvector('french', coalesce("col2",'')), 'D');

I get:

Query returned successfully: 0 rows affected, 11 msec execution time.

My question is why does it work for either column individually but it does not work when together? This link shows that it should be possible to use both columns in the same query (at the end of section 12.3.1).

Edit: here is what the system returns for Laurenz's queries. The first query returns

 oprname | oprleft  | oprright | oprcode     
---------+----------+----------+----------
 ||      | tsvector | tsvector | 3625

The second query returns an empty result set.

score 1 · Accepted Answer · answered Apr 14 '18 at 18:58

1

Your database is corrupted, and you are lacking the function tsvector_concat which is the function behind the || operator.

This is how it should look on a healthy system:

SELECT oprname, oprleft::regtype, oprright::regtype, oprcode
FROM pg_operator
WHERE oid = 3633;

 oprname | oprleft  | oprright |     oprcode     
---------+----------+----------+-----------------
 ||      | tsvector | tsvector | tsvector_concat
(1 row)

SELECT proname, proargtypes::regtype[], prosrc
FROM pg_proc
WHERE oid = 3625;

     proname     |        proargtypes        |     prosrc      
-----------------+---------------------------+-----------------
 tsvector_concat | [0:1]={tsvector,tsvector} | tsvector_concat
(1 row)

The second part is missing in your case.

You should restore from a backup.

Try to figure out how you got into this mess so that you can avoid it in the future.

answered Apr 14 '18 at 18:58

Laurenz Albe

209,280
17
206
263

Thank you Laurenz. See my updated O.P. for the returns of your queries. My results are indeed different, but what I find interesting is that the first query returns 3625 for oprcode. Could it be because I have a db version other than yours? Just before I had this issue I moved some PostGIS functions to a different schema and then back to the public schema. Maybe that messed up my full text functions. – corneliu Apr 15 '18 at 02:08
Just to follow up. I reinstalled the db and it works as it should (I didn't have a backup, because the db did not contain anything important, it was just for developing a java application). Thank you again for your help. – corneliu Apr 15 '18 at 05:06
The result for your queries is just what I expected: The operator function is 3625, but that function does not exist. What would worry me in your place is how your database got corrupted. If you don't know that, you don't know that it cannot happen again. – Laurenz Albe Apr 15 '18 at 12:14
Yes. It's true that it can happen again but at least now I know how to fix it. – corneliu Apr 15 '18 at 20:15

Postgres full text search error when using setweight()

1 Answers1