Include minor upgrade in extension-from-unpackaged script

[strk]

As discussed in ​https://trac.osgeo.org/postgis/ticket/3888#comment:15 it makes sense to always upgrade unpackaged scripts before wrapping in an extension, and it would make it more friendly to switch form unpackaged to packaged.

If we do this, the change introduced in #3892 would need to be reverted, of course.

[strk]

work in progress here: ​https://gitlab.com/postgis/postgis/merge_requests/5

[strk]

In 16063:

Upgrade during create extension from unpackaged

This commit does it for "postgis" and "postgis_topology"
extensions.

Closes #3913

[strk]

I think this commit might have introduced a conceptual error. When including minor upgrade it may happen that the minor upgrade runs a CREATE statement, so that the created new object is installed in the target extension automatically. Later on, when ALTER EXTENSION .. ADD is called if the object is already in the extension you get an error like: ERROR: view raster_columns is already a member of extension "postgis"

Regina: can you produce such occurrence ? I'm not sure our bots are triggering it (but could be wrong) — I'm getting it manually from my "split-raster-from-core" branch…

[strk]

What we could do (after making sure our bots trigger the failure) is wrapping the ALTER EXTENSION ADD and catch exception after carefully checking that the exception is really about "object is already in target extension"

[strk]

I confirm the problem exists in trunk too. Way to reproduce:

 createdb t
 for f in  postgis/postgis.sql raster/rt_pg/rtpostgis.sql spatial_ref_sys.sql; do 
    psql -f $f t
 done
 psql -c 'create extension postgis from unpackaged' t

[strk]

The exception being thrown is a code 55000 aka "object_not_in_prerequisite_state"

[strk]

Patch to catch "object_not_in_prerequisite_state" is here: ​https://gitlab.com/postgis/postgis/merge_requests/7

A second pair of eyes on the safety of such approach is welcome.

Of course we're still missing an automated test from bots or even "make check" for the unpackaged-to-extension loading

[strk]

I've been pointed out to the fact that "object_not_in_prerequisite_state" would also be thrown if the object being added to an extension is already registered as being part of _another_ extension, and that there are possibly other cases in which it can be thrown which do not even relate to extensions…

So the code should be further modified to "manually" implement the "IF NOT EXISTS" part

[strk]

In 16076:

Have output of create_unpackaged survive already-registered objects

References #3913

[strk]

In 16077:

Include upgrade from unpackaged test in check_all_upgrades script

References #3913

[strk]

r16078 further restricts the exception acceptance condition by requiring the associated error message says 'already included in "postgis"'. Not necessarely safe from localized installs (we lack tests for that, I'm afraid)

[strk]

In 16079:

Make already-registered check in unpackaged extension create safer

This version just checks for "postgis" with word boundaries, so
it is immune to localization.

Closes #3913 again

[strk]

This needs to be fixed for postgis_sfcgal too (see #4000).

[strk]

In 16345:

Include minor upgrade in sfcgal unpackaged upgrade

Closes #3913 again (for sfcgal) and hopefully fixes #4000