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 vitus@ice.ru
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