Обсуждение: Problem with to_tsquery() after restore on PostgreSQL 9.0.1

Hi!

I currently testing the upgrade of our 8.3 databases to 9.0.

In some of those restored databases to_tsquery is throwing an error:

magazine=# SELECT to_tsquery('simple','plants');
ERROR:  invalid input syntax for type oid: "simple"
LINE 1: SELECT to_tsquery('simple','plants');

In other databases, it's working fine; the same applies to newly created
databases on this cluster.

Now my Google-fu seems to be somewhat lacking because I couldn't find
anything on this precise error condition.

I dumped the old databases using
su postgres -c "time /opt/pgsql/bin/pg_dump -s mydb | gzip
>~/mydb-schema.sql.`date -I`.gz"
su postgres -c "time /opt/pgsql/bin/pg_dump -a mydb -Fc
>~/mydb-data.`date -I`.pg"
Source version() is PostgreSQL 8.3.7 on x86_64-unknown-linux-gnu,
compiled by GCC gcc (Debian 4.3.2-1.1) 4.3.2
Target version() is PostgreSQL 9.0.1 on x86_64-unknown-linux-gnu,
compiled by GCC gcc (Debian 4.4.5-4) 4.4.5, 64-bit

I suspect it has to do with the Tsearch2-compatibility modules from
contrib - these were compiled and installed to a couple of databases on
the old cluster; I haven't yet compiled and installed them to the new
databases as I'd like to get rid of some dead weight in the migration
process.

http://www.postgresql.org/docs/9.0/static/tsearch2.html doesn't
elaborate further on how to get rid of the replacement tsearch2 module
stuff.

Any ideas on how to resolve this issue?

Kind regards

   Markus

COMPUTEC MEDIA zieht in neue Verlagsraume! Adresse ab dem 27. September 2010: COMPUTEC MEDIA AG, Dr.-Mack-Stra?e 83,
90762Furth. Alle sonstigen Kontaktdaten bleiben unverandert. 

Computec Media AG
Sitz der Gesellschaft und Registergericht: Furth (HRB 8818)
Vorstandsmitglieder: Albrecht Hengstenberg (Vorsitzender) und Rainer Rosenbusch
Vorsitzender des Aufsichtsrates: Jurg Marquard
Umsatzsteuer-Identifikationsnummer: DE 812 575 276

"Markus Wollny" <Markus.Wollny@computec.de> writes:
> I currently testing the upgrade of our 8.3 databases to 9.0.

> In some of those restored databases to_tsquery is throwing an error:

> magazine=# SELECT to_tsquery('simple','plants');
> ERROR:  invalid input syntax for type oid: "simple"

So far as I can see offhand, the only way you'd get that error message
is if to_tsquery were declared to take OID not regconfig as its first
argument.

> I suspect it has to do with the Tsearch2-compatibility modules from
> contrib - these were compiled and installed to a couple of databases on
> the old cluster; I haven't yet compiled and installed them to the new
> databases as I'd like to get rid of some dead weight in the migration
> process.

contrib/tsearch2 does provide a "to_tsquery(oid, text)" function ... I'm
not sure why offhand, nor how come that's managing to show up in front
of the standard definition in your search path.  Try dropping that.

(Another question is why it wasn't failing already in your 8.3 DB.
The behavior shouldn't be any different AFAICS.  Are you sure you
are using the same search_path as before?)

            regards, tom lane

Hi!

Tom Lane <tgl@sss.pgh.pa.us> writes:

> So far as I can see offhand, the only way you'd get that error message
> is if to_tsquery were declared to take OID not regconfig as its first
> argument.
>
> > I suspect it has to do with the Tsearch2-compatibility modules from
> > contrib - these were compiled and installed to a couple of databases
> on
> > the old cluster; I haven't yet compiled and installed them to the
new
> > databases as I'd like to get rid of some dead weight in the
migration
> > process.
>
> contrib/tsearch2 does provide a "to_tsquery(oid, text)" function ...
> I'm
> not sure why offhand, nor how come that's managing to show up in front
> of the standard definition in your search path.  Try dropping that.

Now I simply used the
postgresql-9.0.1/contrib/tsearch2/uninstall_tsearch2.sql to get rid of
the contrib DDL - that seems to have fixed the problem alright; maybe
we'll have to fix some of our code that may use some old function
signatures, but since we've switched to Sphinx for the more demanding
FTS tasks, we're not making as much use of TSearch2 as we used to,
anyway.

> (Another question is why it wasn't failing already in your 8.3 DB.
> The behavior shouldn't be any different AFAICS.  Are you sure you
> are using the same search_path as before?)

Yes, as I simply copied my old postgresql.conf to the test server.
Strange, but as the primary problem seems to be solved alright, I'm
happy anyway :)

Kind regards

   Markus

COMPUTEC MEDIA zieht in neue Verlagsraume! Adresse ab dem 27. September 2010: COMPUTEC MEDIA AG, Dr.-Mack-Stra?e 83,
90762Furth. Alle sonstigen Kontaktdaten bleiben unverandert. 

Computec Media AG
Sitz der Gesellschaft und Registergericht: Furth (HRB 8818)
Vorstandsmitglieder: Albrecht Hengstenberg (Vorsitzender) und Rainer Rosenbusch
Vorsitzender des Aufsichtsrates: Jurg Marquard
Umsatzsteuer-Identifikationsnummer: DE 812 575 276