Re: BUG #6666: pg_upgrade 9.2beta1 plpython/plpython2

On Sun, May 27, 2012 at 11:49:31AM -0700, Adrian Klaver wrote:
> >
> > If you can help me find out how these got defined this way, I might be
> > able to prevent this problem for the next person.
> >
>
> After reading the above thread here is what the queries mentioned return:
>
> production=# SELECT nspname,proname,probin FROM pg_proc,pg_namespace
> WHERE probin LIKE '%python%' and pg_proc.pronamespace=pg_namespace.oid;
>
> nspname | proname | probin
> ------------+-------------------------+------------------
> pg_catalog | plpython_call_handler | $libdir/plpython
> pg_catalog | plpython_inline_handler | $libdir/plpython
> public | plpython_call_handler | $libdir/plpython
> (3 rows)

You are the third person to have this problem, and this was the
information I needed to properly find the cause. I believe it was done
in this 8.1 commit:

e0dedd0559f005d60c69c9772163e69c204bac69

Implement a preliminary 'template' facility for procedural languages,
as per my recent proposal. For now the template data is hard-wired
in proclang.c --- this should be replaced later by a new shared
system catalog, but we don't want to force initdb during 8.1 beta.
This change lets us cleanly load existing dump files even if they
contain outright wrong information about a PL's support functions, such
as a wrong path to the shared library or a missing validator function.
Also, we can revert the recent kluges to make pg_dump dump PL support
functions that are stored in pg_catalog. While at it, I removed the
code in pg_regress that replaced $libdir with a hardcoded path for
temporary installations. This is no longer needed given our support
for relocatable installations.

This moved the helper functions into pg_catalog, but the author probably
didn't realize that public schema helper functions would continue to be
dumped by pg_dump. These helper functions continued to be
dumped/restored until the rename. There are certainly helper functions
for other languages that are still duplicated in the public schema ---
there is nothing unique about plpython. We are only seeing problems
because of the plpython.so rename.

In normal use, a pg_dumpall restore would throw an error about a missing
helper function shared objects, but the pg_catalog entry would continue
to work just fine. Odd we have not heard complaints from users seeing
that error on restore --- odds are, they are just ignoring the error,
which pg_upgrade does not do.

The attached pg_upgrade patch adds checks for this plpython helper
function and reports a proper error, suggesting how to fix the problem:

Performing Consistency Checks
-----------------------------
Checking current, bin, and data directories ok
Checking cluster versions ok

The old cluster has a "plpython_call_handler" function defined
in the "public" schema which is a duplicate of the one defined
in the "pg_catalog" schema. You can confirm this by executing
in psql:

\df *.plpython_call_handler

The "public" schema version of this function was created by a
pre-8.1 install of plpython, and must be removed for pg_upgrade
to complete because it references a now-obsolete "plpython"
shared object file. You can remove the "public" schema version
of this function by running the following command:

DROP FUNCTION public.plpython_call_handler()

in each affected database:

test
test3

Remove the problem functions from the old cluster to continue.
Failure, exiting

We could do the same for other PL languages if they are ever renamed. I
suppose we don't care about fixing the duplicate schema entries.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +