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