Обсуждение: BUG #7661: pgstattuple from unpackaged fails on old installation
The following bug has been logged on the website: Bug reference: 7661 Logged by: Stuart Bishop Email address: stuart@stuartbishop.net PostgreSQL version: 9.1.6 Operating system: Ubuntu 12.04 Description: = The pgstattuple upgrade from unpackaged script expects the pgstatindex function to exist, but it does not on particularly old databases (My pgstattuple originally installed with 8.3 or 8.4?). # create extension pgstattuple; ERROR: function "pgstattuple" already exists with same argument types # create extension if not exists pgstattuple from unpackaged; ERROR: function pgstatindex(text) does not exist
On 11/15/2012 02:19 PM, stuart@stuartbishop.net wrote: > The following bug has been logged on the website: > > Bug reference: 7661 > Logged by: Stuart Bishop > Email address: stuart@stuartbishop.net > PostgreSQL version: 9.1.6 > Operating system: Ubuntu 12.04 > Description: > > The pgstattuple upgrade from unpackaged script expects the pgstatindex > function to exist, but it does not on particularly old databases (My > pgstattuple originally installed with 8.3 or 8.4?). That's a known issue with several of the extensions. You need to upgrade the contrib module install to the current version, *then* wrap the unpackaged contrib module into an extension with "FROM UNPACKAGED". -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On Thu, Nov 15, 2012 at 1:42 PM, Craig Ringer <craig@2ndquadrant.com> wrote: >> The pgstattuple upgrade from unpackaged script expects the pgstatindex >> function to exist, but it does not on particularly old databases (My >> pgstattuple originally installed with 8.3 or 8.4?). pg_relpages(text) was missing too. > That's a known issue with several of the extensions. You need to upgrade > the contrib module install to the current version, *then* wrap the > unpackaged contrib module into an extension with "FROM UNPACKAGED". Yeah, just thought I'd stick it in the... umm... bugtracker, as so far 'FROM unpackaged' has failed in 66% of up updates. Is the real solution is for the foo--unpackaged--1.0.sql script to recreate missing objects before adding them to the extension? -- Stuart Bishop <stuart@stuartbishop.net> http://www.stuartbishop.net/
On 11/15/2012 03:32 PM, Stuart Bishop wrote: >> That's a known issue with several of the extensions. You need to upgrade >> the contrib module install to the current version, *then* wrap the >> unpackaged contrib module into an extension with "FROM UNPACKAGED". > Yeah, just thought I'd stick it in the... umm... bugtracker, as so far > 'FROM unpackaged' has failed in 66% of up updates. Is the real > solution is for the foo--unpackaged--1.0.sql script to recreate > missing objects before adding them to the extension? For simple extensions running the create script should do the job, yes. It's not so clear cut for extensions that define data types, though. If I recall correctly the general advice for those has been to: - Create the new versions of extensions in the DB you're going to restore to; then - restore your database to that DB with the extensions pre-created in it. I'm surprised not to find any documentation on coping with this issue in: http://www.postgresql.org/docs/current/static/contrib.html <http://www.postgresql.org/docs/9.2/static/contrib.html> or http://www.postgresql.org/docs/current/static/extend-extensions.html <http://www.postgresql.org/docs/9.2/static/extend-extensions.html> (though it's possible it's there and I missed it). There used to be brief mention in contrib.html before the extensions changes went in, saying: "After a major-version upgrade of PostgreSQL, run the installation script again, even though the module's objects might have been brought forward from the old installation by dump and restore. This ensures that any new functions will be available and any needed corrections will be applied." ... but I'm not certain that advice is sufficient for all contrib modules. Extensions were created because upgrading DBs that used contrib modules was a painful mess. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Craig Ringer <craig@2ndQuadrant.com> writes: > On 11/15/2012 03:32 PM, Stuart Bishop wrote: >>> That's a known issue with several of the extensions. You need to upgrade >>> the contrib module install to the current version, *then* wrap the >>> unpackaged contrib module into an extension with "FROM UNPACKAGED". >> Yeah, just thought I'd stick it in the... umm... bugtracker, as so far >> 'FROM unpackaged' has failed in 66% of up updates. Is the real >> solution is for the foo--unpackaged--1.0.sql script to recreate >> missing objects before adding them to the extension? > Extensions were created because upgrading DBs that used contrib modules > was a painful mess. Yeah. The goal we set ourselves when making the foo--unpackaged scripts was only to be able to upgrade from the immediately preceding form of the contrib module. I think it's probably true that in many cases adding CREATE OR REPLACE-type commands could allow upgrading from earlier versions as well. But it would be a lot of work to research what's needed and create/test a patch, and it would be work whose value lessens with every passing day. If there's somebody out there who's sufficiently annoyed to do that work, have at it. regards, tom lane