error when updating postgis_topology extension

[kyngchaos]

When updating the postgis_topology extension in a database, there is an error:

ERROR:  relation "topology" already exists

(according to the documentation instructions: ALTER EXTENSION postgis_topology UPDATE TO "2.0.2"; )

[robe]

Which version of PostgreSQL are you running. I also presume your current topology version 2.0.1? or is it 2.0.0.

I just tried on my windows 64 PostgreSQL 9.2 (PostgreSQL 9.2.1, compiled by Visual C++ build 1600, 64-bit)

upgrading postgis_topology from 2.0.1 to 2.0.2 and didn't run into any issues.

[robe]

so to be clear I started off with an extension

postgis_topology version 2.0.1

ALTER EXTENSION postgis_topology UPDATE TO "2.0.2";

[kyngchaos]

Right, updating from 2.0.1 to 2.0.2.

[kyngchaos]

Postgres 9.1.6

[kyngchaos]

Looks clear to me, in the postgis_topology--2.0.1--2.0.2.sql:

CREATE TABLE topology.topology (

without checking if it exists. Other CREATE TABLEs later are the same.

[robe]

That's really odd. My postgis_topology extension script does not have a CREATE TABLE topology.topology. I haven't checked the 9.1 which is here: ​http://download.osgeo.org/postgis/windows/pg91/postgis-pg91-binaries-2.0.2w32.zip

but don't see why it would be different from my 9.2.

I wonder if maybe your 2.0.1-2.0.2.sql scripts somehow are exactly the same as the postgis_topology--2.0.2.sql (are they the same size?) or sed is not stripping which would suggest something went wrong in the sed processing that strips out all the CREATE TABLE stuff. Which version of sed are you running?

[kyngchaos]

they are different - extra stuff at the begin and end of the update version.

During compilation, I see this to create the update sql:

sed -e '/^\(CREATE\|ALTER\) \(CAST\|TYPE\|TABLE\|SCHEMA\|DOMAIN\|TRIGGER\).*;/d' \
	 	 -e '/^\(CREATE\|ALTER\) \(CAST\|TYPE\|TABLE\|SCHEMA\|DOMAIN\|TRIGGER\)/,/\;/d' \
	 	 sql_bits/topology.sql > sql/topology_upgrade_minor.sql

OS X has BSD sed... I tried the sed command in a Terminal (minus the escaping), same results. I added the BSD -E flag for extended (modern) REs and that looks like it removed the CREATEs and other items that don't check for existence first. I guess BSD sed does old BREs by default.

[robe]

kyngchaos,

I think there might be an easy fix for this. I think I had put in this sed stripping at the time before strk had created an upgrade script for topology and I needed it to allow extensions to upgrade since extensions need to complete without error since they are wrapped in a transaction. Being fearful of anything with the word Perl in it, my preferred poison of choice was sed.

I see the one in topology folder Makefile.in uses perl instead of sed, but does essentially the same thing. Can you confirm that the topopolgy_upgrade_20_minor.sql generated by topology/Makefile looks okay (stripped of create table etc?)

If so I guess I can copy the generated file from that (which I should be doing anyway like I do with postgis extension), and then tack on the extra extension logic.

[kyngchaos]

No create tables in the contrib version upgrade script.

[kyngchaos]

Hmm, I was going to try a quick patch myself so I can package a fixed OS X installer, and I see that the postgis extension update script has some non-POSIX sed REs also. But Postgres didn't complain about this one, the postgis extension update ran without error:

sql_bits/postgis_upgrade_minor.sql: ../../postgis/postgis_upgrade_20_minor.sql 
		 sed -e 's/BEGIN;//g' -e 's/COMMIT;//g' \
		 -e '/^\(DROP\|CREATE\) \(CAST\).*;/d' \
...

for (one example):

DROP CAST IF EXISTS (geometry AS geometry);
CREATE CAST (geometry AS geometry) WITH FUNCTION geometry(geometry, integer, boolean) AS IMPLICIT;

probably because it's dropping the cast before creating it.

This one uses sed to strip them out in both the extension and non-extension update scripts.

[robe]

hmm that's odd. Originally I think the reason I needed to get rid of the drop commands was to prevent extension from complaining that you can't drop .. because extension depends on it.

I wonder if maybe I'm dropping CASTS from the extension too before I upgrade knowing they are always readded by perl upgrade script generation. In theory the only thing I should be doing to that minor script is to strip out the BEGIN .. COMMIT since the extension machinery puts one in and doesn't allow explicit ones.

[robe]

made change in trunk 2.1.0SVN at r10848

[robe]

Fixed for 2.0 in r10849. Also put in the get rid of extra building that causes some issues with building the files that was already fixed in 2.1 and added 2.0.2 as a previous target.

[robe]

oops made a typo add r10850 to list