Re: [ADMIN] Tsearch2 removal bit me - how to correct?

On 10/15/2017 10:47, Tom Lane wrote:

Karl Denninger <karl@denninger.net> writes:

Attempting to migrate to 10.0 from 9.6, which has had "tsearch2" loaded
for backward compatibility since "forever" (which I suspect is no longer
really required as the capability is internal and has been for a long
time) results in this blowup logged in loadable_libraries.txt:
could not load library "$libdir/tsearch2": ERROR:  could not access file
"$libdir/tsearch2": No such file or directory

So what's causing this to be requested on startup and how do I kill it? 

Look for '$libdir/tsearch2' in the pg_proc.probin column of each
DB in the installation ...
		regards, tom lane

That worked on one of my clusters, but on another I'm seeing entries in pg_catalog, and can't remove them.

pgsql=# \c fapforum
You are now connected to database "fapforum" as user "pgsql".
fapforum=# select proname from pg_proc where probin like '%tsearch2%';
    proname
----------------
 prsd_end
 prsd_lextype
 prsd_start
 thesaurus_init
(4 rows)

fapforum=# \df prsd_end;
                            List of functions
   Schema   |   Name   | Result data type | Argument data types |  Type
------------+----------+------------------+---------------------+--------
 pg_catalog | prsd_end | void             | internal            | normal
(1 row)

fapforum=# drop function prsd_end(internal);
ERROR:  cannot drop function prsd_end(internal) because it is required by the database system

The others I can drop, but I wind up with these in each database... any idea where the piece is that the original package stuffed in that I need to get rid of so I can kill these?

This has also left me with an interesting other issue that is likely related.

The removal of the functions of course necessitated the removal of the gin indices I was using.  Ok, I understand that.  But the 9.6 documentation says this is valid:

SELECT to_tsvector('english','in the list of stop words');

But....

ticker=# select to_tsvector('english', 'in the list of stop words');
ERROR:  invalid input syntax for type oid: "english"
LINE 1: select to_tsvector('english', 'in the list of stop words');

Interestingly enough if I don't specify the language....
ticker=# select to_tsvector('in the list of stop words');
        to_tsvector
----------------------------
 'list':3 'stop':5 'word':6
(1 row)

Which is correct.  So it appears the internal functions are both there and working.

But, attempting to re-create the indices, omitting the language so it defaults, fails with a complaint that the operand function must be immutable.

ticker=# create index idx_message on post using gin(to_tsvector( message || ' ' || message2 || ' ' || message3));
ERROR:  functions in index expression must be marked IMMUTABLE

That's not so good.....

Yes, the default is specified as english in postgresql.conf

default_text_search_config = 'pg_catalog.english'

I suspect the "remnants" of the previous module are doing this.... any ideas?