Thread

1. Full path to procedural language in the dump is a bug

   Victor Wagner <vitus@ice.ru> — 2002-04-08T18:59:05Z

   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
   
   
   

2. Re: What's the difference?

   Victor Wagner <vitus@ice.ru> — 2002-04-08T19:17:42Z

   >Victor Wagner <vitus@ice.ru> writes:
   >> As far as I understand, following three queries are exactly equivalent:
   
   >Same results, but the second two constrain the planner's choice of join
   >order.  See
   
   >http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/explicit-joins.html
   
   >Whether this is a feature or a bug depends on context...
   
   >			regards, tom lane
   
   I can agree that this is feature if one uses natural or inner joins.
   
   But if query semantic needs outer joins there is no way to tell the
   planner that it is free to choose order of joining.
   
   Only thing left is to join with result of subquery, which makes
   entire query much less readable.
   
   I'd think that simpliest way of writing query should result in most
   freedom for optimizer to choose an execution plan.
   
   It is so for inner joins, but not for outer ones.
   
   
   
   
   
   -- 
   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
   
   
   

3. Re: Full path to procedural language in the dump is a bug

   Tom Lane <tgl@sss.pgh.pa.us> — 2002-04-08T19:32:16Z

   Victor Wagner <vitus@ice.ru> writes:
   > 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:
   
   You're too late: 7.2 in fact does things this way.  Unfortunately,
   for a database reloaded from a 7.1 dump, you do have to do a one-time
   fix of the absolute paths.
   
   			regards, tom lane
   
   

4. Re: What's the difference?

   Tom Lane <tgl@sss.pgh.pa.us> — 2002-04-08T19:43:51Z

   Victor Wagner <vitus@ice.ru> writes:
   >> Same results, but the second two constrain the planner's choice of join
   >> order.  See
   
   >> http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/explicit-joins.html
   
   >> Whether this is a feature or a bug depends on context...
   
   >> regards, tom lane
   
   > I can agree that this is feature if one uses natural or inner joins.
   
   > But if query semantic needs outer joins there is no way to tell the
   > planner that it is free to choose order of joining.
   
   But it is *not* free to alter the join order of outer joins; if it does,
   that will change the result.  See the discussion on the above-linked
   page.
   
   			regards, tom lane