Обсуждение: Full path to procedural language in the dump is a bug

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

>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

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

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