Re: Old tsearch functions

Howard News <howardnews(at)selestial(dot)com> writes:
> On 30/01/2019 18:08, Tom Lane wrote:
>> Note that if you had those functions laying around ever since 8.3,
>> they're probably just "loose" and not wrapped into an extension at all.

> unfortunately running
> create extension tsearch2 from unpackaged;
> caused the following error:
> ERROR: operator family "gist_tsvector_ops" does not exist for access
> method "gist"

That's odd, the tsearch2 extension has certainly been stagnant since
8.3. I wonder if the set of tsearch2 objects you have is even older
than that.

> So I think I will have to create a script to delete the functions etc
> individually unless someone has another idea.

I'd try trimming down the tsearch2--unpackaged--1.0.sql script until
it succeeds. (Don't assume that you've got the exact same set of
objects in every DB, either ...)

> For the tables that contain tsvector columns, is it OK to just run the
> following, or will i need to rebuild the associated index?
> ALTER TABLE public.mytable
>     ALTER COLUMN fts TYPE tsvector ;

The ALTER COLUMN will take care of rebuilding indexes, but just for
certainty I'd suggest spelling that "TYPE pg_catalog.tsvector".

regards, tom lane