[postgis-users] shp2pgsql and schemas

[Mark Cave-Ayland]

Hi strk,

Some thoughts in reply to your post:

> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net 
> [mailto:postgis-users-bounces at postgis.refractions.net] On 
> Behalf Of strk
> Sent: 16 December 2003 08:12
> To: PostGIS Users Discussion; postgis-devel at postgis.refractions.net
> Cc: David Blasby; Paul Ramsey
> Subject: Re: [postgis-users] shp2pgsql and schemas
> 
> 
> Changing the AddGeometryColumn will break compatibility with 
> dumps created by old installations. It might be wiser to
> have a polyphormic function with a different number of args
> for schema-aware behaviours.

It looks as if this will be the case anyway, since looking at Page 40 of
the OGC spec the current PostGIS implementation of AddGeometryColumn()
is missing the schema parameter (even though the database name is
entered into the geometry_columns table). Also, I'm not convinced that
changing AddGeometryColumn would break dumps, since dumps just restore
the geometry_columns table and the appropriate columns without calling
the AddGeometryColumn function itself.

Also bear in mind that most users simply want to restore a complete
database dump in the case of hardware/disk failure and so this becomes
irrelevant as the version of PostGIS restored will be exactly the same
one that was dumped, although this has never worked for me without
manual intervention :)

> Moreover since now the f_table_schema column is filled with
> the wrong value we might require a call to fix_geometry_columns
> making it also cleanup those values when having the same value
> of the database name. On the other hand, this could do the wrong
> thing if somebody is using the schema-aware version with schemas
> that are *really* named as the database.

See above....

For every dump/restore of PostGIS I've ever done, I've had to manually
re-order the pg_dump catalogue in order to get it to restore correctly.
Normally I do something like this:

	1. Take pg_dump of existing database
	2. Drop database
	3. Recreate database
	4. Use pg_restore -l to generate a list file
	5. Remove all references to procedural languages from the list
file
	(because they never occur in the right order during normal
restore)
	6. Remove all references to PostGIS functions from the list file
	(because they never occur in the right order during normal
restore)
	7. Move all index/trigger creation commands to the end of the
list file
	8. Createlang any procedural languages in the new database
	9. Reload postgis.sql
	10. pg_restore -s (schema only - this tests that I've got the
restore order right 	w/o attempting to load the data)
	11. pg_restore (data and indexes)


So basically, if I decide to upgrade the version of PostGIS at the same
time (as opposed to restoring the same version), then after all the
manual work that goes into restoring the DB it makes no difference to
run a PostgisUpdate() or whatever :)

The logic behind this could be quite simple:

	For each row in the geometry_columns table:
		Does the table f_schema_name.f_table_name exist in this
database?
			Yes, leave the entry as it is
			No, set f_schema_name to 'public'
	Next

The only case I could see this not working is if someone had tweaked the
geometry_columns table by hand, but if they can do that, I'm sure they
can fix any resulting problems that may occur :)

Thinking more about it, the one function that concerns me is find_srid()
as it is used in nearly all of our queries :(. Perhaps a polymorphic
version of this should also be created, so using the old version will
raise notice that it is deprecated and assume the default schema to be
'public'.

Finally if a polymorphic version of AddGeometryColumn() were created, I
would expect a similar behaviour for the 'old' version, i.e. raise a
notice saying that the old version is deprecated in place of the new
version, and that by default the table will be assumed to be in the
public schema.

> Yet another possibility is adding a new column to specify wheter
> or not we are dealing with schema-aware records. Old records will
> have a 'null' value in this new column, while new one will have
> something. Note that this will require loading an 
> "postgis_upgrade.sql"
> script to enlarge the geometry_columns definition.

My general experience tells me that adding flags like this is a bad
idea, especially given the huge amount of knowledge currently required
to restore a dump - it can only make the application more unreadable,
and saves those countless hours of head-scrathing months later when
you've added a geometry column and none of the geometric operators work
with it...


Mark.

---

Mark Cave-Ayland
Webbased Ltd.
Tamar Science Park
Derriford
Plymouth
PL6 8BX
England

Tel: +44 (0)1752 764445
Fax: +44 (0)1752 764446


This email and any attachments are confidential to the intended
recipient and may also be privileged. If you are not the intended
recipient please delete it from your system and notify the sender. You
should not copy it or use it for any purpose nor disclose or distribute
its contents to any other person.