Re: BUG #6666: pg_upgrade 9.2beta1 plpython/plpython2

On Thu, May 31, 2012 at 06:30:30PM -0400, Bruce Momjian wrote:
> > Hm, I'm not sure about that. The general charter of pg_dump is to
> > produce a dump that will replicate the state of the database.
> > Editorializing on it in order to make it more likely to reload in a
> > different version of PG seems to violate that charter.
> >
> > I think the current state where pg_upgrade just complains about those
> > functions and tells you to remove them by hand is far safer than
> > creating blind spots in pg_dump.
>
> Agreed. I think the big question is whether the 8.1 move of the PL
> language support functions to pg_catalog should have suppressed dumping
> the pre-8.1 PL functions in the public schema.
>
> Another question is whether having these functions in two schemas
> presents any possible danger. Users using pg_dumpall and restoring (not
> using pg_upgrade) will have the plpython functions removed because they
> will error out, so maybe we should just let the plpython renaming trim
> those out. However, this doesn't remove the other PL lanauge
> duplicates.
>
> I share Tom's caution on this, but I think we need to make sure we are
> addressing any possible risk of an isolated pg_upgrade fix, now that we
> understand the cause.

FYI, this query will show any functions defined in the public schema
who's names match pg_pltemplate helper functions:

SELECT proname
FROM pg_proc JOIN pg_namespace ON (pronamespace = pg_namespace.oid)
WHERE proname IN
(
SELECT tmplhandler FROM pg_pltemplate
UNION
SELECT tmplinline FROM pg_pltemplate
UNION
SELECT tmplvalidator FROM pg_pltemplate
) AND
nspname = 'public';

This is normal in pre-8.1 but might indicate orphaned functions in PG
8.1+.

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

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