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