Hi Ken, 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 <k...@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. >
