Greetings,
I am running ArcGIS Server 10 on Linux using PostgreSQL 9.0. Every time I try and update a feature service I get an error in ArcGIS Server that only says "A database error occurred.". I looked at the PostgreSQL log files and found the following log entry:
ERROR: duplicate key value violates unique constraint "sde_logfile_data_pk"
DETAIL: Key (logfile_data_id, sde_row_id)=(23432, 686) already exists.
STATEMENT: INSERT INTO sde.sde_logfile_data (logfile_data_id,sde_row_id) VALUES (23432, $1)
These 3 lines show up numerous times and I am not sure how to fix this. The feature service was running fine for a while, as there are over 1000 features currently in the database. So I know things were OK for a while.
Thanks,
Mark
It looks like something got hosed up with your SDE_LOGFILE_DATA table, which is one of the tables involved when you make a selection of 1,000 or more features using a geodatabase connection. While not necessarily common of an error, I can't say that I'm surprised because I've seen issues before with the cleanup of rows in that table by ArcSDE. In my mind, there is a short-term fix and a long-term fix to this issue. I know you're using Postgres, but I'm not as familiar with that RDBMS so I'm going to provide an Oracle-based solution. The short-term fix is to truncate both the SDE_LOGFILE_DATA and the SDE_LOGFILES tables and start over with your session: TRUNCATE TABLE SDE.SDE_LOGFILES;
TRUNCATE TABLE SDE.SDE_LOGFILE_DATA;
The long-term fix is to drop those tables altogether and re-create them as Global Temporary tables (or the equivalent in Postgres):DROP TABLE SDE.SDE_LOGFILE_DATA CASCADE CONSTRAINTS;
DROP TABLE SDE.SDE_LOGFILES CASCADE CONSTRAINTS;
CREATE GLOBAL TEMPORARY TABLE SDE.SDE_LOGFILE_DATA
(
logfile_data_id NUMBER(*,0) NOT NULL ENABLE,
sde_row_id NUMBER(*,0) NOT NULL ENABLE
)
ON COMMIT PRESERVE ROWS
/
CREATE INDEX SDE.SDE_LOGFILE_DATA_IDX1
ON SDE.SDE_LOGFILE_DATA(LOGFILE_DATA_ID,SDE_ROW_ID)
/
CREATE INDEX SDE.SDE_LOGFILE_DATA_IDX2
ON SDE.SDE_LOGFILE_DATA(SDE_ROW_ID)
/
GRANT DELETE ON SDE.SDE_LOGFILE_DATA TO SDE
/
CREATE GLOBAL TEMPORARY TABLE SDE.SDE_LOGFILES
(
logfile_name VARCHAR2(256) NOT NULL ENABLE,
logfile_id NUMBER(*,0) NOT NULL ENABLE,
logfile_data_id NUMBER(*,0) NOT NULL ENABLE,
registration_id NUMBER(*,0) NOT NULL ENABLE,
flags NUMBER(*,0) NOT NULL ENABLE,
session_tag NUMBER(*,0) NOT NULL ENABLE,
logfile_data_db VARCHAR2(32),
logfile_data_owner VARCHAR2(32),
logfile_data_table VARCHAR2(98),
column_name NVARCHAR2(32)
)
ON COMMIT PRESERVE ROWS
/
CREATE UNIQUE INDEX SDE.SDE_LOGFILES_PK
ON SDE.SDE_LOGFILES(LOGFILE_ID)
/
CREATE UNIQUE INDEX SDE.SDE_LOGFILS_UK
ON SDE.SDE_LOGFILES(LOGFILE_NAME)
/
CREATE UNIQUE INDEX SDE.SDE_LOGFILES_UK2
ON SDE.SDE_LOGFILES(LOGFILE_DATA_ID)
/
GRANT DELETE ON SDE.SDE_LOGFILES TO SDE
/
Oracle Global Temporary tables, by definition, will truncate themselves when the user disconnects its session. I believe there may be an equivalent type of table Postgres.