Обсуждение: 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