[postgis-devel] Schema support in loader/dumper

[strk]

Summaryzing the problem:

	old postgis installation
	new postgis loader

New postgis loader does make use of the new {Add,Drop}GeoemtryColumn
interface. Functions signature has not changed but the meaning of
the first argument is (now: schema_name, then: db_name).

Old postgis installation had checks to ensure the first argument
was actually a valid database, thus dumper output does not produce
a working SQL for old postgis installations.

What we can do to support old postgis installations by new
postgis loader is to have the user explicitly ask for this behaviour,
making shp2pgsql generate the obsoleted calls. 

Should we add this support ?

--strk;

On Fri, Mar 19, 2004 at 05:25:03PM +0100, strk wrote:
> On Fri, Mar 19, 2004 at 04:43:31AM +0100, strk wrote:
> > On Fri, Mar 19, 2004 at 10:06:56AM +0100, Jan Hartmann wrote:
> > > Hi strk,
> > > 
> > > 
> > > Perhaps I'm doing something very dumb. However,
> > > 
> > > bin_0.8.0/shp2pgsql -s 28992 -d test.shp test zl > test.sql
> > > psql -d zl -f test.sql
> > > 
> > > works, while
> > > 
> > > bin_0.8.1/shp2pgsql -s 28992 -d test.shp test zl > test.sql
> > > psql -d zl -f test.sql
> > > 
> > > gives the following errors
> > >
> > > psql:test.sql:1: ERROR:  column not found in geometry_columns table
> >
> > This seems to come out from DropGeometryColumn
> 
> First test.sql probably contains 'zl' as the first argument
> of DropGeometryColumn, while second test.sql does not (using database
> name as first argument for DropGeometryColumn has been obsoleted).
> Your geometry_columns table could still contain 'dirty' rows, eg.
> rows for whith f_schema_name is actually the database name (zl).
> 
> Running fix_geometry_columns() with newer postgis installations
> would fix this. Your postgis version is probably an old one, so
> you'll have to cleanup the geometry_columns table yourself.
> 
> 	UPDATE geometry_columns SET f_schema_name = NULL;
> 
> > > DROP TABLE
> > > psql:test.sql:3: NOTICE:  CREATE TABLE will create implicit sequence 
> > > "test_gid_seq" for "serial" column "test.gid"
> > > CREATE TABLE
> > > psql:test.sql:4: ERROR:  Invalid database name.
> > 
> > This is probably coming from AddGeometryColumn
> > But it's a long time it doesn't issue that message anymore.
> 
> There was a check before schema-support-introduction to ensure
> the first argument was actually an existing database name.
> That check has been obsoleted by schema-supporting versions since
> the first argument should be a schema name, not a database name.
> 
> If we want to support this setup (old postgis installations /
> pre-schema pgsql / schema-aware loader) we should add a switch
> to the loader forcing it to 'cheat' on the backend using 
> obsoleted database name as first argument.
> This has to be a switch because on new postgis installations
> everything should work transparently (with either pre or post schema pgsql).
> 
> 	shp2pgsql --cheat-on-schema-name
> 
> Note that the suggested fix_geometry_columns() call will probably not
> fix things up since the actual function being called would be the
> pre-schema one.
> 
> You could also try using:
> 	bin_0.8.1/shp2pgsql -s 28992 -d test.shp zl.test > test.sql
> Instead of:
> 	bin_0.8.1/shp2pgsql -s 28992 -d test.shp test zl > test.sql
> 
> In this way the database name is used as it was a schema name,
> thus cheating the backend yourself. Last argument shoud be
> useless and only there for back compatibility.
> 
> Of course this would make your geometry_columns table dirty again,
> so you'll have to set f_schema_name to null again.
> 
> Comments welcome on the back-compatibility issue.
> 
> --strk;
> 
> > 
> > What version of postgis is installed ? 
> > 
> > --strk;
> > 
> > > BEGIN
> > > psql:test.sql:6: ERROR:  column "the_geom" of relation "test" does not exist
> > > psql:test.sql:7: ERROR:  current transaction is aborted, commands 
> > > ignored until end of transaction block
> > > psql:test.sql:8: ERROR:  current transaction is aborted, commands 
> > > ignored until end of transaction block
> > > psql:test.sql:9: ERROR:  current transaction is aborted, commands 
> > > ignored until end of transaction block
> > > psql:test.sql:10: ERROR:  current transaction is aborted, commands 
> > > ignored until end of transaction block
> > > psql:test.sql:11: ERROR:  current transaction is aborted, commands 
> > > ignored until end of transaction block
> > > COMMIT
> > > psql:test.sql:12: NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create 
> > > implicit index "test_pkey" for table "test"
> > > ALTER TABLE
> > >   setval
> > > 
> > > _______________________________________________
> > > postgis-users mailing list
> > > postgis-users at postgis.refractions.net
> > > http://postgis.refractions.net/mailman/listinfo/postgis-users
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-devel