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