[postgis-users] Schema support in loader/dumper

[strk]

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
> 
> Can you send a diff of the two outputs (or send them all) ?
> 
> > 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