GeoServer
  1. GeoServer
  2. GEOS-1885

Failure in PostGIS store when dealing with serial attribute

    Details

    • Type: Bug Bug
    • Status: Open Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 1.6.3
    • Fix Version/s: None
    • Component/s: WFS
    • Labels:
      None
    • Number of attachments :
      0

      Description

      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

        Activity

        Hide
        Jean-Pierre Fiset added a comment -

        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

        Show
        Jean-Pierre Fiset added a comment - 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
        Hide
        Andrea Aime added a comment -

        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?

        Show
        Andrea Aime added a comment - 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?
        Hide
        Jean-Pierre Fiset added a comment -

        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.

        Show
        Jean-Pierre Fiset added a comment - 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.
        Hide
        Andrea Aime added a comment -

        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.

        Show
        Andrea Aime added a comment - 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.
        Hide
        Jean-Pierre Fiset added a comment -

        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.

        Show
        Jean-Pierre Fiset added a comment - 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.

          People

          • Assignee:
            Andrea Aime
            Reporter:
            Jean-Pierre Fiset
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated: