On 16 November 2015 at 11:01, Craig Ringer <cr...@2ndquadrant.com> wrote:
> On 16 November 2015 at 18:44, Simon Riggs <si...@2ndquadrant.com> wrote: > >> >> The pooler knows which statements are reads and writes >> > > I think that's an iffy assumption. > It's not an assumption, its a requirement. If it can't do this in some manner then you can't use a load balancing pooler. Randomly submitting things works as well, since it leads to a write error when you try to write data on a read only server, so you do then learn whether it is a read or a write. Once you know its a write, you submit to master. But you still need to be careful of other effects, so that isn't recommended. It's one we tend to make because otherwise read/write pooling won't work, > but in PostgreSQL there's really no way to know when calling a function. > > What does > > SELECT get_user_stats() > > do? The pooler has _no_ _idea_ unless manually configured with knowledge > about particular user defined functions. > > In the absence of such knowledge it can: > > - send the work to a replica and report the ERROR to the user if it fails > due to an attempted write; > - send the work to a replica, capture an ERROR due to attempted write, and > retry on the master; > - send everything it's not sure about to the master > > If a pooler had insight into the catalogs and if we had readonly / > readwrite attributes on functions, it could be smarter. > pgpool supports white/black function listing for exactly this reason. -- Simon Riggs http://www.2ndQuadrant.com/ <http://www.2ndquadrant.com/> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
