A WFS Insert Transaction, if performed following a WFS Delete Transaction does an insert but, the return value shows an incorrect ID for the inserted record. More specifically, this happens when the delete is of the record with the highest primary key.
Database table, ISS_FEATURES, has primary key FEATURE_ID which is populated by an insert trigger fetching from sequence ISS_FEATURES_FEATURE_ID_SEQ.
Table GT_PK_METADATA entry is:
TABLE_SCHEMA: APP_ISS
TABLE_NAME: ISS_FEATURES
PK_COLUMN: FEATURE_ID
PK_COLUMN_IDX: null
PK_POLICY: sequence
PK_SEQUENCE: ISS_FEATURES_FEATURE_ID_SEQ
GeoServer Data Source has
Expose primary keys checked
Primary key metadata table: GT_PK_METADATA
If the highest PK is 2348, and the next highest PK is 2344, then a delete POST of:
<Transaction xmlns="http://www.opengis.net/wfs" service="WFS" version="1.1.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.opengis.net/wfs http://schemas.opengis.net/wfs/1.1.0/wfs.xsd">
<Delete typeName="iss:ISS_FEATURES" xmlns:feature="iss">
<Filter xmlns="http://www.opengis.net/ogc"><FeatureId fid="ISS_FEATURES.2348”/></Filter>
</Delete>
</Transaction>
Followed by and insert POST of:
<Transaction xmlns="http://www.opengis.net/wfs" service="WFS" version="1.1.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.opengis.net/wfs http://schemas.opengis.net/wfs/1.1.0/wfs.xsd">
<Insert>
<ISS_FEATURES xmlns="iss">
<GEOMETRY><Point xmlns="http://www.opengis.net/gml" srsName="EPSG:3857"><pos srsDimension="2">-13856700.583603898 6269882.445366477</pos></Point></GEOMETRY>
<NAME>expecting ID of 2349</NAME>
</ISS_FEATURES>
</Insert>
</Transaction>
Produces this response:
<?xml version="1.0" encoding="UTF-8"?><wfs:TransactionResponse xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:wfs="http://www.opengis.net/wfs" xmlns:gml="http://www.opengis.net/gml" xmlns:ogc="http://www.opengis.net/ogc" xmlns:ows="http://www.opengis.net/ows" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="1.1.0" xsi:schemaLocation="http://www.opengis.net/wfs http://hixon.th.gov.bc.ca:8081/ogs-geoV06/schemas/wfs/1.1.0/wfs.xsd"><wfs:TransactionSummary>
<wfs:totalInserted>1</wfs:totalInserted>
<wfs:totalUpdated>0</wfs:totalUpdated><wfs:totalDeleted>0</wfs:totalDeleted></wfs:TransactionSummary><wfs:TransactionResults/><wfs:InsertResults>
<wfs:Feature>
<ogc:FeatureId fid="ISS_FEATURES.2345"/>
</wfs:Feature>
</wfs:InsertResults>
</wfs:TransactionResponse>
Examining the inserted record shows it has a FEATURE_ID = 2349.
My suspicion is that GeoServer is unable to view the NEXTVAL of the sequence in GT_PK_METADATA, and falling back on something like
SELECT MAX(FEATURE_ID) + 1 FROM ISS_FEATURES
to report the inserted PK.
Running on:
GeoServer 2.25.2
Tomcat 9.0.102
Open JDK 17.0.11_9
OS RHEL 9.5
Oracle 12c
WFS 1.1.0 requests return an incorrect ogc:FeatureId fid.
Repeating using WFS Version 2.0.0 Insert returns an incorrect fes:ResourceId rid.
Repeated using GT_PK_METADATA.POLICY of assigned and autogenerated.
Anyone else experienced this?
Any thoughts as to where to look next?
Many thanks in advance for clues or hints.
Peter