From: | Howard News <howardnews(at)selestial(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Old tsearch functions |
Date: | 2019-01-31 15:47:18 |
Message-ID: | 2d19eef3-23cd-3797-cf4d-44b2e8e54e05@selestial.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 31/01/2019 14:58, Tom Lane wrote:
> 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
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]
From | Date | Subject | |
---|---|---|---|
Next Message | Piotr Włodarczyk | 2019-01-31 16:37:00 | oracle_fwd - is it safe or not? |
Previous Message | Tom Lane | 2019-01-31 14:58:11 | Re: Old tsearch functions |