Обсуждение: [ADMIN] Tsearch2 removal bit me - how to correct?

Attempting to migrate to 10.0 from 9.6, which has had "tsearch2" loaded for backward compatibility since "forever" (which I suspect is no longer really required as the capability is internal and has been for a long time) results in this blowup logged in loadable_libraries.txt:

could not load library "$libdir/tsearch2": ERROR:  could not access file "$libdir/tsearch2": No such file or directory

The server log says:

2017-10-15 10:14:58.593 CDT [86864] ERROR:  could not access file "$libdir/tsearch2": No such file or directory
2017-10-15 10:14:58.593 CDT [86864] STATEMENT:  LOAD '$libdir/tsearch2'

Which is true, incidentally.

The problem is that I can't find any databases under the old database collection that have the extension loaded any longer (checking all of them don't show any with it in; I removed a few old databases that were no longer needed and DID have it, along with the tsearch2() function itself in a few more), so I don't know what to drop in order to clear this, and it's blocking an attempt to upgrade to 10.0.

So what's causing this to be requested on startup and how do I kill it?  There doesn't appear to be an obvious way to deinstall it from the 9.6 cluster....

--

Karl Denninger <karl@denninger.net> writes:
> Attempting to migrate to 10.0 from 9.6, which has had "tsearch2" loaded
> for backward compatibility since "forever" (which I suspect is no longer
> really required as the capability is internal and has been for a long
> time) results in this blowup logged in loadable_libraries.txt:
> could not load library "$libdir/tsearch2": ERROR:  could not access file
> "$libdir/tsearch2": No such file or directory

> So what's causing this to be requested on startup and how do I kill it? 

Look for '$libdir/tsearch2' in the pg_proc.probin column of each
DB in the installation ...
        regards, tom lane


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Karl Denninger <karl@denninger.net> writes:

Attempting to migrate to 10.0 from 9.6, which has had "tsearch2" loaded
for backward compatibility since "forever" (which I suspect is no longer
really required as the capability is internal and has been for a long
time) results in this blowup logged in loadable_libraries.txt:
could not load library "$libdir/tsearch2": ERROR:  could not access file
"$libdir/tsearch2": No such file or directory

So what's causing this to be requested on startup and how do I kill it? 

Look for '$libdir/tsearch2' in the pg_proc.probin column of each
DB in the installation ...
		regards, tom lane

Thanks... found it.

On 10/15/2017 10:47, Tom Lane wrote:

Karl Denninger <karl@denninger.net> writes:

Attempting to migrate to 10.0 from 9.6, which has had "tsearch2" loaded
for backward compatibility since "forever" (which I suspect is no longer
really required as the capability is internal and has been for a long
time) results in this blowup logged in loadable_libraries.txt:
could not load library "$libdir/tsearch2": ERROR:  could not access file
"$libdir/tsearch2": No such file or directory

So what's causing this to be requested on startup and how do I kill it? 

Look for '$libdir/tsearch2' in the pg_proc.probin column of each
DB in the installation ...
		regards, tom lane

Thanks... found it.

--

New problem -- davical appears to rely on something that got changed to disallowed....

Oct 15 11:07:08 NewFS postgres[92560]: [5-1] 2017-10-15 11:07:08.473 CDT [92560]
 ERROR:  set-returning functions are not allowed in CASE at character 129
Oct 15 11:07:08 NewFS postgres[92560]: [5-2] 2017-10-15 11:07:08.473 CDT [92560]
 HINT:  You might be able to move the set-returning function into a LATERAL FROM
 item.
Oct 15 11:07:08 NewFS postgres[92560]: [5-3] 2017-10-15 11:07:08.473 CDT [92560]
 QUERY:
Oct 15 11:07:08 NewFS postgres[92560]: [5-4]      SELECT group_id FROM group_mem
ber WHERE member_id = $1
Oct 15 11:07:08 NewFS postgres[92560]: [5-5]          UNION
Oct 15 11:07:08 NewFS postgres[92560]: [5-6]      SELECT expanded.g_id FROM (SEL
ECT CASE WHEN $2 > 0 THEN expand_memberships( group_id, $2 - 1) END AS g_id
Oct 15 11:07:08 NewFS postgres[92560]: [5-7]                                   F
ROM group_member WHERE member_id = $1) AS expanded
Oct 15 11:07:08 NewFS postgres[92560]: [5-8]                           WHERE exp
anded.g_id IS NOT NULL;
Oct 15 11:07:08 NewFS postgres[92560]: [5-9]
Oct 15 11:07:08 NewFS postgres[92560]: [5-10] 2017-10-15 11:07:08.473 CDT [92560
] CONTEXT:  SQL function "expand_memberships" during startup

That's a killer until the Davical people work on their code....

Karl Denninger <karl@denninger.net> writes:

