(sorry Greg for the duplicate -- lack of List Reply in Gmail is
driving me nuts...)
On Fri, Jul 10, 2009 at 4:17 PM, Greg Sabino Mullanewrote:
Similar, yes: using Bucardo to create two masters and a handful (~8) of slaves.
Rather than a middleware to route things around (which can be dangerous
as a transaction should probably not do writes to the master and select
from a slave due to the async nature of Slony and Bucardo), the application
is tasked with the job of knowing which to hit and when. Specifically, there
are two classes of database handles, "readwrite" and "readonly". The
application then chooses which one to use depending on the situation, oft
times switching back and forth as needed (with the general rule of "any
changes made via the readwrite handle should continue to use that handle,
even for reads, in the current session").
Or longer? In a web application it's possible that the "next" request
happens before the slave is synced.
In a web application with many application servers (and non-sticky sessions)
the next request could be handled by any process on any web server.
It would seem that
something would need to be in the session or request (url) to indicate
that a write just happened so that all reads (from that web-session)
need to go to the master for some amount of time to allow the slave to
sync. Doesn't sound too pretty.
The other danger in trying to be too transparent is that you quickly get
into advanced SQL parsing, and deciding if the SQL statement contains any
write access to the database - easy enough for IUD, but tricky when a SELECT
contains function calls, any of which may or may not do database writes.
True. That's perhaps a bit easier to control since we are the ones
creating the functions.
But I do see your point.