Re: [BUGS] BUG #6532: pg_upgrade fails on Python stored procedures - Mailing list pgsql-hackers

On Thu, Mar 15, 2012 at 09:18:36PM +0700, Stuart Bishop wrote:
> On Thu, Mar 15, 2012 at 9:01 PM, Stuart Bishop <stuart@stuartbishop.net> wrote:
> 
> > Yes, it is there. I can see the library with the new name of
> > plpython2.so, not the old plpython.so from 8.4. createlang installs
> > the language just fine if I build a cluster and database myself.
> 
> As expected, symlinking plpython2.so to plpython.so works around
> things. I have no idea if this work around will cause problems when
> upgrading the db to PG 9.2+.

[ Thread moved to hackers.]

Well, it will because, by creating the symlink, you allowed this
function to be restored into the new database, and it isn't properly
hooked to the plpython language.  I wonder if you should just delete it
because I believe you already have the right plpython2 helper functions
in place.  Can you run this query for me in one of the problem databases
in the new and/or old cluster and send me the output:
SELECT proname,probin FROM pg_proc WHERE probin LIKE '%python%';

What we need is for pg_dumpall to _not_ output those handlers.

I did some more digging on this.  I am afraid it is related to this
problem I discovered on March 5 where the plpython2 helper functions
remain after you drop the plpythonu language:
http://archives.postgresql.org/pgsql-hackers/2012-03/msg00254.php

However, in testing upgrades from 8.4 and 9.0, I don't see those helper
functions in the pg_dumpall output, which is very good news.  It means
this python problem will not hit all users, and hopefully few.

Remember, the fix for pg_upgrade in 9.1.3 was to have the shared library
file check be adjusted for plpython --- it didn't relate to what
pg_dumpall dumps, and as far as I can tell, it is working fine.  

I did this for testing:
PGDATA=/u/pgsql.old/data pgstartsleep 2aspg /u/pgsql.old/bin/createlang plpythonu testsql -c 'CREATE OR REPLACE
FUNCTIONpymax (a integer, b integer) RETURNSinteger AS     $$         if a > b:           return a         return b
$$LANGUAGE plpythonu;' testaspg /u/pgsql.old/bin/psql -c 'DROP LANGUAGE plpythonu CASCADE;' testaspg
/u/pgsql.old/bin/psql-c "SELECT proname,probin FROM pg_proc WHERE                probin LIKE '%python%';"
testPGDATA=/u/pgsql.old/datapgstop
 

The SELECT outputs two row from pg_proc:
         proname         |      probin-------------------------+------------------ plpython_call_handler   |
$libdir/plpythonplpython_inline_handler | $libdir/plpython(2 rows)
 

showing that even with the plpython language gone, the handler functions
are still here.  However, those functions do _not_ appear in the
pg_dumpall --binary-upgrade --schema-only output, unlike what you are
seeing.  What the reporter from March 5 and you are seeing are cases
where the support functions are being output, which triggers the
pg_upgrade failure because the shared library was renamed.  For the
March 5 reporter, they actually removed plpython, but still had the
handlers, and the handlers were being dumped by pg_dumpall.

The big question is why do the handlers sometimes get dumped, and
sometimes not.  The good news is that my testing shows that they are
often _not_ dumped, and pg_upgrade works fine.

This the query pg_dumpall is using:
SELECT tableoid, oid, proname, prolang, pronargs, proargtypes,prorettype, proacl, pronamespace, (SELECT rolname FROM
pg_catalog.pg_rolesWHERE oid = proowner) AS rolname FROM pg_proc p WHERE NOTproisagg AND (pronamespace != (SELECT oid
FROMpg_namespace WHEREnspname = 'pg_catalog'));
 

and I don't get any output running it on my old cluster.  Do you get
rows output?  Specifically, is your handler not in the pg_catalog
schema?

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +