Postgres database editing error in ArcMap 10.2.2

[Ramasamy Annamalai]

Hi all,

I have a polygon layer from postgres database connected in ArcMap. I am having error while doing "save edits".

I have setup trigger functions to audit the changes, delete & update works perfectly on existing elements. But when I add a new element and modify it then save edits then errors are showing up.

I loose the last edit from the layer, then I have to close and open the arcmap so that I can update the audit table.

Editing in QGIS works perfectly, Is anybody have same experience, if you have solution it would be great to share.

Versions:

ArcGIS is 10.2.2  , ST-links is 4.0.0

Postgres is 9.2 & PostGIS is 2.0

64 bit  windows.

Thanks all

Ram

[Chun]

Does your DB table has some [NotAllowNull] columns? If so, you  need to populate values into those fields (in layer) before save.

 

  

[Ramasamy Annamalai]

Hi Chun,

Thanks for your reply. I want to make sure which table you are talking about ( is it the actual table or the audit table)

Actual table has gid column as NotAllowNull value, which is the identity column for the table. ( this column has auto increment on in order to populate itself when there is new record added to the table.)

The audit table has all columns from the actual table ( gid is set to allow null values in this table) and time stamp , user_id and operation columns are set as not null columns.

As you suggested I populated the gid column for new records then I did "save edits" then stop editing. I did not get any error so far.

I continued my testing as follows,

When I start editing and modifying the newly added or an existing feature, it disappears on the screen and the save changes not updated in the actual table.

Then I reloaded the actual table from ST-link, then I can see the records in the attribute table for the feature that disappeared,  but not the geometry on the screen. Even "zoom to feature" did not show anything.

But one good thing happened which is, the changes I made on the actual table recorded in the audit table. I can see records in the table and geometries on the screen.

I am not sure what goes wrong here. If anyone have answer for this that would be great.

One thing I want to mention that, the gid column auto updated by the database within the actual table, no matter what the value I enter during the editing session, it takes the value from the database.

For this case I no need to populate the value.

I have added actual table and its audit table structure in case if you are questioned about the table structure.

--ACTUAL TABLE--

  CREATE TABLE corehole_clearing

(

  gid integer NOT NULL DEFAULT nextval('corehole_clearing_gid_seq'::regclass),

  atsid character varying(254) DEFAULT NULL::character varying,

  geom geometry,

  CONSTRAINT corehole_clearing_pkey PRIMARY KEY (gid),

  CONSTRAINT corehole_clearing_the_geom_check CHECK (srid(geom) = 4269),

  CONSTRAINT corehole_clearing_the_geom_check1 CHECK (geometrytype(geom) = 'MULTIPOLYGON'::text OR geom IS NULL),

  CONSTRAINT corehole_clearing_the_geom_check2 CHECK (ndims(geom) = 2)

)

WITH (

  OIDS=FALSE

);

CREATE TRIGGER corehole_clearing_history

  AFTER INSERT OR UPDATE OR DELETE

  ON corehole_clearing

  FOR EACH ROW

  EXECUTE PROCEDURE corehole_clearing_audit();

 

 --TRIGGER FUNCTION--

 CREATE OR REPLACE FUNCTION corehole_clearing_audit()

  RETURNS trigger AS

$BODY$

    BEGIN

        --

        -- Create a row in corehole_clearing_history to reflect the operation performed on corehole_clearing,

        -- make use of the special variable TG_OP to work out the operation.        --

        IF (TG_OP = 'DELETE') THEN

            INSERT INTO corehole_clearing_history SELECT 'DELETED',  now(), user, OLD.*;

            RETURN OLD;

        ELSIF (TG_OP = 'UPDATE') THEN

            INSERT INTO corehole_clearing_history SELECT 'UPDATED', now(), user, NEW.*;

            RETURN NEW;

        ELSIF (TG_OP = 'INSERT') THEN

            INSERT INTO corehole_clearing_history SELECT 'INSERTED', now(), user, NEW.*;

            RETURN NEW;

        END IF;

        RETURN NULL; -- result is ignored since this is an AFTER trigger

    END;

$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100;

--AUDIT TABLE--- 

 CREATE TABLE corehole_clearing_history 

(

  operation character(10) NOT NULL,

  stamp timestamp without time zone NOT NULL,

  userid text NOT NULL,

  gid integer,

  atsid character varying(254) DEFAULT NULL::character varying,

  geom geometry,

  CONSTRAINT corehole_clearing_the_geom_check CHECK (srid(geom) = 4269),

  CONSTRAINT corehole_clearing_the_geom_check1 CHECK (geometrytype(geom) = 'MULTIPOLYGON'::text OR geom IS NULL),

  CONSTRAINT corehole_clearing_the_geom_check2 CHECK (ndims(geom) = 2)

)

WITH (

  OIDS=FALSE

);