Failure in PostGIS store when dealing with serial attribute

To reproduce the problem, one needs a Postgesql/Postgis data store. The table in the database:

CREATE TABLE image (
id text,
"imageURL" text,
dummy_geom geometry,
image_id serial
);

The WFS request against geoserver:

<?xml version="1.0" encoding="UTF-8"?>
<Transaction xmlns:kpa="http://atlases.gcrc.carleton.ca/ns/kpa" xmlns="http://www.opengis.net/wfs" xmlns:gml="http://www.opengis.net/gml" xmlns:ogc="http://www.opengis.net/ogc" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://atlases.gcrc.carleton.ca/ns/kpa http://127.0.0.1:8080/geoserver/wfs?request=DescribeFeatureType&amp;SERVICE=WFS&amp;VERSION=1.0.0&amp;TYPENAME=kpa:image" version="1.0.0" service="WFS" lockAction="ALL"><Insert><kpa:image fid="newkpa:image.9223372036854775807"><kpa:id></kpa:id>
<kpa:imageURL></kpa:imageURL>
<kpa:dummy_geom><gml:MultiPoint srsName="EPSG:4326"><gml:pointMember><gml:Point><gml:coordinates decimal="." cs="," ts=" ">-106.46338791772723,69.60092733055353</gml:coordinates>
</gml:Point>
</gml:pointMember>
</gml:MultiPoint>
</kpa:dummy_geom>
</kpa:image>
</Insert>
</Transaction>

Here is the reply from geoserver:

<?xml version="1.0" encoding="UTF-8"?><wfs:WFS_TransactionResponse version="1.0.0" xmlns:wfs="http://www.opengis.net/wfs" xmlns:ogc="http://www.opengis.net/ogc" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.opengis.net/wfs http://127.0.0.1:8080/geoserver/schemas/wfs/1.0.0/WFS-transaction.xsd"><wfs:InsertResult><ogc:FeatureId fid="none"/></wfs:InsertResult> <wfs:TransactionResult> <wfs:Status> <wfs:FAILED/> </wfs:Status> <wfs:Message>Error performing insert</wfs:Message> </wfs:TransactionResult></wfs:WFS_TransactionResponse>

Here is part of the log:

25 Apr 21:04:29 ERROR [data.jdbc] - SQL Exception writing geometry columnERROR: null value in column "image_id" violates not-null constraint
org.postgresql.util.PSQLException: ERROR: null value in column "image_id" violates not-null constraint

Here is the PostGIS SQL request (obtained from Wireshark):

C....PC_3.P.....INSERT INTO "public"."image" ("id","imageURL","dummy_geom","image_id") VALUES ('','',setSRID('0000000004000000010000000001C05A9DA825CC00004051667597E80000'::geometry,4326),null)...B............D....P.E.........S...

Note that the "image_id" attribute is included with a "null" value, yet it was not included in the WFS request.

Here is the SQL request that should be sent (performed via PSQL):

kpa=# INSERT INTO image ("id","imageURL","dummy_geom") VALUES ('','',setSRID('0000000004000000010000000001C05A9DA825CC00004051667597E80000'::geometry,4326));

INSERT 33126 1

Hmmm... this is not an easy fix... we don't have any data structure providing the metadata needed to recognize a serial column other than the primary key.
Wondering, would it be possible to work around this problem using a trigger in the db?

The type SERIAL is a short cut for:

1. Type INTEGER
2. Create a sequence
3. Associate a default value to the sequence.

As a result, the table looks like:

Table "public.image"
Column | Type | Modifiers
-------------------------------------------------------------------
id | text |
imageURL | text |
dummy_geom | geometry |
image_id | integer | NOT NULL default nextval('image_image_id_seq'::regclass)

Given the database has a default value for the column, and that the INSERT message does not include any information about the associated attribute, it stands to reason that the code should not attempt to insert anything about this column and exclude it from the transaction.

I suggest that the keyword "default" be used to determine this state, not "serial", since "serial" is just a creation shortcut.

I have been too synthetic in my comment above, and I don't have time to explain it completely, but the problem boils down to the fact that we don't have neither the ability to recognize a default other that a static value, nor the ability to decide wheter a missing value shoudl be interpreted as a null or as something that must not be included in the insert statement. The problem is deep, structural, fixing it will require some major changes in how the jdbc datastores do work and/or in the way geotools features are conceived.

I have submitted a similar report with geotools (http://jira.codehaus.org/browse/GEOT-1806) in regards to the WFS data store.

I have a feeling that in the end, this is a geotools issue. There needs to be a way of specifying, via the geotools API, that an attribute is not specified purposefully. But, arguing for this behaviour to be fixed without explaining the context behind it is a sure way for the bug to be ignored. Hence, the need to report it within geoserver and linking it to the way WFS-T is specified.

However, there is a real need for this fix, since it relates with the way databases are naturally constructed: it is up to the database to assign unique identifiers.

I sympathize with the developers in this situation. I think you are right when stating that this might be big. This problem does not seem to be confined to the JDBC or Postgis data stores. I understand that it might not get addressed in the short terms.

However, I believe this is a significant and necessary fix. Solutions to work around this problem should probably not be considered valid in the long run.

Thanks for your help in this matter. If I can support you in anyway, please let me know.