Re: Questions about TSearch2 and PG 8.2

On Monday 30 April 2007 13:20, Markus Wollny wrote:
> Hello!
>
> I'm in the process of upgrading our PostgreSQL 8.1 databases to PostgreSQL
> 8.2.4. I have stumbled over a minor issue with the upgrade and some helpful
> suggestions here:
> http://people.planetpostgresql.org/xzilla/index.php?/archives/291-The-pain-
>that-is-tsearch2-8.1-8.2-upgrading.html
>
> I shall try tonight with an plain SQL dump, but as some of my DBs are quite
> large, I usually use the custom dump format. As I would like to move the
> tsearch2-stuff in ist own schema as suggested, I tried using a restore
> list. I'd like to report that everything works as expected, but I've got a
> slight problem with the custom schema part. I created the target-db,
> created a schema tsearch2 and installed the tsearch2-functions, operators,
> configuration and whatnot into this new schema. Then I edited the restore
> list so that the tsearch2-bits would not be created from the dump file
> again. However, the binary-dump tries to create the textindex-columns with
> a tsvector-type which explicitly references the public schema.
>
> Instead of
>
> CREATE TABLE someschema.article
> (
> id integer,
> mytext text,
> idxfti tsvector
> );
>
> it tries to create the table like this
>
> CREATE TABLE someschema.article
> (
> id integer,
> mytext text,
> idxfti public.tsvector
> );
>
> As the tsvector-type is defined in the tsearch2-schema, this is bound to
> fail, even with the search_path set to include the tsearch2-schema. I
> assume that this happens because the table article is not in the same
> schema as the original tsvector-type and the default search_path is being
> ignored on the dump in order to be on the safe side. This "double-checking"
> breaks the migration in my case, however, so is there some way that would
> allow me to change the table definition on restore from using just tsvector
> instead of the explicit public.tsvector? I already tried editing the binary
> dump, but that just resulted in a corrupted dump-file. I there's no other
> way, I'll go the plain dump route, of course, but I'd just like to check
> this issue.
>

I think you want to split it into 2 parts... do a schema only dump in plain
sql to get the schema loaded, then use the custom format to pull the data
over.

> My second question concerns the new Gin (Generalized Inverted Index) index
> type. Is it stable enough for production yet and would it yield a high
> enough performance gain in comparison the GiST? Does it make much sense
> using a Gin-index alongside the GiST-one? Would we need to change anything
> in the application code in order to make use of Gin - like using
>
> where idxfti @> to_tsquery('default_german', 'Fundstück')
>
> instead of
>
> where idxfti @@ to_tsquery('default_german', 'Fundstück')
>
> ? The docs here http://www.sai.msu.su/~megera/wiki/Gin are still a bit
> thin, so any hint to some further examples would be greatly appreciated.
>

Gin is certainly stable enough for production use, we're using it on some
projects at OmniTI at least.

I've got some gin/gist information up as well at
http://people.planetpostgresql.org/xzilla/index.php?/archives/278-PostgreSQL-full-text-search-testing.html,
also be sure check the trackback links... and also I think magnus had a post
on gin/gist wrt the postgresql.org website.

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL