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

On 05/26/2012 07:24 PM, Bruce Momjian wrote:
> On Sat, May 26, 2012 at 11:42:09PM +0000, adrian(dot)klaver(at)gmail(dot)com wrote:
>> The following bug has been logged on the website:
>>
>> Bug reference: 6666
>> Logged by: Adrian Klaver
>> Email address: adrian(dot)klaver(at)gmail(dot)com
>> PostgreSQL version: 9.0.7
>> Operating system: Linux/OpenSuse 12.1
>> Description:
>>

>>
>> I just tried an upgrade from 9.0.7 to 9.2beta1 using pg_upgrade. I ran it
>> first with -c and everything passed. When I did a live upgrade it failed
>> with this message in pg_upgrade_restore.log:
>>
>> CREATE FUNCTION plpython_call_handler() RETURNS language_handler
>> LANGUAGE c
>> AS '$libdir/plpython', 'plpython_call_handler';
>> psql:pg_upgrade_dump_db.sql:13541: ERROR: could not access file
>> "$libdir/plpython": No such file or directory
>>
>> That is indeed true, there is no plpython, there is plpython2 instead. I
>> created a symlink plpython --> plpython2 and the upgraded succeeded after a
>> re-initdb of the new cluster.
>
> It took me a little while to remember the cause of this problem, but I
> found it!
>
> http://archives.postgresql.org/pgsql-hackers/2012-03/msg01101.php
>
> This was reported in March, 2012 --- please read the entire thread. The
> problem is that you have a plpython_call_handler defined in the "public"
> schema, and it is being dumped out by pg_dumpall, rather than using a
> generic CREATE LANGUAGE statement. Odds are this is left over from an
> older version of Postgres.

This particular database dates back to version 7.2, though plpythonu was not
added until version 8.0.x using the following:

CREATE PROCEDURAL LANGUAGE 'plpythonu'
HANDLER plpython_call_handler;

Until now my procedure has been to do dump/restore to move from one major version to another.

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

production=# SELECT tableoid, oid, proname, prolang, pronargs, proargtypes,
prorettype, proacl, pronamespace, (SELECT rolname FROM pg_catalog.pg_roles WHERE
oid = proowner) AS rolname FROM pg_proc p WHERE NOT proisagg AND
(pronamespace != (SELECT oid FROM pg_namespace WHERE
nspname = 'pg_catalog'));
tableoid | oid | proname | prolang | pronargs | proargtypes | prorettype | proacl | pronamespace | rolname
----------+-----+---------+---------+----------+-------------+------------+--------+--------------+---------
(0 rows)

If you need any more information let me know.

Thanks,

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com