Attempting to migrate to 10.0 from 9.6, which has had "tsearch2" loaded
for backward compatibility since "forever" (which I suspect is no longer
really required as the capability is internal and has been for a long
time) results in this blowup logged in loadable_libraries.txt:
could not load library "$libdir/tsearch2": ERROR:  could not access file
"$libdir/tsearch2": No such file or directory

So what's causing this to be requested on startup and how do I kill it? 

Look for '$libdir/tsearch2' in the pg_proc.probin column of each
DB in the installation ...
		regards, tom lane

That worked on one of my clusters, but on another I'm seeing entries in pg_catalog, and can't remove them.

pgsql=# \c fapforum
You are now connected to database "fapforum" as user "pgsql".
fapforum=# select proname from pg_proc where probin like '%tsearch2%';
    proname
----------------
 prsd_end
 prsd_lextype
 prsd_start
 thesaurus_init
(4 rows)

fapforum=# \df prsd_end;
                            List of functions
   Schema   |   Name   | Result data type | Argument data types |  Type
------------+----------+------------------+---------------------+--------
 pg_catalog | prsd_end | void             | internal            | normal
(1 row)

fapforum=# drop function prsd_end(internal);
ERROR:  cannot drop function prsd_end(internal) because it is required by the database system

The others I can drop, but I wind up with these in each database... any idea where the piece is that the original package stuffed in that I need to get rid of so I can kill these?

On 10/15/2017 10:47, Tom Lane wrote:

Karl Denninger <karl@denninger.net> writes:

Attempting to migrate to 10.0 from 9.6, which has had "tsearch2" loaded
for backward compatibility since "forever" (which I suspect is no longer
really required as the capability is internal and has been for a long
time) results in this blowup logged in loadable_libraries.txt:
could not load library "$libdir/tsearch2": ERROR:  could not access file
"$libdir/tsearch2": No such file or directory

So what's causing this to be requested on startup and how do I kill it? 

Look for '$libdir/tsearch2' in the pg_proc.probin column of each
DB in the installation ...
		regards, tom lane

That worked on one of my clusters, but on another I'm seeing entries in pg_catalog, and can't remove them.

pgsql=# \c fapforum
You are now connected to database "fapforum" as user "pgsql".
fapforum=# select proname from pg_proc where probin like '%tsearch2%';
    proname
----------------
 prsd_end
 prsd_lextype
 prsd_start
 thesaurus_init
(4 rows)

fapforum=# \df prsd_end;
                            List of functions
   Schema   |   Name   | Result data type | Argument data types |  Type
------------+----------+------------------+---------------------+--------
 pg_catalog | prsd_end | void             | internal            | normal
(1 row)

fapforum=# drop function prsd_end(internal);
ERROR:  cannot drop function prsd_end(internal) because it is required by the database system

The others I can drop, but I wind up with these in each database... any idea where the piece is that the original package stuffed in that I need to get rid of so I can kill these?

This has also left me with an interesting other issue that is likely related.

The removal of the functions of course necessitated the removal of the gin indices I was using.  Ok, I understand that.  But the 9.6 documentation says this is valid:

SELECT to_tsvector('english','in the list of stop words');

But....

ticker=# select to_tsvector('english', 'in the list of stop words');
ERROR:  invalid input syntax for type oid: "english"
LINE 1: select to_tsvector('english', 'in the list of stop words');

Interestingly enough if I don't specify the language....
ticker=# select to_tsvector('in the list of stop words');
        to_tsvector
----------------------------
 'list':3 'stop':5 'word':6
(1 row)

Which is correct.  So it appears the internal functions are both there and working.

But, attempting to re-create the indices, omitting the language so it defaults, fails with a complaint that the operand function must be immutable.

ticker=# create index idx_message on post using gin(to_tsvector( message || ' ' || message2 || ' ' || message3));
ERROR:  functions in index expression must be marked IMMUTABLE

That's not so good.....

Yes, the default is specified as english in postgresql.conf

default_text_search_config = 'pg_catalog.english'

I suspect the "remnants" of the previous module are doing this.... any ideas?

Karl Denninger <karl@denninger.net> writes:
> That worked on one of my clusters, but on another I'm seeing entries in
> pg_catalog, and can't remove them.

> pgsql=# \c fapforum
> You are now connected to database "fapforum" as user "pgsql".
> fapforum=# select proname from pg_proc where probin like '%tsearch2%';
>     proname
> ----------------
>  prsd_end
>  prsd_lextype
>  prsd_start
>  thesaurus_init
> (4 rows)

There *should* be a pg_catalog.prsd_end built-in function, but it's
going to have null probin.  I think you're being careless about
schema names here.

> fapforum=# \df prsd_end;
>                             List of functions
>    Schema   |   Name   | Result data type | Argument data types |  Type
> ------------+----------+------------------+---------------------+--------
>  pg_catalog | prsd_end | void             | internal            | normal
> (1 row)

