Subject: pg_upgrade changes can it use CREATE EXTENSION?

From: "Regina Obe" <[email-masked]>
Date: Wed, 30 Aug 2017 17:33:37 -0400

I'm not too familiar with the innards of pg_upgrade, but we've been
discussing it a lot for past couple of days and how it's causing issues for
PostGIS upgrades.

I think this thread covers most of the issues.

https://lists.osgeo.org/pipermail/postgis-devel/2017-August/026355.html

My thought was is it possible for pg_upgrade to be taught to use CREATE
EXENSION if asked? 

Right now we don't support PostgreSQL 11 on PostGIS 2.3 and we really would
like not to because there are too many changes done in 11 that we feel
queezy about backporting.
Even if we did, package maintainers would have to provide 2.3 on 11 and 2.4
on 11 just so people can pg_upgrade to PostgreSQL 11 and then 

ALTER EXTESNION postgis UPDATE;

To postgis 2.4.0

Given that latest PostgreSQL 11 head already doesn't compile against PostGIS
2.4, I'm not confident we can fix 2.4 for 11.  So this will continue to be
more of a problem especially at the rate that PostgreSQL is changing these
days.

Right now crafty users have to do something like this to use pg_upgrade

https://gist.github.com/Komzpa/994d5aaf340067ccec0e

My solution of let's not call it postgis-2.4  but just postgis-2  from
thenceforward for the life of 2 major series because we don't break backward
compatibility often in a PostGIS minor version got shot down.

Any thoughts on this?

Thanks,
Regina Obe
PostGIS PSC member

-- 
Sent via pgsql-hackers mailing list ([email-masked])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

---

From: Tom Lane <[email-masked]>
Date: Wed, 30 Aug 2017 18:01:58 -0400

> My thought was is it possible for pg_upgrade to be taught to use CREATE

We intentionally *don't* do that; pg_dump goes to a lot of trouble to
duplicate the old extension contents exactly, instead.  There are a bunch
of corner cases that would fail if we allowed the new installation to
have different extension contents than the old.  Believe you me, we'd
rather have just issued CREATE EXTENSION, but it doesn't work.

Looking quickly at the thread you cite, I wonder how much of this problem
is caused by including version numbers in the library's .so filename.
Have you considered not doing that?  Our experience with maintaining the
contrib modules is that it's easier to attach a version number to an
individual function (in its C name, where it's irrelevant to SQL users).
If you incompatibly upgrade a given function, you can leave a stub behind,
with the old C symbol, that does nothing but throw an error if called.
Or you can keep on supporting the old API if it's easy enough; it
doesn't have to be a library-wide decision.

> compatibility often in a PostGIS minor version got shot down.

The thread you mention doesn't seem to include any arguments why not
to do that.

			regards, tom lane

---

From: "Regina Obe" <[email-masked]>
Date: Thu, 31 Aug 2017 00:53:57 -0400

Sorry for the cross posting on this one, but I think it's important both groups are aware.

> is caused by including version numbers in the library's .so filename.

Most of it is.  That's why I proposed at least only bumping on major upgrade.  So postgis 2.4 so would be called postgis-2.so instead of postgis-2.4.so

We would only change on disk format during major in which case pg_upgrade wouldn’t work for folks anyway (such as what happened going from PostGIS 1.5 to 2.0)  

> doesn't have to be a library-wide decision.

People were all worked up about breaking ABI  and also not being able to run two different versions of PostGIS in same cluster.
We rarely break ABI and if we did, like you said it wouldn't kill us
to keep the old C name around until we did a major upgrade.

So I'm all for that idea.  I figure we'll rarely need to do that anyway.

It's mostly PostGIS developers like me that need to run two different versions of PostGIS in same cluster mostly for regression testing.
Which is why I proposed having a configure switch which is by default off.

Here is my original vote request.
https://lists.osgeo.org/pipermail/postgis-devel/2017-August/026319.html

 >                   regards, tom lane

Some people had issue with trying to do that at PostGIS 2.4 right after we already released the alpha and are less than a month away from release.
Though technically we haven't released beta yet so I didn't think it was that big of a deal.

But I'm willing to wait for PostGIS 2.5 to appease people.

Tom, as always, thanks for being a voice of reason,

Regina

---

From: Sandro Santilli <[email-masked]>
Date: Tue, 5 Sep 2017 10:22:23 +0200

> rather have just issued CREATE EXTENSION, but it doesn't work.

Did you mean `pg_upgrade` ("goes to a lot of trouble") ?
Because I'm pretty sure I saw a `CREATE EXTENSION` in a dump created by
pg_dump from PostgreSQL 9.6

> Have you considered not doing that? 

The name change is intentional, to reflect a promise we make between
patch-level releases but not between minor-level releases. The promise
to keep C function signatures referenced by SQL objects immutable and
behavior compatible.

--strk;

---

From: Tom Lane <[email-masked]>
Date: Tue, 05 Sep 2017 09:22:18 -0400

> Did you mean `pg_upgrade` ("goes to a lot of trouble") ?

To be precise, pg_dump when working on behalf of pg_upgrade (that is, with
the --binary-upgrade switch).

> to keep C function signatures referenced by SQL objects immutable and

FWIW, I do not think that the library file name is a useful place to
try to enforce such a thing.  Changing the file name creates complications
for upgrade, and it doesn't actually stop you from breaking anything.

			regards, tom lane