Thread: Old tsearch functions

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)

Thanks

Howard.

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]