topic Re: sdetable -o create_mv_view not working in Data Management Questions

sdetable -o create_mv_view not working

ShaunMcCloud — Sun, 12 Dec 2021 02:22:25 GMT

I have a PostgreSQL + PostGIS ArcSDE (PostgreSQL 8.4 & ArcSDE 10.0) that I need to create mv_views in. I'm able to create 16 of the 18 I need just fine. For the first feature class I use the following command

sdetable -o create_mv_view -T address_location_mv -t address_location -i sde:postgresql:192.168.7.24 -D staging -u sde -p ******* -s 192.168.7.24

and I get the following as CLI output

ArcSDE 10.0  for PostgreSQL Build 1343 Thu Feb 17 11:45:42  2011
Attribute        Administration Utility
-----------------------------------------------------
        Error: Invalid SQL syntax (-42).
        Error: Unable to create MV view address_location_mv for table address_location.

For the second table I use the following command

sdetable -o create_mv_view -T fire_hydrants_mv -t fire_hydrants -i sde:postgresql:192.168.7.24 -D staging -u sde -p ******* -s 192.168.7.24

and I get the following as CLI output

ArcSDE 10.0  for PostgreSQL Build 1343 Thu Feb 17 11:45:42  2011
Attribute        Administration Utility
-----------------------------------------------------
        Error: Invalid SQL syntax (-42).
        Error: Unable to create MV view fire_hydrants_mv for table fire_hydrants.

My C:\Program Files (x86)\ArcGIS\ArcSDE\pgexe\etc\sdedc_PostgreSQL.log file contains the following for the two commands above

SHOW SEARCH_PATH = '"$user", public, sde' 
Instance initialized . . .
[Thu Mar 15 10:49:07 2012] [56] [PROV-MASTER] ev.SQLstate = 42601
SDE DBMS Error: -42  ERROR:  syntax error at or near "$1"
LINE 1: ...de_level, parcel_uid, segment_uid, c1_exception,  $1 , globa...
                                                             ^
QUERY:  INSERT INTO staging.sde.a20(objectid, source, user_id, date_modified, versionname, site_unique_id, add_number, add_pre, add_name, add_type, add_suf, add_fullname, add_building, add_unit, add_floor, add_room, add_seat, add_loc_notes, supp_info, lastname, firstname, areacode, phone, p_sub, p_block, p_lot, mail_add, mail_city, mail_state, mail_zip, add_hist_a, rr_add, alias_add, address_class, stru_type, stru_mat1, stru_mat2, stru_color1, stru_color2, stru_height, stru_fence, stru_notes1, stru_notes2, date_create, zip5, exchange, telco, msag_comm, comm_name, postal_comm, esn, mapgrid, prec, vote, collect_method, geocode_level, parcel_uid, segment_uid, c1_exception,  $1 , globalid, sde_state_id, shape) VALUES ((Select (Base_ID) From staging.sde.i20),  $2 ,  $3 ,  $4 ,  $5 ,  $6 ,  $7 ,  $8 ,  $9 ,  $10 ,  $11 ,  $12 ,  $13 ,  $14 ,  $15 ,  $16 ,  $17 ,  $18 ,  $19 ,  $20 ,  $21 ,  $22 ,  $23 ,  $24 ,  $25 ,  $26 ,  $27 ,  $28 ,  $29 ,  $30 ,  $31 ,  $32 ,  $33 ,  $34 ,  $35 ,  $36 ,  $37 ,  $38 ,  $39 ,  $40 ,  $41 ,  $42 ,  $43 ,  $44 ,  $45 ,  $46 ,  $47 ,  $48 ,  $49 ,  $50 ,  $51 ,  $52 ,  $53 ,  $54 ,  $55 ,  $56 ,  $57 ,  $58 ,  $59 ,  $60 ,  $61 , staging.sde.SDE_get_current_state(),  $62 )
CONTEXT:  SQL statement in PL/PgSQL function "v20_insert" near line 8

[Thu Mar 15 10:49:07 2012] [56] [PROV-MASTER] Unable to create IMV insert rule function for view address_location_mv for table sde.address_location[Thu Mar 15 10:49:08 2012] [56] [PROV-MASTER] In DB_instance_close...
[Thu Mar 15 10:49:08 2012] [56] [PROV-MASTER] 
Describe Cache...
[Thu Mar 15 10:49:08 2012] [56] [PROV-MASTER] 
 Cache Size............................. 200
