Postgresql Replication of Geodatabase error

2045
3
05-02-2014 09:51 AM
Highlighted
New Contributor II
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
Reply
0 Kudos
3 Replies
Highlighted
Esri Esteemed Contributor
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
Reply
0 Kudos
Highlighted
New Contributor II
Thanks, Vince.  It was surprising to me, though, that we don't experience the same problem when using either a View or Query Layer.
Reply
0 Kudos
Highlighted
Esri Esteemed Contributor
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
Reply
0 Kudos