Re: BUG #6532: pg_upgrade fails on Python stored procedures

The following bug has been logged on the website:

Bug reference: 6532
Logged by: Stuart Bishop
Email address: stuart(at)stuartbishop(dot)net
PostgreSQL version: 9.1.3
Operating system: Ubuntu
Description:

The 9.1.3 changelog states pg_upgrade's handing of plpython stored
procedures was fixed, but that does not appear to be the case:

postgres(at)aargh:~$ /usr/lib/postgresql/9.1/bin/pg_upgrade --version
pg_upgrade (PostgreSQL) 9.1.3
postgres(at)aargh:~$ /usr/lib/postgresql/9.1/bin/pg_upgrade
--old-bindir=/usr/lib/postgresql/8.4/bin
--new-bindir=/usr/lib/postgresql/9.1/bin --old-datadir=8.4/main
--new-datadir=9.1/mig --old-port=5433 --new-port=5435
Performing Consistency Checks
-----------------------------
Checking current, bin, and data directories ok
Checking cluster versions ok
Checking database user is a superuser ok
Checking for prepared transactions ok
Checking for reg* system oid user data types ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for large objects ok
Creating catalog dump ok
Checking for prepared transactions ok
Checking for presence of required libraries ok

| If pg_upgrade fails after this point, you must
| re-initdb the new cluster before continuing.
| You will also need to remove the ".old" suffix
| from 8.4/main/global/pg_control.old.

Performing Upgrade
------------------
Adding ".old" suffix to old global/pg_control ok
Analyzing all rows in the new cluster ok
Freezing all rows on the new cluster ok
Deleting new commit clogs ok
Copying old commit clogs to new server ok
Setting next transaction id for new cluster ok
Resetting WAL archives ok
Setting frozenxid counters in new cluster ok
Creating databases in the new cluster ok
Adding support functions to new cluster ok
Restoring database schema to new cluster
psql:/var/lib/postgresql/pg_upgrade_dump_db.sql:3992: ERROR: could not
access file "$libdir/plpython": No such file or directory

There were problems executing "/usr/lib/postgresql/9.1/bin/psql" --set
ON_ERROR_STOP=on --no-psqlrc --port 5435 --username "postgres" -f
"/var/lib/postgresql/pg_upgrade_dump_db.sql" --dbname template1 >>
"/dev/null"
Failure, exiting

The relevant section of pg_upgrade_dump_db.sql is:

CREATE FUNCTION plpython_call_handler() RETURNS language_handler
LANGUAGE c
AS '$libdir/plpython', 'plpython_call_handler';

Hi,

On Thursday, March 15, 2012 02:13:29 PM stuart(at)stuartbishop(dot)net wrote:
> The 9.1.3 changelog states pg_upgrade's handing of plpython stored
> procedures was fixed, but that does not appear to be the case:
> ...
> access file "$libdir/plpython": No such file or directory
Well. That looks like you didn't install plpython on the new cluster. Are you
sure its there?

Andres

On Thu, Mar 15, 2012 at 8:54 PM, Andres Freund <andres(at)anarazel(dot)de> wrote:
> Hi,
>
> On Thursday, March 15, 2012 02:13:29 PM stuart(at)stuartbishop(dot)net wrote:
>> The 9.1.3 changelog states pg_upgrade's handing of plpython stored
>> procedures was fixed, but that does not appear to be the case:
>> ...
>> access file "$libdir/plpython": No such file or directory
> Well. That looks like you didn't install plpython on the new cluster. Are you
> sure its there?

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.

--
Stuart Bishop <stuart(at)stuartbishop(dot)net>
http://www.stuartbishop.net/

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

--
Stuart Bishop <stuart(at)stuartbishop(dot)net>
http://www.stuartbishop.net/

On Thu, Mar 15, 2012 at 01:13:29PM +0000, stuart(at)stuartbishop(dot)net wrote:
> The following bug has been logged on the website:
>
> Bug reference: 6532
> Logged by: Stuart Bishop
> Email address: stuart(at)stuartbishop(dot)net
> PostgreSQL version: 9.1.3
> Operating system: Ubuntu
> Description:
>
> The 9.1.3 changelog states pg_upgrade's handing of plpython stored
> procedures was fixed, but that does not appear to be the case:

The change in 9.1.3 was to allow the pg_upgrade code which checks if all
the shared librarires are in place to see plpython.so as equivalent to
plpython2.so. I did not modify pg_dump at all, which is where you are
seeing the error.

