Re: 8.2.6 > 8.3 blows up

Joshua D. Drake wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On Sun, 02 Mar 2008 15:46:25 -0600
> Karl Denninger <karl(at)denninger(dot)net> wrote:
>
>
>> I'm not quite clear what I have to do in terms of if/when I can drop
>> the old tsearch config stuff and for obvious reasons (like not
>> running into this in the future) I'd like to. Can I just run the
>> "untsearch2" script against those databases or will that destroy the
>> search functionality? Are there changes necessary in the SQL code
>> (the documentation implies not unless I'm doing "odd" things)
>>
>
> One of the goals for 8.3 and the integrated tsearch was to remove
> exactly this problem.
>
> Sincerely,
>
> Joshua D. Drake
>

Unfortunately there are code changes necessary on my end in one of my
major applications; the attempt at compatability is somewhat less than
successful from here.....

The problem is that I was holding the ts_vector in a column in the table
with a GIST index on that column. This fails horribly under 8.3; it
appears to be ok on the reload but as there is a trigger on updates any
update or insert fails immediately with a data mistype complaint.

The fix is to rejigger the query to go directly at the fields and build
a gin or gist index directly on the underlying, which is not difficult
but DOES require code and schema changes. I'll get through it but this
is going to bite people with some authority if they have applications
that were doing things the same way I was - there was nothing esoteric
about the way I had coded it (although it could be argued it was
somewhat wasteful of disk space.)

-- Karl Denninger
karl(at)denninger(dot)net