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.
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.
Kind regards
Markus
Computec Media AG
Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818)
Vorstandsmitglieder: Johannes S. Gözalan (Vorsitzender) und Niels Herrmann
Vorsitzender des Aufsichtsrates: Jürg Marquard
Umsatzsteuer-Identifikationsnummer: DE 812 575 276
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