> There were problems executing "/usr/lib/postgresql/9.1/bin/psql" --set
> ON_ERROR_STOP=on --no-psqlrc --port 5435 --username "postgres" -f
> "/var/lib/postgresql/pg_upgrade_dump_db.sql" --dbname template1 >>
> "/dev/null"
> Failure, exiting
>
>
> The relevant section of pg_upgrade_dump_db.sql is:
>
> CREATE FUNCTION plpython_call_handler() RETURNS language_handler
> LANGUAGE c
> AS '$libdir/plpython', 'plpython_call_handler';

OK, I am pretty confused by this.

Here is all I get in the pg_dumpall --binary-upgrade output for
plpython when I create one plpython function:

--
-- Name: plpythonu; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: postgres
--

CREATE OR REPLACE PROCEDURAL LANGUAGE plpythonu;


ALTER PROCEDURAL LANGUAGE plpythonu OWNER TO postgres;

SET search_path = public, pg_catalog;

--
-- Name: pymax(integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION pymax(a integer, b integer) RETURNS integer
LANGUAGE plpythonu
AS $$
if a > b:
return a
return b
$$;


ALTER FUNCTION public.pymax(a integer, b integer) OWNER TO postgres;

I have repeatedly upgraded from 9.0.X to 9.1.3 and am seeing no
failures. The big question is what are you doing that is causing the
plpython_call_handler() function to be dumped? That is an internal
function. What is your old PG version? I tested 8.4 and also could not
get the failure you see either.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

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

On Fri, Mar 16, 2012 at 03:10:05PM +0700, Stuart Bishop wrote:
> > I have repeatedly upgraded from 9.0.X to 9.1.3 and am seeing no
> > failures.  The big question is what are you doing that is causing the
> > plpython_call_handler() function to be dumped?  That is an internal
> > function.  What is your old PG version?  I tested 8.4 and also could not
> > get the failure you see either.
>
> This database schema began its life on PostgreSQL 7.4 over 8 years
> ago, so there may well be something unexpected lurking in there.

[ Email moved to hackers, and trimmed.]

That might be the cause --- see my posting asking for details.

> I can reproduce the error with the attached schema. It was created
> using 8.4's pg_dump. If I create a fresh 8.4 cluster and restore this,
> pg_dump and pg_dumpall spit out the plpython_call_handler statements.
> I think I've stripped out everything in there not in core or contrib.

Thanks. Yes, this actually does show the cause:

> --
> -- Name: plpgsql_call_handler(); Type: FUNCTION; Schema: public; Owner: -
> --
>
> CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler
> LANGUAGE c
> AS '$libdir/plpgsql', 'plpgsql_call_handler';
>
>
> --
> -- Name: plpython_call_handler(); Type: FUNCTION; Schema: public; Owner: -
> -- ******
>
> CREATE FUNCTION plpython_call_handler() RETURNS language_handler
> LANGUAGE c
> AS '$libdir/plpython', 'plpython_call_handler';
>

Notice these are all in the public schema, which is causing pg_dumpall
to output them, and the rename of plpython is causing the failure. Do
you have these functions also in the pg_catalog schema? If so, they
must be dead functions left over from an old release of Postgres.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

On Sat, Mar 17, 2012 at 12:54 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> 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%';

# 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
public | plpython_call_handler | $libdir/plpython
(2 rows)

I have no idea how I managed to grow the duplicate in the public
schema, but this does seem to be the source of the confusion. I might
be able to dig out when I grew it from revision control, but I don't
think that would help.

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

Or pick it up in the check stage and make the user resolve the
problem. If I shot myself in the foot in some particularly obtuse way,
it might not be sane to bend over backwards making pg_upgrade repair
things.

--
Stuart Bishop <stuart(at)stuartbishop(dot)net>
http://www.stuartbishop.net/

On Sat, Mar 17, 2012 at 01:57:29AM +0700, Stuart Bishop wrote:
> On Sat, Mar 17, 2012 at 12:54 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>
> > 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%';
>
> # 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
> public | plpython_call_handler | $libdir/plpython
> (2 rows)
>
> I have no idea how I managed to grow the duplicate in the public
> schema, but this does seem to be the source of the confusion. I might
> be able to dig out when I grew it from revision control, but I don't
> think that would help.

Yes, if you delete the public one, you should be fine. If you need
CASCADE, then something is wrong because their is some depenency on it.
Odds are it might have gotten created before we had full schema support
for language or something. The March 5 reporter probably had the same
problem, so isn't just you.

> > What we need is for pg_dumpall to _not_ output those handlers.
>
> Or pick it up in the check stage and make the user resolve the
> problem. If I shot myself in the foot in some particularly obtuse way,
> it might not be sane to bend over backwards making pg_upgrade repair
> things.

I think we need someone to figure out how this happened before we
actually adjust anything. At least we know what to advise people now.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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