This does not prove that there's not a, say, public.prsd_end.  Try
"\df *.prsd_end" to see all the functions by that name.
        regards, tom lane


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Karl Denninger <karl@denninger.net> writes:
> ticker=# select to_tsvector('english', 'in the list of stop words');
> ERROR:  invalid input syntax for type oid: "english"

WFM.  I think you didn't get rid of the tsearch2 version of
to_tsvector in that database, as that version would take a plain
OID argument not regconfig.
        regards, tom lane


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

On 10/15/2017 18:27, Tom Lane wrote:

Karl Denninger <karl@denninger.net> writes:

ticker=# select to_tsvector('english', 'in the list of stop words');
ERROR:  invalid input syntax for type oid: "english"

WFM.  I think you didn't get rid of the tsearch2 version of
to_tsvector in that database, as that version would take a plain
OID argument not regconfig.
		regards, tom lane

I think I found most of the problems (it's working now on the internal functions and I was able to recreate the indices) EXCEPT that I still have references to tsearch2 in pg_proc, which means it won't upgrade, and they're in the system catalog so I can't drop the functions either.

ticker=# select proname from pg_proc where probin like '%tsearch2%';
    proname
----------------
 prsd_end
 prsd_lextype
 prsd_start
 thesaurus_init
(4 rows)

ticker=# \df prsd_end;
                            List of functions
   Schema   |   Name   | Result data type | Argument data types |  Type
------------+----------+------------------+---------------------+--------
 pg_catalog | prsd_end | void             | internal            | normal
(1 row)

On 10/15/2017 18:25, Tom Lane wrote:

Karl Denninger <karl@denninger.net> writes:

That worked on one of my clusters, but on another I'm seeing entries in
pg_catalog, and can't remove them.

pgsql=# \c fapforum
You are now connected to database "fapforum" as user "pgsql".
fapforum=# select proname from pg_proc where probin like '%tsearch2%';
    proname
----------------
 prsd_end
 prsd_lextype
 prsd_start
 thesaurus_init
(4 rows)

There *should* be a pg_catalog.prsd_end built-in function, but it's
going to have null probin.  I think you're being careless about
schema names here.

fapforum=# \df prsd_end;
                            List of functions
   Schema   |   Name   | Result data type | Argument data types |  Type
------------+----------+------------------+---------------------+--------
 pg_catalog | prsd_end | void             | internal            | normal
(1 row)

This does not prove that there's not a, say, public.prsd_end.  Try
"\df *.prsd_end" to see all the functions by that name.
		regards, tom lane

You are correct.  Thanks; got it.  (That's what I get for not being specific)

On 10/15/2017 18:42, Karl Denninger wrote:

On 10/15/2017 18:25, Tom Lane wrote:

Karl Denninger <karl@denninger.net> writes:

That worked on one of my clusters, but on another I'm seeing entries in
pg_catalog, and can't remove them.

pgsql=# \c fapforum
You are now connected to database "fapforum" as user "pgsql".
fapforum=# select proname from pg_proc where probin like '%tsearch2%';
    proname
----------------
 prsd_end
 prsd_lextype
 prsd_start
 thesaurus_init
(4 rows)

There *should* be a pg_catalog.prsd_end built-in function, but it's
going to have null probin.  I think you're being careless about
schema names here.

fapforum=# \df prsd_end;
                            List of functions
   Schema   |   Name   | Result data type | Argument data types |  Type
------------+----------+------------------+---------------------+--------
 pg_catalog | prsd_end | void             | internal            | normal
(1 row)

This does not prove that there's not a, say, public.prsd_end.  Try
"\df *.prsd_end" to see all the functions by that name.
		regards, tom lane

You are correct.  Thanks; got it.  (That's what I get for not being specific)

One more question - for safety do I also need to drop all the other previously-declared public functions such as rank*() (in all its forms) and concat even thought they do not reference the shared library, or are they safe to leave there?  I see there are pg_catalog copies of these as well....

Karl Denninger <karl@denninger.net> writes:
> One more question - for safety do I also need to drop all the other
> previously-declared public functions such as rank*() (in all its forms)
> and concat even thought they do not reference the shared library, or are
> they safe to leave there?  I see there are pg_catalog copies of these as
> well....

I dunno about safety offhand, but I'd definitely make an effort to get
rid of them --- at minimum, you're risking confusion by leaving them
around.  All of the modern text search support functions are in
pg_catalog.
        regards, tom lane


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

On 10/15/2017 21:18, Tom Lane wrote:

Karl Denninger <karl@denninger.net> writes:

One more question - for safety do I also need to drop all the other
previously-declared public functions such as rank*() (in all its forms)
and concat even thought they do not reference the shared library, or are
they safe to leave there?  I see there are pg_catalog copies of these as
well....

I dunno about safety offhand, but I'd definitely make an effort to get
rid of them --- at minimum, you're risking confusion by leaving them
around.  All of the modern text search support functions are in
pg_catalog.
		regards, tom lane

Thanks.  Appreciate it; if we're in the same place at some point I owe 'ya a couple of beers :)