postgres nonversioned editing - sequence vs next_rowid

1209
0
09-09-2019 07:36 PM
RobertMueller2
New Contributor II

I have Postgres 10.6/PostGIS 2.2 set up with ArcGIS Desktop 10.6.1 and would like to allow feature class editing  (non-versioned) of an enterprise FGDB outside of ArcDesktop (PgAdmin, dare I say - other non-ESRI products), which requires auto-incrementing the objectid. 

One option is to sequence the objectid, something like this. This method works great - inserts add new objectid's exactly as expected - and is easy to apply within the database. The complete lack of info on this solution is why I am so cautious. There are some internet articles (See Well's blog below) using next_rowid but almost everything (what little there is) - is using ArcGIS Desktop 10.2 and ArcSDE.

CREATE SEQUENCE data.test_objectid_seq; /*creates new sequence*/

ALTER TABLE data.test

  ALTER COLUMN objectid set default nextval ('data.test_objectid_seq');

GRANT USAGE, SELECT ON data.test_objectid_seq TO data; /*grants usage*/

(Note – See additional information regarding use of OIDS and auto-incrementing columns from: https://www.postgresql.org/docs/current/sql-createtable.html)

The second is to utilize the stored procedure via next_rowid - which is apparently the "ESRI" suggested method. This however doesn't seem to be easy to implement..

Does anyone have thoughts or experience doing this?

Links I've found:

Editing nonversioned geodatabase data in PostgreSQL using SQL—Help | ArcGIS Desktop 

Next_RowID—Help | ArcGIS Desktop 

What is an ObjectID?—Help | ArcGIS Desktop 

Auto-Incrementing ObjectID Field Outside of ArcGIS- Thomas Colson‌ had an interesting comment around "messing with the ObjectID outside of ESRI" with a link to Christian Wells's Blog

Note - I've found that the records don't actually alternate. For a brand new feature class for the first record, the sequence 1 and the ESRI block value (sde.next_rowid) =1, which there are collisions in the database. In the attached - I've previously added 419 records and then deleted all records in the table, then started re-adding points with ArcGIS Pro adds on the left, and another software on the right. If you noticed the OIDs, the ESRI objectids continued where the table left off (now at 420), while the other software (using the sequence) re-started at 1 (up to 194). So I think unless you hack the sequence (starting at a very high number) or the ESRI internal OID (adding and deleting lots of records) to get a large enough "starting" number, neither of which is really ideal of suggested for production environments, this is probably not a viable method.

On to testing use of  "sde.next_rowid" with an insert trigger.

0 Kudos
0 Replies