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/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
