Re: Subtle pg_dump problem...

Christopher,

On Wed, 12 May 2004, Christopher Kings-Lynne wrote:

> OK, I'll try to explain it better.
>
> 1. Tsearch2 requires access to several tables.
>
> 2. You can edit the tsearch2.sql script and change the "set schema =
> ..." to contrib.

Aha, this is what I thought about.

>
> 3. You load all the tsearch2 objects into contrib.
>

createdb qq
psql qq -c "create schema contrib"
psql qq < tsearch2_contrib.sql

> 4. You create a table in the public schema with a column of type
> contrib.vector, and a trigger of contrib.tsearch2.
>

qq=# create table test ( a text, fts contrib.tsvector);
CREATE TABLE

> 5. You pg_dump that table, you get:
>
> SET search_path = public, pg_catalog;
>
> COPY ...
>
> (Because the table is in the public schema)

done.

>
> 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

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

> 7. You get this error because the tsearch2 code depends on the current
> search path, and since contrib is not in the search path, the restore fails.
>
> 8. This problem occurs because tsearch2 is dependent on the current
> user's search_path. Instead, it should be independent of the current
> user's search path, and instead try to find its configuration tables in
> the same schema in which the vector type or the tsearch2 trigger
> function resides.
>
> This assumes that the user has installed all the tsearch2 objects into
> the same schema, which I think is reasonable.
>
> This problem will occur for anyone who has multiple schemas and tries to
> create vector columns in tables that refer to the vector type in another
> schema.
>
> Does that make sense?

Sorry, I don't see the problem. I just pg_dump whole db and recreated without
any problem. For working with tsearch2 I should set correct search_path,
but what's wrong with this ?

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 :)

One remark:

I applied regprocedure_7.4.patch.gz to be able dump/restore
without issue with OIDs. Upgrading existed tsearch2 installation
should be easy ( thanks Andrew for his script ):
Actually, for playing with schema I added set search_path = contrib; to his script.
Original script is available from
http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/regprocedure_update.sql

psql qq < regprocedure_update.sql ( first line is "set search_path = contrib;")

now, database qq could be dumped/restored without problem.

>
> Chris
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83