Opened 6 years ago

Closed 4 years ago

Last modified 20 months ago

#2382 closed defect (fixed)

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

Reported by: robe Owned by: pramsey
Priority: blocker Milestone: PostGIS 2.2.0
Component: build/upgrade/install Version: trunk
Keywords: Cc:

Description

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

Attachments (2)

2382.patch (376 bytes) - added by vmo 5 years ago.
2382_1.patch (725 bytes) - added by vmo 5 years ago.

Download all attachments as: .zip

Change History (43)

comment:1 Changed 6 years ago by robe

Milestone: PostGIS 2.2.0PostGIS 2.1.0
Owner: changed from strk to 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.

comment:2 Changed 6 years ago by 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

comment:3 Changed 6 years ago by Bborie Park

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

comment:4 Changed 6 years ago by 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 :)

comment:5 Changed 6 years ago by Bborie Park

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

comment:6 Changed 6 years ago by 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.

comment:7 Changed 6 years ago by robe

Milestone: PostGIS 2.1.0PostGIS 2.2.0

comment:8 Changed 6 years ago by 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.

comment:9 Changed 5 years ago by robe

Summary: Can't install postgis extension with pgAdmin 1.18 beta 1Can't install postgis extension ERROR: attempt to redefine parameter "postgis.backend"

comment:10 Changed 5 years ago by 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)

comment:11 Changed 5 years ago by robe

Summary: Can't install postgis extension ERROR: attempt to redefine parameter "postgis.backend"Can't install postgis ERROR: attempt to redefine parameter "postgis.backend"

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.

comment:12 Changed 5 years ago by robe

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

comment:13 Changed 5 years ago by 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)

comment:14 Changed 5 years ago by 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"

comment:15 Changed 5 years ago by 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.

comment:16 Changed 5 years ago by 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.

comment:17 Changed 5 years ago by 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?

comment:18 Changed 5 years ago by robe

comment:19 Changed 5 years ago by 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)

comment:20 Changed 5 years ago by robe

Priority: mediumblocker

comment:21 Changed 5 years ago by robe

Owner: changed from robe to pramsey

comment:22 Changed 5 years ago by 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.

comment:23 Changed 5 years ago by pramsey

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

comment:24 Changed 5 years ago by pramsey

Resolution: fixed
Status: newclosed

comment:25 Changed 5 years ago by strk

Resolution: fixed
Status: closedreopened

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

comment:26 Changed 5 years ago by 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.

comment:27 Changed 5 years ago by 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.

comment:28 in reply to:  27 ; Changed 5 years ago by vmo

s/also/although/

comment:29 in reply to:  28 Changed 5 years ago by 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.

comment:30 Changed 5 years ago by 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 ?

comment:31 Changed 5 years ago by 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

comment:32 Changed 5 years ago by 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 .

Changed 5 years ago by vmo

Attachment: 2382.patch added

comment:33 Changed 5 years ago by 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

comment:34 Changed 5 years ago by 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.

comment:35 Changed 5 years ago by 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.

Changed 5 years ago by vmo

Attachment: 2382_1.patch added

comment:36 Changed 5 years ago by 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.

comment:37 Changed 5 years ago by strk

Resolution: fixed
Status: reopenedclosed

comment:38 Changed 4 years ago by pramsey

Resolution: fixed
Status: closedreopened

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)

comment:39 Changed 4 years ago by 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=# 

comment:40 Changed 4 years ago by pramsey

Resolution: fixed
Status: reopenedclosed

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.

comment:41 Changed 20 months ago by 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 ?

Note: See TracTickets for help on using tickets.