parallel pg installation and functions gotcha
I've just spent a day trying to migrate my databases from 7.1 to 7.3.2. The
only problem has been that every time a function was called, I got the
following error:
FATAL: Pre-7.3 object file made an elog() call. Recompile.
The problem, I now realize, is that the output of pg_dumpall contains lines
like
CREATE FUNCTION plpgsql_call_handler () RETURNS opaque
AS '/usr/local/lib/plpgsql.so', 'plpgsql_call_handler'
LANGUAGE "C";
However, my approach to upgrading involved installing a parallel copy of the
pg binaries, in a directory other than /usr/local/. So my functions were,
apparently, using the 7.1 plpgsql.so.
My solution: switch the lines in the output of pg_dumpall to point to the
current plpgsql.so, and restore normally. Everything works now.
A caution in INSTALL's 'If You Are Upgrading' section might be useful for
others trying to have two different sets of binaries on the same machine.
Joe Slag <joe.slag@walkerart.org> writes:
CREATE FUNCTION plpgsql_call_handler () RETURNS opaque
AS '/usr/local/lib/plpgsql.so', 'plpgsql_call_handler'
LANGUAGE "C";
My solution: switch the lines in the output of pg_dumpall to point to the
current plpgsql.so, and restore normally. Everything works now.
The preferred definition nowadays uses a version-independent
reference to the installation's library directory:
CREATE FUNCTION plpgsql_call_handler () RETURNS language_handler
AS '$libdir/plpgsql', 'plpgsql_call_handler'
LANGUAGE c;
Unfortunately we were not bright enough to make it like that from day
one, so old dump files are a hazard :-(
regards, tom lane