br8kwall

Data type for auto-incrementing OBJECTID in Postgres?

Discussion created by br8kwall on Dec 10, 2013
Latest reply on May 19, 2014 by greenlaw
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.

Outcomes