From: | Bruce Momjian <bruce(at)momjian(dot)us> |
---|---|
To: | Stuart Bishop <stuart(at)stuartbishop(dot)net> |
Cc: | PostgreSQL-development <pgsql-hackers(at)postgreSQL(dot)org> |
Subject: | Re: [BUGS] BUG #6532: pg_upgrade fails on Python stored procedures |
Date: | 2012-03-16 17:54:02 |
Message-ID: | 20120316175402.GD8738@momjian.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs 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(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. +
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2012-03-16 17:56:44 | Re: BUG #6535: LEFT JOIN on large table is altering data |
Previous Message | Alex Hunsaker | 2012-03-16 17:48:24 | Re: BUG #6511: calling spi_exec_query from non-main package, results in: couldn't fetch $_TD |
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2012-03-16 18:08:53 | Re: [BUGS] BUG #6532: pg_upgrade fails on Python stored procedures |
Previous Message | Tom Lane | 2012-03-16 17:47:54 | Re: patch: autocomplete for functions |