One-way replication and SQL updates in Enterprise Geodatabase

[aricraimundo]

Using ArcGIS Pro 3.0.3 and PostgreSQL 12.4.

I'm running into an issue with a one-way replication in my Enterprise Geodatabase. I have a main geodatabase that sends data to the replica, and the replication works fine when I make edits using ArcGIS Pro. However, I'm having trouble executing SQL statements to update data from the _evw view, and the changes are not synchronizing with the replica.

I've checked my permissions and syntax, and everything seems to be in order. My changes reflects on the main database but not on the replica.

The Synchronize Changes tool works well but my data is not syncronized.

I'm editing the Default version of the data just using a simple SQL using an SQL client (dBeaver).
UPDATE myfeatureclass_evw SET my_field = 'XXX' WHERE [condition];

I'm hoping that someone might be able to offer some guidance or advice on what might be causing this issue. Has anyone else encountered a similar problem? Are there any replication settings or configurations that I should check to ensure that my changes are being synchronized properly?

Thank you in advance.

[MarlonAmaya]

Hi @aricraimundo 

I want to confirm that I understand correctly,

Your edits to a version view on Parent  using SQL are not being sent to Child when you run the Sync tool?

As a validity check, are edits conducted in Pro (not version view using SQL) being synced correctly to Child?

Have you ran a Compress?

https://pro.arcgis.com/en/pro-app/latest/tool-reference/data-management/compress.htm

Marlon

[aricraimundo]

Hi @MarlonAmaya 

Yes, you are correct. The edits conducted in Pro are synced and with SQL aren't.

I've run a Compress now as you suggested but the result was the same.

Below are some screenshots from ArcGIS Pro and the query I'm running.

Thanks for you prompt response.

[MarlonAmaya]

Hi @aricraimundo 

To confirm that there is nothing potentially wrong with this feature class, can you edit this exact feature in Pro and run sync again and let me know if the changes goes to Child.

Is your compress going to a state_end_count of 1? Are you performing the Compress with no other users connected?

Marlon

[aricraimundo]

Hi @MarlonAmaya 

I ran the sync again using Pro and it worked (see image below).

I'm doing some tests in my machine. No other users involved.

I don't know what you mean about state_end_count. I'm sending queries after Compress+Sync.

Thanks again

[MarlonAmaya]

Hi @aricraimundo ,

Disregard the state_end_count question. 

Please make an edit to the version view using SQL once again.

Afterwards, run the Export Data Changes tool. The output can be to a FGDB.

https://pro.arcgis.com/en/pro-app/latest/tool-reference/data-management/export-data-change-message.h...

Do we see the changes extracted to the FGDB?

Marlon

[aricraimundo]

Hi @MarlonAmaya 

With SQL the Export Data Changes tool doesn't show any changes. With Pro it does.

Do you know if the update through SQL in the view _evw is not allowed? I believe it was allowed in old versions (see link below).

https://desktop.arcgis.com/en/arcmap/latest/manage-data/using-sql-with-gdbs/edit-versioned-data-usin...

[MarlonAmaya · Accepted solution]

Hi @aricraimundo ,

It appears we are seeing the limitation mentioned on the BUG below. 

https://support.esri.com/en/bugs/nimbus/TklNMTAzMTA2

However, I was able to get it to work by creating a replica from an edit version and respectively editing the version view in the referenced edit version.

Marlon

[aricraimundo]

Hi @MarlonAmaya

I find it strange that there is a limitation in updating a simple text column. It seems like there should be no issue with this type of update.

In my actual scenario (although this is just a test), I will need to update other types of columns as well, including simple types like text, double, long, etc.

I will reach out to Esri Support for further assistance and guidance.

Thank you for your help!

[MarlonAmaya]

Hi @aricraimundo ,

You are welcome. The limitation is not the type of update, rather taking the change from the version view in default and syncing the changes to Child.

Yes, please reach out to Support if you need further assistance.

Marlon