pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedTsearch2 removal bit me - how to correct?
13+ messages / 2 participants
[nested] [flat]
* Tsearch2 removal bit me - how to correct?
@ 2017-10-15 15:37 Karl Denninger <karl@denninger.net>
0 siblings, 1 reply; 13+ messages in thread
From: Karl Denninger @ 2017-10-15 15:37 UTC (permalink / raw)
To: pgsql-admin
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 <mailto:karl@denninger.net>
/The Market Ticker/
/[S/MIME encrypted email preferred]/
Attachments:
smime.p7s (application/pkcs7-signature, 4.8K) download
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Tsearch2 removal bit me - how to correct?
@ 2017-10-15 15:47 Tom Lane <tgl@sss.pgh.pa.us>
parent: Karl Denninger <karl@denninger.net>
0 siblings, 2 replies; 13+ messages in thread
From: Tom Lane @ 2017-10-15 15:47 UTC (permalink / raw)
To: Karl Denninger <karl@denninger.net>; +Cc: 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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Tsearch2 removal bit me - how to correct?
@ 2017-10-15 15:54 Karl Denninger <karl@denninger.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 13+ messages in thread
From: Karl Denninger @ 2017-10-15 15:54 UTC (permalink / raw)
To: pgsql-admin
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.
--
Karl Denninger
karl@denninger.net <mailto:karl@denninger.net>
/The Market Ticker/
/[S/MIME encrypted email preferred]/
Attachments:
smime.p7s (application/pkcs7-signature, 4.8K) download
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Tsearch2 removal bit me - how to correct?
@ 2017-10-15 16:10 Karl Denninger <karl@denninger.net>
parent: Karl Denninger <karl@denninger.net>
0 siblings, 0 replies; 13+ messages in thread
From: Karl Denninger @ 2017-10-15 16:10 UTC (permalink / raw)
To: pgsql-admin
On 10/15/2017 10:54, Karl Denninger wrote:
>
> 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 <mailto:karl@denninger.net>
/The Market Ticker/
/[S/MIME encrypted email preferred]/
Attachments:
smime.p7s (application/pkcs7-signature, 4.8K) download
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Tsearch2 removal bit me - how to correct?
@ 2017-10-15 19:49 Karl Denninger <karl@denninger.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 2 replies; 13+ messages in thread
From: Karl Denninger @ 2017-10-15 19:49 UTC (permalink / raw)
To: pgsql-admin
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?
--
Karl Denninger
karl@denninger.net <mailto:karl@denninger.net>
/The Market Ticker/
/[S/MIME encrypted email preferred]/
Attachments:
smime.p7s (application/pkcs7-signature, 4.8K) download
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Tsearch2 removal bit me - how to correct?
@ 2017-10-15 22:00 Karl Denninger <karl@denninger.net>
parent: Karl Denninger <karl@denninger.net>
1 sibling, 1 reply; 13+ messages in thread
From: Karl Denninger @ 2017-10-15 22:00 UTC (permalink / raw)
To: pgsql-admin
On 10/15/2017 14:49, Karl Denninger wrote:
> 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 <mailto:karl@denninger.net>
/The Market Ticker/
/[S/MIME encrypted email preferred]/
Attachments:
smime.p7s (application/pkcs7-signature, 4.8K) download
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Tsearch2 removal bit me - how to correct?
@ 2017-10-15 23:25 Tom Lane <tgl@sss.pgh.pa.us>
parent: Karl Denninger <karl@denninger.net>
1 sibling, 1 reply; 13+ messages in thread
From: Tom Lane @ 2017-10-15 23:25 UTC (permalink / raw)
To: Karl Denninger <karl@denninger.net>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Tsearch2 removal bit me - how to correct?
@ 2017-10-15 23:27 Tom Lane <tgl@sss.pgh.pa.us>
parent: Karl Denninger <karl@denninger.net>
0 siblings, 1 reply; 13+ messages in thread
From: Tom Lane @ 2017-10-15 23:27 UTC (permalink / raw)
To: Karl Denninger <karl@denninger.net>; +Cc: 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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Tsearch2 removal bit me - how to correct?
@ 2017-10-15 23:36 Karl Denninger <karl@denninger.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 13+ messages in thread
From: Karl Denninger @ 2017-10-15 23:36 UTC (permalink / raw)
To: 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)
--
Karl Denninger
karl@denninger.net <mailto:karl@denninger.net>
/The Market Ticker/
/[S/MIME encrypted email preferred]/
Attachments:
smime.p7s (application/pkcs7-signature, 4.8K) download
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Tsearch2 removal bit me - how to correct?
@ 2017-10-15 23:42 Karl Denninger <karl@denninger.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 13+ messages in thread
From: Karl Denninger @ 2017-10-15 23:42 UTC (permalink / raw)
To: pgsql-admin
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)
--
Karl Denninger
karl@denninger.net <mailto:karl@denninger.net>
/The Market Ticker/
/[S/MIME encrypted email preferred]/
Attachments:
smime.p7s (application/pkcs7-signature, 4.8K) download
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Tsearch2 removal bit me - how to correct?
@ 2017-10-15 23:55 Karl Denninger <karl@denninger.net>
parent: Karl Denninger <karl@denninger.net>
0 siblings, 1 reply; 13+ messages in thread
From: Karl Denninger @ 2017-10-15 23:55 UTC (permalink / raw)
To: pgsql-admin
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 <mailto:karl@denninger.net>
/The Market Ticker/
/[S/MIME encrypted email preferred]/
Attachments:
smime.p7s (application/pkcs7-signature, 4.8K) download
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Tsearch2 removal bit me - how to correct?
@ 2017-10-16 02:18 Tom Lane <tgl@sss.pgh.pa.us>
parent: Karl Denninger <karl@denninger.net>
0 siblings, 1 reply; 13+ messages in thread
From: Tom Lane @ 2017-10-16 02:18 UTC (permalink / raw)
To: Karl Denninger <karl@denninger.net>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Tsearch2 removal bit me - how to correct?
@ 2017-10-16 02:34 Karl Denninger <karl@denninger.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 13+ messages in thread
From: Karl Denninger @ 2017-10-16 02:34 UTC (permalink / raw)
To: 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 :)
--
Karl Denninger
karl@denninger.net <mailto:karl@denninger.net>
/The Market Ticker/
/[S/MIME encrypted email preferred]/
Attachments:
smime.p7s (application/pkcs7-signature, 4.8K) download
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2017-10-16 02:34 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-10-15 15:37 Tsearch2 removal bit me - how to correct? Karl Denninger <karl@denninger.net>
2017-10-15 15:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-10-15 15:54 ` Karl Denninger <karl@denninger.net>
2017-10-15 16:10 ` Karl Denninger <karl@denninger.net>
2017-10-15 19:49 ` Karl Denninger <karl@denninger.net>
2017-10-15 22:00 ` Karl Denninger <karl@denninger.net>
2017-10-15 23:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-10-15 23:36 ` Karl Denninger <karl@denninger.net>
2017-10-15 23:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-10-15 23:42 ` Karl Denninger <karl@denninger.net>
2017-10-15 23:55 ` Karl Denninger <karl@denninger.net>
2017-10-16 02:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-10-16 02:34 ` Karl Denninger <karl@denninger.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox