Re: BUG #7661: pgstattuple from unpackaged fails on old installation

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