Re: Old tsearch functions

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.
>
> You could fix that in a 9.5 database by running
>
> create extension tsearch2 from unpackaged;
>
> which should be enough to collect the relevant objects into an
> extension. At that point you could try doing "drop extension tsearch2".
> Likely it'll fail due to dependencies on the extension objects, but
> at least the error message will give you an idea of what you need to
> fix before you can drop it. In any case, this certainly beats trying
> to manually identify and drop the obsolete types and functions.
>
> You will need to do this in 9.5, or at the latest 9.6, because we
> dropped support for that extension in v10.
>
> regards, tom lane

Thanks Tom,

unfortunately running

create extension tsearch2 from unpackaged;

caused the following error:

ERROR: operator family "gist_tsvector_ops" does not exist for access
method "gist"

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

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 column ALTER TABLE public.mytable
    ALTER COLUMN fts TYPE tsvector ;

The current type is public.tsvector;

Thanks.