[Thu Mar 15 10:49:08 2012] [56] [PROV-MASTER] 
 Number of Table Objects Cached          (0)
[Thu Mar 15 10:49:08 2012] [56] [PROV-MASTER] 
 Number of Selects Cached                (0)
[Thu Mar 15 10:49:08 2012] [56] [PROV-MASTER] 
 Number of Joins Cached (DBMS-described) (0)
[Thu Mar 15 10:49:08 2012] [56] [PROV-MASTER] 
    Cached     Cached
     Hits    Statements
          ---------------------
[Thu Mar 15 10:49:08 2012] [56] [PROV-MASTER] 
[Thu Mar 15 10:49:08 2012] [56] [PROV-MASTER]     SELECT's DBMS Described: [0][0]

SHOW SEARCH_PATH = '"$user", public, sde' 
Instance initialized . . .
[Thu Mar 15 10:50:40 2012] [57] [PROV-MASTER] ev.SQLstate = 42601
SDE DBMS Error: -42  ERROR:  syntax error at or near "$1"
LINE 1: ...hydrant_unique_id, notes, psi, maintenance_date,  $1 , globa...
                                                             ^
QUERY:  INSERT INTO staging.sde.a11(objectid, source, user_id, date_modified, versionname, hydrant_unique_id, notes, psi, maintenance_date,  $1 , globalid, sde_state_id, shape) VALUES ((Select (Base_ID) From staging.sde.i11),  $2 ,  $3 ,  $4 ,  $5 ,  $6 ,  $7 ,  $8 ,  $9 ,  $10 ,  $11 , staging.sde.SDE_get_current_state(),  $12 )
CONTEXT:  SQL statement in PL/PgSQL function "v11_insert" near line 8

[Thu Mar 15 10:50:40 2012] [57] [PROV-MASTER] Unable to create IMV insert rule function for view fire_hydrants_mv for table sde.fire_hydrants[Thu Mar 15 10:50:40 2012] [57] [PROV-MASTER] In DB_instance_close...
[Thu Mar 15 10:50:40 2012] [57] [PROV-MASTER] 
Describe Cache...
[Thu Mar 15 10:50:40 2012] [57] [PROV-MASTER] 
 Cache Size............................. 200
[Thu Mar 15 10:50:40 2012] [57] [PROV-MASTER] 
 Number of Table Objects Cached          (0)
[Thu Mar 15 10:50:40 2012] [57] [PROV-MASTER] 
 Number of Selects Cached                (0)
[Thu Mar 15 10:50:40 2012] [57] [PROV-MASTER] 
 Number of Joins Cached (DBMS-described) (0)
[Thu Mar 15 10:50:40 2012] [57] [PROV-MASTER] 
    Cached     Cached
     Hits    Statements
          ---------------------
[Thu Mar 15 10:50:40 2012] [57] [PROV-MASTER] 
[Thu Mar 15 10:50:40 2012] [57] [PROV-MASTER]     SELECT's DBMS Described: [0][0]

Does anyone have some suggestions on what I can try to get these views created?

Re: sdetable -o create_mv_view not working

JakeSkinner — Thu, 15 Mar 2012 16:37:36 GMT

Looks like this may be failing due to a field name. Can you provide a list of one of the feature class' field names and type?

Re: sdetable -o create_mv_view not working

ShaunMcCloud — Sun, 12 Dec 2021 02:22:28 GMT

