[Geoserver-devel] [jira] Created: (GEOS-2772) Postgis datastore generates null value in sql statement which overrides table's "DEFAULT now()" declaration

GeoServer GeoServer Developer
,
1

Postgis datastore generates null value in sql statement which overrides table's "DEFAULT now()" declaration
-----------------------------------------------------------------------------------------------------------

                 Key: GEOS-2772
                 URL: http://jira.codehaus.org/browse/GEOS-2772
             Project: GeoServer
          Issue Type: Bug
          Components: PostGIS
    Affects Versions: 1.7.0
         Environment: Debian 4
            Reporter: G Forty
            Assignee: Andrea Aime

CREATE TABLE user_location
(
  user_name character varying(50) NOT NULL,
  user_bbox geometry NOT NULL,
  user_enter_ts timestamp without time zone DEFAULT now(),
  user_location_id serial NOT NULL,
  CONSTRAINT user_location_id PRIMARY KEY (user_location_id)
)

Using javascript to generate timestamp(ISO-8601 format) [SUCCESSFUL]:
        //generated from user's mouse moves
  var feature = new OpenLayers.Feature.Vector(

            new OpenLayers.Geometry.MultiPolygon(bounds.toGeometry()));
        // Set feature attributes. user_bbox is generated from
  var d = new Date();
        feature.attributes = {'user_name':USER,
                             'user_enter_ts':d.toISO8601String(6)};
        // Add it to the layer.
        map.layers[locLayerNum].addFeatures([feature]);
        // Post.
        map.layers[locLayerNum].commit();

Resultant POST body:
<wfs:Transaction xmlns:wfs="http://www.opengis.net/wfs&quot; version="1.0.0" service="WFS"><wfs:Insert>
<feature:user_location xmlns:feature="https://www.djc2.org/schemas&quot;&gt;&lt;feature:user\_bbox&gt;
<gml:MultiPolygon xmlns:gml="http://www.opengis.net/gml&quot;&gt;&lt;gml:polygonMember&gt;&lt;gml:Polygon&gt;&lt;gml:outerBoundaryIs&gt;&lt;gml:LinearRing&gt;
<gml:coordinates decimal="." cs="," ts=" ">-159.43359375,-17.05078125 42.36328125,-17.05078125 42.36328125
,50.44921875 -159.43359375,50.44921875 -159.43359375,-17.05078125</gml:coordinates></gml:LinearRing>
</gml:outerBoundaryIs></gml:Polygon></gml:polygonMember></gml:MultiPolygon>
</feature:user_bbox><feature:user_name>test01</feature:user_name>
<feature:user_enter_ts>2009-03-18T15:32:47.024Z</feature:user_enter_ts></feature:user_location></wfs:Insert></wfs:Transaction>

---------------------------------------------------------------------------------------------------------------------------------------------------

Now we remove the javascript generated timestamp and allow postgres to generate the timestamp with the DEFAULT now() declaration in CREATE TABLE:
  //feature attribute with timestamp removed.
  feature.attributes = {'user_name':USER};

Resultant POST body:
<wfs:Transaction xmlns:wfs="http://www.opengis.net/wfs&quot; version="1.0.0" service="WFS"><wfs:Insert>
<feature:user_location xmlns:feature="https://www.djc2.org/schemas&quot;&gt;
<feature:user_bbox><gml:MultiPolygon xmlns:gml="http://www.opengis.net/gml&quot;&gt;
<gml:polygonMember><gml:Polygon><gml:outerBoundaryIs>
<gml:LinearRing><gml:coordinates decimal="." cs="," ts=" ">-273.1640625,-50.9765625 130.4296875,-50.9765625 130.4296875
,84.0234375 -273.1640625,84.0234375 -273.1640625,-50.9765625</gml:coordinates>
</gml:LinearRing>
</gml:outerBoundaryIs></gml:Polygon></gml:polygonMember></gml:MultiPolygon>
</feature:user_bbox><feature:user_name>test01</feature:user_name></feature:user_location></wfs:Insert></wfs:Transaction>

Fails. No now() value inserted into user_enter_ts as a NULL value is inserted by postgis.
INSERT INTO "public"."user_location"
("user_name","user_bbox","user_enter_ts")
VALUES
('test01',setSRID('...'::geometry,4326),null)

Thanks.

--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://jira.codehaus.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira