Re: Old tsearch functions

On 31/01/2019 14:58, Tom Lane wrote:
> Howard News <howardnews@selestial.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

Thanks again Tom.

You may be correct about how old the version of tsearch was. I have not 
seen the tsearch2--unpackaged--1.0.sql script yet, but the following 
seems to cope ok. It works on both the 9.5 version and the 11.1 version.

[CODE]

begin;

-- Repeat the line below for each table with public.tsvector column:

alter table if exists mytable_with_fts business alter column fts type 
pg_catalog.tsvector;

drop domain if exists public.tsvector;
drop domain if exists public.tsquery;
drop domain if exists public.gtsvector;
drop domain if exists public.gtsq;

-- This is how I created a list of functions in the public namespace
-- SELECT 'DROP FUNCTION ' || ns.nspname || '.' || proname
--       || '(' || oidvectortypes(proargtypes) || ');'
-- FROM pg_proc INNER JOIN pg_namespace ns ON (pg_proc.pronamespace = 
ns.oid)
-- WHERE ns.nspname = 'public'  order by proname;


drop function if exists public._get_parser_from_curcfg();
drop function if exists public.concat(tsvector, tsvector);
drop function if exists public.headline(text, tsquery);
drop function if exists public.headline(oid, text, tsquery);
drop function if exists public.headline(text, tsquery, text);
drop function if exists public.headline(oid, text, tsquery, text);
drop function if exists public.length(tsvector);
drop function if exists public.lexize(oid, text);
drop function if exists public.numnode(tsquery);
drop function if exists public.parse(oid, text);
drop function if exists public.parse(oid, text);
drop function if exists public.parse(text);
drop function if exists public.parse(text,text);
drop function if exists public.plainto_tsquery(text);
drop function if exists public.plainto_tsquery(oid, text);
drop function if exists public.plpgsql_call_handler();
drop function if exists public.plpgsql_validator(oid);
drop function if exists public.querytree(tsquery);
drop function if exists public.rank(tsvector, tsquery);
drop function if exists public.rank(real[], tsvector, tsquery);
drop function if exists public.rank(tsvector, tsquery, integer);
drop function if exists public.rank(real[], tsvector, tsquery, integer);
drop function if exists public.rank_cd(tsvector, tsquery);
drop function if exists public.rank_cd(real[], tsvector, tsquery);
drop function if exists public.rank_cd(tsvector, tsquery, integer);
drop function if exists public.rank_cd(real[], tsvector, tsquery, integer);
drop function if exists public.rewrite(tsquery, text);
drop function if exists public.rewrite(tsquery, tsquery, tsquery);
drop function if exists public.setweight(tsvector, "char");
drop function if exists public.show_curcfg();
drop function if exists public.stat(text);
drop function if exists public.stat(text, text);
drop function if exists public.strip(tsvector);
drop function if exists public.to_tsquery(text);
drop function if exists public.to_tsquery(oid, text);
drop function if exists public.to_tsvector(text);
drop function if exists public.to_tsvector(oid, text);
drop function if exists public.token_type(integer);
drop function if exists public.token_type(text);
drop function if exists public.token_type();
drop function if exists public.ts_debug(text);
drop function if exists public.tsq_mcontained(tsquery, tsquery);
drop function if exists public.tsq_mcontains(tsquery, tsquery);
drop function if exists public.tsquery_and(tsquery, tsquery);
drop function if exists public.tsquery_not(tsquery);
drop function if exists public.tsquery_or(tsquery, tsquery);


drop type if exists public.statinfo;
drop type if exists public.tokenout;
drop type if exists public.tokentype;
drop type if exists public.tsdebug;


commit;

[/CODE]