address_location is as follows

  objectid integer NOT NULL,
  source character varying(35),
  user_id character varying(35),
  date_modified timestamp without time zone,
  versionname character varying(74),
  site_unique_id character varying(74),
  add_number integer,
  add_pre character varying(2),
  add_name character varying(35),
  add_type character varying(4),
  add_suf character varying(2),
  add_fullname character varying(75),
  add_building character varying(35),
  add_unit character varying(12),
  add_floor integer,
  add_room character varying(35),
  add_seat character varying(35),
  add_loc_notes character varying(74),
  supp_info character varying(255),
  lastname character varying(52),
  firstname character varying(52),
  areacode integer,
  phone character varying(15),
  p_sub character varying(255),
  p_block character varying(255),
  p_lot character varying(255),
  mail_add character varying(75),
  mail_city character varying(30),
  mail_state character varying(2),
  mail_zip integer,
  add_hist_a character varying(75),
  rr_add character varying(75),
  alias_add character varying(75),
  address_class integer,
  stru_type character varying(35),
  stru_mat1 character varying(35),
  stru_mat2 character varying(35),
  stru_color1 character varying(35),
  stru_color2 character varying(35),
  stru_height smallint,
  stru_fence character varying(42),
  stru_notes1 character varying(255),
  stru_notes2 character varying(255),
  date_create timestamp without time zone,
  zip5 integer,
  exchange character varying(35),
  telco character varying(15),
  msag_comm character varying(35),
  comm_name character varying(35),
  postal_comm character varying(35),
  esn smallint,
  mapgrid character varying(5),
  prec character varying(5),
  vote character varying(5),
  collect_method character varying(35),
  geocode_level character varying(35),
  parcel_uid character varying(38),
  segment_uid character varying(38) DEFAULT '{00000000-0000-0000-0000-000000000000}'::character varying,
  c1_exception integer,
  status character varying(35),
  globalid character varying(38) NOT NULL DEFAULT '{00000000-0000-0000-0000-000000000000}'::character varying,
  shape geometry

fire_hydrants is as follows

objectid integer NOT NULL,
  source character varying(35),
  user_id character varying(35),
  date_modified timestamp without time zone,
  versionname character varying(74),
  hydrant_unique_id character varying(74),
  notes character varying(255),
  psi integer,
  maintenance_date timestamp without time zone,
  status character varying(35),
  globalid character varying(38) NOT NULL DEFAULT '{00000000-0000-0000-0000-000000000000}'::character varying,
  shape geometry

Neither is showing any reserved names being used as a field name.

Re: sdetable -o create_mv_view not working

JakeSkinner — Thu, 15 Mar 2012 17:16:15 GMT

Looks like the problem lies with the fields of type 'timestamp'. I ran a quick test and added a field called 'Date_Modified' of type 'timestamp' to the feature class. I registered the feature class as versioned and then attempted to create the multiversion view. I received a similar error within my log file:

Unable to create IMV insert trigger for view airports_mv for table VECTOR.AIRPORTS

I then deleted the timestamp field and was able to successfully create the multiversion view.

You may want to consider logging a tech support incident regarding this matter to see if there is a workaround.

Re: sdetable -o create_mv_view not working

ShaunMcCloud — Sun, 12 Dec 2021 02:22:30 GMT

Looks like the problem lies with the fields of type 'timestamp'. I ran a quick test and added a field called 'Date_Modified' of type 'timestamp' to the feature class. I registered the feature class as versioned and then attempted to create the multiversion view. I received a similar error within my log file:

Unable to create IMV insert trigger for view airports_mv for table VECTOR.AIRPORTS

I then deleted the timestamp field and was able to successfully create the multiversion view.

You may want to consider logging a tech support incident regarding this matter to see if there is a workaround.

I will do that. Of course, I just ran into another issue. Trying to select the data from one of the views I have created only returns the original records and nothing I've added or deleted. Only if I copy & paste the view creation query using pgAdmin III and modify it to not include the last WHERE NOT EXISTS clause do I get the new records. Any ideas on that?

Basically

SELECT b.objectid, b.source, b.user_id, b.date_modified, b.versionname, b.cnty_unique_id, b.county_name, b.county_fips, b.globalid, b.objectid - b.objectid AS sde_state_id, b.shape
 FROM county b
        WHERE NOT (EXISTS ( SELECT d.sde_deletes_row_id, d.sde_state_id
  FROM d16 d
   WHERE d.sde_state_id = 0 AND sde_in_current_lineage(d.deleted_at::integer::bigint) = true AND b.objectid = d.sde_deletes_row_id AND d.sde_state_id = 0))
