Re: full text search in 8.3

Tom Lane wrote:
> Andy Colson <andy(at)camavision(dot)com> writes:
>> I have an 8.2 database that has full text searching. I tried to
>> backup/restore it to 8.3 but got lots of errors:
>> ...
>> I didn't really expect it to totally work, but I'm not sure how to move
>> my db.
>
> Did the data transfer over? The declarations of the former contrib
> functions would of course fail, but type tsvector is still there.
> I would like to think that ignoring pg_restore's whining would get
> you most of the way there.
>
> (As noted in the beta release notes, somebody really really needs to
> write a migration guide for existing tsearch2 users.)
>
> regards, tom lane

Ahh.. Ya know, I didn't even look. I saw all the errors and just
stopped it.

So I tried again: The long answer is no, the table with the tsvector
did not get created, and thus, not copied:

pg_restore: [archiver (db)] could not execute query: ERROR: type
"tsvector" is only a shell
LINE 11: vectors tsvector
^
Command was: CREATE TABLE times (
rowid integer NOT NULL,
empid integer NOT NULL,
idate timestamp without time zone,
jobi...
pg_restore: [archiver (db)] could not execute query: ERROR: relation
"public.times" does not exist
Command was: ALTER TABLE public.times OWNER TO andy;

In the backup script, the tsvector stuff is recreated, I assume that's why:

<snip>
CREATE TYPE tsquery (
INTERNALLENGTH = variable,
INPUT = tsquery_in,
OUTPUT = tsquery_out,
ALIGNMENT = int4,
STORAGE = plain
);

ALTER TYPE public.tsquery OWNER TO andy;

--
-- Name: tsvector; Type: SHELL TYPE; Schema: public; Owner: andy
--

CREATE TYPE tsvector;

--
-- Name: tsvector_in(cstring); Type: FUNCTION; Schema: public; Owner: andy
--

CREATE FUNCTION tsvector_in(cstring) RETURNS tsvector
AS '$libdir/tsearch2', 'tsvector_in'
LANGUAGE c STRICT;
<snip>

The rest of the tables, however, did get created/copied ok.

-Andy