Re: Subtle pg_dump problem...

>>6. However, it is now not possible to restore the sql script as it was
>>dumped, as you get this error:
>>
>>ERROR: relation "pg_ts_cfg" does not exist
>>
>
>
> No problem,
>
> megera(at)mira:~/app/pgsql/tsearch2/test_scheme$ createdb qq
> CREATE DATABASE
> megera(at)mira:~/app/pgsql/tsearch2/test_scheme$ psql qq -c "create schema contrib"
> CREATE SCHEMA
> psql qq < ./tsearch2_contrib.sql
> psql qq < ./test.dump
> SET
> SET
> SET
> SET
> CREATE TABLE

Is that because you didn't insert any data into the table before dumping
it? You will get the same error that follows:

> But I get error later:
>
> qq=# insert into test(a) values( 'the hot dog');
> INSERT 3478544 1
> qq=# update test set fts=contrib.to_tsvector(a);
> ERROR: relation "pg_ts_cfg" does not exist
>
> after setting proper search_path it worked:
>
> qq=# set search_path to public,contrib;
> SET
> qq=# update test set fts=contrib.to_tsvector(a);
> UPDATE 1

My point is that if you pg_dump a table that has data in it, pg_dump
will set yoru search_path for you, and so the restore will fail.

> pg_dump qq > qq.dump
> dropdb qq
> createdb qq
> psql qq < qq.dump
> qq=# set search_path to public,contrib;
> SET
> qq=# update test set fts=contrib.to_tsvector(a);
> UPDATE 1
>
>
> works like a charm :)

I bet you don't have any data in the table.

Chris