Can't upgrade postgis ERROR: attempt to redefine parameter "postgis.backend"

[robe]

I'm pretty sure this is a bug in pgAdmin 1.18 beta 1 (and might be related to the bug I already posted that they have resolved). Putting here for now since it seems to have something to do with GUC.

I ruled out 9.3 beta as culprit since I can install extension with pgAdmin dialog in pgAdmin 1.16 with no issue.

Running

CREATE EXTENSION postgis VERSION "2.2.0dev";

Works fine in both pgAdmin 1.18 beta 1 and 1.16.1

Using the extension dialog in 1.16.1 also works.

However: usig the pgAdmin 1.18 beta 1 dialog gives this error:

ERROR:  attempt to redefine parameter "postgis.backend"

What pgAdmin III 1.18.beta 1 is doing beyond CREATE EXTENSION I have no clue. All the above excersises are running against PostgreSQL 9.3 beta2. haven't tried on lower

[robe]

Actually does the same thing on 2.1.0rc1 as I would expect since we introduced the postgis.backend GUC in 2.1. Wonder how I missed this error. Perhaps I've always been using a production version of pgAdmin before.

I'll report up the chain but will try to formulate a GUCCY postgisless extension to demonstrate the point.

[robe]

Okay this is even more complicated. I started with a fresh new database, installed PostGIS with 2.1.0rc1 with pgAdmin 1.18 beta1 and it worked just dandy.

Then I proceeded to upgrade my database to 2.2.0dev (using pgAdmin 1.18 beta 1) GUI and ran into the error.

It also does the same thing if I installed 2.1.0rc1 — do a DROP EXTENSION cascade with the interface, and then install 2.2.0dev with the GUI.

So it is possible that its not specifically a pgAdmin 1.18beta 1 bug but some feature of 1.18 checking GUCs or something that is catching an incomplete removal of postgis.backend GUC.

Perhaps I should try the same exercise with another extension that uses GUCs

[Bborie Park]

Just an FYI. I added a GDAL_DATA GUC for 2.2.0dev. I hope that isn't causing this issue.

[robe]

No the same issue exists in 2.1 code as well I think even just creating 2.1 extension, dropping and recreating the 2.1 extension triggers the issue.

Before I spend too much time composing a fake extension, I'll put in an email to the pgAdmin folks to find out what they changed in pgAdmin III 1.18 beta 1 that would trigger this. The other issue I put in was random thread aborts alerts when I was doing ddl like stuff which they couldn't replicate but think they know causes and fixed it ​http://www.postgresql.org/message-id/CAG7mmoyjTz1UTtewqFLVocahfoYZPfjMWgtFbGYJ0C88z+-yMA@mail.gmail.com

So could very well only be an issue that shows up under windows.

One of these days I really have to learn how to compile pgAdmin myself

[Bborie Park]

This problem doesn't seem to exist for PostGIS 2.0.4dev with pgAdmin 1.18 beta1 from source on Linux.

[robe]

Well it wouldn't for 2.0.4 cause we don't have GUCs in that

actually I was able to replicate the issue on my 64-bit without using pgAdmin.

Just doing upgrade from postgis 2.1.0beta3 to 2.1.0rc1 with gui and then 2.2.0dev without the gui via psql

ALTER EXTENSION postgis UPDATE TO "2.2.0dev";

gives error:

ERROR:  attempt to redefine parameter "postgis.backend"

So perhaps I was mistaken about pgAdmin III involved.What bugs me extra is I can't consistently trigger the issue as I thought I was able to on my 32-bit windows 7.

[nicklas]

I just had this error.

The database has lived for some months and I have just updated the source code (svn trunk), compiled and installed.

Now I get this message quite randomly when I run queries involving postgis.

I haven't seen it on a fresh database I have.

It is Postgres 9.2.

[robe]

see also #2658

strk are you able to consistently reproduce the issue. Sorry haven't had time to test upgrade this weekend but I think its only an issue with PostGIS 2.2.0 with introduction of GUCS (possibly 2.1 if you are running sfcgal too since that has GUCs)

[robe]

strk,

When I first saw this problem I thought it was just an extension issue. I just tried the old fashioned way — installing 2.1.2dev then upgrading with postgis_upgrade.sql to 2.2.0 and got the same error

query result with 1 row discarded.



ERROR:  attempt to redefine parameter "postgis.backend"
********** Error **********

ERROR: attempt to redefine parameter "postgis.backend"
SQL state: XX000

