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

Bruce Momjian 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.

Hi,

I am running into a similar problem without using pg_upgrade.

My application invokes:

  DROP EXTENSION IF EXISTS hstore CASCADE

followed by:
  CREATE EXTENSION IF NOT EXISTS hstore SCHEMA public

and I get:

org.postgresql.util.PSQLException: ERROR: duplicate key value violates
unique constraint "pg_extension_name_index"
  Detail: Key (extname)=(hstore) already exists.
    at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2198)
    at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1927)
    at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:561)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:405)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:397)
[snip]

Where do we go from here?

Thanks,
Gili



--
View this message in context:
http://postgresql.nabble.com/BUG-6706-pg-upgrade-fails-when-plpgsql-dropped-re-created-tp5714220p5829814.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.