topic Error when using SQL DELETE on archive view in Geodatabase Questions

Error when using SQL DELETE on archive view

SiCasley — Tue, 03 Jan 2017 22:47:10 GMT

I get the following error when I try to delete from an archive enabled feature class view (DELETE FROM myschema.mytable_evw WHERE objectid = 3;):

ERROR: date/time field value out of range: "12.31.9999 23:59:59.999"

LINE 2: ... WHERE objectid = old.objectid AND gdb_to_date = '12.31.999...

HINT: Perhaps you need a different "datestyle" setting.

QUERY: UPDATE myschema.mytable SET gdb_to_date = current_timestamp(3) AT TIME ZONE 'UTC'

WHERE objectid = old.objectid AND gdb_to_date = '12.31.9999 23:59:59.999'

CONTEXT: PL/pgSQL function myschema.nvv_update_11() line 15 at SQL statement

********** Error **********

ERROR: date/time field value out of range: "12.31.9999 23:59:59.999"

SQL state: 22008

Hint: Perhaps you need a different "datestyle" setting.

Context: PL/pgSQL function myschema.nvv_update_11() line 15 at SQL statement

Updating and inserting records to the view works, but not deleting. Is this deliberate, and if so, why?

I'm using a 10.3.1 geodatabase in postgres 9.3.15 on linux.

Thanks

Simon

Re: Error when using SQL DELETE on archive view

ChrisSmith7 — Wed, 04 Jan 2017 15:50:19 GMT

Have you tried setting the datestyle for for your transaction/session?

Re: Error when using SQL DELETE on archive view

SiCasley — Wed, 04 Jan 2017 18:17:40 GMT

Thanks for the suggestion Chris, but it doesn't seem to make a difference as the datestyle that causes the problem - "12.31.9999 23:59:59.999" - is written in the trigger function created by SDE for an archive view. Seems odd that it should be written in this way when all other dates in the function are written as "9999-12-31 23:59:59".

Re: Error when using SQL DELETE on archive view

SiCasley — Thu, 05 Jan 2017 23:29:43 GMT

I take it back - the datestyle setting was the issue It needed to be set on the database (or in postgres.conf) to 'ISO MDY' before enabling archiving on the feature class. If you make the change after the trigger function has already been created, the SQL DELETE returns successfully but the WHERE gdb_to_date = "12.31.9999 23:59:59.999" clause still fails to delete the feature out of the base table. With the datestyle set to MDY before archiving, the trigger function changes slightly to use "12.31.9999 23:59:59" (without the .999) and the SQL DELETE works.