This is the sfcgal GUC isn't it that switches between geos and sfcgal backend. I'm not sure why it's only an issue upgrading from 2.1 to 2.2 and not 2.1.1 to 2.1.2dev etc.

Where is this thing defined. Doesn't seem to be explicit in the scripts so assume it's done in some postgis C function check.

[robe]

Here is where it's set

​http://postgis.net/docs/doxygen/2.2/d9/d85/lwgeom__backend__api_8c_a0bc0c2461640915a80936b0b7cf83b74.html#a0bc0c2461640915a80936b0b7cf83b74

and gets called in _PG_init.

I wonder if it only shows up between minor upgrade e.g. 2.1 to 2.2 because that is the only time when the 2.1 and 2.2 are simultaneously active (as 2.2.so/dll is swapping out a possibly live 2.1.so/dll)

[robe]

seems any function replace of a 2.1 function seems to trigger it. I just tried replacing my postgis-2.1 with this one

CREATE OR REPLACE FUNCTION geometry_overabove(geom1 geometry, geom2 geometry)
	RETURNS bool
	AS '$libdir/postgis-2.2', 'gserialized_overabove_2d'
	LANGUAGE 'c' IMMUTABLE STRICT;

and voila

ERROR:  attempt to redefine parameter "postgis.backend"

[pramsey]

This is pretty awful. You can probably exercise this bug in the raster space by replacing one of the raster functions (would be nice to confirm that). I think I can see how it's happening: you've got two postgis shared libraries, and initially just the old one is loaded, but then you run your create-or-replace command referencing the other shared library, so pgsql loads it and calls init on it. Boom, the init tries to define the GUC, using a name that is already bound (by the old library) and there's your error.

I'd guess the way around this is in the GUC code to check for existence of the parameter before attempting to bind it, and just no-op in that case, assuming that it only occurs in these upgrade moments and that usually a module won't be loaded simultaneously in the same database.

[robe]

Just tried running the rtpostgis_upgrade.sql script and that actually runs just dandy. No problems. Though the gdal_path GUC is new in 2.2 so 2.1 wouldn't have it anyway so it could have the same issue but one we won't see until someone tries to upgrade to 2.3 that has raster.

The note that nicklas put in seems to suggest it's not just on upgrade. " The database has lived for some months and I have just updated the source code (svn trunk), compiled and installed.

Now I get this message quite randomly when I run queries involving postgis. "

Nicklas if you are around, how did you upgrade? wondering if maybe you just had some code left pointing at old 2.1 lib and when 2.1 and 2.2 run is when you get that error.

[pramsey]

It'll only manifest if you've got a 2.1 GUC in the library. RT didn't have any GUC in 2.1?

[robe]

nope RTs GUC got introduced in 2.2 — ​http://postgis.net/docs/manual-dev/postgis_gdal_datapath.html

[robe]

To test out the 2 live so/dll theory, I was able to work around the problem by doing

