[Featureserver] PostGIS insert fails due to null value in column "gid"

[Sacha Black]

thanks Josh, this tip solved my problem.

my case needed to be able to alter an existing table to automatically
assign a new gid to the incoming new polygons. the postgres steps were
fairly basic:

CREATE SEQUENCE scribble_layer_gid_seq;
ALTER TABLE scribble_layer ALTER COLUMN gid SET DEFAULT
NEXTVAL('scribble_layer_gid_seq');

this post was helpful for learning the steps:
http://www.pointbeing.net/weblog/2008/03/mysql-versus-postgresql-adding-an-auto-increment-column-to-a-table.html

after this change to my table the featureserver inserts into the
PostGIS layer work as expected.

appreciate the help. also thanks to the featureserver guys for such a cool tool.

Sacha

On Thu, Sep 4, 2008 at 5:41 PM, Josh Livni <josh at umbrellaconsulting.com> wrote:

> Yeah, you could theoretically send a gid, but better to have your postgres
> table automatically assign one for new records.
> One way to do this is, when you create your table, set your gid column as
> SERIAL.
>
> example:  CREATE TABLE my_table (gid SERIAL, some_attribute TEXT,
> the_geom(GEOMETRY);
>
> You can check the postgres docs for adding a sequence to an existing table.
>
>  -Josh
>
> Sacha Black wrote:
>>
>> With the tips from Andrew and Yves, the demo is working as expected
>> (in Win XP via apache and python 2.5), with the default scribble DBM.
>>
>> Now I have created a scribblePG which is connected to a PostGIS layer:
>>
>> [scribblePG]
>> type=PostGIS
>> dsn=host=localhost dbname=xxxx user=xxxx password=xxxx
>> layer=xxxx_scribble
>> fid=gid
>> geometry=wkb_geometry
>> gaping_security_hole=yes
>>
>> I think the connection is working fine because when I check like this:
>>
>> http://localhost/featureserver/featureserver.cgi/scribblePG/all.gml
>>
>> I get a gml record containing the expected 2 polygons from the layer.
>> I assume this means that my psycopg2 is installed correctly.
>>
>> However, when I modify the demo to use my scribblePG layer, I am
>> missing something. Because while I can see the scribblePG layer
>> drawing on my map in OpenLayers, I can't seem to write new polygons to
>> the layer.
>>
>> When I draw a new polygon on the map and hit "upload to server" I get
>> the "Features uploaded to server." success message. No Javascript
>> errors are reported in Firefox. Yet the polygon is not saved to the
>> scribblePG layer at all, and disappears from the map as soon as the
>> "upload to server" button is pushed.
>>
>> Andrew suggested that this might be a simplejson problem, but I think
>> that the demo would not have worked if simplejson was not working.
>>
>> Instead the error have seems to be related to the fact that I am not
>> passing a new feature id to PostGIS. The error as reported in the
>> Apache error log looks like this:
>>
>> An error occurred: null value in column "gid" violates not-null
>> constraint\r
>> File "\\FeatureServer\\Server.py", line 298, in cgiHandler\r
>> format, content = service.dispatchRequest( params, path_info, host,
>> post_data, request_method, accepts )\r
>> File "\\FeatureServer\\Server.py", line 145, in dispatchRequest\r
>> result = method(action)\r
>> File "\\FeatureServer\\DataSource\\PostGIS.py", line 142, in create\r
>> cursor.execute(str(sql), self.feature_values(feature))\r
>>
>> I tried to address this by passing a hidden key/value pair from the
>> HTML side like so:
>>
>>        <input type="hidden" id="key3" value="gid" /><input
>> type="hidden" id="value3" value="2" /> <br />
>>
>> but this didn't seem to make any difference at all, and I guess that
>> makes sense as the gid value is the internal feature id that is not
>> normally edited by the user (I think?). The gid field is required
>> (it's a primary key), and required to not be null.
>>
>> So my question is : how do I get an automatic sequential number to
>> insert into the gid field ? Or am I going in the wrong direction here
>> ?
>>
>> Sacha
>> _______________________________________________
>> Featureserver mailing list
>> Featureserver at openlayers.org
>> http://featureserver.org/mailman/listinfo/featureserver
>>
>