Обсуждение: [pgadmin-support] Removing pg_trgm Extension - Upgraded from 9.1 to 9.5

Поиск
Список
Период
Сортировка

[pgadmin-support] Removing pg_trgm Extension - Upgraded from 9.1 to 9.5

От
Chuck Reed
Дата:

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.

 

Thanks,

Chuck Reed

a{color:inherit;text-decoration:none;} a:visited{color:inherit;}
Chuck Reed
System Software Architect - Worley
a: 303 Timber Creek, Hammond LA 70403
Privileged/Confidential Information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and notify the sender by reply email. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Worley shall be understood as neither given nor endorsed by it.

Re: [pgadmin-support] Removing pg_trgm Extension - Upgraded from 9.1to 9.5

От
"David G. Johnston"
Дата:
On Fri, Jan 6, 2017 at 12:40 PM, Chuck Reed <chuckreed@worleyco.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.