[postgis-users] shp2pgsql supports Primary Keys and Sequences....

[Jeff Lounsbury]

The latest CVS version shp2pgsql now uses a serial type (which uses sequences) and
creates primary keys!

Please let me know if there are any problems with the new features...
-Jeff Lounsbury


Chris Holmes wrote:

> > > Are there plans for that improvement to shp2pgsql Paul?
> >
> > Yes, I think we should modify shp2pgsql to make the unique ID be
> > declared as a primary key. It can only help, and it cannot hurt.
> >
> > > Will it be in the next release?
> >
> > I will ask Jeff to put that it ASAP, so it will be in the next release.
> Awesome.
>
> > > Will it use a sequence as suggested in the email?
> >
> > That will be harder, as sequence management is something which has been
> > changing from PgSQL version to version. Particularly from the
> > perspective of create PgSQL dump files (which is the high performance
> > operating mode of shp2pgsql). The "serial" as a column type is actually
> > an addition which postdates PostGIS. (Although sequences have been
> > around forever, in order to use them before you had to explicitly tie
> > the default value of a column to their next value.)
> Well, if you don't do sequences I'm curious as to how you'll deal with the
> problem of using the append flag in shp2pgsql, as it currently repeats the
> gid's if I'm not mistaken, just using the row number.
>
> > > And if it does declare a sequence is it reasonable for us to use that sequence?
> >
> > So the operation would be
> > - I need a new feature ID!
> > - What is the primary key of that feature table?
> > - What is the next value of the sequence joined to that pkey?
> >
> > Do feature IDs have to be unique within the WFS, or unique within a
> > feature class?
> I'm not quite sure what you're asking, but if by feature class you mean
> datasource, then it just needs to be unique for the datasource.  If you
> can get me id numbers for each table in postgis then I can make them
> unique for the wfs, by appending the tablename and setting the namespace
> of the schema.  So if I declare a table called rail and a table called
> road, then both can use their own sequences, because the postgis
> datasource will create fids like rail.34 and road.34, each for the primary
> key that is 34.  If my geoserver install also uses another database that
> also has a rail table, then geoserver will have them be declared with
> different namespaces, and thus ns1:rail.34 will be different than
> ns2:rail.34 (assuming the prefixes correspond to full namespace uri's).
> OID's are unique for a whole database, but we definitely don't have that
> requirement.  Is that what you were asking?
>
> > What are they being used for? Why does the WFS have to
> > create them? If a new feature is being created, can't you just insert
> > into the table and let the DBMS handle things?
> A wfs has to create them because the insert operation requires you to
> return a set of fids for the newly created features.  I think this makes
> sense so that users can easily reference the features they just created,
> and it's part of the spec so we have to do it.  A feature to be inserted
> does not specify its fid, and I'm pretty sure wfs's can just ignore the
> fid to be inserted, as the fid should depend on the data backend.  If
> you've got a way for the dbms to handle things and report back a unique id
> that can be used to refer to the newly inserted id, that will be
> persistant even if the fields change, I'm all ears.
>
>         Chris