It is very natural to careful system administrator to upgrade production database following way:
1. Install new version of PostgreSQL in alternate location 2. Start it on alternate port 3. restore all the data from latest backup 4. Test the installation 5. And only than put it in production use. Unfortunately, pg_dump places full pathname to the shared library, which implements procedural language, into dump file. So, if you just load dump into new version, installed into alternate location, wrong version of the library would be used. Today I've again spent two hours fighting the problem that all plpgsql functions stopped to work when I've upgraded from 7.1.3 to 7.2.1. It required passing the dump file through sed to fix the pathes, and reloading entire database (which was about 300Mb). And first I have to understand where the problem lies. Of course, there is the way to fix the problem quickly using new "create or replace function" command, introduced in 7.2. But it requires experienced developer rather than system administrator to quickly find this solution. So, one has to remember that when moving dump from one PostgreSQL installation to another, one has to check all pathes to standard shared objects in the dump file. And this is hard to remember, becouse upgrades which require re-creation of database from dump fortunately do not happen too often. I propose solution to this problem - define a predefined substitution variable pg_lib in the psql which points to the library directory of current installation, and make pg_dump output procedural language implementation following way: CREATE FUNCTION "plpgsql_call_haldler" () RETURNS opaque AS :pg_lib || "/plpgsql.so", 'plpgsql_call_handler' LANGUAGE 'C'; This would also simplify moving databases from one server to another (even on different platoform) and writing SQL scripts which create procedural lanugages, which is often neccessary when installing complicated software system, and placing language definition into the same SQL file as function definition would improve maintainability, compared with invoking createlang as separate command. -- Victor Wagner [EMAIL PROTECTED] Chief Technical Officer Office:7-(095)-748-53-88 Communiware.Net Home: 7-(095)-135-46-61 http://www.communiware.net http://www.ice.ru/~vitus ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
