Обсуждение: Eeek! Major problem after reload with tsearch2
Ok, I found out what was going on with the tsearch2 module and reloading after an upgrade to 8.0.1 from 7.4.1 The data now loads cleanly, and selects are fine. HOWEVER, when I attempt an update, I issue the following SQL command (this table has a tsearch2 vector in it:) update post set invisible='0' where ordinal='2843' And get back: ERROR: cache lookup failed for function 36476 CONTEXT: SQL statement "select lt.tokid, map.dict_name from public.pg_ts_cfgmap as map, public.pg_ts_cfg as cfg, public.token_type( $1 ) as lt where lt.alias = map.tok_alias and map.ts_name = cfg.ts_name and cfg.oid= $2 order by lt.tokid desc;" Aieeee! A reindex did nothing. What did I miss? Looks like there's something missing, but what?! -- -- Karl Denninger (karl@denninger.net) Internet Consultant & Kids Rights Activist http://www.denninger.net My home on the net - links to everything I do! http://scubaforum.org Your UNCENSORED place to talk about DIVING! http://www.spamcuda.net SPAM FREE mailboxes - FREE FOR A LIMITED TIME! http://genesis3.blogspot.com Musings Of A Sentient Mind
If I remove the full-text indexing from the table involved (remove the indices and triggers) then the system is fine. Somehow, it appears that there's some kind of internal consistency problem with the tsearch2 package..... Attempts to recreate the indices (after dropping them) fail with the same error; it looks like something is badly mangled internally in the tsearch2 module... even though it DOES appear that it loaded properly. -- -- Karl Denninger (karl@denninger.net) Internet Consultant & Kids Rights Activist http://www.denninger.net My home on the net - links to everything I do! http://scubaforum.org Your UNCENSORED place to talk about DIVING! http://www.spamcuda.net SPAM FREE mailboxes - FREE FOR A LIMITED TIME! http://genesis3.blogspot.com Musings Of A Sentient Mind On Thu, Feb 03, 2005 at 02:37:04PM -0600, Karl Denninger wrote: > Ok, I found out what was going on with the tsearch2 module and reloading > after an upgrade to 8.0.1 from 7.4.1 > > The data now loads cleanly, and selects are fine. > > HOWEVER, when I attempt an update, I issue the following SQL command (this > table has a tsearch2 vector in it:) > > update post set invisible='0' where ordinal='2843' > > And get back: > > ERROR: cache lookup failed for function 36476 CONTEXT: SQL statement > "select lt.tokid, map.dict_name from public.pg_ts_cfgmap as map, > public.pg_ts_cfg as cfg, public.token_type( $1 ) as lt where lt.alias = > map.tok_alias and map.ts_name = cfg.ts_name and cfg.oid= $2 order by > lt.tokid desc;" > > Aieeee! > > A reindex did nothing. > > What did I miss? Looks like there's something missing, but what?! > > -- > -- > Karl Denninger (karl@denninger.net) Internet Consultant & Kids Rights Activist > http://www.denninger.net My home on the net - links to everything I do! > http://scubaforum.org Your UNCENSORED place to talk about DIVING! > http://www.spamcuda.net SPAM FREE mailboxes - FREE FOR A LIMITED TIME! > http://genesis3.blogspot.com Musings Of A Sentient Mind > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > > %SPAMBLOCK-SYS: Matched [@postgresql.org], message ok
Karl Denninger <karl@denninger.net> writes:
> Ok, I found out what was going on with the tsearch2 module and reloading
> after an upgrade to 8.0.1 from 7.4.1
> ERROR: cache lookup failed for function 36476
I think that tsearch2's control tables contain function OIDs, which
means that you can't just dump them out of one installation and load
into a new one ... you have to regenerate the table contents by running
the tsearch2.sql script.
regards, tom lane
This is know issue with OIDS. You,probably, needed to apply regprocedure_7.4.patch.gz patch from http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/ Oleg On Thu, 3 Feb 2005, Karl Denninger wrote: > Ok, I found out what was going on with the tsearch2 module and reloading > after an upgrade to 8.0.1 from 7.4.1 > > The data now loads cleanly, and selects are fine. > > HOWEVER, when I attempt an update, I issue the following SQL command (this > table has a tsearch2 vector in it:) > > update post set invisible='0' where ordinal='2843' > > And get back: > > ERROR: cache lookup failed for function 36476 CONTEXT: SQL statement > "select lt.tokid, map.dict_name from public.pg_ts_cfgmap as map, > public.pg_ts_cfg as cfg, public.token_type( $1 ) as lt where lt.alias = > map.tok_alias and map.ts_name = cfg.ts_name and cfg.oid= $2 order by > lt.tokid desc;" > > Aieeee! > > A reindex did nothing. > > What did I miss? Looks like there's something missing, but what?! > > -- > Regards, Oleg _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
The patch is in the 8.0.1 version of Tsearch2 already. The problem is that I have a dump from a 7.4.1 database taken with the 8.0.1 pg_dumpall that I need to be able to get back online on 8.0.x. Is the only option to find all the functions in the tsearch.sql file, drop them by hand, remove all the tsearch2 index fields, then reload tsearch2.sql and re-create the indices? That's not impossible, but a shizload of work, as there's no good way that I can see to drop all the tsearch2 functions in a single step (e.g. I'd have to go through the tsearch2.sql file individually, find all the entries, drop them, etc.) Another possibility.... Does a pg_dumpall dump functions as well? It appears not from the documentation - so if I drop the columns and then dump the database, I should have a "clean" dump without the OID stuff in it. If I then re-init and reload the data, I should then be able to do so without the tsearch2.sql stuff. I can then reload the tsearch2.sql functions and re-create the indices. Sound plausible? - -- Karl Denninger (karl@denninger.net) Internet Consultant & Kids Rights Activist http://www.denninger.net My home on the net - links to everything I do! http://scubaforum.org Your UNCENSORED place to talk about DIVING! http://www.spamcuda.net SPAM FREE mailboxes - FREE FOR A LIMITED TIME! http://genesis3.blogspot.com Musings Of A Sentient Mind On Fri, Feb 04, 2005 at 12:23:02AM +0300, Oleg Bartunov wrote: > This is know issue with OIDS. > You,probably, needed to apply regprocedure_7.4.patch.gz > patch from http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/ > > Oleg > On Thu, 3 Feb 2005, Karl Denninger wrote: > > > Ok, I found out what was going on with the tsearch2 module and reloading > > after an upgrade to 8.0.1 from 7.4.1 > > > > The data now loads cleanly, and selects are fine. > > > > HOWEVER, when I attempt an update, I issue the following SQL command (this > > table has a tsearch2 vector in it:) > > > > update post set invisible='0' where ordinal='2843' > > > > And get back: > > > > ERROR: cache lookup failed for function 36476 CONTEXT: SQL statement > > "select lt.tokid, map.dict_name from public.pg_ts_cfgmap as map, > > public.pg_ts_cfg as cfg, public.token_type( $1 ) as lt where lt.alias = > > map.tok_alias and map.ts_name = cfg.ts_name and cfg.oid= $2 order by > > lt.tokid desc;" > > > > Aieeee! > > > > A reindex did nothing. > > > > What did I miss? Looks like there's something missing, but what?! > > > > -- > > > > Regards, > Oleg > _____________________________________________________________ > Oleg Bartunov, sci.researcher, hostmaster of AstroNet, > Sternberg Astronomical Institute, Moscow University (Russia) > Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ > phone: +007(095)939-16-83, +007(095)939-23-83 > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > > > %SPAMBLOCK-SYS: Matched [@postgresql.org], message ok
On Thu, 3 Feb 2005, Karl Denninger wrote: > The patch is in the 8.0.1 version of Tsearch2 already. > > The problem is that I have a dump from a 7.4.1 database taken with the > 8.0.1 pg_dumpall that I need to be able to get back online on 8.0.x. you had to apply patch to 7.4.1 db before dumping or use regprocedure_update.sql to update your live 7.4.1 database ! pg_dump (pg_dumpall) has nothing with the problem. After applying patch and dumping db you have OID-free db and should have no problem ! > > Is the only option to find all the functions in the tsearch.sql file, drop > them by hand, remove all the tsearch2 index fields, then reload > tsearch2.sql and re-create the indices? > > That's not impossible, but a shizload of work, as there's no good way that > I can see to drop all the tsearch2 functions in a single step (e.g. I'd > have to go through the tsearch2.sql file individually, find all the > entries, drop them, etc.) > > Another possibility.... > > Does a pg_dumpall dump functions as well? It appears not from the > documentation - so if I drop the columns and then dump the database, I > should have a "clean" dump without the OID stuff in it. > > If I then re-init and reload the data, I should then be able to do so > without the tsearch2.sql stuff. I can then reload the tsearch2.sql > functions and re-create the indices. > > Sound plausible? > No, if you have OIDs in db pg_dump* will dump them. Try regprocedure_update.sql and read http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch-V2-intro.html If you have 7.4.1 db running you might go way described earlier. > - > Regards, Oleg _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83