Old tsearch functions

On 1/30/19 9:07 AM, Howard News wrote:

Hi,

upgrading from 9.5 to 11.1, I have a few tsearch problems that appear to
relate to a much older migration (8.3)

Originally, for those that remember that far back, tsearch was a contrib
module. However it appears that I still have some of the old tsearch
functions in my database, and these were copied into the pg_dump that I
restored into v11 and have caused some confusion and problems.

For example, select to_tsquery('english', 'hello') would not work. The
reason is the additional functions, domains etc that were added to the
public schema, probably from the contrib module, which was removed in v10,

Now I can delete all these extra functions and domains and change the
types of all my tsvector columns from "public.tsvector" to the inbuilt
type "tsvector", but is there an easier way or a script that exists to
do this automatically? (I have a lot of databases with this issue)

Is to late to experiment with dropping the extension on a 9.5 database
and then dumping?

Thanks

Howard.

--
Adrian Klaver
adrian.klaver@aklaver.com

On 30/01/2019 17:20, Adrian Klaver wrote:

Is to late to experiment with dropping the extension on a 9.5 database
and then dumping?

Not a problem - The problem only shows in development so far. I have all
the originals in 9.5 and backups to try this on.

Howard News <howardnews@selestial.com> writes:

On 30/01/2019 17:20, Adrian Klaver wrote:

Is to late to experiment with dropping the extension on a 9.5 database
and then dumping?

Not a problem - The problem only shows in development so far. I have all
the originals in 9.5 and backups to try this on.

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

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.

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

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]