Thread: Subtle pg_dump problem...
I have a table with a tsearch2 index on it. Now, I have all the tsearch2 stuff installed into a 'contrib' schema. I have had to change the default database schema to include the contrib schema as behind-the-scenes, tsearch2 looks for its tables, and cannot find them even if the function itself is schema-qualfified. This might well be a tsearc2 bug. Anyway, this means the table is dumped like this: SET SESSION AUTHORIZATION 'auadmin'; SET search_path = public, pg_catalog; COPY ... Which give this error upon restoring: ERROR: relation "pg_ts_cfg" does not exist CONTEXT: COPY food_categories, line 1: "79 102 Vegetables, Salads & Legumes \N 'legum':3 'salad':2 'veget':1" It's because the search_path needs to be like this for it to work: SET search_path = public, contrib, pg_catalog; Chris
Thanks Christopher, we'll look into the issue. Oleg On Fri, 7 May 2004, Christopher Kings-Lynne wrote: > I have a table with a tsearch2 index on it. Now, I have all the > tsearch2 stuff installed into a 'contrib' schema. I have had to change > the default database schema to include the contrib schema as > behind-the-scenes, tsearch2 looks for its tables, and cannot find them > even if the function itself is schema-qualfified. This might well be a > tsearc2 bug. > > Anyway, this means the table is dumped like this: > > SET SESSION AUTHORIZATION 'auadmin'; > > SET search_path = public, pg_catalog; > > COPY ... > > Which give this error upon restoring: > > ERROR: relation "pg_ts_cfg" does not exist > CONTEXT: COPY food_categories, line 1: "79 102 Vegetables, > Salads & Legumes \N 'legum':3 'salad':2 'veget':1" > > It's because the search_path needs to be like this for it to work: > > SET search_path = public, contrib, pg_catalog; > > Chris > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > Regards, Oleg _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
Did you guys find any solution to this in the end? Chris Oleg Bartunov wrote: > Thanks Christopher, > we'll look into the issue. > > Oleg > On Fri, 7 May 2004, Christopher Kings-Lynne wrote: > > >>I have a table with a tsearch2 index on it. Now, I have all the >>tsearch2 stuff installed into a 'contrib' schema. I have had to change >>the default database schema to include the contrib schema as >>behind-the-scenes, tsearch2 looks for its tables, and cannot find them >>even if the function itself is schema-qualfified. This might well be a >>tsearc2 bug. >> >>Anyway, this means the table is dumped like this: >> >>SET SESSION AUTHORIZATION 'auadmin'; >> >>SET search_path = public, pg_catalog; >> >>COPY ... >> >>Which give this error upon restoring: >> >>ERROR: relation "pg_ts_cfg" does not exist >>CONTEXT: COPY food_categories, line 1: "79 102 Vegetables, >>Salads & Legumes \N 'legum':3 'salad':2 'veget':1" >> >>It's because the search_path needs to be like this for it to work: >> >>SET search_path = public, contrib, pg_catalog; >> >>Chris >> >> >>---------------------------(end of broadcast)--------------------------- >>TIP 2: you can get off all lists at once with the unregister command >> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >> > > > Regards, > Oleg > _____________________________________________________________ > Oleg Bartunov, sci.researcher, hostmaster of AstroNet, > Sternberg Astronomical Institute, Moscow University (Russia) > Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ > phone: +007(095)939-16-83, +007(095)939-23-83
On Wed, 12 May 2004, Christopher Kings-Lynne wrote: > Did you guys find any solution to this in the end? > Not yet. Could you send me a simple test suite ? I'm not experienced with schema and don't know how to load tsearch2 into specific schema. Oleg > Chris > > Oleg Bartunov wrote: > > > Thanks Christopher, > > we'll look into the issue. > > > > Oleg > > On Fri, 7 May 2004, Christopher Kings-Lynne wrote: > > > > > >>I have a table with a tsearch2 index on it. Now, I have all the > >>tsearch2 stuff installed into a 'contrib' schema. I have had to change > >>the default database schema to include the contrib schema as > >>behind-the-scenes, tsearch2 looks for its tables, and cannot find them > >>even if the function itself is schema-qualfified. This might well be a > >>tsearc2 bug. > >> > >>Anyway, this means the table is dumped like this: > >> > >>SET SESSION AUTHORIZATION 'auadmin'; > >> > >>SET search_path = public, pg_catalog; > >> > >>COPY ... > >> > >>Which give this error upon restoring: > >> > >>ERROR: relation "pg_ts_cfg" does not exist > >>CONTEXT: COPY food_categories, line 1: "79 102 Vegetables, > >>Salads & Legumes \N 'legum':3 'salad':2 'veget':1" > >> > >>It's because the search_path needs to be like this for it to work: > >> > >>SET search_path = public, contrib, pg_catalog; > >> > >>Chris > >> > >> > >>---------------------------(end of broadcast)--------------------------- > >>TIP 2: you can get off all lists at once with the unregister command > >> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > >> > > > > > > Regards, > > Oleg > > _____________________________________________________________ > > Oleg Bartunov, sci.researcher, hostmaster of AstroNet, > > Sternberg Astronomical Institute, Moscow University (Russia) > > Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ > > phone: +007(095)939-16-83, +007(095)939-23-83 > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > Regards, Oleg _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
Christopher, I don't quite understand the problem Did you move pg_ts_* table to schema 'contrib' ? What functions you schema-qualified and how ? It's always required to set search_path properly. Oleg On Wed, 12 May 2004, Christopher Kings-Lynne wrote: > Did you guys find any solution to this in the end? > > Chris > > Oleg Bartunov wrote: > > > Thanks Christopher, > > we'll look into the issue. > > > > Oleg > > On Fri, 7 May 2004, Christopher Kings-Lynne wrote: > > > > > >>I have a table with a tsearch2 index on it. Now, I have all the > >>tsearch2 stuff installed into a 'contrib' schema. I have had to change > >>the default database schema to include the contrib schema as > >>behind-the-scenes, tsearch2 looks for its tables, and cannot find them > >>even if the function itself is schema-qualfified. This might well be a > >>tsearc2 bug. > >> > >>Anyway, this means the table is dumped like this: > >> > >>SET SESSION AUTHORIZATION 'auadmin'; > >> > >>SET search_path = public, pg_catalog; > >> > >>COPY ... > >> > >>Which give this error upon restoring: > >> > >>ERROR: relation "pg_ts_cfg" does not exist > >>CONTEXT: COPY food_categories, line 1: "79 102 Vegetables, > >>Salads & Legumes \N 'legum':3 'salad':2 'veget':1" > >> > >>It's because the search_path needs to be like this for it to work: > >> > >>SET search_path = public, contrib, pg_catalog; > >> > >>Chris > >> > >> > >>---------------------------(end of broadcast)--------------------------- > >>TIP 2: you can get off all lists at once with the unregister command > >> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > >> > > > > > > Regards, > > Oleg > > _____________________________________________________________ > > Oleg Bartunov, sci.researcher, hostmaster of AstroNet, > > Sternberg Astronomical Institute, Moscow University (Russia) > > Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ > > phone: +007(095)939-16-83, +007(095)939-23-83 > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > Regards, Oleg _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
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. 3. You load all the tsearch2 objects into contrib. 4. You create a table in the public schema with a column of type contrib.vector, and a trigger of contrib.tsearch2. 5. You pg_dump that table, you get: SET search_path = public, pg_catalog; COPY ... (Because the table is in the public schema) 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 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? Chris
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@mira:~/app/pgsql/tsearch2/test_scheme$ createdb qq CREATE DATABASE megera@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@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
>>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@mira:~/app/pgsql/tsearch2/test_scheme$ createdb qq > CREATE DATABASE > megera@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
> No problem, Actually, I did some more testing and I properly understand the problem now - and it won't happen in the general restoring case. What fails is if you "pg_dump -a" to just dump the DATA from a table containing a tsearch2 trigger that is in a different schema. Then you delete all the rows from the table. Then you try to execute the sql script created from pg_dump to restore the data. It will fail because the sql script will automatically set the search_path to public, pg_catalog. And then as the COPY command inserts each row, it will fail immediately as the tsearch2 trigger will not be able to find its config table. Does that make sense? Chris
Christopher,
here is a cut'n paste from test script (patch applied):
dropdb qq
createdb qq
psql qq -c "create schema contrib;"
psql qq < tsearch2_contrib-2.sql
psql qq -c "create table test ( a text, fts contrib.tsvector);"
psql qq -c "insert into test(a) values ('I hit a dog');"
psql qq -c "set search_path = public,contrib; update test set fts = to_tsvector(a);"
pg_dump qq > qq.dump
There's certainly one record and after restoring I could use tsearch2
as usual (of course, setting search_path properly).
Oleg
On Wed, 12 May 2004, Christopher Kings-Lynne wrote:
> >>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@mira:~/app/pgsql/tsearch2/test_scheme$ createdb qq
> > CREATE DATABASE
> > megera@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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
Regards, Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
On Wed, 12 May 2004, Christopher Kings-Lynne wrote: > > No problem, > > Actually, I did some more testing and I properly understand the problem > now - and it won't happen in the general restoring case. > > What fails is if you "pg_dump -a" to just dump the DATA from a table > containing a tsearch2 trigger that is in a different schema. > > Then you delete all the rows from the table. > > Then you try to execute the sql script created from pg_dump to restore > the data. > > It will fail because the sql script will automatically set the > search_path to public, pg_catalog. And then as the COPY command inserts > each row, it will fail immediately as the tsearch2 trigger will not be > able to find its config table. > > Does that make sense? Hmm, what other hackers thinks ? This is not just a tsearch2 problem, it could happens with any such kind of things, like defining user defined type in one scheme, using it in another, dumping separate data. Could pg_dump be enough smart to set search_path properly ? > > Chris > Regards, Oleg _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
Oleg Bartunov <oleg@sai.msu.su> writes:
> Hmm, what other hackers thinks ? This is not just a tsearch2 problem,
> it could happens with any such kind of things, like defining user defined
> type in one scheme, using it in another, dumping separate data.
> Could pg_dump be enough smart to set search_path properly ?
It could not. I think the fundamental point here is that it is a real
bad idea for the tsearch routines to make any assumptions about the
current search path. What I would suggest is that the internal objects
used by the tsearch routines (such as pg_ts_cfg) should be required to
live in a specific schema ("tsearch2" seems like a good name) and that
all the internal references inside the tsearch functions should be fully
qualified names.
You could perhaps make this private schema name be selectable at the
time tsearch is built ... but I'm not sure it's worth the trouble.
regards, tom lane
> It could not. I think the fundamental point here is that it is a real
> bad idea for the tsearch routines to make any assumptions about the
> current search path. What I would suggest is that the internal objects
> used by the tsearch routines (such as pg_ts_cfg) should be required to
> live in a specific schema ("tsearch2" seems like a good name) and that
> all the internal references inside the tsearch functions should be fully
> qualified names.
I think a better solution is to change tsearch2 to have two assumptions:
1. All tsearch2 objects will be loaded in the same schema, name not
important.
2. When an object foo is called and needs to refer to another object
bar, it should assume that bar exists in the same schema as foo, and NOT
in the current search_path.
Chris
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> 2. When an object foo is called and needs to refer to another object
> bar, it should assume that bar exists in the same schema as foo, and NOT
> in the current search_path.
That would be great if a C function could find out what schema it had
been declared in, but I don't think it can readily do so.
regards, tom lane
> That would be great if a C function could find out what schema it had > been declared in, but I don't think it can readily do so. There's no context information available to it at all? Even if you go contrib.tsearch2 qualfication? How about making it so that the default context for functions is their own schema? :) Chris
On Wed, 12 May 2004, Tom Lane wrote: > Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes: > > 2. When an object foo is called and needs to refer to another object > > bar, it should assume that bar exists in the same schema as foo, and NOT > > in the current search_path. > > That would be great if a C function could find out what schema it had > been declared in, but I don't think it can readily do so. TODO candidate ? > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend > Regards, Oleg _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
>>> That would be great if a C function could find out what schema it had
>>> been declared in, but I don't think it can readily do so.
>>
>> TODO candidate ?
> Seems like it would be a good thing.
I take that back: you can find it out if you really want to. You can
get your own function OID from the fcinfo->flinfo struct, look that up
in pg_proc, and get the pronamespace field. If you want the schema
name, and not just its OID, that'll cost you a second cache lookup.
Not too many lines of code, though it might be wise to fix things so you
need not repeat this each time through the function.
regards, tom lane
>>That would be great if a C function could find out what schema it had >>been declared in, but I don't think it can readily do so. > > > TODO candidate ? Seems like it would be a good thing. Chris