Re: Eeek! Major problem after reload with tsearch2

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(at)denninger(dot)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(at)sai(dot)msu(dot)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 [(at)postgresql(dot)org], message ok