Error editing with feature service when using non-esri postgresql tables

06-10-2022 11:14 AM
Occasional Contributor III

We have been attempting to publish an editable feature service containing data from a non-Esri PostgreSQL database (i.e. not a geodatabase). I have found various documentation suggesting this is possible, including the following:

We are able to publish an editable feature service that uses data from PostgreSQL tables that meet the criteria (e.g. single geometry column and type, auto incrementing id field etc). However, when we try to add features to a layer in the resulting feature service, we are getting the following error in ArcGIS Pro:

ERROR: Unable to complete operation. Rowbuffer creation failed. The table does not have an auto-incrementing column.

And a similar error if we attempt to edit in a web map:
"addResults":["success":false,"error":"code":1060,"description":"Rowbuffer creation failed. The table does not have an auto-incrementing column.[testedits]"],"updateResults":[],"deleteResults":[]

The steps taken were as follows:
1) In ArcGIS Pro, connect to the postgresql database as the data owner user (i.e. the owner of the schema in which the new table will reside).

2) Right-click the database connection and create a new point feature class.

3) Right-click the resulting feature class and add permissions (SELECT/INSERT/UPDATE/DELETE) for the "edit" user that will be used for publishing.

4) In pgAdmin add permissions (ALL) on the sequence used that was created for the objectid field for the "edit" user (otherwise a different permissions error occurs).

5) In ArcGIS Pro - connect using "edit" user and add the feature class/table as a layer to a new map, setting the objectid as the unique id and setting the projection as required.

6) Publish the resulting map as a feature service with editing enabled to ArcGIS Server (registering a new connection with the server if required). Note we are not using Portal.

6) Once published successfully, add the resulting feature service to a new map in ArcGIS Pro (e.g. via Add data from path and using the feature layer url).

7) Start editing and attempt to create a new feature. (The above error occurs at this point)

We have also tried various other things, including:

  • Creating a new postgresql table with geometry column and then using the "Add Incrementing ID Field" geoprocessing tool to add an ID column to the table.
  • Using an existing table with an auto incrementing id.

All of these methods result in the same error message when attempting to add a new feature to the resulting feature service.

If I use pgAdmin to connect as the "edit" user and perform a SQL insert on the same table, the insert is successful and the objectid has been set correctly for the inserted record, which suggests the permissions for the "edit" user are correct.

ArcGIS-wise we are using the latest versions:

  • ArcGIS Pro 2.9.3 on Windows Server 2019.
  • ArcGIS Server 10.9.1 (with all current patches installed) on Windows Server 2019.

We have so far tried the following for postgresql:

  • PostgreSQL 11.15 with postgis 2.5.5 on Ubuntu 18.04.
  • PostgreSQL 13.7 with postgis 3.2.1 on Ubuntu 20.04.
  • Azure Database for PostgreSQL Flexible Server.

All exhibit the same issue.

I have also tried the same concept with a SQL Server 2019 database and this works as expected.

We are already working with Esri UK support on this, but would be interested to know if anyone else has managed to publish and use an editable feature service from non-esri/geodatabase tables in a postgres database successfully?

0 Kudos
0 Replies