I haven’t seen a lot of talk about this problem, but as I have seen the question pop-up recently among even some well-known postgresql users, I thought maybe I ought to write some notes to help out future generations and all that. Basically it all revovles around the following errors when upgrading an 8.1 system with tsearch2 into an 8.2 system.
/opt/pgsql/bin/pg_dump -U postgres -h crank-va-1 toady | psql -U postgres toady82c
CREATE FUNCTION
ALTER FUNCTION
CREATE FUNCTION
ALTER FUNCTION
ERROR: could not find function "snb_ru_init" in file "/opt/pgsql823/lib/tsearch2.so"
ERROR: function public.snb_ru_init(internal) does not exist
ALTER TABLE
ALTER TABLE
ALTER TABLE
ERROR: function "snb_ru_init(internal)" does not exist
CONTEXT: COPY pg_ts_dict, line 3, column dict_init: "snb_ru_init(internal)"
ALTER TABLE
ALTER TABLE
The problem stems (pun only slightly intended) from the fact that the tsearch libraries in 8.2 do not contain the function “snb_ru_init” (it was split into two functions, a koi8 version and a utf8 version), so when you attempt to dump/restore your 8.1 tsearch, it tries to create the function it used in 8.1 and then chokes after that function is not found. How bad this is for you depends on a number of things.
If you did COPY style dumps, then you wont have any information in pg_ts_dict, which probably means that none of your tsearch languages will work, which means your full text searching will be completly shot (but fixable, just more work). If you did INSERT style dumps, then the only thing you should be missing is the russian tsearch configuration, which, if you don’t speak russian, should not effect you at all and you can actually just ignore the error and move on with your life. However that’s not really what I would recommend, only because it’s possible that this problem will bite you again if you do nothing. Instead I recoommend folks take a little bit of pain now to clean up thier tsearch setup to avoid problems down the line. Here are the steps I would recommend:
- Install 8.2 and compile it with tsearch2 support. Go ahead and create the database you will be upgrading into, but don’t add anything to it yet.
- Connecting into your new database and create a new schema “tsearch2”
- Modify your tsearch2.sql file: change the set schema_path bit to point to tsearch2, and move it within the install transaction while you are there (why it is split out I have no idea, but seems dumb to me)
- Log back into your database and install tsearch into the tsearch2 schema
- Modify your default search path (either db wide or at the user level) and add the tsearch2 schema into it.
- Now, create an SQL dump of your 8.1 install, search through it, and remove the entries/data for the pg_ts_* tables. Also look for any explicit set search path entries and either remove them, or add the tsearch2 schema to them as needed.You should now be able to run this dump into your 8.2 setup and have a working installation.
- Alternativly, if you have a large dump, you should be able to do a -Fc dump and use a restore list to remove the pg_ts_* bits on restore. I didn’t test this, but it should work
For the curious, the way this works is that any datatypes / triggers / etc… that reference tsearch are going to look in the search path for something resembling the functions / tables they need, so as long as they find your 8.2 version, they will be happy.
Also be aware that if you have a more complicated setup, you might need to do more. On one system I have only one database is uses tsearch, so I modified that databases users search_path to include tsearch2 and left the cluster wide setting alone. This also meant I needed to grant usage on the tsearch2 schema to that databases user.
One last note, going back to the “this problem might bite you again” part. Currently there is a patch against postgresql 8.3 to merge tsearch2 into the core postgresql system. While it’s still too early to tell if this patch will be accepted, if it is you can be sure it is going to cause more heartache for tsearch2 enabled dumps, so being able to easily manipulate your tsearch2 support will pay nice dividends at that point.