Re: Lost plpgsql function

Richard Huxton <dev(at)archonet(dot)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