Re: 8.2.6 > 8.3 blows up

Tom Lane wrote:
> Karl Denninger <karl(at)denninger(dot)net> writes:
>
>> It looks like the problem had to do with the tsearch2 module that I have
>> in use in a number of my databases, and which had propagated into
>> template1, which meant that new creates had it in there.
>>
>
> The old tsearch2 module isn't at all compatible with 8.3. I'd suggest
> removing it from the 8.2 source databases where possible, ie wherever
> you're not actually using it. Where you are using it, the cure is to
> install the 8.3 version of contrib/tsearch2 into the target database
> *before* restoring. See
> http://www.postgresql.org/docs/8.3/static/textsearch-migration.html
> http://www.postgresql.org/docs/8.3/static/tsearch2.html
>
> regards, tom lane
>
>
> %SPAMBLOCK-SYS: Matched [postgresql.org], message ok
>
Yeah, I read that in the docs...

But there are compatability problems with the tsearch2 contrib module in
8.3 and backwards constructs and code, with the most serious being that
it simply doesn't work correctly for some of the older ways of
formatting queries and storage.

Specifically, if you store the ts_vector in the table via a different
column and have a GIST index on it, this fails with the 8.3 tsearch2
module loaded as all updates or inserts return a complaint when the
trigger fires - the claim is that there's a data type mismatch.

The simplest fix is to rework the index to go directly off the
underlying column and then query off that but that requires a change to
both the query and the schema. Not a big deal to do once you figure out
what's up, but it DOES require code changes.

See my other message to the list on this; I have worked around it in the
applications affected but this is likely to bite people with some
authority until they get their arms around it.

Karl Denninger (karl(at)denninger(dot)net)
http://www.denninger.net