"i" support tables out of sync with feature class tables

[ViktorSafar]

We have a Postgres backed geodatabase which among other things has a few non-spatial tables (statskogsmaavilt in the example below). 

These tables have been written to by using raw SQL statements - which, I suspect, created a problem.

I am now using the REST API to write into the non-spatial tables and am getting exceptions like 

Unable to perform applyEdits operation. Error: Underlying DBMS error [ERROR:  duplicate key value violates unique constraint "r129_sde_rowid_uk"
DETAIL:  Key (rid)=(471) already exists. (X.Y.statskogsmaavilt)::SQLSTATE=23505] [StatskogSmaavilt].

I suspect the "i" support table's base_id / last_id is out of sync with what is actually in the given table.

Is there a way to correct this?

I have observed how the data in the "i" table change when I ran applyEdits on a table that is not corrupted but I could not find a pattern (sometimes it would create a new object with objectid of base_id and increment the  base_id in the "i"  table but other times it would increment by ~40 or use a value that is lower than the current value of  base_id )

I also inspected the relevant  sde.iXY_get_ids function and concluded it should just simply increment but that was contradictory to what I observed above.

[HaseebAnwar]

Facing the same problem. @ViktorSafar did you find a solution to this?

[ViktorSafar · Accepted solution]

I did not really get any guidance from Esri (I raised it as a support case they just bumped it around to 10 different people before asking for database backup - which we could not provide due to reasons).

I ended up calling the next_rowid function in a loop to get the iX table's last_id to be the same as the max(id) from the affected table.

select * from sde.next_rowid('scheme', 'tablename')

[HaseebAnwar]

One workaround is to adjust the id sequence in the index tables manually. Find the ID table by looking at the index name, which in the above case is "r129_sde_rowid_uk". Looking at the name, take 129 and query the table i129. This table i29 holds the last and next objectid for the index named "r129_sde_rowid_uk".

Once you find the index table, you must update the new index to your desired number. After updating, the feature class will use your provided ID next time.

Changing ArcGIS maintained tables directly is not recommended so please create a backup before making any change and please proceed with CAUTION.