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@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

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