Re: [BUGS] BUG #6532: pg_upgrade fails on Python stored procedures

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(at)stuartbishop(dot)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 pgstart
sleep 2
aspg /u/pgsql.old/bin/createlang plpythonu test
sql -c 'CREATE OR REPLACE FUNCTION pymax (a integer, b integer) RETURNS
integer AS
$$
if a > b:
return a
return b
$$ LANGUAGE plpythonu;' test
aspg /u/pgsql.old/bin/psql -c 'DROP LANGUAGE plpythonu CASCADE;' test
aspg /u/pgsql.old/bin/psql -c "SELECT proname,probin FROM pg_proc WHERE
probin LIKE '%python%';" test
PGDATA=/u/pgsql.old/data pgstop

The SELECT outputs two row from pg_proc:

proname | probin
-------------------------+------------------
plpython_call_handler | $libdir/plpython
plpython_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_roles WHERE oid = proowner) AS rolname FROM pg_proc p WHERE NOT
proisagg AND (pronamespace != (SELECT oid FROM pg_namespace WHERE
nspname = '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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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