UNION ALL 
 SELECT a.objectid, a.source, a.user_id, a.date_modified, a.versionname, a.cnty_unique_id, a.county_name, a.county_fips, a.globalid, a.sde_state_id, a.shape
  FROM a16 a
 WHERE NOT (EXISTS ( SELECT d.sde_deletes_row_id, d.sde_state_id
  FROM d16 d
   WHERE sde_in_current_lineage(d.deleted_at::integer::bigint) = true AND a.objectid = d.sde_deletes_row_id AND a.sde_state_id = d.sde_state_id AND sde_in_current_lineage(a.sde_state_id::integer::bigint) = true)) AND sde_in_current_lineage(a.sde_state_id::integer::bigint) = true;

does not work but

SELECT b.objectid, b.source, b.user_id, b.date_modified, b.versionname, b.cnty_unique_id, b.county_name, b.county_fips, b.globalid, b.objectid - b.objectid AS sde_state_id, b.shape
 FROM county b
        WHERE NOT (EXISTS ( SELECT d.sde_deletes_row_id, d.sde_state_id
  FROM d16 d
   WHERE d.sde_state_id = 0 AND sde_in_current_lineage(d.deleted_at::integer::bigint) = true AND b.objectid = d.sde_deletes_row_id AND d.sde_state_id = 0))
UNION ALL 
 SELECT a.objectid, a.source, a.user_id, a.date_modified, a.versionname, a.cnty_unique_id, a.county_name, a.county_fips, a.globalid, a.sde_state_id, a.shape
  FROM a16 a;

does work.

Re: sdetable -o create_mv_view not working

JakeSkinner — Thu, 15 Mar 2012 17:28:57 GMT

You will need to set the version to query. For example, execute the following first:

SELECT sde.sde_set_current_version('VECTOR.Version1');

This will set the version to a versioned named 'Version1'. Then you will be able to query the edits that exist in the version. Here is some more info:

http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#/in_PostgreSQL/006z0000001q000000/

Re: sdetable -o create_mv_view not working

ShaunMcCloud — Thu, 15 Mar 2012 17:31:04 GMT

You will need to set the version to query. For example, execute the following first:
SELECT sde.sde_set_current_version('VECTOR.Version1');
This will set the version to a versioned named 'Version1'. Then you will be able to query the edits that exist in the version. Here is some more info:

http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#/in_PostgreSQL/006z0000001q000000/

That did it, and now I feel stupid.

Re: sdetable -o create_mv_view not working

tKasiaTuszynska — Mon, 19 Mar 2012 19:52:04 GMT

Shawn,
Per your original post;
I am able to reproduce your experience, on 10.0 sde for postgres, I am not able to create a mv view, with the table definitions given in your post.
The issue is not reproducable in 10.1.
It is up to you if you want to pursue this to be fixed for 10 sp5 or will you be migrating to 10.1?

Most Sincerely,
Kasia Tuszynska
ArcSDE Product Engineer

Re: sdetable -o create_mv_view not working

ShaunMcCloud — Mon, 19 Mar 2012 19:56:31 GMT

Shawn,
Per your original post;
I am able to reproduce your experience, on 10.0 sde for postgres, I am not able to create a mv view, with the table definitions given in your post.
The issue is not reproducable in 10.1.
It is up to you if you want to pursue this to be fixed for 10 sp5 or will you be migrating to 10.1?

Most Sincerely,
Kasia Tuszynska
ArcSDE Product Engineer

Kasia,
My understanding is that we will be migrating to 10.1 but I am not sure on the time frame. I believe it will be after we have released the product I was creating the views for. However, the views were just a means to an end for extracting the deltas out of a SDE. Using the views I was able to create, I figured out how to extract the views out using Npgsql & C# so I do not see the need to pursue it for 10.0 SP5 at this time.

Re: sdetable -o create_mv_view not working

tKasiaTuszynska — Mon, 19 Mar 2012 21:41:36 GMT

Shawn,
I am glad that we are not hindering you too much.
If this is fixed for sp5, you will see it listed as a known issue on the sp download page.
Thanks for testing,
Sincerely,
Kasia

Re: sdetable -o create_mv_view not working

ShaunMcCloud — Tue, 17 Apr 2012 15:38:03 GMT

Just a quick update on this. It also appears that STATUS as a field name causes an issue. Removing the STATUS field from address_location & fire_hydrants fixes the following "Error: Invalid SQL syntax (-42)."