2

What goes on when Geoserver handles updates to my table using WFS-T?

I have a table

AREA (
ID NUMBER(10) NOT NULL PRIMARY KEY,
SOME_OTHER_ID NUMBER(10) NOT NULL FOREIGN KEY REFERENCES FOO(ID),
GEOMETRY MDSYS.SDO_GEOMETRY NOT NULL
);

and a sequence SEQ_AREA for the primary key values.

When I publish this table as a Geoserver layer, it seems that it automatically detects that ID is the primary key and does not show it in the layer's properties.

When a new row is inserted, Geoserver does not use the index - how does it determine the next value? Does it do SELECT MAX(id) FROM AREA or something like that? Could I make it use the sequence?

And - if I create a view and a layer from that - the view does not have primary key as such - how can I make Geoserver understand it so that it is updatable? When I create a view such as:

CREATE VIEW TESTVIEW AS
  SELECT a.ID, foo.somefield, a.some_other_id, a.GEOMETRY 
  FROM FOO foo, AREA a
  WHERE foo.ID = a.SOME_OTHER_ID;  

insert into USER_SDO_GEOM_METADATA (table_name, column_name, diminfo, srid)
values ('TESTVIEW ',
'GEOMETRY',
SDO_DIM_ARRAY
(
  SDO_DIM_ELEMENT('X',50199, 761274,0.5),
  SDO_DIM_ELEMENT('Y',6582464, 7799839,0.5)
),
3047); 

The layer ends up being viewable OK, but cannot be updated / inserted. When updates are attempted through wfs-t I get:

2013-02-26 19:00:33,636 ERROR [org.geoserver.ows] - org.geoserver.wfs.WFSTransactionException: {http://www.opengeospatial.net/cite}TESTVIEW is read-only

1 Answer 1

1

I solved this by adding a database trigger which modifies attempts to update the view's data (which is not possible since it is read-only) into updates to the source data. Like so:

CREATE OR REPLACE TRIGGER AREA_INSERT 
INSTEAD OF INSERT ON TESTVIEW
REFERENCING NEW AS n 
     FOR EACH ROW
BEGIN
  insert into area (id, some_other_id, geometry) values (:n.ID, :n.some_other_id, :n.geometry); 
END;
/

...and a similar trigger for updates.

I also seem to have disabled primary key constraint for this view

alter view TESTVIEW add constraint TESTVIEW_PK primary key (ID) disable;

but can't remember why this was important.

1
  • I just came across this note, about the exact same issue. I did not know that defining a primary key on a view was at all possible. Jul 12, 2018 at 12:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.