Re: pg_upgrade libraries check

On 05/27/2012 06:40 AM, Bruce Momjian wrote:
> On Fri, May 25, 2012 at 11:08:10PM -0400, Bruce Momjian wrote:
>> On Fri, May 25, 2012 at 10:20:29AM -0400, Andrew Dunstan wrote:
>>> pg_upgrade is a little over-keen about checking for shared libraries
>>> that back functions. In particular, it checks for libraries that
>>> support functions created in pg_catalog, even if pg_dump doesn't
>>> export the function.
>>>
>>> The attached patch mimics the filter that pg_dump uses for functions
>>> so that only the relevant libraries are checked.
>>>
>>> This would remove the need for a particularly ugly hack in making
>>> the 9.1 backport of JSON binary upgradeable.
>> Andrew is right that pg_upgrade is overly restrictive in checking _any_
>> shared object file referenced in pg_proc. I never expected that
>> pg_catalog would have such references, but in Andrew's case it does, and
>> pg_dump doesn't dump them, so I guess pg_upgrade shouldn't check them
>> either.
>>
>> In some sense this is a hack for the JSON type, but it also gives users
>> a way to create shared object references in old clusters that are _not_
>> checked by pg_upgrade, and not migrated to the new server, so I suppose
>> it is fine.
> OK, now I know it is _not_ fine. :-(
>
> I just realized the problem as part of debugging the report of a problem
> with plpython_call_handler():
>
> http://archives.postgresql.org/pgsql-hackers/2012-03/msg01101.php
> http://archives.postgresql.org/pgsql-bugs/2012-05/msg00205.php
>
> The problem is that functions defined in the "pg_catalog" schema, while
> no explicitly dumped by pg_dump, are implicitly dumped by things like
> CREATE LANGUAGE plperl.
>
> I have added a pg_upgrade C comment documenting this issue in case we
> revisit it later.

"things like CREATE LANGUAGE plperl" is a rather vague phrase. The PL
case could be easily handled by adding this to the query:

OR EXISTS (SELECT 1 FROM pg_catalog.pg_language WHERE lanplcallfoid
= p.oid)

Do you know of any other cases that this would miss?

The fact is that unless we do something like this there is a potential
for unnecessary pg_upgrade failures. The workaround I am currently using
for the JSON backport of having to supply a dummy shared library is
almost unspeakably ugly. If you won't consider changing the query, how
about an option to explicitly instruct pg_upgrade to ignore a certain
library in its checks?

cheers

andrew