Re: Lost plpgsql function

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Richard Huxton" <dev@archonet.com>
Cc: "Mattias Kregert" <mattias@kregert.se>; <lnd@hnit.is>; <pgsql-general@postgresql.org>
Sent: Thursday, January 22, 2004 4:35 PM
Subject: Re: [GENERAL] Lost plpgsql function


> Richard Huxton <dev@archonet.com> writes:
> > On Thursday 22 January 2004 09:58, Mattias Kregert wrote:
> >> I ran into this problem a couple of days ago when I upgraded from 7.0.3 to
> >> 7.4.1. I used the 7.4.1 pg_dumpall, but it created a dump file which tried
> >> to load the old plpgsql.so. I had to fix the dump file manually before
> >> loading it into the new db. There were some other annoyances too, like the
> >> 7.4.1 pg_dumpall dumping out CR in the file, and then the 7.4.1. psql
> >> comlained about it and told me to use \r instead... I had to take out the
> >> "LOCATION" in create database too because it complained about "no such
> >> environment variable". I guess pg_dumpall doesn't understand the
> >> differences between the different versions, so you always have to check the
> >> dump file manually if you are changing version.
>
> > In reality, there probably always will be "one more thing" when upgrading a
> > large database, but that doesn't mean the problems you're having can't be
> > fixed.
>
> > Put together small examples and submit them as bugs. If you can offer patches
> > too, that would make the developers happy.
>
> None of those items are likely to get fixed.
>
> The plpgsql path issue is not pg_dump's fault: the problem is that there
> is an absolute path to the shared library file recorded in the pg_proc
> entry for plpgsql's call handler.  That was how we did things back then.
> The correct entry nowadays is "$libdir/plpgsql" which sidesteps the
> question of exactly where the Postgres shared libraries live; but there
> isn't any reasonable way AFAICS for pg_dump to make that substitution.
> It'd have to replace *any* path in a pg_proc entry with $libdir, which
> would undoubtedly break as many cases as it fixed.
>
> The CR problem is likewise essentially a bug in the older server, or at
> least a definitional incompatibility.  The only way to fix it would be
> a retroactive fix in 7.0.3 and other ancient versions; which requires a
> time machine we haven't got :-(  The simplest workaround I can think of
> is to use pg_dump's "dump using INSERTs" option when upgrading from a
> pre-7.2 release to 7.4.
>
> As for "no such environment variable", that's plain old pilot error.
> If you wanna use environment-variable-defined locations, you gotta
> remember to set the environment variable for the postmaster.
>
> regards, tom lane
>

No no... "no such environment variable" is not "pilot error". I have never used env variables to define locations.
pg_dumpall 7.4.1 puts this stuff in the dump file when dumping a 7.0.3 db. Did this kind of alternate location thing
evenexist in 7.0.3?? Maybe it is interpreting something wrong because the db is too old? 

/M