Re: BUG #6706: pg_upgrade fails when plpgsql dropped/re-created

On Tue, Jul 10, 2012 at 2:43 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> On Tue, Jul 10, 2012 at 01:03:18PM -0700, Maciek Sakrejda wrote:
>> So, is there hope of a better fix here for 9.2 (specifically for
>> preserving extension ownership on pg_upgrade and dump/restore, though
>> I understand it may not make sense to do that if we can't fix a number
>> of related issues)? If not, is the below catalog-twiddling sane,
>> lacking an ALTER EXTENSION foo OWNER TO ...?
>>
>> postgres=# update pg_extension set extowner = (select oid from
>> pg_roles where rolname = 'maciek') where extname = 'plpgsql';
>
> There is no hope that any changes to extensions will be preserved across
> upgrades any better than it was in 9.1 --- the change is only that
> pg_upgrade will not fail.

Even in the absence of preservation, the problem is there doesn't seem
to be a way to re-seat the extension into the correct, previous
configuration either. One can't just DROP CASCADE plpgsql, lest
existing UDFs depend up on them, and one can't reassign the owner
in-place either to fix the situation. Except, maybe, via this catalog
hack.

--
fdr