Data type for auto-incrementing OBJECTID in Postgres?

JohnZastrow — Sat, 11 Dec 2021 21:16:28 GMT

Using 10.2 Arcmap/ArcGIS Server on Windows I've successfully published some map services from my Postgres/PostGIS data and now I want to enable editing the features through a JavaScript web app.

So, following the JS API examples I am enabling both the geometry service and the Feature Access service with all the Operations and Capabilities enabled.

In short I'm using the serial data type for the OBJECTID primary key (tried in upper with "OBJECTID" in the DDL too) which appears to be a shortcut for creating an integer, auto-incrementing key as follows:

CREATE TABLE test_schema.volmonwgs84e
(
  objectid serial NOT NULL,
  volmon24_i integer,
  site_id integer,
  waterbody character varying(30),
  geom geometry(MultiPoint,4326),
  CONSTRAINT volmonwgs84e_pkey PRIMARY KEY (objectid)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE test_schema.volmonwgs84e
  OWNER TO postgres;

-- Index: test_schema.volmonwgs84e_geom_gist

-- DROP INDEX test_schema.volmonwgs84e_geom_gist;

CREATE INDEX volmonwgs84e_geom_gist
  ON test_schema.volmonwgs84e
  USING gist
  (geom);

However, when I go to publish the service (from ArcMap) the Analyzer shows the following error:

"00166: Layer's ObjectID is not maintained by the database and the feature service is being published with Create capability enabled"

Right-clicking the error kindly offers to "Add an auto-incrementing ID" for me, to which it later responds with the following. AFAIK, I ALREADY have an auto-incrementing field anyhow.

"Executing: AddIncrementingIDField testing.test_schema.volmonwgs84e BOO
Start Time: Tue Dec 10 15:55:06 2013
ERROR 000152: You cannot add an incrementing ID field to a table registered with the geodatabase.
Failed to execute (AddIncrementingIDField).
Failed at Tue Dec 10 15:55:07 2013 (Elapsed Time: 0.70 seconds)

So, what data type and rules need to be on this auto-incrementing field, and what is AddIncrementingIDField trying to do for me?

Thanks!

UPDATE:

Thinking that my issue was the "new" PostGIS/Postgres approaches to things I've also tried creating a table with these older approach as follows:

1. manually setting the sequence on integer PK column instead of using the SERIAL type,
2. using "SELECT AddGeometryColumn" to create the geometry column, and therein using the old constraints approach with the final argument = false (http://postgis.refractions.net/docs/AddGeometryColumn.html)

CREATE SEQUENCE test_schema.mypoint_const_b_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE test_schema.mypoint_const_b_seq
  OWNER TO postgres;

CREATE TABLE test_schema.mypoint_const_b
(
  objectid integer NOT NULL DEFAULT nextval('test_schema.mypoint_const_b_seq'::regclass),
  volmon24_i integer,
  site_id integer,
  waterbody character varying(30),
  CONSTRAINT mypoint_const_b_pkey PRIMARY KEY (objectid)
)
WITH (
  OIDS=FALSE
);

SELECT AddGeometryColumn ('test_schema','mypoint_const_b','geom',4326,'MULTIPOINT',2, false);

ALTER TABLE test_schema.mypoint_const_b
  OWNER TO postgres;

CREATE INDEX mypoint_const_b_geom_gist
  ON test_schema.mypoint_const_b
  USING gist
  (geom);

But I still get a fatal error when trying to create a service with Feature Access "Create" capability enabled with the same message:

"00166: Layer's ObjectID is not maintained by the database and the feature service is being published with Create capability enabled"

I know that the database IS maintaining the PK because I can add features using QGIS just fine.

PS, buried in this excellent link (http://proceedings.esri.com/library/userconf/proc13/tech-workshops/tw_192.pdf) I see that Arc doesn't like fields with quotes (which is how you force CAPS on OBJECTID. Lowercase objectid appears to be just fine to ArcMap at least.

Re: Data type for auto-incrementing OBJECTID in Postgres?

JasonGreenlaw — Mon, 19 May 2014 19:40:08 GMT

I have run into similar roadblocks with my own workflow.

I assumed you were using a Query Layer in your map service, but I am confused by the two seemingly contradictory error messages:

00166: Layer's ObjectID is not maintained by the database and the feature service is being published with Create capability enabled

ERROR 000152: You cannot add an incrementing ID field to a table registered with the geodatabase

So it sounds like your table is registered with the geodatabase, but does not have a geodatabase-maintained OBJECTID field and instead maintains an auto-incrementing field, which is strange to me.

In my experience (using ArcPy), geodatabase registration (unfortunately) results in the auto-increment constraint being removed from the OBJECTID column, as ESRI wants you to instead call the sde.next_rowid(schema, table) function when inserting new records via SQL. In this case, you shouldn't have to worry about calling the AddIncrementingIDField function, as the OBJECTID field should suffice. But how were you able to register the table with the geodatabase without having your autoincrement constraint removed?

Were you able to find a workaround?

topic Data type for auto-incrementing OBJECTID in Postgres? in ArcGIS Enterprise Questions

Data type for auto-incrementing OBJECTID in Postgres?

Re: Data type for auto-incrementing OBJECTID in Postgres?