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

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(dot)klaver(at)gmail(dot)com