With that level of dinamism of application servers where there is no way to keep consistency among them , as you say concurrency must be turned into a single thread to make sure schema migration will not be locked up by application threads .
Have you though about constraining connections with a connection pooler to handle incoming concurrency ?
Cheers, A.A.
On Mon, Mar 24, 2014 at 10:59 AM, Ken Barber <ken@bob.sh> wrote:
> Do you really need to allow web server connections to the database during a > schema migration ? Why not locking them up either with pg_hba.cong or a > firewal rule or symply shut it off temporarily ?
So this would be ideal if we could control the situation 100%, to be clear our software is a shipped product (http://github.com/puppetlabs/puppetdb), not something in-house so we have to deal with cases that involve users not reading docs and doing crazy stuff and still trying to preserve integrity :-).
The more I think on the issue, the more it is around dealing with two potential scenarios:
* Someone has upgraded our software in two (or more) places and they race to migrate. * Someone has upgraded our software in only one place and other application servers are not aware of that and continue to attempt to work as before.
My current line of thought is around:
* Bumping our migration transaction isolation level to serializable. For two migrations that are racing, I'd prefer that the loser backs off, then realizes the migration has already happened and skips that step. * Perhaps locking tables across the board (or an a case-by-case, depending on how hard-core we need it). While this might seem unnecessary with serialization, it might fill some other gaps I have fears about. * Having the other (older & still running) application servers handle a bump in the schema version properly by either stopping processing and returning errors, or something else more clever. Without a constant check on each transaction though, this could still allow some failures through.
In most cases I'd prefer a fatal error rather than old application servers to still continue processing and failing. There is also a lot to be said about taking an app out of commission from a load balancer perspective to avoid continued failed reception of messages an old app server can't possibly process. ken.