1) shut down postgres service 2) rename my postgis-2.1.dll to postgis-2.1.dll.old 3) Start up postgres service 4) run my postgis_upgrade 2.2 script on my 2.1 database (one thing I did notice is that when I navigate to my postgis 2.1 database before upgrading, I do get an error — can't load postgis-2.1.dll (because of course I got rid of it) ) (which suggests maybe the guc is trying to be loaded on first access of the database)

[pramsey]

First fix attempt committed at r12350. This will need to be checked against all our PostgreSQL supported versions, since it calls deep into the guts of the server for information on what GUCs are already defined.

[pramsey]

Wait, better fix with less reaching into the guts at r12351

[strk]

I'm still seeing this when going from 2.1.2 to 2.2.0dev, what am I missing?

[strk]

Here is how I test:

make -C topology check RUNTESTFLAGS='-v --extension --upgrade-path 2.1.2--2.2.0dev'

And the output:

Creating database 'postgis_reg' 
Preparing db 'postgis_reg' using: CREATE EXTENSION postgis VERSION '2.1.2'
Upgrading PostGIS in 'postgis_reg' using: ALTER EXTENSION postgis UPDATE TO '2.2.0dev'
 failed (Error encountered altering EXTENSION POSTGIS: /tmp/pgis_reg/regress_log)
-----------------------------------------------------------------------------
createlang: language "plpgsql" is already installed in database "postgis_reg"
CREATE EXTENSION
CREATE EXTENSION
ERROR:  attempt to redefine parameter "postgis.backend"
-----------------------------------------------------------------------------
Died at ../../regress/run_test.pl line 1286.

[vmo]

I see that the address returned by GetConfigOption() introduced in 12350 is never pfree'd.

Also, there is something fishy going on, because also the postgis.backend is defined (en hence the DefineCustomStringVariable in lwgeom_init_backend bypassed) the assign hook does not work.

[vmo]

s/also/although/

[vmo]

My bad for the palloc (I read the wrong doc), but the fact that the hook does not seem to work still bothers me.

[vmo]

@strk: I tried your test:

make -C topology check RUNTESTFLAGS='-v —extension —upgrade-path 2.1.2—2.2.0dev'

and it only fails at:

uninstall .. failed (Object count pre-install (4112) != post-uninstall (4113))

The result is the same with and without the changes in lwgeom_init_backend from r12350.

Which version of postgresql did you use ?

[strk]

PostgreSQL 9.3.4+, just tried again now and confirm the same problem (attempt to redefine parameter "postgis.backend").

Remember you have to install postgis in order to get the extension-based upgrade to use the latest upgrade script.

I'm using r12618

[vmo]

Thanks for the 'Remember', I'm able to reproduce now.

The fix in r12350 don't actually fix this, but prevents the loading of sfcgal backend. I think r12350 is responsible for some of the diff in #2755: some output (e.g. ST_Distance(geom, empty)) comes from geos functions that are not properly replaced by sfcgal ones.

The result from your test is apparently the same with/without r12350

svn merge -r 12350:12349 .

[vmo]

The patch prevents loading of the old postgis.so caused by the call to postgis_lib_version().

<strk> calling lib_version() should fail to load the old library <strk> and hopefully that would trigger the EXCEPTION that would try script_installed() <strk> such failure should never occur if you're upgrading from 1.0.0, so the scripts_installed() problem doesn't hold <strk> the only failure point I can imagine is if the old library is already loaded <strk> like you source postgis_upgrade.sql in an existing session <strk> either with \i from a manually kept-open session or via some pooler

[strk]

r12641 fixes this by making the upgrade script LOAD any shared object referenced by the target script upfront. It fixes my run_test call:

make -C topology check RUNTESTFLAGS='-v --extension --upgrade-path 2.1.2--2.2.0dev'

As noted in the commit log, the presence of a pre-loaded postgis library would still break the upgrade (for upgrades attempted in a not-new session). The only way to fix that case I can think of would be to have a CREATE-OR-REPLACE like code to install the GUC — this would be kind of the other way taken by r12351, which keeps the old definition rather than overriding it.

[strk]

The r12351 commit was reverted with r12642, and upgrades in new sessions still work fine. I suggest to try at implementing the "replace-if-exists" policy for the GUC, so that even upgrades in sessions with pre-loaded old postgis library will work.

[vmo]

strk, this patch improves the solution in the case more than one lib is used by a module. A hash is used as a set and every /'$libdir∨[^{']+'/ is loaded.}

[pramsey]

You haven't really solved the problem, you've just ducked it a different way. It seems pretty trivial to exercise,

foo=# create extension postgis with version '2.1.8';
CREATE EXTENSION
foo=# alter extension postgis update to '2.2.0dev';
ERROR:  attempt to redefine parameter "postgis.backend"
foo=# 

As long as you CREATE and ALTER in the same session you have both libraries loaded at once. In fact, if you do *anything* PostGIS-related with your session once you connect and before you ALTER, you'll end up with the problem. Why not retain the test to let existing GUCs survive during init? (r12351)

[pramsey]

Also, an upgrade from 2.0.7 doesn't work, even when you start a new session between the CREATE and ALTER steps

psql (9.3.9)
Type "help" for help.

foo=# create extension postgis with version '2.0.7';
CREATE EXTENSION
foo=# \q
Crane:~ pramsey$ psql foo
psql (9.3.9)
Type "help" for help.

foo=# alter extension postgis update to '2.2.0dev';
ERROR:  attempt to redefine parameter "postgis.backend"
foo=# 

[pramsey]

Re-applied the GUC def'n patch at r13902, which allows me to upgrade in a "dirty" session that already has the old library loaded in it, at the cost of locking the GUC value to the old one until such time as the user re-connects to a clean session. A warning is emitted.

[strk]

As I'm getting these WARNINGs multiple times during upgrades (we have multiple GUCs now) I looked at the issue again and wondering… why didn't we just opt for undefining the old GUC and replace with the new ? Apart from the difficulty to tell what's old and what's new, in case both libraries survive. Actually I'm thinking we should include lib version in those WARNINGs so at least we know if the complaining one is the *new* or the old, because it is still possible that the old library is loaded *after* the new library, right ?