Re: Removing pg_trgm Extension - Upgraded from 9.1 to 9.5

On Fri, Jan 6, 2017 at 12:40 PM, Chuck Reed <chuckreed(at)worleyco(dot)com> wrote:

> Hi everyone,
>
>
>
> We have recently upgraded from postgres 9.1 to 9.5. When we create a
> database dump from 9.5 and attempt to restore it on another 9.5 server, we
> are running into the following warnings retarding the pg_trgm extension:
>
>
>
> pg_restore: WARNING: type input function gtrgm_in should not be volatile
>
> pg_restore: WARNING: type output function gtrgm_out should not be volatile
>
>
>
> I’m assuming these functions were added from the 9.1 extension and should
> be re-created from the 9.5 extension. This led me to try dropping the
> current pg_trgm extension, but it seems like the database doesn’t recognize
> it was already created:
>
>
>
> =# drop extension pg_trgm;
>
> ERROR: extension "pg_trgm" does not exist
>
>
>
> Trying to re-create the extension doesn’t complain that it’s already
> installed, but fails due to the functions already being in place:
>
>
>
> =# create extension pg_trgm;
>
> ERROR: function "set_limit" already exists with same argument types
>
>
>
> We haven’t run into this issue before and I am looking for advice on how
> to proceed. Should we just manually drop all of the tables associated with
> pg_trgm and then attempt to use CREATE EXTENSION again? Any help is greatly
> appreciated.
>
>
>

​See comment regarding "unpackaged" (under old_version) here:

​https://www.postgresql.org/docs/9.6/static/sql-createextension.html

Ideally you can do this on the 9.1 server which then should make the
upgrade go smoothly.

David J.