[raster] Add uninstall_rtpostgis.sql machinery

[mloskot]

Following PostGIS (r4147), it would be nice to implement uninstall_rtpostgis.sql and related scripts for WKT Raster.

[robe]

I'll take a stab at this using the same logic I'm using to build the topology upgrade script. Firs tthing though will be to define a

raster_drop.sql.in.c

similar in concept to

postgis_drop.sql.in.c

To catalog all the functions we've converted to default params or otherwise made obsolete. I'll tackle that first since taht part can't be formed from the install script.

[robe]

hmm I misread this one as upgrage script. For extensions turns out the extension model takes care of the uninstall without you lifting a finger. My extensions uninstall perfectly (and warn if you have dependencies), even though I didn't define an uninstall script.

[dustymugs]

Possible use of pg_dump --clean --schema-only to generate uninstall_rtpostgis.sql. A static one would be the default but make should be able to generate a dynamic one. make would need to create a database (like regress), populate the database, run the dump, create the uninstall script and then drop the database. Regression test could test the static (or dynamic) uninstall script to make sure the uninstall works.

[dustymugs]

Bash script to generate uninstall.sql files. Really rough at the moment.

[dustymugs]

Attached bash script to generate uninstall sql files for postgis and raster. As attached, script runs from raster/rt_pg. Generated uninstall files appear to work for a clean data loaded with postgis.sql and rtpostgis.sql.

At least on PostgreSQL 9.0, the order of DROP statements isn't perfect for TYPEs and any dependent functions as the DROP TYPE uses CASCADE.

I'll attach the uninstall files.

[dustymugs]

Uninstall sql for postgis generated by uninstall_script.

[dustymugs]

Uninstall sql for raster rtpostgis generated by uninstall_script.

[strk]

dustymug: did you check if the generated uninstall_postgis.sql passes regression testing ? (it now includes testing uninstall_postgis.sql, using the one under postgis/).

I'd love to see all the uninstall scripts automated

[strk]

Feel free to commit the uninstall script under utils/.

BTW, the utils/create_undef.pl script looks like having the same goal, only using the postgis.sql file as input. Originates in 2001 by Dave, seemingly maintained up to 2005 and evidently also tested in 2010.

[strk]

I've fixed up the create_undef.pl script and tested with the existing regress test. Seems good to me, and maybe can be also improved to avoid the "CASCADE". Will try it against raster as well and report here.

[dustymugs]

I just tested the standard regression for uninstall and it failed (before 3738 != after 3747). The uninstall sql created by the bash script also fails due to the return status (ERROR message for type "spheroid" does not exist when dropping the related functions). I'll have to tweak the bash script so that it removes unnecessary DROP calls.

I agree with seeing the uninstall scripts automated.

[strk]

So with r8525 the uninstall_postgis.sql script is generated by utils/create_undef.pl, now moving on to testing it against raster for uinstall_rtpostgis.sql

[strk]

For the record; current show-stopper for create_undef is removing the "DEFAULT" specification from function signatures. There's no regexp I can think of that deals with nested (), found in the default for unknown srid: "ST_SRID('POINT(0 0)'::geometry)".

A workaround would be avoiding function calls in DEFAULT specifications, another would be writing a smarter parser.

[dustymugs]

So I found a show-stopper for using pg_dump. The uninstall scripts work great and removing everything except several OPERATOR FAMILY objects (btree_geography_ops, btree_geometry_ops, gist_geography_ops, gist_geometry_ops_2d, gist_geometry_ops_nd). I checked the raw output of pg_dump and find no mention of these OPERATOR FAMILY objects. After searching the PostgreSQL mailing lists, Tom Lane mentions something about why some OPERATOR FAMILY objects will NOT be part of pg_dump output...

 http://archives.postgresql.org/pgsql-hackers/2011-09/msg00362.php

[strk]

No problem, the create_undef.pl way is still going good (overnight :)

I've improved the stripper for DEFAULT spec and changed the default to 0, matching what postgis.sql does. Now the uninstall_rtpostgis.sql comes out well-formed and I think it works cleaning everything out.

Next problem _enabling_ automated testing for it is that the regression test of raster is creating a data table for use by tests and never dropping it. It is advisable that every test needing that table includes (\i) the creator as first thing and includes a "dropper" as last thing.

Will take more time but would allow you to run each test in isolation, which is important while debugging specific bugs.

For the time being I'm adding to the hack by listing a drop_xxy testcase the same way as it is done with create_xxxy testcase (fakes).

Also, I'm cleaning up tests that create objects internally to drop them as last step. Plain raster uninstall seems to work very well.

[strk]

With r8553 the work here is completed. uninstall_rtpostgis.sql will be created by reading rtpostgis.sql the run_test script (used by "make check") will test raster uninstall.