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

On 05/29/2012 05:44 PM, Bruce Momjian wrote:
> 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.

Yes, I had the same layout for plpgsql. Already got rid of the public entry.

>
> 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

Thanks.

>
> 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.

Not sure what duplicate entries you are referring to. The two pg_catalog
entries refer to different handlers, the call handler and the inline
handler.

>


--
Adrian Klaver
adrian.klaver@gmail.com