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.
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
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.
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.