topic Postgresql Replication of Geodatabase error in Data Management Questions

Postgresql Replication of Geodatabase error

MatthewRantala — Fri, 02 May 2014 16:51:47 GMT

Hello,

We are using Postgresql (9.2) database replication to distribute the load between a master and a slave. For a variety of reasons, we thought replicating at the Postgresql level instead of using ArcGIS's Geodatabase replication (we have a hot stand-by that we're replicating using both streaming and log-shipping) would work better for us.

One of the limitations of using a postgresql replication in this way is that the slave is read-only, which we thought would be fine. We would make all the changes on the master and they would be pushed to the slave. And that is working great.

The problem is that users who connect to the slave often receive this error (ERROR: cannot execute nextval() in a read-only transaction::SQL state: 25006) while do a spatial selection, either by doing "Select by Location" or using the Select Tool.

[ATTACH=CONFIG]33564[/ATTACH]

The error is sensitive to how large of a geographic area the user is selecting--smaller areas work fine, larger areas receive the error.

I have found that if I access the same data via either a View or Query Layer (Select * from featureclass) , I do not receive the error at all so that is a possible work-around. However, both of these are 3-4 times slower than just accessing the feature class directly.

I've got a support request working it's way through "channels" but wondered if the collective wisdom here might have any suggestions. Thanks for any ideas!

Database servers: Windows 2008 r2, Postgresql 9.2
Clients: ArcGIS 10.2.1, Windows 7

Re: Postgresql Replication of Geodatabase error

VinceAngelo — Fri, 02 May 2014 17:05:03 GMT

You are almost certainly hitting an issue with the use of ArcSDE logfiles in a read-only database
(logfiles implement a selection set to "remember" which rows have been selected). I doubt
there's any way around this within the enterprise geodatabase data model.

- V

Re: Postgresql Replication of Geodatabase error

MatthewRantala — Mon, 05 May 2014 11:57:05 GMT

Thanks, Vince. It was surprising to me, though, that we don't experience the same problem when using either a View or Query Layer.

Re: Postgresql Replication of Geodatabase error

VinceAngelo — Mon, 05 May 2014 16:21:34 GMT

Query Layers are direct read from the database, and do not directly utilize geodatabase
functionality (like logfiles). The performance benefit from ArcSDE is partially from using
geodatabase functionality, like storing the envelope and not using the spatial-first query
when it wouldn't be efficient. Unfortunately, you can't have it both ways.

- V