Failure in PostGIS store when dealing with serial attribute


When adding a layer in geoserver against a database table that contains a serial attribute, it is impossible to submit a WFS request without specifying the serial attribute. An error is returned. The expected behaviour would be for the database to assign the next available value for the serial attribute.

What happens is that geoserver attempts to set the attribute to null. Given that a serial is mapped to a non null integer, the database can not accept the request.

In fact, the expected behaviour should be that geoserver submit a SQL request to PostGIS without specifying the missing attribute. This way, the database would perform the request correctly.

This error was found on geoserver/trunk/geoserver@8885




April 10, 2015, 4:05 PM

CodeHaus Comment From: jpfiset - Time: Fri, 25 Apr 2008 20:30:19 -0500
<p>To reproduce the problem, one needs a Postgesql/Postgis data store. The table in the database:</p>

<p> CREATE TABLE image (

id text,

"imageURL" text,

dummy_geom geometry,

image_id serial


<p>The WFS request against geoserver:</p>

<p>&lt;?xml version="1.0" encoding="UTF-8"?&gt;

&lt;Transaction xmlns:kpa="" xmlns="" xmlns:gml="" xmlns:ogc="" xmlns:xsi="" xsi:schemaLocation=" <a href=";SERVICE=WFS&amp;VERSION=1.0.0&amp;TYPENAME=kpa:image" class="external-link" rel="nofollow">;amp;SERVICE=WFS&amp;amp;VERSION=1.0.0&amp;amp;TYPENAME=kpa:image</a>" version="1.0.0" service="WFS" lockAction="ALL"&gt;&lt;Insert&gt;&lt;kpa:image fid="newkpa:image.9223372036854775807"&gt;&lt;kpa:id&gt;&lt;/kpa:id&gt;


&lt;kpa:dummy_geom&gt;&lt;gml:MultiPoint srsName="EPSG:4326"&gt;&lt;gmlointMember&gt;&lt;gmloint&gt;&lt;gml:coordinates decimal="." cs="," ts=" "&gt;-106.46338791772723,69.60092733055353&lt;/gml:coordinates&gt;








<p>Here is the reply from geoserver:</p>

<p>&lt;?xml version="1.0" encoding="UTF-8"?&gt;&lt;wfs:WFS_TransactionResponse version="1.0.0" xmlns:wfs="" xmlns:ogc="" xmlns:xsi="" xsi:schemaLocation=" <a href="" class="external-link" rel="nofollow"></a>"&gt;&lt;wfs:InsertResult&gt;&lt;ogc:FeatureId fid="none"/&gt;&lt;/wfs:InsertResult&gt; &lt;wfs:TransactionResult&gt; &lt;wfs:Status&gt; &lt;wfs:FAILED/&gt; &lt;/wfs:Status&gt; &lt;wfs:Message&gt;Error performing insert&lt;/wfs:Message&gt; &lt;/wfs:TransactionResult&gt;&lt;/wfs:WFS_TransactionResponse&gt;</p>

<p>Here is part of the log:</p>

<p>25 Apr 21:04:29 ERROR <span class="error">&#91;data.jdbc&#93;</span> - 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</p>

<p>Here is the PostGIS SQL request (obtained from Wireshark):</p>

<p>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...</p>

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

<p>Here is the SQL request that should be sent (performed via PSQL):</p>

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

<p>INSERT 33126 1</p>

April 10, 2015, 4:05 PM

CodeHaus Comment From: aaime - Time: Sat, 26 Apr 2008 03:15:11 -0500
<p>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?</p>

April 10, 2015, 4:05 PM

CodeHaus Comment From: jpfiset - Time: Tue, 6 May 2008 10:53:40 -0500
<p>The type SERIAL is a short cut for:</p>

<p>1. Type INTEGER

2. Create a sequence

3. Associate a default value to the sequence.</p>

<p>As a result, the table looks like:</p>

<p> 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)</p>

<p>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.</p>

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

April 10, 2015, 4:05 PM

CodeHaus Comment From: aaime - Time: Tue, 6 May 2008 11:00:31 -0500
<p>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.</p>

April 10, 2015, 4:05 PM

CodeHaus Comment From: jpfiset - Time: Tue, 6 May 2008 12:36:53 -0500
<p>I have submitted a similar report with geotools (<a href="" class="external-link" rel="nofollow"></a>) in regards to the WFS data store.</p>

<p>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.</p>

<p>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.</p>

<p>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. </p>

<p>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.</p>

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







Fix versions


Affects versions