Re: BUG #6666: pg_upgrade 9.2beta1 plpython/plpython2 - Mailing list pgsql-bugs

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@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

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